Allow unique indexes (via `UniqueConstraint`) to be created/dropped `CONCURRENTLY` on PostgreSQL

[cliffordgama]

Code of Conduct

• I agree to follow Django's Code of Conduct

Feature Description

A new pair of contrib.postgres operations to allow adding and dropping unique indexes using PostgreSQL's CONCURRENTLY option.

Problem

Django supports unique constraints via UniqueConstraints in a model’s Meta.constraints. Some of these constraints are implemented as unique indexes using CREATE UNIQUE INDEX, and can make use of the CONCURRENTLY option to avoid locking the table.

Currently, only Index objects in Meta.indexes can use AddIndexConcurrently and RemoveIndexConcurrently. Unique constraints in Meta.constraints do not have an equivalent mechanism, even when they are indexes behind the hood and could safely support CONCURRENTLY.

Request or proposal

proposal

Additional Details

Not all UniqueConstraints are implemented as indexes. Constraints defining fields, and none of includes, condition, or opclasses use ALTER TABLE ... ADD CONSTRAINT (source) while the unique constraints defining expressions or any of includes, condition, opclasses use CREATE UNIQUE INDEX.

The CONCURRENTLY option only applies to constraints backed by CREATE UNIQUE INDEX.

Implementation Suggestions

Introduce AddConstraintConcurrently and RemoveConstraintConcurrently:

• If a UniqueConstraint is implemented via CREATE UNIQUE INDEX, these operations add the CONCURRENTLY keyword.
• Otherwise, they behave like the standard AddConstraint and RemoveConstraint.

A proof of concept PR demonstrates feasibility.

[github-actions]

Thank you cliffordgama for sharing your idea! We have a lot of them so please be patient. You can see the current queue here.

Community instructions

For commenters, please use the emoji reactions on the issue to express support, and/or concern easily. Please use the comments to ask questions or contribute knowledge about the idea. It is unhelpful to post comments of "I'd love this" or "What's the state of this?"

Reaction Guide

• 👍 This is something I would use
• 👎 This is something that would cause problems for me or Django
• 😕 I’m indifferent to this
• 🎉 This is an easy win

[cliffordgama]

I wrote about this feature proposal on my blog.