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

On Thu, Mar 15, 2012 at 6:43 AM, Aleksey Tsalolikhin <
atsaloli(dot)tech(at)gmail(dot)com> wrote:

>
> Is there any way to consolidate the pages on the slave without taking
> replication offline?
>

maybe CLUSTER?

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

filip(at)dev=> CLUSTER foobar using foobar_pkey;
CLUSTER
filip(at)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