Re: A 154 GB table swelled to 527 GB on the Slony slave. How to compact it?

On Wed, Mar 7, 2012 at 10:05 AM, Aleksey Tsalolikhin
<atsaloli(dot)tech(at)gmail(dot)com> wrote:
>  We're replicating a PostgreSQL 8.4.x database using Slony1-1.2.x
>
>  The origin database "data/base" directory is 197 GB in size.
>
>  The slave database "data/base" directory is 562 GB in size and is
>  over 75% filesystem utilization which has set off the "disk free" siren.
>
>  My biggest table* measures 154 GB on the origin, and 533 GB on
>  the slave.  (*As reported by
>
>  SELECT relname as "Table", pg_size_pretty(pg_total_relation_size(relid))
>   As "Size" from pg_catalog.pg_statio_user_tables
>   ORDER BY pg_total_relation_size(relid) DESC;
>  )
>
>  I took a peek at this table on the slave using pgadmin3. The table
>  has auto-vacuum enabled, and TOAST autovacuum enabled.
>
>  There are 8.6 million live tuples, and 1.5 million dead tuples.
>
>  Last autovacuum was over a month ago.
>
>  Last autoanalyze was 3 hours ago.
>
>  Table size is 4 Gigs, and TOAST table size is 527 Gigs.
>  Indexes size is 3 Gigs.
>
>  Autovacuum threshold is 20%, and the table is just under that threshold.
>
>  I ran vacuum analyze verbose.  But the filesystem is still at 76%
> utilization.
>  In fact, now, the "data/base" directory has grown to 565 GB.
>
>  Why is my slave bigger than my master?  How can I compact it, please?

Do you have a long running transaction on the slave? vacuum will not
reuse space that was freed after the longest running transaction.

You need to use the CLUSTER command to compact it, or VACUUM FULL
followed by a REINDEX if you don't have enough disk space to run
CLUSTER. And neither of these will do anything if the space is still
live because some old transaction might still need to access the old
tuples.

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/