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
Attachment:
signature.asc
Description: PGP signature
