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

On Wed, Mar 14, 2012 at 8:24 PM, Aleksey Tsalolikhin
<atsaloli(dot)tech(at)gmail(dot)com> wrote:
> On Tue, Mar 13, 2012 at 5:14 AM, Vick Khera <vivek(at)khera(dot)org> wrote:
>> I'll bet what happened was postgres re-wrote your table for you,
>> effectively doing a compaction.  You can get similar effect by doing
>> an alter table and "changing" an INTEGER field to be INTEGER.
>> Postgres does not optimize that do a no-op, so you get the re-writing
>> effect.
>
> How does table rewriting work?  Does it happen a row at a time or all at once?

When you do something like alter type or update without a where
clause, it will have to make a new copy of every old copy in the
table.

> In other words, how much free disk space is needed on an 800 TB filesystem
> to rewrite a 550 TB table?  (Have I got enough space?)

If you update the whole table at once (a la alter table alter column
or update with no where) then it has to have the space for all the
real data to fit. The size of the file system isn't important as much
as how much free space is left. IFF it's 800TB with exactly 550TB
used, then you have 250TB free. 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. This requires proper vacuuming and on
older versions free space map settings. With a really large table,
older versions of pgsql (<=8.3) tended to blow out their free space
map if you didn't crank them up. Newer versions just need proper
regular vacuuming.