Thread: Reindexdb and REINDEX

"Rod Dutton" <rod@e-rm.co.uk> writes:
> My question is: will using the contrib/reindexdb or REINDEX sql command do
> essentially the same job as dropping and re-creating the indexes.  I.E. do
> you get a fully compacted and balanced index?

Yes.

> How is concurrency handled by contrib/reindexdb and REINDEX (I know you can
> create an index on the fly with no obvious lock outs)?

In 8.0 they are almost equivalent, but in earlier releases
REINDEX takes an exclusive lock on the index's parent table.

The details are:

DROP INDEX: takes exclusive lock, but doesn't hold it long.
CREATE INDEX: takes ShareLock, which blocks writers but not readers.

So when you do it that way, readers can use the table while CREATE INDEX
runs, but of course they have no use of the dropped index.  Putting the
DROP and the CREATE in one transaction isn't a good idea if you want
concurrency, because then the exclusive lock persists till transaction
end.

REINDEX before 8.0: takes exclusive lock for the duration.

This of course is a dead loss for concurrency.

REINDEX in 8.0: takes ShareLock on the table and exclusive lock on
the particular index.

This means that writers are blocked, but readers can proceed *as long as
they don't try to use the index under reconstruction*.  If they try,
they block.

If you're rebuilding a popular index, you have a choice of making
readers do seqscans or having them block till the rebuild completes.

One other point is that DROP/CREATE breaks any stored plans that use the
index, which can have negative effects on plpgsql functions and PREPAREd
statements.  REINDEX doesn't break plans.  We don't currently have any
automated way of rebuilding stored plans, so in the worst case you may
have to terminate open backend sessions after a DROP/CREATE.

            regards, tom lane