Re: Best practice when reindexing in production

On Wed, May 29, 2013 at 6:47 PM, Alan Hodgson <ahodgson(at)simkin(dot)ca> wrote:
> On Wednesday, May 29, 2013 06:25:21 PM Daniele Varrazzo wrote:
>> My solution has been to become pg_repack maintainer. YMMV. Just don't
>> expect vacuum to reduce the indexes size: it doesn't.
>
> It's not supposed to. It is supposed to keep them from indefinitely growing,
> though, which it does reasonably well at.

My experience is different. I've repeated this test often. This is PG 9.1:

piro=# create table test (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
serial column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
piro=# insert into test (id) select generate_series(1,10000000);
INSERT 0 10000000

The table size is:

piro=# select pg_size_pretty(pg_relation_size('test'::regclass));
pg_size_pretty
----------------
306 MB
(1 row)

...and the index size is:

piro=# select pg_size_pretty(pg_relation_size('test_pkey'::regclass));
pg_size_pretty
----------------
171 MB
(1 row)

piro=# delete from test where id <= 9900000;
DELETE 9900000

piro=# select pg_size_pretty(pg_relation_size('test'::regclass)),
pg_size_pretty(pg_relation_size('test_pkey'::regclass));
pg_size_pretty | pg_size_pretty
----------------+----------------
306 MB | 171 MB
(1 row)

My statement is that vacuum doesn't reclaim any space. Maybe sometimes
in the tables, but never in the index, in my experience.

piro=# vacuum test;
VACUUM
piro=# select pg_size_pretty(pg_relation_size('test'::regclass)),
pg_size_pretty(pg_relation_size('test_pkey'::regclass));
pg_size_pretty | pg_size_pretty
----------------+----------------
306 MB | 171 MB
(1 row)

Vacuum full is a different story, but doesn't work online.

piro=# vacuum full test;
VACUUM
piro=# select pg_size_pretty(pg_relation_size('test'::regclass)),
pg_size_pretty(pg_relation_size('test_pkey'::regclass));
pg_size_pretty | pg_size_pretty
----------------+----------------
3144 kB | 1768 kB

In our live system we have a small table of active records in a
transient state. No record stages there for a long time. The size of
the table stays reasonable (but not really stable) but not the
indexes. One of them (friendly labeled "the index of death") is 5-6
columns wide and, given enough time, regularly grows into the
gigabytes for a table in the order of the ~100k records, only tamed by
a pg_repack treatment (previously by a create concurrently and drop).

-- Daniele