Simple Problem

If you’re reading this then delete operations on your Postgres database are probably giving you some headaches.

Simple Solution

This problem can have various causes, but one common cause is a missing index on a foreign key column. To be specific, the foreign key would be referencing the table you’re trying to delete from. Double check that all foreign key references back to your table have indexes.

More Explanation

Before Postgres deletes a record, it has to check that deleting that record won’t violate a foreign key constraint.

For example, we have two tables: author and book. Each book has exactly one author, so each book record has a foreign key referencing the author table.

So, book.author_id is a foreign key that references author.id.

To DELETE FROM author WHERE id = 1, Postgres has to go and check if any book records reference author WHERE id = 1. Postgres has to do this check to ensure that deleting author WHERE id = 1 won’t violate the foreign key constraint from books to authors.

All of this is perfectly reasonable. It maintains data integrity.

Postgres automatically creates indexes on primary keys and unique constraints. However, here’s the problem: Postgres doesn’t automatically create indexes on the referencing side of foreign key relationships.

So no index is added to book.author_id unless it is added manually. It’s simple but easy to forget.

And, it can lead to huge performance issues when deleting large numbers of author records. Exactly what “large” means may vary from one system to another, but see below for a concrete example.

Real World Example

Here are some numbers from a real world situation to show how much of a difference indexes can make.

In this example, about 9k rows were being deleted from the Postgres database.

  Time
Without Index 22m 44s 891ms
With Index 1s 199ms

In this case, adding one simple index reduced execution time by more than 99.9%!

Lesson learned: indexing foreign key columns can dramatically improve DELETE performance in Postgres.