Skip site navigation (1) Skip section navigation (2)

Re: Concurrent Reindex on Primary Key for large table

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Concurrent Reindex on Primary Key for large table
Date: 2012-02-08 18:28:20
Message-ID: jgueru$2vk$1@dough.gmane.org (view raw or flat)
Thread:
Lists: pgsql-sql
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




In response to

Responses

pgsql-sql by date

Next:From: Edward W. RouseDate: 2012-02-08 20:01:36
Subject: time interval math
Previous:From: rvergheseDate: 2012-02-08 18:07:29
Subject: Concurrent Reindex on Primary Key for large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group