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

On 03/07/2012 06:27 PM, Aleksey Tsalolikhin wrote:

>>   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 ran VACUUM FULL on this table, but it is still over 500 GB in size.
> And growing...
> I'm up to 77% utilization on the filesystem.
>
> "check_postgres --action=bloat" now returns OK.  So it's not bloat.
> What else could it be?

Try disabling replication on that table and clustering the table and
then re-enabling replication. I would have to double check but I think
check_postgres --action=bloat only checks for dead space, not usable
space, so you could actually still have bloat, just bloat that is usable.

Alternatively you could disable replication on that table, truncate the
table, and then re-enable replication for that table. A concern would be
is that it is a large table regardless, which means you are going to
hold open a transaction to refill it.


JD


>
> Best,
> Aleksey
>


--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579