Re: When/if to Reindex

"Steven Flatt" <steven(dot)flatt(at)gmail(dot)com> writes:
> So, can we simply trust what's in pg_class.relpages and ignore looking
> directly at the index?

No, we can't. In the light of morning I remember more about the reason
for the aforesaid patch: it's actually unsafe to read the pg_class row
at all if you have not got lock on the index. We are reading with
SnapshotNow in order to be sure we see up-to-date info, and that means
that a concurrent update of the row (eg, for REINDEX to report the new
relfilenode) can have the following behavior:

1. REINDEX inserts the new modified version of the index's pg_class row.

2. Would-be reader process visits the new version of the pg_class row.
It's not committed yet, so we ignore it and continue scanning.

3. REINDEX commits.

4. Reader process visits the old version of the pg_class row. It's
now committed dead, so we ignore it and continue scanning.

5. Reader process bombs out with a complaint about no pg_class row for
the index.

So we really have to have the lock.

> This is a fairly serious concern for us, that
> reindex is blocking all readers of the parent table.

I'm afraid you're kinda stuck: I don't see any fix that would be
practical to put into 8.2, or even 8.3 considering that it's way too
late to be thinking of implementing REINDEX CONCURRENTLY for 8.3.

You might be able to work around it for now by faking such a reindex
"by hand"; that is, create a duplicate new index under a different
name using CREATE INDEX CONCURRENTLY, then exclusive-lock the table
for just long enough to drop the old index and rename the new one
to match.

It's probably worth asking also how badly you really need routine
reindexing. Are you certain your app still needs that with 8.2,
or is it a hangover from a few releases back? Could more aggressive
(auto)vacuuming provide a better solution?

regards, tom lane