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

On Fri, Mar 16, 2012 at 2:03 PM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:
>
>
> I *think* you can get away with only sufficient free space to store the
> *new* table and indexes

Yeah; I don't have that much free space. Just 30 GB short. :(

> Depending on your schema and which tables are using space, you might be able
> to start clustering the smaller tables and progressively free additional
> space that can be used when clustering the larger tables.

Clever idea, thank you. Unfortunately the usage is 95%+ in this one big table.

>> Too bad there is no way to compact the rows/pages within the table
>> without using an
>> temporary table.  That would be the silver bullet for my particular
>> predicament.
>
> Isn't that "VACUUM FULL"? Much slower than CLUSTER and can cause index bloat
> (solvable by subsequent reindexing) but does not create a duplicate of the
> table.

I *did* run "VACUUM FULL" on the table, but I still have holes in my pages.
(i.e. examining a page using "select ctid" shows me row 2, 8, 15, 20, etc.
not 1 2 3 4 ...)

Is this a problem with "VACUUM FULL" or with my understanding?

Thank you, Scott, for the suggestion to move the big table to a separate
Slony replication set; and to Alban for moving it to a different TABLESPACE.
That is especially interesting, and I think exactly what I was looking for
(rewrite the table using an external storage device). Unfortunately I am out
of time to resolve this "live"; we'll take things down tomorrow night
(I only get
Saturday nights for maintenance, and not too often, either) and just re-sync.

This has been extremely educational; thank you!

And I still don't know why I have holes in my pages....... should I have holes
in my pages?? why only on the slave and not on the master? (the slave has
4x the number of pages compared to the master, and there's less rows per
page on the slave.)

Yours very truly,
Aleksey