On Wed, May 29, 2013 at 6:47 PM, Alan Hodgson <ahodgson@simkin.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