Speeding Up Batch Deletes and Updates in PostgreSQL

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.

1

delete * from pokemon_box where pokedex_no=6;

First we ran this one delete to see how long it would take.

1

delete * from pokemon_box where id in (select id from pokemon_box where pokedex_no=6 limit 1);

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.

1
2
3
4
5
6

begin; -- Begin a transaction
-- Delete a single row
explain analyze delete * from pokemon_box where id in (select id from pokemon_box where pokedex_no=6 limit 1);
-- Delete 10 rows
explain analyze delete * from pokemon_box where id in (select id from pokemon_box where pokedex_no=6 limit 10);
abort; -- Rollback or abort the transaction

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.

1
2
3
4
5
6
7
8
9

begin;
-- Drop foreign key constraint that is slowing down delete
alter table items_in_bag drop constraint items_in_bag_pokemon_holding_item_id_fkey;

delete * from pokemon_box where pokedex_no=6;

--Restore database state
alter table items_in_bag add foreign key (pokemon_holding_item_id) references pokemon_box(id);
commit;

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.

1
2
3
4
5
6
7
8
9

begin;

alter table [TABLE_NAME] disable trigger [TRIGGER_NAME];

update [TABLE_NAME] set [UPDATE] where [CONDITION];

alter table [TABLE_NAME] disable trigger [TRIGGER_NAME];

commit;