To: pgsql-sql@postgresql.org
From: Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: Concurrent Reindex on Primary Key for large table
Date: Wed, 08 Feb 2012 19:28:20 +0100
Lines: 23
Message-ID: <jgueru$2vk$1@dough.gmane.org>
References: <1328724449781-5467243.post@n5.nabble.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; de;
	rv:1.8.1.21) Gecko/20090302 Thunderbird/2.0.0.21 Mnenhy/0.7.5.666
In-Reply-To: <1328724449781-5467243.post@n5.nabble.com>

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