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

> 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.

Source: https://schemity.com/blog/postgres-unique-constraint-vs-unique-index/

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](https://schemity.com/blog/postgres-generated-column-vs-trigger/) 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](https://www.postgresql.org/docs/current/sql-altertable.html) describes exactly this for "situations where a new constraint needs to be added without blocking table updates for a long time":

```sql
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](https://schemity.com/blog/postgres-constraint-naming-convention/) 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:

```sql
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](https://www.postgresql.org/docs/current/sql-createindex.html) 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](https://schemity.com/doc/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](https://schemity.com/blog/should-ai-agents-write-database-migrations/) 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:

![Schemity's Preview changes findings drawer for a users table with 2 million rows: the planned migration ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email), one planned change, no lint findings, and two impact findings - adds a UNIQUE on users, fails if duplicates exist among ~2M rows, and checking the new key holds back reads and writes to users while it reads ~2M rows, 189 MB on disk - beside the users entity with a U marker on email](https://schemity.com/images/blog/postgres-unique-constraint-vs-unique-index/impact-unique-constraint.webp)

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 same Schemity findings drawer for a unique index on users (email): the planned migration CREATE UNIQUE INDEX users_email_idx ON users USING btree (email), one planned change reading unique index on users (email) added, no lint findings, and two impact findings - adds a UNIQUE on users, fails if duplicates exist among ~2M rows, and building the index holds back writes to users while it reads ~2M rows, 189 MB on disk - beside the users entity with a U marker on email and idx: 1 in its footer](https://schemity.com/images/blog/postgres-unique-constraint-vs-unique-index/impact-unique-index.webp)

The limits are worth stating. Schemity's own [migration SQL](https://schemity.com/doc/migration-sql-diff/) 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.

![Schemity's SQL to analyse dialog holding the two-statement recipe: CREATE UNIQUE INDEX CONCURRENTLY users_email_idx ON users (email), then ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_idx](https://schemity.com/images/blog/postgres-unique-constraint-vs-unique-index/migration-file-concurrent-recipe-input.webp)

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.

![Schemity's SQL migration drawer after analysing the recipe: 2 statements, 2 analysed, 0 not analysed, 0 bookkeeping, and one finding under Can fail on existing data - adds a UNIQUE on users, fails if duplicates exist among ~2M rows - with Count exactly showing exactly 1 row, and the note that nothing in the file is executed](https://schemity.com/images/blog/postgres-unique-constraint-vs-unique-index/migration-file-concurrent-recipe.webp)

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](https://schemity.com/doc/check-constraints-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.

## Related reading

What a unique rule actually covers when one of its columns can be empty is in [unique constraints and nullable columns](https://schemity.com/blog/unique-constraints-and-nullable-columns/). For the checks a migration linter makes and a schema linter makes, see [schema linting vs migration linting](https://schemity.com/blog/schema-linting-vs-migration-linting/). And for another change that fails on data already in the table, see [when skipping foreign key constraints is right](https://schemity.com/blog/should-you-use-foreign-key-constraints/), which covers orphan rows that stop a new foreign key.

## Frequently asked questions

### Is a unique constraint the same as a unique index in PostgreSQL?

Mostly. PostgreSQL enforces every unique constraint with a unique B-tree index it creates for you, so lookups and duplicate checks behave the same. The differences are at the edges: only an index can be partial or built on an expression, and only a constraint can be `DEFERRABLE` or named in `ON CONFLICT ON CONSTRAINT`. A deferrable constraint cannot be used by `ON CONFLICT` at all.

### Unique constraint vs unique index postgres: which should I use?

A unique constraint when the rule covers every row and only plain columns, and a unique index when it needs a `WHERE` clause or an expression. The lock is not a reason to pick one over the other: on a large table, build the index `CONCURRENTLY` either way and promote it to a constraint if you want one.

### Does `ALTER TABLE ADD CONSTRAINT UNIQUE` lock the table?

Yes. It takes an `ACCESS EXCLUSIVE` lock and holds it while it builds the index over every row, so even a plain `SELECT` waits. A plain `CREATE UNIQUE INDEX` takes a `SHARE` lock, which lets reads through and blocks writes. `CREATE UNIQUE INDEX CONCURRENTLY` blocks neither, but cannot run inside a transaction.