Lorixen.
EN

Slow deletes in Postgres? Index your foreign key columns

Short answer: If deleting a customer, product or account in a PostgreSQL-backed app takes seconds or times out, while other queries are fast, check the foreign keys pointing at that table. PostgreSQL doesn't create an index on the referencing column when you declare a foreign key, so every delete or key update has to scan the child tables to check for matching rows. Add indexes on foreign key columns in large child tables, using CREATE INDEX CONCURRENTLY on a live database, and deletes usually become fast again.

Why deletes get slow

A foreign key says "every order.customer_id must match a row in customers". PostgreSQL enforces it in both directions:

  • When you insert or update an order, it checks that the customer exists. That lookup uses the customer table's primary key index, so it's quick.
  • When you delete a customer, or change its key, PostgreSQL must check whether any orders still point at it, or apply the ON DELETE rule, such as cascading or setting to null.

That second check is where the trouble is. The PostgreSQL documentation explains that because a delete from the referenced table, or an update of a referenced column, requires a scan of the referencing table for rows matching the old value, it's often a good idea to index the referencing columns too. It then states plainly that declaring a foreign key constraint doesn't automatically create an index on the referencing columns, because it isn't always needed and there are many ways to index.

Without that index, every customer deletion reads the whole orders table, plus every other table that references customers, such as invoices, notes, addresses or bookings. On small tables, nobody notices. Once those tables hold years of data, deletes crawl, lock rows for longer and can time out.

Symptoms

  • "Delete account" or "remove product" actions are slow or time out, while viewing and editing are fast.
  • Admin bulk-deletes or data cleanup jobs take far longer than expected.
  • ON DELETE CASCADE chains feel sluggish, because each level scans its child table.
  • Merging duplicate customers, which updates many references, is slow.
  • Lock waits appear during cleanup jobs, as long-running deletes hold locks on rows others need.

Find the missing indexes

  1. List foreign keys on your tables. Your schema tool, ORM migrations or the system catalogues will show each constraint's referencing table and columns.
  2. Check each for an index whose leading column or columns match the foreign key's columns. A composite index starting with customer_id counts; one with customer_id second usually doesn't help this check.
  3. Prioritise big, busy child tables referenced by rows you delete or re-key.

Several community queries list foreign keys without supporting indexes; many database monitoring tools flag them too.

Confirm with EXPLAIN ANALYZE

Run the slow delete inside a transaction you roll back, with EXPLAIN ANALYZE, on a staging copy:

  • Start a transaction, run EXPLAIN ANALYZE DELETE FROM customers WHERE id = 123;, then roll back.

PostgreSQL's documentation on EXPLAIN notes that the reported execution time includes time spent running triggers that fire, and foreign key checks are carried out by internal triggers, so the output typically shows time spent per constraint. A large figure against one constraint points to the child table that needs an index.

Add the indexes safely

On a production database, a plain CREATE INDEX blocks writes to the table while it builds. For live systems, use CREATE INDEX CONCURRENTLY, which builds without blocking normal reads and writes, at the cost of taking longer and not being allowed inside a transaction block. Run it during a quieter period, and check afterwards that the index is valid; if a concurrent build fails, it can leave an invalid index you need to drop and recreate.

Then rerun your EXPLAIN ANALYZE test. The constraint check time should drop sharply.

Don't index blindly

Every index costs disk space and slows inserts and updates a little. You don't need indexes for:

  • Foreign keys on small lookup tables that rarely change.
  • References to parent rows you never delete or re-key.
  • Columns already covered as the first column of another index.

Index the foreign keys on large tables where parents are deleted, merged or re-keyed. Many of those indexes help normal queries too, such as "show this customer's orders".

Large cleanups need batching too

Even with the right indexes, deleting hundreds of thousands of rows in one statement holds locks for a long time and produces a burst of write activity. For big cleanups, delete in batches of a few thousand rows, committing between batches, and pause briefly if the database is busy. Combined with the indexes, that keeps the app responsive while old data is removed.

Make it part of your schema habits

  • When adding a foreign key in a migration, add the matching index in the same migration unless you've decided it isn't needed.
  • Note the decision in a comment if you skip the index on purpose.
  • Review slow-query logs after releases that add new relationships.

A worked example

Consider a hypothetical Brighton subscription-box company whose admin tool for deleting test customers and spam sign-ups had become painfully slow. A rolled-back EXPLAIN ANALYZE of one delete showed most of the time spent checking two constraints: from the shipments table and the event log table, both large, neither indexed on customer_id. The developer added both indexes with CREATE INDEX CONCURRENTLY on a quiet evening, verified they were valid, and the delete that had taken several seconds became near-instant. The team now adds foreign key indexes in the same migration as the constraint.

FAQ

Does MySQL behave the same way?

MySQL's InnoDB engine requires an index on foreign key columns and creates one if missing. PostgreSQL leaves the choice to you.

Will the index speed up normal page loads too?

Often, yes, for queries that filter child rows by their parent.

Can I add many indexes at once?

You can, but building them one at a time with CONCURRENTLY spreads the load on a live system.

Is cascading delete the problem?

Cascades add work, but the main cost is usually the missing index on each child table.

If parts of your app slow down whenever records are deleted, tell us which tables are involved and we'll help find the missing indexes.

Sources

WhatsAppBook a call