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

On Wed, Mar 14, 2012 at 9:06 PM, Aleksey Tsalolikhin
<atsaloli(dot)tech(at)gmail(dot)com> wrote:
>
> On Wed, Mar 14, 2012 at 7:38 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>>
>> The good news is that if the table is
>> bloated, it should be able to just write to the free space in the
>> table that's already there.
>
> Thank you, I got it.  The table is not bloated, as per
> check_postgres.pl --action=bloat

Are you sure you're checking the toast table that goes with whatever
parent table?

Easy way to tell. du -s /var/lib/data/base dir, then update a few
thousand rows, roll it back, and run du -s again. Compare. If the du
numbers stay the same then you're updating pre-allocated space and
should be ok. If there's a delta, compute it per tuple updated,
multiply by tuples and that's how much you'll need.

If the du -s numbers don't change or only a little then feel free to
either run a single update while running

watch "df -h /var/lib/where/my/data/dir/lives"

and being ready to hit CTRL-C if you see if running your machine out of memory.