PostgreSQL

PostgreSQL Foreign Key Indexing Strategy for Join Heavy Queries

5 min read by DebuggedIt

Quick answer

PostgreSQL creates an index on the referenced primary key but not on the foreign key column that points to it. Without an index on the referencing column,...

PostgreSQL creates an index on the referenced primary key but not on the foreign key column that points to it. Without an index on the referencing column, joins and lookups by parent can scan the whole child table, and deletes or updates on the parent become slow. Index foreign keys that you join on or that need fast parent deletes, but avoid duplicating indexes you already have.

Why Are Foreign Key Columns Not Indexed Automatically?

When you declare a foreign key, PostgreSQL requires a unique index on the referenced columns, which a primary key already provides. The referencing side is a different story. The database does not assume you will search the child table by that column, so it leaves the choice to you.

This surprises many developers coming from other systems. The constraint guarantees consistency, but it does not make queries fast. A table of orders with a customer_id foreign key will happily scan every order to find one customer's orders if no index exists on customer_id.

The cost is invisible on small tables and brutal on large ones, which is why the problem often appears only after a table has grown in production.

Where Does a Missing Foreign Key Index Hurt?

There are two main places. The first is the obvious one: queries that filter or join on the child column. The second is less visible and often more damaging: maintenance of the constraint itself.

  • Joins from parent to child: fetching all orders for a customer needs an efficient lookup by customer_id.
  • Parent deletes and key updates: PostgreSQL must check that no child rows reference the parent, and without an index that check scans the child table for every deleted parent row.
  • Cascading actions: ON DELETE CASCADE has to find the child rows to remove, which also needs the index.
  • Lock duration: slow checks keep locks longer, which increases contention with concurrent writes.

A bulk delete of old parent records can therefore take minutes or hours when the child table is large and unindexed, even though the delete itself looks simple.

How Should You Design the Indexes?

Start by indexing foreign keys that appear in joins or frequent filters. A plain B-tree index on the referencing column is usually enough. If queries combine the foreign key with another filter or sort, a composite index with the foreign key first often serves both purposes.

CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- composite version when queries also sort by date
CREATE INDEX idx_orders_customer_created
  ON orders (customer_id, created_at DESC);

The composite index can also support lookups by customer_id alone, since that column is the leftmost one. In that case the single-column index becomes redundant and can be dropped, which saves space and write overhead.

On a busy production table, create indexes with the concurrent option to avoid blocking writes, and check the documentation for its limitations, such as not being allowed inside a transaction block.

When Can You Skip the Index?

Every index slows down inserts and updates and consumes storage, so do not add them blindly. Skip the index when the child table is small, when you never query by that column, and when parent rows are never deleted or have their keys changed.

Look at real usage before deciding. The statistics views show how often each index is scanned, so you can find indexes that are never used. Review them over a representative period, because a rarely used index may still protect a critical monthly job.

  • Check for foreign keys without a supporting index using a catalog query or a schema review tool.
  • Confirm with EXPLAIN that your join queries actually use the new index.
  • Drop indexes that are duplicated by a wider composite index with the same leading column.
  • Reassess after major changes in data volume or query patterns.

Frequently Asked Questions

Does PostgreSQL automatically index foreign keys?

No. It indexes the referenced primary or unique key, but not the referencing column. You must create that index yourself.

Do I need an index on every foreign key?

Not always. Add one when you join or filter on the column, or when parent deletes and updates matter. Small, rarely queried tables may not need it.

Why is deleting a parent row so slow?

PostgreSQL has to verify or cascade to child rows, and without an index on the foreign key it scans the child table for each deletion.

Is a composite index good enough for the foreign key?

Yes, if the foreign key column is the leftmost column of the index. Then a separate single-column index is usually unnecessary.

Summary

Foreign key constraints do not create indexes on the referencing column, so add them where joins and parent deletes need speed. Prefer composite indexes that serve several queries, create them concurrently in production, and remove redundant ones. Consult the PostgreSQL documentation for your version.

You might also like