A partial index stores entries only for rows satisfying its predicate. This can reduce index size when the useful subset is small, but it can also enforce a rule that applies only to that subset. For example, a seat may have many historical reservations while allowing at most one active reservation. A partial unique index expresses that rule at the database boundary.

Membership follows the predicate

CREATE INDEX ... WHERE active includes rows for which active evaluates to true. False and null do not satisfy the predicate. Whether null should be allowed is a separate schema decision; the experiment makes active non-null so each reservation is explicitly active or inactive. The indexed key need not be the same column used by the predicate.

Membership changes with ordinary writes. An inactive row becoming active must enter the index and pass its checks; an active row becoming inactive leaves the active subset. This is not an immutable archive boundary. Predicate evaluation and changing membership still cost work, so a smaller index does not mean every update is free or automatically faster.

Uniqueness can apply to a subset

A unique index on seat WHERE active rejects two active rows for the same seat, while permitting any number of inactive historical rows. The index enforces that rule even if no SELECT ever chooses it. Query acceleration and integrity enforcement are different reasons to retain an index, so usage counters alone cannot justify deleting this one.

An application-level “check first, insert later” is weaker because another transaction can insert between the check and the write. A unique index coordinates conflicting writes in PostgreSQL; the loser must handle a uniqueness error or another chosen conflict policy. The fixture exercises rejection sequentially, while concurrency behavior remains part of the database’s constraint contract rather than a claim about an application booking workflow.

Plan queries with the same boundary

To use a partial index as an access path, the planner must recognize that the query’s conditions imply the index predicate. Merely querying the seat does not prove the caller wants only active rows. A query that explicitly asks for active reservations can qualify, although a tiny table may still be cheaper to scan. A logically similar expression is useful only if the planner can establish the implication.

Do not create hundreds of overlapping partial indexes to simulate partitioning. Planning and maintenance costs grow, and the optimizer does not automatically understand them as one coordinated partition scheme. Choose a predicate from a stable workload or integrity requirement, then verify the actual query and transition behavior.

When it breaks

Forgetting the predicate in a query can return old reservations alongside the current one, even though active uniqueness is enforced perfectly. Conversely, treating the index as globally unique can reject valid history in application logic. Document whether the rule concerns all rows or only the current subset, and test both populations.

A failed reactivation is another important boundary. Changing an old inactive reservation back to active can conflict with the current holder. The application must keep the old row inactive when that update fails and report the conflict, not silently replace the active booking. Bulk imports and status migrations need the same checks as ordinary insertion.

Check it quickly

Save this block as partial-unique.sql and run psql -X -v ON_ERROR_STOP=1 -f partial-unique.sql in a disposable PostgreSQL 18 database. It creates a temporary reservation table, so no real seat inventory is touched.

\set ON_ERROR_STOP on
BEGIN;
CREATE TEMP TABLE reservations (id integer PRIMARY KEY, seat text NOT NULL, active boolean NOT NULL);
CREATE UNIQUE INDEX one_active_seat ON reservations (seat) WHERE active;
INSERT INTO reservations VALUES (1,'A1',true),(2,'A1',false),(3,'A1',false);
DO $$ BEGIN
  INSERT INTO reservations VALUES (4,'A1',true);
  RAISE EXCEPTION 'two active reservations accepted';
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'active duplicate rejected';
END $$;
UPDATE reservations SET active=false WHERE id=1;
INSERT INTO reservations VALUES (4,'A1',true);
DO $$ BEGIN
  UPDATE reservations SET active=true WHERE id=2;
  RAISE EXCEPTION 'reactivation bypassed uniqueness';
EXCEPTION WHEN unique_violation THEN RAISE NOTICE 'conflicting reactivation rejected';
END $$;
SELECT active,count(*) FROM reservations GROUP BY active ORDER BY active;
SELECT id FROM reservations WHERE active AND seat='A1';
DO $$ BEGIN
  IF (SELECT id FROM reservations WHERE active AND seat='A1') IS DISTINCT FROM 4 THEN
    RAISE EXCEPTION 'wrong active reservation';
  END IF;
  IF (SELECT count(*) FROM reservations WHERE NOT active) <> 3 THEN
    RAISE EXCEPTION 'history was not preserved';
  END IF;
END $$;
ROLLBACK;

The first active duplicate is rejected while two historical duplicates are accepted. Deactivating reservation one releases the seat for reservation four. Reactivating reservation two then fails because four is active. The final counts are three inactive rows and one active row, whose identifier is four. Assertions check both the current holder and retained history.

PostgreSQL 18.6 passes these checks. The exception blocks catch only the expected uniqueness violation; an unexpected success raises a different error and fails the script. Rollback removes the fixture. This verifies subset membership and uniqueness transitions, not payment atomicity, distributed reservations, or the performance of a large historical table.

Sources