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

On Mon, Aug 27, 2012 at 11:24 PM, Liron Shiri <lirons(at)checkpoint(dot)com> wrote:
> 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

Very interesting. That is more or less the same concept, but it might
eliminate some variables. What's your workload on the bloaty toast
table? Mine is per the bug report, which is repeated concatenation of
strings.

> 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.

My database has taken many days (over a week) to stabilize. I was
about to write that it never stops growing (we'd eventually have to
VACUUM FULL or do a column rotation), but that is not true. This
graph is a bit spotty for unrelated reasons, but here's something like
what I'm seeing:

http://i.imgur.com/tbj1n.png

The standby promotion sticks out quite a bit. I wonder if the
original huge size is not the result of a huge delete (which I
surmised) but rather another standby promotion. We tend to do that a
lot here.

> 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?

One weakness of Postgres is can't really debloat online or
incrementally yet, but luckily your table is quite small: you can use
"CLUSTER" to lock and re-write the table, which will then be small.
Do not use VACUUM FULL on this old release, but for future reference,
VACUUM FULL has been made more like CLUSTER in newer releases anyway,
and one can use that in the future. Both of these do table rewrites
of the live data

--
fdr