Re: Concurrent Reindex on Primary Key for large table

rverghese wrote on 08.02.2012 19:07:
> I have a large table with about 60 million rows, everyday I add 3-4 million,
> remove 3-4 million and update 1-2 million. I have a script that reindexes
> concurrently a couple of times a week, since I see significant bloat. I have
> autovac on and the settings are below. I can't concurrently reindex the
> primary key, since there can be only one on a table.

With 9.1 you can create a new index and drop and re-create the primary key using the new index.

This still requires an exclusive lock on the table, but only for a very short moment:

The following example is more or less taken from the manual:
http://www.postgresql.org/docs/9.1/static/sql-altertable.html

create unique concurrently new_index on your_table(your_pk);
alter table your_table drop primary key;
alter table your_table add primary key using index new_index;

As this can be done in one transaction it should be safe with regards to the primary key.

Thomas