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
>
>
>  My biggest table measures 154 GB on the origin, and 533 GB on
>  the slave.
>
>  Why is my slave bigger than my master?  How can I compact it, please?

On Wed, Mar 7, 2012 at 11:54 PM, Stuart Bishop
<stuart(at)stuartbishop(dot)net> wrote back:
>
> 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.

Dear Stuart,

We do not run any transactions on the slave besides we pg_dump the
entire database every 3 hours. I don't have enough disk space to CLUSTER
the table; I ran VACUUM FULL yesterday, and I just fired up a REINDEX
TABLE.

I'd love to get some insight into how much logical data I have versus how
much physical space it is taking up. Is there some admin tool or command
or query that will report that? For each table (and index), I'd like
to know how
much data is in that object (logical data size) and how much space it is taking
up on disk (physical data size).

Yours,
Aleksey