On Fri, 3 Mar 2023 at 23:17, Conner Bean <conner.bean@xxxxxxxxxx> wrote: I wanted to avoid using a unique index since dropping them requires an exclusive lock and cannot be done concurrently. My thought was to then use a unique constraint, since I've read unofficial docs[0] that say these can be dropped safely with no lock. You should try the official documents. You won't find any wording in those that say that a unique constraint can be dropped without any locking. If you look at https://www.postgresql.org/docs/current/sql-altertable.html you'll see "Note that the lock level required may differ for each subform. An ACCESS EXCLUSIVE lock is acquired unless explicitly noted.", and if you look at DROP CONSTRAINT that it mentions nothing about any lower-level locks, so you can assume that DROP CONSTRAINT obtains an access exclusive lock on the table being altered. If you have a look at https://www.postgresql.org/docs/15/sql-dropindex.html check out the CONCURRENTLY option. That option allows an index to be dropped without blocking concurrent reads and writes to the table. It seems like just having a unique index without the constraint is likely your best bet if you can't afford to block any traffic for the brief moment it would take to drop the constraint.
That doc page says this about CONCURRENTLY:
"
There are several caveats to be aware of when using this option. Only one index name can be specified, and the CASCADE option is not supported. (Thus, an index that supports a UNIQUE or PRIMARY KEY constraint cannot be dropped this way.)
"
--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.
