From: | Daniele Varrazzo <daniele(dot)varrazzo(at)gmail(dot)com> |
---|---|
To: | Alan Hodgson <ahodgson(at)simkin(dot)ca> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Best practice when reindexing in production |
Date: | 2013-05-29 19:22:13 |
Message-ID: | CA+mi_8Zbc_MBRnRujHNpSNXx4pYF1F2oWHpYD1z_4wJJNAeD4g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Josh Berkus | 2013-05-30 00:14:30 | Performance bug in prepared statement binding in 9.2? |
Previous Message | Alan Hodgson | 2013-05-29 17:47:04 | Re: Best practice when reindexing in production |