Foreign keys feel automatic. You declare REFERENCES customers(id) and Postgres handles the rest. Mostly. There is one piece it does not handle: the index on the child side.
Most teams I have worked with did not realize this and paid for it later. The symptom is consistent — DELETE FROM customers WHERE id = 1 is much slower than expected, even on a small parent table.
Here is why and what to do.
What Postgres does automatically
When you declare a foreign key:
CREATE TABLE orders (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
...
);
Postgres ensures customer_id always references a valid customers.id. It does this by checking the parent on every insert/update of customer_id. The parent's primary key is already indexed, so the lookup is fast.
What Postgres does NOT do: index orders.customer_id. The child column has no index by default. This affects every operation that needs to look up child rows by parent.
The most expensive case: cascading delete
ALTER TABLE orders
ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;
-- Now delete a customer:
DELETE FROM customers WHERE id = 1;
For the cascade to work, Postgres needs to find every orders row with customer_id = 1 and delete those too. Without an index on orders.customer_id, this is a full table scan of orders.
On a 100-million-row orders table, deleting one customer can take 30 seconds. Multiplied by hundreds of customers in a cleanup batch, the operation that should take a minute takes hours.
The same applies to ON DELETE SET NULL and ON DELETE RESTRICT — both need to find the child rows before they can act.
The query to find missing FK indexes
This is one of the most useful diagnostic queries to keep around:
SELECT
c.conrelid::regclass AS child_table,
string_agg(a.attname, ',' ORDER BY array_position(c.conkey, a.attnum)) AS fk_columns,
c.conname AS fk_constraint
FROM pg_constraint c
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 (c.conkey::int[]) <@ (i.indkey::int[]::int[])
)
GROUP BY c.conrelid, c.conname, c.conkey
ORDER BY c.conrelid::regclass;
This lists every foreign key whose child columns are not covered by an index. On most production databases I have looked at, this returns at least a few rows.
Adding the index without locking
The fix is straightforward — add the index. For a busy production table, do it concurrently:
CREATE INDEX CONCURRENTLY orders_customer_id_idx
ON orders (customer_id);
For composite foreign keys (rare but real), the index column order should match the FK declaration:
CREATE TABLE order_items (
order_id BIGINT,
product_id BIGINT,
FOREIGN KEY (order_id, product_id) REFERENCES order_lines(order_id, product_id)
);
CREATE INDEX CONCURRENTLY order_items_order_product_idx
ON order_items (order_id, product_id);
What about composite indexes that already cover the FK
If you have an index on (customer_id, created_at), that index also serves the FK lookup on customer_id (because of the leading-column rule). You do not need a separate single-column index.
The FK index check query above accounts for this — it looks for any index whose leading columns match the FK columns, not just exact-match indexes.
Cost vs benefit
Is there a case where adding an FK index is wrong? Rarely. The cost:
- Storage: an extra index per FK column.
- Write throughput: every insert/update of the FK column writes to the additional index.
The benefit: cascading delete is fast, parent-side lookups (SELECT * FROM orders WHERE customer_id = X) are fast, JOINs against the parent benefit.
For most tables, the benefit dominates. The exception: a heavily-written child table with a FK that is never queried. In that rare case, the index is pure write tax. But in practice, if a FK exists, applications usually do query by it eventually.
What I check for new schemas
Whenever I review a migration that adds a foreign key, I look for the corresponding index:
-- Bad: only the FK
CREATE TABLE orders (
...
customer_id BIGINT NOT NULL REFERENCES customers(id)
);
-- Good: FK + matching index
CREATE TABLE orders (
...
customer_id BIGINT NOT NULL REFERENCES customers(id)
);
CREATE INDEX orders_customer_id_idx ON orders (customer_id);
For multi-million-row tables it makes a measurable difference. For small tables it is cheap insurance. Either way, the discipline is to write the index alongside the FK, not to add it later when the slow query lands on someone else's desk.
The diagnostic query above is worth running quarterly. New code adds new FKs; the indexes do not always come with them. Catching it before it matters is cheaper than catching it during an incident.