Re: Index corruption with CREATE INDEX CONCURRENTLY

On Fri, Feb 17, 2017 at 8:23 AM, Keith Fiske <keith(at)omniti(dot)com> wrote:
> It's not the load I'm worried about, it's the locks that are required at
> some point during the rebuild. Doing an index rebuild here and there isn't a
> big deal, but trying to do it for an entire heavily loaded, multi-terabyte
> database is hardly trivial. And I'd say doing a scan is far less invasive
> than actually rebuilding the index since little to no writing is actually
> being done.

Certainly, the checks that amcheck performs that don't require a
heavier lock (just a SELECT-style AccessShareLock) were vastly less
expensive than reindexing, and of course had only negligible potential
to block other operations. And, REINDEX certainly is a foot-gun,
lock-wise, which is why I try to avoid it.

The difference with a test that could detect this variety of
corruption is that that would need to visit the heap, which tends to
be much larger than any one index, or even all indexes. That would
probably need to be random I/O, too. It might be possible to mostly
not visit the heap, though -- I'm not sure offhand. I'd have to study
the problem in detail, which I have no time for at the moment.

--
Peter Geoghegan