Re: Investigating the reason for a very big TOAST table size

There were no "hot standby" configuration, but the DB has start grow fast after restoring from a base backup as described in http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-BASE-BACKUP

The DB has been growing for a while, and now it seems to become stable after adjusting the autovacuum cost parameters to be more aggressive.

The DB version is 8.3.7.

Do you think it might be the same issue?
What can we do in order to decrease DB size?

-----Original Message-----
From: Daniel Farina [mailto:daniel(at)heroku(dot)com]
Sent: Monday, August 27, 2012 7:42 PM
To: Liron Shiri
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Investigating the reason for a very big TOAST table size

On Sun, Aug 26, 2012 at 5:46 AM, Liron Shiri <lirons(at)checkpoint(dot)com> wrote:
> 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?

Is the size of the database continuing to grow over time, or is it stable?

Have you done a hot-standby promotion on this database, perchance? I have an open bug report on an unusual situation that began after that:
http://archives.postgresql.org/pgsql-bugs/2012-08/msg00108.php

--
fdr

Scanned by Check Point Total Security Gateway.