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:
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.
In response to
pgsql-sql by date
|Next:||From: Edward W. Rouse||Date: 2012-02-08 20:01:36|
|Subject: time interval math|
|Previous:||From: rverghese||Date: 2012-02-08 18:07:29|
|Subject: Concurrent Reindex on Primary Key for large table|