Re: How to rebuild index efficiently

On Mon, Aug 03, 2020 at 01:04:45PM -0500, Ron wrote:
> same definition, and when that is complete, drop the old index.  The
> locking that is required here is modest: CREATE INDEX CONCURRENTLY
> needs to lock the table briefly at a couple of points in the
> operation, and dropping the old index requires a brief lock on the
> table.   It is, however, much less overall lock time than REINDEX would be.
>
> Of course, you need enough disk space... :)

A SHARE UPDATE EXCLUSIVE lock is taken during a CIC, meaning that
writes and reads are allowed on the parent table while the operation
works, but no DDLs are allowed (roughly).  The operation takes a
couple of transactions to complete, and  there are two wait points
after building and validating the new index to make sure that there
are no transactions remaining around that may cause visiblity issues
once the new index is ready to use and becomes valid.  So the
operation is longer, takes more resources, but it has the advantage to
be non-disruptive.
--
Michael