Contents
Do you need an index for delete in PostgreSQL?
For foreign key references the column that references to another table must be indexed (that is, the source column, not the target column). PostgreSQL does not automatically create such indexes for you and DELETE is the only common query where you really really need that index.
Which is the best way to batch delete in PostgreSQL?
The best option is to run a batch delete so that triggers are not hit. Disable the triggers before deleting, then re-enable them. This saves you a very large amount of time. For example: ALTER TABLE tablename DISABLE TRIGGER ALL; DELETE …; ALTER TABLE tablename ENABLE TRIGGER ALL; A major key here is you want to minimize the depth of subqueries.
How big of a database does PostgreSQL have?
The DB had reached about 1.5 GB (it’s expected to grow to several 10s GB within weeks) when I had to do a bulk delete in a very central table in the main schema. All concerned foreign keys are marked ON DELETE CASCADE.
Why is delete Cascade trigger slow in PostgreSQL?
As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow. I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.
How long does delete query take in PostgreSQL?
We are running a very simple delete query on it which takes 45 minutes to complete: 1- Added an index on timestamp column, which did not help. 2- Removed the rows in batches of 20 or 50 using a function, which was still awfully slow.
How to speed up delete time in PostgreSQL?
Continue until you’re happy with the single line delete response time (I got one query to go from 25.6 seconds to 15 ms or about 1700x faster simply by adding different indexes). Then you can proceed to complete your full delete without any hacks.