Most PostgreSQL foreign keys should stay NOT DEFERRABLE, which is what PostgreSQL gives you when you write nothing. A deferrable key earns its place only where a transaction has to write a child row before its parent, and even there DEFERRABLE INITIALLY IMMEDIATE, deferred on purpose by the one transaction that needs it, is usually better than INITIALLY DEFERRED on every key. Deferring does not skip the check, and in total it cost about the same. It moves the check, and the error, to COMMIT.

Everything below ran on PostgreSQL 18.3 in a throwaway Docker container on a laptop, with an authors table and three books tables whose author_id references it in each of the three modes, and, for the timings, a customers table of 100,000 rows and orders tables of 1,000,000 rows.

What do NOT DEFERRABLE, INITIALLY IMMEDIATE and INITIALLY DEFERRED mean?

They say when PostgreSQL checks that the parent row exists: after each statement, or at commit, and whether a transaction may move that check.

The middle row is the one people overlook. A key declared DEFERRABLE INITIALLY IMMEDIATE behaves like an ordinary key in every transaction except the one that asks otherwise. SET CONSTRAINTS books_can_author_id_fkey DEFERRED, then an insert of a book whose author does not exist yet, then the author, commits cleanly. Naming a key that is not deferrable fails with constraint "books_now_author_id_fkey" is not deferrable, while SET CONSTRAINTS ALL DEFERRED passes over such keys without a word: the next orphan insert against a NOT DEFERRABLE key was still refused at once.

Some things are never deferred, whatever the key says. The CREATE TABLE documentation says NOT NULL and CHECK “are not deferrable”, so a deferred author_id that is also NOT NULL still refuses a NULL on the spot. “Referential actions are executed as part of the data changing command, even if the constraint is deferred”: an ON DELETE CASCADE key emptied the child table the moment the parent was deleted, before commit. And ON DELETE RESTRICT ignores deferral completely. On a deferred key, deleting a referenced author failed at once with violates RESTRICT setting of foreign key constraint, where the same delete under the default NO ACTION waits for commit, so delete-and-reinsert of the parent works. The difference between those two actions is the subject of ON DELETE SET NULL vs CASCADE.

Should foreign keys be deferrable in PostgreSQL?

Only the keys a real transaction has to write out of order, and then usually as INITIALLY IMMEDIATE.

• Most keys: NOT DEFERRABLE. An order that points at a missing customer fails on the INSERT that wrote it, with the key and the value in the message, while the code that caused it is still on the stack.
• A key that one job writes out of order: DEFERRABLE INITIALLY IMMEDIATE. A sync that deletes and reloads a parent table, a bulk import that does not sort its files, or two rows that reference each other, as in circular foreign keys. That job runs SET CONSTRAINTS ... DEFERRED at the start of its transaction, and every other writer keeps the immediate check.
• Keys written by a loader that inserts in any order: DEFERRABLE INITIALLY DEFERRED. Fixtures and dump-style loaders insert table by table without knowing the dependency order. This is the case Django designed for.
• Whatever you pick, the parent key cannot be deferrable. A foreign key may not reference a deferrable unique constraint, and PostgreSQL refuses it: cannot use a deferrable unique constraint for referenced table "codes". A foreign key needs a parent key that is checked immediately, which is one more reason to think before making a unique constraint deferrable, as discussed in unique constraint vs unique index.

What does a deferred foreign key cost?

The same check, run later, with the error separated from the statement that caused it.

Timed six times each, inserting 1,000,000 orders in one transaction:

The deferred INSERT looks six to nine times faster, and it is not: the million checks are queued and run at COMMIT, which then takes twelve seconds. The total came out within a second or two of the immediate key, sometimes a little under it. A timer or slow-query log that watches the INSERT sees a fast statement, and the twelve seconds are charged to a COMMIT that nobody thinks of as slow.

The error moves with it. With a deferred key, INSERT INTO books_later VALUES (2, 99) returned INSERT 0 1, the next statement ran, and the COMMIT failed with insert or update on table "books_later" violates foreign key constraint "books_later_author_id_fkey". Everything else in the transaction rolled back, including an author inserted in the same transaction that had nothing wrong with it. Code that wraps one save() in a try block to catch an integrity error never sees this one; it comes out of the commit. Django’s test framework accounts for it: its TestCase documentation lists that it “checks deferrable database constraints at the end of each test”, because the transaction of a test never commits.

Deferred checks also block schema changes in the same transaction. After inserting a row into the deferred books_later, ALTER TABLE books_later ADD COLUMN title text failed with cannot ALTER TABLE "books_later" because it has pending trigger events. Altering the parent authors in the same transaction still worked: the events are queued on the table whose rows were written. Running SET CONSTRAINTS ALL IMMEDIATE first executes the queued checks and the ALTER goes through. Django’s PostgreSQL schema editor runs that kind of statement after adding a column that carries a foreign key, before dropping a foreign key, and after filling a new column’s default, so that a data migration and a schema change can share one transaction.

Which frameworks make foreign keys deferrable?

Django makes every foreign key its migrations create on PostgreSQL DEFERRABLE INITIALLY DEFERRED; Rails and plain SQL make none.

Django’s choice follows from fixtures: loaddata inserts objects in file order, so a child can arrive before its parent. When we read Saleor, a Django application, for the PostgreSQL round-trip benchmark, 265 of its 266 foreign keys were deferred; the other, from order_order_gift_cards to order_order, was NOT DEFERRABLE. A team that moves such a schema to MySQL or SQL Server loses the behaviour entirely, since neither database can defer a foreign key.

How do I make an existing foreign key deferrable without re-checking every row?

Use ALTER TABLE ... ALTER CONSTRAINT, which changes only the catalog, instead of dropping the key and adding it back.

ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE orders ALTER CONSTRAINT orders_customer_id_fkey NOT DEFERRABLE;

On the 1,000,000-row orders table each statement took between 0.6 and 3.4 ms. The alternative, ALTER TABLE orders DROP CONSTRAINT ... followed by ADD CONSTRAINT ... FOREIGN KEY, re-checks every order against customers and, held open in a transaction, showed AccessExclusiveLock on both tables in pg_locks:

The parent table is the expensive part. customers is usually the table every request reads, and the drop-and-add way stops those reads until the migration commits. The ACCESS EXCLUSIVE on customers comes from the DROP CONSTRAINT: a bare ADD CONSTRAINT ... FOREIGN KEY took SHARE ROW EXCLUSIVE on both tables, which holds back writes but lets reads through. Adding the key back NOT VALID and running VALIDATE CONSTRAINT later shortens the write block too, and is still more work than one ALTER CONSTRAINT. Two limits apply. The ALTER TABLE documentation says “only foreign key constraints may be altered in this fashion”, and trying it on a primary key fails with is not a foreign key constraint, so a unique or primary key constraint that should become deferrable has to be dropped and created again, index and all. And ALTER CONSTRAINT still takes ACCESS EXCLUSIVE on the referencing table and holds it until commit, so run it in a short transaction and set a lock_timeout first on a busy table.

How does Schemity show when a foreign key is checked?

Each PostgreSQL relationship records its check time, and changing it plans the in-place ALTER CONSTRAINT.

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. An ERD connected to PostgreSQL reads each key’s deferrability from the live database, and Import SQL reads it from a pg_dump. The relationship dialog shows it in a Checked select with three options, which are the three rows of the first table above: On each statement, On each statement, can be deferred, and At commit (deferred). The setting is written back into the REFERENCES clause of every generated CREATE TABLE and ADD FOREIGN KEY. How relationships are drawn and edited is in relationships.

When you design the next one and only the check time of an existing key changes, the planned migration is one ALTER TABLE "orders" ALTER CONSTRAINT "orders_customer_id_fkey" DEFERRABLE INITIALLY DEFERRED;, with no key dropped and no rows re-checked. Until this post, the planner dropped the key and added it back, which is the expensive column of the last table. A hand-written migration file holding the same statement, which the analysis used to list as unread, is now read as a change that reads no rows. Both fixes are in the development build and ship in the next release. When the key’s columns or actions change as well, it is still dropped and re-added, and the new key carries the new check time.

Converting a PostgreSQL diagram to another database drops the setting, and the conversion report counts the deferrable keys and says what that means there, as it does for types in converting a MySQL schema to PostgreSQL in the other direction: MySQL and SQL Server cannot defer a key, so rows loaded child first are refused, and Snowflake does not enforce foreign keys at all. What Schemity does not do: SQLite can defer a key, but Schemity does not record it for SQLite, so a converted key is checked on each statement there; no lint rule questions a deferred key, though the circular foreign key rule counts one as the way out of a NOT NULL cycle and stops reporting that cycle; and the impact analysis does not warn that a migration writing rows and then altering the same table will hit pending trigger events.

PostgreSQL deferrable foreign keys, in one list

Defer a key only for the transaction that needs it, and change an existing key in place.

• NOT DEFERRABLE is the default and the right choice for most keys: the error comes from the statement that caused it.
• DEFERRABLE INITIALLY IMMEDIATE plus SET CONSTRAINTS ... DEFERRED gives one job out-of-order inserts without changing anything for other writers.
• INITIALLY DEFERRED moves the cost and the error to COMMIT: 1,000,000 inserts took 13 to 15 s either way.
• NOT NULL, CHECK, ON DELETE RESTRICT and cascading actions are never deferred, and no foreign key may reference a deferrable unique constraint.
• Change a foreign key’s check time with ALTER CONSTRAINT, not drop and add, so the parent table stays readable.