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

maybe CLUSTER?

filip@dev=> create table foobar (id serial primary key, load text);
CREATE TABLE
filip@dev=> insert into foobar(load)select md5(random()::text) from generate_series(1,100000);
INSERT 0 100000
filip@dev=> delete from foobar where id%4<>3; -- delete 75% of the table
DELETE 75000
filip@dev=> select pg_relation_size('foobar');
 pg_relation_size
------------------
          6832128
(1 row)

filip@dev=> CLUSTER foobar using foobar_pkey;
CLUSTER
filip@dev=> select pg_relation_size('foobar');
 pg_relation_size
------------------
          1712128
(1 row)

Of course events destined to this table will be queued by Slony while the table is locked.


Filip