Re: Best practice when reindexing in production

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux