From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Concurrent Reindex on Primary Key for large table
Date: Wed, 08 Feb 2012 19:28:20 +0100
Message-ID: <jgueru$2vk$1@dough.gmane.org> (raw)
In-Reply-To: <1328724449781-5467243.post@n5.nabble.com>
References: <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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: spam_eater@gmx.net
Subject: Re: Concurrent Reindex on Primary Key for large table
In-Reply-To: <jgueru$2vk$1@dough.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox