Partial Unique Indexes for Conditional Constraints #

A rule that seems simple — each customer may have one active subscription, but any number of cancelled ones — has no plain unique constraint that expresses it. Teams work around it with application checks that race, or with a full unique index on (customer_id, status) that allows two active rows as long as their statuses differ in case. A partial unique index states the rule exactly and lets the database enforce it.

Partial indexes are introduced in partial index implementation. Their unique variant is both a constraint and an index, with a few sharp edges around ON CONFLICT and planner proofs.

The Index Condition #

CREATE UNIQUE INDEX CONCURRENTLY subscriptions_one_active_idx
  ON subscriptions (customer_id)
  WHERE status = 'active';

Uniqueness is enforced only among rows matching the predicate. Two cancelled subscriptions for the same customer are fine; a second active one raises a unique violation. The index is also small: it contains only active rows, so it is fast to scan and cheap to maintain — rows that do not match the predicate are not indexed at all, and transitions into and out of the predicate are index inserts and deletes.

Three consequences to know:

Common shapes: one active row per key; one default per group (WHERE is_default); one row per key among non-deleted rows (WHERE deleted_at IS NULL); one in-progress job per resource.

What a partial unique index can and cannot do A comparison table. A partial unique index enforces uniqueness only among matching rows, stays small, and requires ON CONFLICT to repeat its predicate. A full unique constraint enforces uniqueness for all rows, can back a foreign key, and always provides a planner uniqueness proof. partial unique full unique enforces among matching rows all rows index size only matching rows all rows ON CONFLICT must repeat WHERE plain inference foreign key target no yes planner proof only if query implies always choose partial when the rule genuinely applies to a subset

Annotated Evidence #

Creating it on data that already violates the rule fails, which is the point:

CREATE UNIQUE INDEX CONCURRENTLY subscriptions_one_active_idx
  ON subscriptions (customer_id) WHERE status = 'active';
ERROR:  could not create unique index "subscriptions_one_active_idx"
DETAIL:  Key (customer_id)=(48120) is duplicated.
-- find the offenders before retrying
SELECT customer_id, count(*) FROM subscriptions
WHERE status = 'active' GROUP BY 1 HAVING count(*) > 1;

Once created, the upsert must repeat the predicate:

INSERT INTO subscriptions (customer_id, plan, status)
VALUES (48120, 'pro', 'active')
ON CONFLICT (customer_id) WHERE status = 'active'
DO UPDATE SET plan = EXCLUDED.plan;
Insert on subscriptions  (actual time=0.09..0.09 rows=0 loops=1)
  Conflict Resolution: UPDATE
  Conflict Arbiter Indexes: subscriptions_one_active_idx
  Tuples Inserted: 0
  Conflicting Tuples: 1
-- without the WHERE clause: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

And queries that imply the predicate use the small index:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM subscriptions WHERE customer_id = 48120 AND status = 'active';

Index Scan using subscriptions_one_active_idx on subscriptions  (actual rows=1 loops=1)
  Index Cond: (customer_id = 48120)
  Buffers: shared hit=3
-- the status condition is satisfied by the index predicate, not re-checked
Three places the predicate matters Three items are annotated. The creation error naming a duplicated key shows existing violations. The Conflict Arbiter Indexes line shows the partial index was inferred because the statement repeated its predicate. The Index Cond without the status condition shows the predicate is implied, not re-checked. could not create unique index … duplicated pre-existing violations Conflict Arbiter Indexes: …_one_active_idx ON CONFLICT repeated the WHERE Index Cond: (customer_id = 48120) status implied by the predicate the same WHERE clause appears in the index, the upsert and the query

Step-by-Step Resolution #

  1. State the rule precisely, including what happens to rows outside the subset. “One active per customer; cancelled rows unrestricted” is a specification; “no duplicates” is not.

  2. Clean existing violations with the GROUP BY … HAVING query above before creating the index.

  3. Create it concurrently so writes continue; a failed build leaves an invalid index to clean up, as described in invalid indexes after failed concurrent builds.

  4. Update the write paths so every upsert repeats the predicate in ON CONFLICT.

  5. Check query predicates match. Queries must include a condition that implies status = 'active' for the index to be usable, following partial index predicate matching rules.

  6. Keep a plain index for the other rows if queries also search cancelled subscriptions; the partial index does not contain them.

  7. Document the rule in the migration, because a partial unique index is easy to mistake for an ordinary index and drop.

Size of the constraint A full unique index on customer_id and status covers all 84 million subscription rows and occupies 3.1 gigabytes. The partial unique index covers only the 2.1 million active rows and occupies 62 megabytes, and an active-subscription lookup reads three buffers instead of five. full index (all rows) 3.1 GB, 84M entries partial index (active) 62 MB, 2.1M entries smaller index, and it expresses the actual business rule

Before and After #

-- BEFORE: application-level check, races under concurrency
two concurrent requests → two active subscriptions for customer 48120

-- AFTER: CREATE UNIQUE INDEX … (customer_id) WHERE status = 'active'
second request → ERROR: duplicate key value violates unique constraint "subscriptions_one_active_idx"

Where the rule and the schema can drift apart #

A partial unique index enforces its rule against the predicate as written. If the application later introduces a third state — paused, say, which should also count as occupying the customer’s single slot — the index silently permits one active and one paused subscription at once. Rules of this shape are worth restating whenever the state machine changes, and the index predicate updated in the same migration: WHERE status IN ('active', 'paused').

Case and type matter too. WHERE status = 'active' does not match rows stored as 'Active', so a mixed-case column can hold violations the index never sees. Where the column is free text, normalising it — a check constraint, an enum type, or a lookup table — is part of making the constraint trustworthy. An enum has the additional advantage that adding a value is a deliberate migration, which is exactly the moment to revisit the predicate.

There is also a concurrency subtlety worth stating plainly: the index enforces the rule at the moment each row is written, using the standard unique-index mechanics, so two concurrent transactions inserting an active row for the same customer will not both succeed — one waits and then fails. That is exactly the guarantee an application-level check cannot provide, because between its SELECT and its INSERT another transaction can slip in. Handling the resulting unique violation explicitly — retry, or convert it into a domain error — is part of adopting the constraint.

Common Pitfalls #

Forgetting the predicate in ON CONFLICT. Inference fails with an error. Diagnostic signal: “no unique or exclusion constraint matching”. Fix: repeat the index’s WHERE clause.

Expecting foreign keys to reference it. They cannot. Diagnostic signal: error creating the foreign key. Fix: a full unique constraint on the referenced columns.

Assuming the planner always knows the rule. Uniqueness proofs need the query to imply the predicate. Diagnostic signal: missing Inner Unique on joins. Fix: include the predicate in the join or query.

Nullable predicate columns. WHERE deleted_at IS NULL indexes only non-deleted rows; queries must use IS NULL too, not = NULL or a coalesce. Diagnostic signal: the index unused by soft-delete queries. Fix: match the predicate exactly.

Frequently Asked Questions #

How do I enforce uniqueness only for some rows? #

Create a unique index with a WHERE clause: CREATE UNIQUE INDEX … ON t (key) WHERE condition. Uniqueness is then enforced only among rows satisfying the condition, and the index contains only those rows.

Why does ON CONFLICT not find my partial unique index? #

Inference requires the statement to repeat the index’s predicate: ON CONFLICT (key) WHERE condition. Without it, PostgreSQL cannot tell which partial index you mean and reports that no matching constraint exists.

Can a foreign key reference a partial unique index? #

No. Foreign keys require a non-partial unique constraint or unique index on the referenced columns.

Up: Partial Index Implementation