Foreign keys are not overhead, and the thing you measured was a missing index

Someone benchmarks a delete on a table with foreign key constraints. It takes 90 seconds. They drop the constraints and the same delete takes 200 milliseconds. The conclusion writes itself: foreign keys are slow, remove them, ship it.

Six months later a reconciliation job finds 40,000 order line items whose parent order does not exist. Nobody can say when they appeared or which code path created them, because the thing that would have refused to create them is gone and nothing replaced it.

The benchmark was real. The conclusion drawn from it was wrong, and the reason is specific enough to be worth spelling out.

What the check actually costs

An insert into a child table with a foreign key does one extra thing: it looks up the referenced key in the parent table to confirm it exists.

That lookup goes through the parent’s primary key index. The primary key index is the most frequently touched structure in most tables, which means it is very likely already in the buffer pool, which means the lookup is a few memory accesses rather than a disk read. Measured against everything else in an insert, including the network round trip to send it, that cost disappears into the noise.

Postgres also takes a lock while doing it, and the lock is weaker than people assume. It acquires FOR KEY SHARE on the parent row, which conflicts with deleting that row or updating its key columns, and does not conflict with ordinary updates to other columns. A child insert does not block a parent update unless that update touches the referenced key.

So the insert path is genuinely cheap. The delete path is where the 90 seconds came from, and the cause is not the constraint.

The index that Postgres does not create for you

When you declare a foreign key, the parent side is indexed automatically because it references a primary key or unique constraint. The child side is not indexed at all unless you do it.

That asymmetry produces the pathological delete. To remove a parent row, the database must prove no child references it. With no index on the child’s referencing column, proving that means scanning the entire child table.

CREATE TABLE order_items (
    id          bigserial PRIMARY KEY,
    order_id    bigint NOT NULL REFERENCES orders(id),
    sku         text NOT NULL
);

-- No index on order_items.order_id exists.
-- This delete scans order_items once per deleted order.
DELETE FROM orders WHERE created_at < now() - interval '2 years';

Delete 10,000 old orders against a child table of 50 million rows and the database performs 10,000 sequential scans of 50 million rows. That is the 90 seconds, and it has nothing to do with constraint checking being expensive.

CREATE INDEX idx_order_items_order_id ON order_items (order_id);

One index. The scan becomes a lookup and the delete returns to normal.

I would go further: an unindexed foreign key is close to always a mistake, and it is worth auditing for directly rather than waiting to be surprised.

-- Foreign keys with no index on the referencing column.
SELECT c.conrelid::regclass AS child_table,
       a.attname            AS column_name
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = c.conkey[1]
WHERE c.contype = 'f'
  AND NOT EXISTS (
      SELECT 1 FROM pg_index i
      WHERE i.indrelid = c.conrelid
        AND i.indkey[0] = c.conkey[1]
  );

Most schemas that have been growing for a few years return rows from that query, and each row is a delete waiting to be slow.

What you give up when you drop them

The constraint is not doing bookkeeping. It is holding an invariant, and the invariant does not stop being needed when the enforcement goes away.

flowchart TB
    subgraph db["Enforced in the database"]
        W1["Any writer, any service,<br/>any migration script"] --> FK["Foreign key"]
        FK --> G1["Invariant holds"]
    end

    subgraph app["Enforced in application code"]
        W2["The service that<br/>remembers to check"] --> OK["Invariant holds"]
        W3["A batch job"] --> BAD["Orphans"]
        W4["A manual fix in psql"] --> BAD
        W5["A second service<br/>on the same schema"] --> BAD
    end

    style G1 stroke:#4ade80,stroke-width:3px,color:#fff
    style BAD stroke:#ef4444,stroke-width:3px,color:#fff

The application code path usually does check. That is not where orphans come from. They come from the batch job written by a different team, the data fix someone ran during an incident, the second service that reads and writes the same schema, and the migration that deleted rows in the wrong order.

The database is the one place every writer passes through. Enforcing there covers writers you have not met yet, which includes every future one.

There is also a diagnostic quality worth naming. With the constraint, a bad write fails immediately, at the moment it happens, with a message naming the exact relationship. Without it, the write succeeds and the problem surfaces months later as a report with wrong totals, at which point the cause is unrecoverable. Losing the constraint costs you the timestamp on the bug.

The cases where dropping them is defensible

Bulk loading is real. Loading 500 million rows with per-row constraint checks is meaningfully slower than loading them without, and the standard practice of dropping constraints, loading, and re-adding them with a single validating pass is sound. Note that this is temporary removal around a known operation, not a schema decision.

Postgres also lets you defer instead:

ALTER TABLE order_items
    ADD CONSTRAINT fk_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- Insert children before parents, fix ordering later, whatever the load needs.
COMMIT;   -- all constraints validated here

Deferring solves circular reference problems and load ordering without giving up the guarantee. The check happens at commit rather than per statement.

Service boundaries are the other legitimate case, and it is a hard constraint rather than a preference. A foreign key lives inside one database. If orders and inventory belong to different services with different data stores, no constraint can span them, and integrity has to come from events, sagas, or a reconciliation job that detects and reports drift.

What I see conflated is these two situations. The fact that you cannot have foreign keys between services gets used to justify dropping them within a service, where the database is right there and perfectly capable of enforcing the relationship. Those are different problems with different answers.

Cascades deserve more suspicion than the keys do

ON DELETE CASCADE is where I would actually spend the scepticism.

A cascade turns one delete into an unbounded amount of work that is invisible at the call site. Deleting a customer can remove orders, line items, shipments, payment records and audit rows, in one transaction, holding locks on all of them, with no indication in the statement you wrote that any of it was going to happen.

ON DELETE RESTRICT is the safer default for anything that matters. It forces the caller to delete children explicitly, in batches they control, which means the work is visible and interruptible. Cascades are appropriate for genuinely owned data with bounded fan-out, such as a row’s own translations, and questionable for anything that fans out to millions.

This connects directly to soft deletes, because setting a deleted_at column satisfies every foreign key in the schema while breaking the invariant they were protecting. The constraint sees a live row. Nothing fires. You kept the syntax and lost the guarantee, which is arguably worse than dropping the key outright, because the schema still claims the relationship is enforced.

What I would do this week

Run the unindexed foreign key query against production. Add the missing indexes. That single step resolves most performance complaints attributed to foreign keys, and it costs one index per relationship.

Check whether any cascade in the schema can fan out to more rows than you would want to delete in one transaction, and convert those to restrict.

If the constraints are already gone, write the reconciliation query that finds orphans and run it on a schedule. It will not stop them being created, and it will at least tell you when it happened, which is the diagnostic property you gave up.