optimizing a particular case of bulk deletion in postgresql
The usual advice when you complain about slow bulk deletions in postgresql is "make sure the foreign keys (pointing to the table you are deleting from) are indexed". This is because postgresql doesn't create the indexes automatically for all the foreign keys (FK) which can be considered a degree of freedom or a nuisance, depends how you look at it. Anyway, the indexing usually solves the performance issue. Unless you stumble upon a FK field that is not indexable. Like I did.
The field in question has the same value repeated over thousands of rows. Neither B-tree nor hash indexing works so postgresql is forced to do the slow sequential scan each time it deletes the table referenced by this FK (because the FK is a constraint and an automated check is triggered). Multiply this by the number of rows deleted and you'll see the minutes adding up.
There is another way to deal with slow constraints: disable them completely at table level. For foreign key related constraints you also need to be a superuser. No way around this limitation. So this is what I do logged in as 'postgres':
- ALTER TABLE mytable DISABLE TRIGGER ALL;
- # bulk deletion from mytable here
- ALTER TABLE mytable ENABLE TRIGGER ALL;
Of course, now it's up to me to make sure that the database is consistent and each FK points to an existing entry, but the speed gain is totally worth it.
Category: postgresql
Leave a Comment :
Leave a Comment