If you’re reading this then delete operations on your Postgres database are probably giving you some headaches.
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.
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:
book has exactly one
author, so each
book record has a foreign key referencing the
book.author_id is a foreign key that references
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.
|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.