“How did it get late so soon?”
Photo by Murray Campbell on Unsplash
Performance
I recently had a query from a colleague regarding a slow DELETE query on PostgreSQL. On the face of it, it was simple enough. It had no CASCADE , and they were only deleting a few records at a time using a subquery (in the absence of a LIMIT for DELETE ). This small batch approach was working, but it was too slow to keep up, resulting in a growing backlog.
Before making any changes, I had a look at the performance to run the query for a batch of 20 records. It took around 1400ms to complete. After a bit of poking around, I got the same query to complete in 12ms. Here’s how.
Background
PostgreSQL primary keys have indexes associated with them by default. Because of this, people automatically assume the same of foreign keys, and use them liberally. Unfortunately (although sensibly), foreign keys are not indexed by default. That’s the first caveat.
The second caveat is that, if a record being deleted is referenced via a foreign key, PostgreSQL runs a trigger to check whether that record’s removal will result in a referential integrity violation. This happens per record, per reference. If you are removing 100 records, each with two references, PostgreSQL runs 200 triggers each with a function call associated with them. Those triggers need to check whether the primary key being deleted exists in the list of foreign keys in the related table, and if that list is long and unindexed, it can take a while.
Consider the case above of 100 records and 200 triggers: if the list of foreign keys contains a million records and no index, you’re running a sequential scan of (worst case) the whole million references, and you’re doing that 200 times.
An EXPLAIN ANALYZE can quickly highlight this, if you suspect you have a similar problem.
Changes
As it happens, my colleague’s two foreign keys were unindexed, and the backlog was large. Because the backlog was growing, the queries were running increasingly slower as the sequential scans had more work to do.