Son Tran

Posted on Sep 26 Originally published at schemity.com

PostgreSQL Unique Constraint vs Unique Index: Which to Use, and How to Add One Without Locking the Table

#postgres #sql #migrations #database

Disclosure: I build Schemity, a desktop ERD tool - this post is from our blog and uses it for the examples.

TL;DR: Use a unique constraint when the rule covers every row and plain columns, because only a constraint can be DEFERRABLE or named in ON CONFLICT ON CONSTRAINT, though not both at once. Use a unique index when the rule needs a WHERE clause or an expression such as lower(email). On a large table, add either one by building the index with CREATE UNIQUE INDEX CONCURRENTLY first, because a plain ALTER TABLE ... ADD CONSTRAINT ... UNIQUE holds back reads and writes until it has checked every row.

In PostgreSQL a unique constraint and a unique index enforce uniqueness the same way. The constraint is implemented by a unique index that PostgreSQL creates for you. Where they differ is what you can attach to them, and how much of the table each one locks while it is being added. On a table with a million rows, the lock is the part you will notice.

The choice is usually made by the framework before anyone thinks about it. On an existing table, Django's unique=True and a plain UniqueConstraint emit ALTER TABLE ... ADD CONSTRAINT ... UNIQUE, and switch to CREATE UNIQUE INDEX only when the constraint has a condition or an expression. Rails' add_index :users, :email, unique: true always emits CREATE UNIQUE INDEX. Both look like one harmless line in a migration file, and on a large table the two statements lock it in different ways.

What is the difference between a unique constraint and a unique index?

Everything below was run on PostgreSQL 18.3:

	Unique constraint	Unique index
How it is enforced	A unique B-tree index created for it	Itself
Can be the target of a foreign key	Yes	Yes, unless it is partial
`ON CONFLICT (email)`	Yes, unless it is `DEFERRABLE`	Yes (a partial index needs the matching `WHERE` too)
`ON CONFLICT ON CONSTRAINT name`	Yes, unless it is `DEFERRABLE`	No, the constraint does not exist
Partial, e.g. `WHERE deleted_at IS NULL`	No	Yes
On an expression, e.g. `lower(email)`	No, syntax error	Yes
`DEFERRABLE`	Yes	No, syntax error
`NULLS NOT DISTINCT` (PostgreSQL 15+)	Yes	Yes
Can be built `CONCURRENTLY`	Not directly	Yes
Lock while being added	`ACCESS EXCLUSIVE`: reads and writes wait	`SHARE`: writes wait, reads continue

Two rows are worth a sentence. DEFERRABLE is the reason some tables need a constraint: with UNIQUE (pos) DEFERRABLE, UPDATE t SET pos = pos + 1 succeeds, because uniqueness is checked at the end of the statement rather than row by row (INITIALLY DEFERRED moves the check to commit). The same update against a unique index, or against a constraint that is not deferrable, fails partway with duplicate key value violates unique constraint, even though the final state is valid. The price is ON CONFLICT: PostgreSQL refuses a deferrable constraint as its arbiter, in either form. The partial and expression rows are the reason some rules can only be an index, and they are the rules most tables actually have: an email unique among live accounts, or unique regardless of case.

Should I use a unique constraint or a unique index in PostgreSQL?

Default to the constraint and switch to the index when the rule needs something only an index can express:

The rule covers every row, on plain columns: use a unique constraint. It is recorded in pg_constraint as a constraint, which is where schema tools read uniqueness rules from, and \d users labels it UNIQUE CONSTRAINT rather than listing a bare index. It also keeps DEFERRABLE or ON CONFLICT ON CONSTRAINT available, one or the other.
The rule has a condition (unique among rows that are not soft-deleted): use a partial unique index. No constraint can say it. The trade-off is that no foreign key can point at it, and every ON CONFLICT has to repeat its WHERE.
The rule is on an expression (lower(email)): use a unique index on the expression, or store the normalised value in a generated column and put a constraint on that.
You need to reorder or swap values in one statement: use a DEFERRABLE constraint, and give up ON CONFLICT on that key.

The one thing that should not decide it is the lock, because the lock problem has the same answer for both.

Will adding a unique constraint lock the table?

Yes, and harder than the unique index. In a throwaway database, with ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email) held open in one session, a plain SELECT count(*) FROM users in a second session timed out on its lock. With CREATE UNIQUE INDEX held open instead, the same SELECT returned at once and only the INSERT waited. pg_locks shows why: the ALTER TABLE takes ACCESS EXCLUSIVE, and the index build takes SHARE.

How long they hold it grows with the table. On 2,000,000 rows, on a laptop, the ADD CONSTRAINT took 2.9 seconds, and for those 2.9 seconds nothing could read users. The build is a sort, so the time grows faster than the row count, and faster again once the sort no longer fits in maintenance_work_mem: a table fifty times larger holds the lock for minutes, and every query that arrives in the meantime queues behind it.

CREATE UNIQUE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, which lets both reads and writes through while it scans the table twice. It has two rules of its own. It cannot run inside a transaction block, so migration frameworks need their per-migration transaction turned off: disable_ddl_transaction! with algorithm: :concurrently in Rails, and atomic = False in Django. Django's AddIndexConcurrently cannot help here, because its Index has no unique option, so a concurrent unique index in Django is a RunSQL statement. And if it fails, it leaves the index behind.

How do I add a unique constraint to a large table without blocking?

Build the index concurrently, then promote it. The ALTER TABLE documentation describes exactly this for "situations where a new constraint needs to be added without blocking table updates for a long time":

CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users (email);

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_idx;

The second statement still takes ACCESS EXCLUSIVE, but it does not scan anything: on the same 2,000,000 rows it took 0.8 milliseconds, against 2.9 seconds for the direct ADD CONSTRAINT. PostgreSQL renames the index to the constraint name as it goes. The short lock still has to be granted, though, and it waits behind any open transaction that has touched the table, while every query that arrives after it waits too. Run SET lock_timeout = '2s' before the ALTER TABLE and retry if it times out, so a long-running report cannot turn a 0.8 millisecond step into an outage. The index must be valid, non-partial, not on an expression, and a B-tree with default sort order, so this route only ends in a constraint for a rule that could have been a constraint anyway. For a partial or expression rule, stop after the first statement.

Before either statement, check for duplicates, because both fail on them:

SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1;

A failed plain ADD CONSTRAINT rolls back cleanly with could not create unique index "users_email_key" and a detail line naming the first duplicated key. A failed CONCURRENTLY build reports the same error but leaves users_email_idx in place with indisvalid = false. The CREATE INDEX documentation is plain about the cost: it is ignored for queries "however it will still consume update overhead". Drop it, fix the data, and build again.

How Schemity shows the cost before the migration runs

The point of reading the lock table above is to see the impact of every change before it reaches production, and a migration file does not show you any of it. CREATE UNIQUE INDEX and ADD CONSTRAINT ... UNIQUE are each one line, and nothing in the text says which one stops reads, or whether the data will let either succeed.

Schemity is database design software that reads your live database, shows the impact of every schema change before it runs, and keeps the diagram as a file in Git.

When a pending change adds uniqueness to a connected PostgreSQL table, impact analysis reports it twice. Under Can fail on existing data it says "Adds a UNIQUE on users, fails if duplicates exist among ~2M rows", and for a rule on plain columns it adds a Count exactly button that runs one read-only duplicate count under a timeout. Under Holds back other statements it names which lock you will get: "Checking the new key holds back reads and writes to users while it reads ~2M rows" for a constraint, or "Building the index holds back writes to users while it reads ~2M rows" for a unique index, with the table's size on disk. The lock finding is skipped below 10,000 rows, where the lock is over before anyone waits on it. The same findings come up for a migration file written by Django, Rails, Prisma or an AI agent when you analyse it against the connected database without running it.

Preview changes shows the same two findings next to the statement that causes them, so the reader sees the ALTER TABLE and its cost together:

Add the same rule as a unique index instead and the duplicate finding stays, but the lock finding drops "reads and": the index build holds back writes only.

The limits are worth stating. Schemity's own migration SQL never uses CONCURRENTLY, so on a big table the finding is your cue to run the two-statement recipe above in your migration tool instead. Schemity reads that recipe too: paste it into the SQL migration drawer (Shift+F7) and both statements are analysed against the connected database, with nothing in the file executed.

The report keeps the finding that matters and drops the one that no longer applies. The concurrent build can still fail on the duplicate, and Count exactly finds exactly one row. There is no lock finding, because the build lets reads and writes through and the promotion reads no rows.

On the canvas, a column covered by a constraint or a plain unique index carries the same U marker, as described in check constraints and composite unique. The diagram does not read a partial index's WHERE clause, so a partial unique index shows as unique on its columns without its condition, and an index on an expression such as lower(email) does not appear at all.

DEV Community