Investigating the reason for a very big TOAST table size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



 

 

From: Liron Shiri
Sent: Sunday, August 26, 2012 3:47 PM
To: 'pgsql-performance@xxxxxxxxxxxxxx'
Subject: Investigating the reason for a very big TOAST table size
Importance: High

 

Hi,

 

We have a table which its TOAST table size is 66 GB, and we believe should be smaller.

The table size is 472 kb. And the table has 4 columns that only one of them should be toasted.

 

The table has only 8 dead tuples, so apparently this is not the problem.

 

This table contains a column with bytea type data (kept as TOAST). We tried to check what is the size of the toasted data in each row by using the following query (the data_blob is the bytea column):

 

SELECT nid, octet_length(data_blob) FROM my_table ORDER BY octet_length(data_blob) DESC;

 

This result contain 1782 rows. The sizes I get from each row are between 35428 to 42084.

 

1782 * 38000 = 67716000 byte = 64.579 MB .

 

What can be the reason for a table size of 66 GB? What else should I check?

 

Thanks in advance,

Liron


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux