When working with relational data in PostgreSQL, it’s common to define foreign key relationships to maintain referential integrity. However, one subtle but important performance issue arises if you forget to add an index on those foreign key columns—especially when you use ON DELETE CASCADE. Without that index, deletions can become dramatically slower, sometimes by orders of magnitude.

What happens during a delete

When you delete a row from a table that has one or more referencing tables, PostgreSQL must check whether any rows in those referencing tables depend on the row being deleted. If the foreign key is defined with ON DELETE CASCADE, PostgreSQL must not only check but also delete all those dependent rows.

For every deleted parent row, PostgreSQL executes a trigger function that scans each referencing table to find matching rows.

If the foreign key column in the child table is not indexed, PostgreSQL has no choice but to perform a sequential scan—it looks through the entire child table to find those matches.

This means:

• Deleting a single parent row requires scanning the entire child table.
• Deleting multiple parent rows repeats that process for each deleted row.
• The cost grows multiplicatively with both the number of deleted parent rows and the size of the child table.

As a result, what should be a quick delete can degrade into a full-table scan repeated hundreds or thousands of times.

Why indexes make such a difference

Adding an index on the foreign key column in the child table allows PostgreSQL to perform a fast lookup instead of scanning the entire table.

With an index:

• The trigger can locate matching rows in logarithmic time using the index.
• Cascaded deletes become efficient, even when the referencing table is large.
• Referential integrity checks run quickly, keeping transactions short and reducing lock contention.

Without an index:

• Deletes can take seconds or minutes instead of milliseconds.
• The delete operation can block other queries for long periods.
• Autovacuum has more work to do cleaning up dead tuples from those slow operations.

In large systems, this can even cause cascading performance issues—queues backing up, long-running transactions piling up, and increased I/O load.

Example

Consider two tables:

CREATE TABLE parent (
  id bigint PRIMARY KEY
);

CREATE TABLE child (
  id bigint PRIMARY KEY,
  parent_id bigint REFERENCES parent(id) ON DELETE CASCADE
);

If you forget to add an index on child.parent_id and execute:

DELETE FROM parent WHERE id IN (SELECT id FROM parent LIMIT 100);

PostgreSQL will:

1. Delete 100 parent rows.
2. For each deleted parent row, scan the entire child table to find matches.
3. Potentially perform hundreds of full scans if the child table is large.

Now add the index:

CREATE INDEX CONCURRENTLY idx_child_parent_id ON child (parent_id);

The same delete will instead use the index to find child rows directly. The query time typically drops from seconds (or even minutes) to milliseconds—a 100× or more improvement in performance.

When to add foreign key indexes

As a general rule:

• Always add an index on every foreign key column, even if you’re not currently using cascade deletes.
• Especially ensure indexes exist for any foreign keys that participate in ON DELETE CASCADE or ON UPDATE CASCADE relationships.
• Check every level of a cascade chain—each child table that references another should also have its own foreign key index.

You can safely create the index concurrently in production:

CREATE INDEX CONCURRENTLY idx_table_foreign_key ON table (foreign_key_column);

This avoids write blocking while the index builds.

Summary

Indexes on foreign key columns are not just about query performance—they are essential for maintaining fast, predictable deletes in PostgreSQL. Without them:

• Cascaded deletes can trigger full-table scans and multiply query times.
• Referential integrity checks slow down dramatically.
• Long-running transactions can block other operations.

With them:

• Deletions are efficient.
• Cascade operations scale linearly instead of exponentially.
• Your database remains responsive and stable under load.

If you’re seeing unexpectedly slow deletes in PostgreSQL, check your foreign keys first. Adding the missing indexes is often the simplest and most effective fix.