We ran into some slow insert and delete queries this week. In one instance, we had to delete tens of thousands of rows from a table. In another we were updating hundreds of thousands of row in the database. In both cases we went through a similar process to analyze and optimize the queries.
Slow Delete
This is what our slow delete looked like. In this case there were about 10 million rows that needed to be deleted.
|
|
First we ran this one delete to see how long it would take.
|
|
Analyzing Slowness
When trying to speed up a slow query, PostgreSQL’s explain analyze
is your best friend. Limit the number of updates and run it with an explain analyze
to figure out what is slow. Remember to run this in a transaction if you don’t want to modify the database yet since explain analyze
will run the insert/update/delete.
|
|
The explain analyze
gives us a breakdown of the timing of the query. We had a constraint or trigger which is being checked or being triggered every row which was taking up the majority of the time.
Speeding Up the Query
To speed up these queries, we want to remove the constraints that are being checked or the triggers that are being triggered. However this could leave our database in a bad state if something is modified that breaks the constraints. To prevent this, we want to remove the constraints/triggers, update the rows, and then add back the constraints/triggers all in a transaction.
We could also do the update or delete in a single migration, since migrations are run in transactions.
Constraint
To speed up our delete, this is how we dropped the foreign key constraint and then ran our delete before restoring the foreign key constraint.
|
|
Trigger
To speed up our bulk update, we ran these commands. We actually didn’t use the begin;
and commit;
since our update was inside a migration which is already in a transaction.
|
|