The Assumption That Destroys PostgreSQL Latency

If you come from a MySQL (InnoDB) or Microsoft SQL Server background, you have likely grown accustomed to a helpful convenience: whenever you define a foreign key constraint, the database engine quietly creates a backing index on that column automatically.

When developers switch to PostgreSQL, they carry that exact mental model with them:

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  total_amount NUMERIC(10, 2) NOT NULL
);

You run the migration. The table is created. The foreign key constraint is enforced. Everything looks pristine.

Except PostgreSQL did NOT create an index on customer_id.

“Of all the performance traps developers encounter when adopting PostgreSQL, unindexed foreign keys are by far the most insidious. Everything runs quickly in staging with 1,000 rows. In production with 10 million rows, deleting a single parent row suddenly halts the entire database.” — Senior PostgreSQL DBA & Reliability Engineer

Here is why PostgreSQL does not automatically index foreign keys, what disaster occurs when you omit them, and how to detect and prevent them automatically.

---

Why Doesn’t PostgreSQL Auto-Index Foreign Keys?

The PostgreSQL core engineering team has debated this design decision for decades. The rationale comes down to philosophical control over write amplification:

1. Every Index Has a Cost: Indexes consume disk space and must be updated on every single INSERT, UPDATE, and DELETE.
2. Not All Foreign Keys Need Indexes for Reading: If you only join from orders to customers (orders.customer_id = customers.id), the primary key index on customers(id) is sufficient for the join.
3. The Index is on the Parent: The primary key or unique constraint on the referenced (parent) table is already indexed. Postgres leaves the decision of indexing the referencing (child) table to the database engineer.

While logically sound in theory, in real-world application engineering, omitting foreign key indexes almost always leads to a production outage.

---

The Disaster: What Happens When You Delete a Parent Record?

To understand why unindexed foreign keys are catastrophic, consider what happens when you run this simple query:

DELETE FROM customers WHERE id = '8c33f272-969c-43f1-b9cb-1b918a58f4a1';

Because orders has a foreign key referencing customers(id) with ON DELETE CASCADE (or RESTRICT), PostgreSQL must verify whether any rows in orders belong to this customer.

Scenario A: With an Index on orders(customer_id)

PostgreSQL performs an Index Scan on orders. It locates the customer’s 3 orders in 0.04 milliseconds, deletes them, and commits the transaction immediately.

Scenario B: Without an Index on orders(customer_id)

Because there is no index, PostgreSQL has no way of finding matching rows other than scanning the physical table from start to finish. It launches a Sequential Scan (Seq Scan) across all 5,000,000 rows in orders.

QUERY PLAN:
Delete on customers  (cost=0.00..8.27 rows=1)
  ->  Index Scan using customers_pkey on customers
Trigger for constraint orders_customer_id_fkey on customers:
  ->  Seq Scan on orders (cost=0.00..128452.00 rows=5120000 width=6)
        Filter: (customer_id = '8c33f272-969c-43f1-b9cb-1b918a58f4a1'::uuid)

The Domino Effect: Table-Level Locking

It gets worse. While PostgreSQL is performing that sequential scan across millions of rows, it acquires a ROW SHARE / lock on the orders table. Any concurrent write, update, or migration trying to touch orders gets queued behind it.

Your database connection pool exhausts itself within seconds, request queues back up, and your entire web application throws 504 Gateway Timeout errors.

---

The Query to Find All Missing Foreign Key Indexes in Your Database

You can run this query against any production PostgreSQL database right now to discover every unindexed foreign key in your schema:

SELECT
    c.conrelid::regclass AS child_table,
    c.conname AS foreign_key_name,
    STRING_AGG(a.attname, ', ' ORDER BY x.n) AS fk_columns,
    c.confrelid::regclass AS parent_table
FROM pg_constraint c
JOIN LATERAL UNNEST(c.conkey) WITH ORDINALITY AS x(attnum, n) ON TRUE
JOIN pg_attribute a ON a.attnum = x.attnum AND a.attrelid = c.conrelid
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND (i.indkey::smallint[])[0 : ARRAY_LENGTH(c.conkey, 1) - 1] @> c.conkey
  )
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY child_table;

If this query returns rows, those child tables are actively at risk of sequential scans during delete or update operations!

---

How to Fix It Safely in Production

When adding indexes to large production tables, never run a standard CREATE INDEX, as it will lock the table against writes:

-- DANGEROUS on production with millions of rows:
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- SAFE in production: Does not block concurrent reads or writes!
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

---

Automatic Prevention with Sepio’s Schema Linter

Remembering to manually write index statements for every single foreign key in your DDL or Drizzle schema is error-prone.

This is why Sepio includes a built-in, real-time AST Diagnostic Engine:

• Whenever you drag a relationship line between two tables on the Sepio canvas, Sepio’s linter automatically verifies whether a covering index exists on the child table’s foreign key column.
• If missing, a visual amber warning badge appears on the relationship line with a 1-click “Add Covering Index” action.
• When exporting to Drizzle ORM, Prisma, or raw SQL DDL, Sepio ensures that all foreign keys have clean, explicit indexes generated automatically.

---

Frequently Asked Questions

Does every foreign key column need an index?

In 98% of cases, yes. The only exception is tiny lookup tables that will never exceed a few dozen rows (e.g., a static table of 5 subscription tiers) and where rows are never deleted. For all customer-generated or variable-sized tables, omitting an index on a foreign key is an architectural bug.

Does a composite index count as a foreign key index?

Yes, provided the foreign key column is the leading (leftmost) column in the composite index. For example, an index on (customer_id, created_at) will satisfy PostgreSQL’s lookup requirements for foreign key checks on customer_id. However, an index on (created_at, customer_id) cannot be used for foreign key checks because B-Trees can only seek using prefix columns.

Does MySQL InnoDB require foreign key indexes?

Yes. MySQL’s InnoDB storage engine will automatically create an index on any foreign key column if one does not already exist. PostgreSQL does not do this, which is why developers migrating from MySQL often experience sudden latency drops.