Re: does reindex need exclusive table access?

Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> writes:
> On 01/11/11 02:51, Gauthier, Dave wrote:
>> Does "reindex table foo" require no other users accessing the foo
>> table? Trying to understand why this seems to be stalled when I attempt
>> this on a live DB (if runs fine/fast on a copy of the DB that no one uses).

> Yes, it requires an exclusive lock.
> There is not currently any 'REINDEX CONCURRENTLY' command - not unless
> it's been added in a very recent version and I haven't noticed yet. You
> can CREATE INDEX CONCURRENTLY then drop the old index, though.

Yeah. The hard part of that is not actually the "reindex", it's the
"drop the old index" ... because the old index might be in use by
concurrent SELECTs. So dropping the old index absolutely requires an
exclusive lock, to ensure there are no read-only transactions depending
on that version of the index. Building a new index can be done with
a much weaker lock.

A straight reindex doesn't have a lot of choice here. We could have it
take a lesser lock while it's rebuilding the index, and then try to
upgrade to exclusive lock to move the new version into place --- but
upgrading your lock is a well-known recipe for causing deadlocks.

The one good thing about build-a-new-index-with-REINDEX-CONCURRENTLY-
and-then-drop-the-old-index is that the DROP requires exclusive lock
for only a tiny amount of time, and if the DROP does fail and roll back
because of conflicts, you haven't lost the work of building the new
index version. You can just try the DROP again.

> A workaround for reindexing while live is to begin a transaction, create
> the new index with a new name, drop the old one, rename the new one to
> the old one, and commit. This only requires an exclusive lock for the
> period of the drop and rename.

I'd do that in two transactions, so you don't lose the rebuild work
if there's a problem getting exclusive lock.

regards, tom lane