Why Your Foreign Key Constraint Locks More Than You Think

Postgres versionAll supported versionsCategoryLocking / Foreign KeysSymptomUnexpected lock contention under concurrent writes

Foreign key constraints are good. You should use them. But they interact with Postgres's locking system in ways that are easy to miss and occasionally produce unexpected contention in high-write systems. Here is what is actually happening when you INSERT a child row or DELETE a parent row, and how to avoid the common traps.

The FOR KEY SHARE Lock

When you insert a row into a child table with a foreign key, Postgres must verify that the referenced parent row exists. To prevent the parent row from being deleted between the check and the commit of your transaction, Postgres acquires a FOR KEY SHARE lock on the parent row.

This is a row-level lock, and it is held for the duration of the child table's transaction. FOR KEY SHARE is the weakest row lock mode — it conflicts with FOR UPDATE and FOR NO KEY UPDATE, but multiple FOR KEY SHARE locks on the same row are compatible with each other and with normal reads.

In practice: if you are doing a bulk INSERT of 10,000 rows into an orders table that references a customers row, you are acquiring a FOR KEY SHARE lock on that customers row 10,000 times. Each acquisition is brief, but they are serialized through the lock manager. If another transaction holds a FOR UPDATE on that customers row — say, an UPDATE of the customer's billing address — your bulk INSERT will block until that UPDATE commits.

The Missing Index on the Child Column

Postgres does not automatically create an index on the foreign key column in the child table. This is not an oversight. The parent column must have a unique index (which the primary key provides), but the child column has no such requirement.

The problem appears when you DELETE a parent row. Before Postgres can delete a customer, it must verify no orders reference that customer. The verification requires scanning the orders table for any row with that customer_id. If there is no index on orders.customer_id, this is a sequential scan of the entire orders table, acquiring an EXCLUSIVE lock on the table during that scan.

An unindexed foreign key column on a large child table means every parent DELETE becomes a full table scan. At low volumes this is invisible. At scale, it appears as lock queue buildups and slow DELETE operations on parent rows, often at inconvenient times.

The fix is always the same:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

Always create an index on foreign key columns in child tables. The write overhead is small. The benefit for parent deletions and updates is significant.

The Deadlock Pattern

A less obvious consequence of FK locking appears as deadlocks in concurrent INSERT workloads. Consider two transactions inserting orders for different customers simultaneously:

-- Transaction A
INSERT INTO orders (customer_id, ...) VALUES (101, ...);
-- acquires FOR KEY SHARE on customers row 101
INSERT INTO orders (customer_id, ...) VALUES (102, ...);
-- needs FOR KEY SHARE on customers row 102

-- Transaction B (concurrent)
INSERT INTO orders (customer_id, ...) VALUES (102, ...);
-- acquires FOR KEY SHARE on customers row 102
INSERT INTO orders (customer_id, ...) VALUES (101, ...);
-- needs FOR KEY SHARE on customers row 101

FOR KEY SHARE locks are compatible with each other, so this specific example would not deadlock. But if either transaction holds a stronger lock on those customer rows — a FOR UPDATE from a separate UPDATE statement in the same transaction — deadlocks become possible. The pattern appears most often in application code that updates parent rows and inserts child rows in different orders across concurrent transactions. The fix is to acquire parent locks in a consistent order.

DEFERRABLE Constraints

By default, FK constraints are checked immediately at the statement level. If you insert a child row that references a parent that does not exist yet, the statement fails immediately, even if you plan to insert the parent later in the same transaction.

You can change this with the DEFERRABLE option:

ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers(id)
  DEFERRABLE INITIALLY DEFERRED;

With INITIALLY DEFERRED, the FK check runs at COMMIT time rather than at statement execution time. This allows a transaction to insert rows in any order — child before parent — as long as the constraint is satisfied by the time the transaction commits.

This is useful for circular references, bulk loads where order is unpredictable, or any import process where establishing a consistent insert order is impractical. The tradeoff is that violations are detected later in the transaction lifecycle, which can make debugging FK errors harder.

Always Index the Child FK Column

The one operational rule that follows from all of this: every foreign key column in a child table should have an index. Not sometimes. Always. The cost is a small write overhead per INSERT and some additional disk space. The benefit is that parent DELETEs and UPDATEs do not degrade into sequential scans, and the lock contention profile of your FK relationships stays predictable.

Postgres does not enforce this for you. The responsibility is yours. There are tools — pg_query advisors, the pg_fk_missing_index view if you add it via a custom function, and various database health checkers — that will scan for unindexed FK columns and flag them. Running this check when you create a new FK is a good habit.

-- Find FK columns without a supporting index
SELECT
  c.conname AS constraint_name,
  r.relname AS table_name,
  a.attname AS column_name
FROM pg_constraint c
JOIN pg_class r ON r.oid = c.conrelid
JOIN pg_attribute a ON a.attrelid = c.conrelid
  AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND a.attnum = ANY(i.indkey)
  );

Run this on any production database and deal with what comes back. There is almost always something there.

Building a product and tracking your progress publicly? builds.anethoth.com is a build ledger for software in progress — honest milestones, known limitations, what you're looking for next.