PostgreSQL Deferrable Constraints: How to Swap Unique Values and Survive Circular Foreign Keys

I tried to swap the sort positions of two rows — give row A position 2 and row B position 1 — inside one transaction. It failed with a unique violation on the very first UPDATE, even though the final state had no duplicates at all. The transaction never got to run the second statement.

The reason is that, by default, PostgreSQL checks constraints immediately after each statement, not at the end of the transaction. Mid-swap, two rows briefly held the same position, and the immediate check rejected it. Deferrable constraints fix exactly this: they let you postpone the check to commit time, when the data is consistent again.

Immediate versus deferred checking

A normal constraint is checked the instant a row is inserted or updated. That is usually what you want — fail fast, fail close to the bug. But it means the data must be valid after every single statement, even in the middle of a multi-statement operation that is only consistent once finished.

A DEFERRABLE constraint can be told to wait. Marked INITIALLY DEFERRED (or switched at runtime), it is not checked until the transaction commits. As long as everything is valid by then, the intermediate states are allowed.

Making a constraint deferrable

You declare the constraint as deferrable and choose its default timing. Only deferrable constraints can be deferred; you cannot defer an ordinary one after the fact, so this is a design-time decision for the constraints you know you will need to.

-- A unique constraint we can defer to commit time
ALTER TABLE items
  ADD CONSTRAINT items_position_uniq UNIQUE (list_id, position)
  DEFERRABLE INITIALLY IMMEDIATE;

-- Now the swap works: defer the check for this transaction
BEGIN;
SET CONSTRAINTS items_position_uniq DEFERRED;
UPDATE items SET position = 2 WHERE id = 1;  -- transient duplicate, allowed
UPDATE items SET position = 1 WHERE id = 2;  -- resolved before commit
COMMIT;                                       -- check runs here, passes

The classic case: circular foreign keys

Two tables that reference each other are impossible to populate with immediate foreign keys: inserting the first row fails because the row it points to does not exist yet, and vice versa. Deferrable foreign keys break the deadlock — you insert both rows, then the references are validated together at commit.

Bulk loads benefit too. If you are loading interrelated data and the order is awkward, deferring foreign key checks to the end of the transaction lets you insert in any order and validate once.

ALTER TABLE employees
  ADD CONSTRAINT emp_manager_fk FOREIGN KEY (manager_id)
  REFERENCES employees(id)
  DEFERRABLE INITIALLY DEFERRED;

Know the trade-offs

Deferring moves the error to commit time, which can make failures harder to localize — the statement that caused the problem already succeeded, and COMMIT is what reports the violation. For interactive debugging, immediate checking is friendlier.

Deferred checks also accumulate work until commit, so a transaction touching huge numbers of rows holds the pending checks until the end. Use deferral where the semantics require it (swaps, circular refs, order-independent loads), not as a default for everything.

• By default constraints are checked immediately after each statement.
• DEFERRABLE constraints can postpone the check to commit time.
• Use INITIALLY DEFERRED or SET CONSTRAINTS ... DEFERRED per transaction.
• Enables value swaps, circular foreign keys, and order-independent bulk loads.
• Trade-off: violations surface at COMMIT, which is harder to debug.

The practical standard

The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.