I have a database on PostgreSQL 9.2 that has a main schema with around 70 tables and a variable number of identically structured per-client schemas of 30 tables each. The client schemas have foreign keys referencing the main schema and not the other way around.

I just started filling the database with some real data taken from the previous version. 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.

It was no surprise that this would take a long time but after 12 hours it became clear that I was better off starting over, dropping the DB and launching the migration again. But what if I need to repeat this operation later when the DB is live and much larger? Are there alternative, faster methods?

Would it be much faster if I wrote a script that will browse the dependent tables, starting at the table furthest from the central table, deleting the dependent rows table by table?

An important detail is that there are triggers on some of the tables.

  • 22
    After 5 years, I'm changing the accepted answer. Slow DELETEs are almost always caused by missing indexes on foreign keys that directly or indirectly reference the table being deleted from. Triggers that fire on DELETE statements can slow things down too, although the solution is almost always to make them run faster (e.g. by adding missing indexes) and almost never to disable all triggers.
    – jd.
    Aug 4, 2018 at 4:53

6 Answers 6


I had a similar problem. 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.

  • 1
    Wow, this helped me delete 8M records in a few minutes. But what I don't understand is that my table only held references to other tables, no other tables hold references to my table. So what exactly is the effect here? (I'm not using ON DELETE CASCADE)
    – msrd0
    Sep 20, 2018 at 21:01
  • 23
    This solved it for me as well. For anyone trying this, you can do an EXPLAIN (ANALYZE, BUFFERS) query on a single row delete and it should show you which foreign key constraints took the longest (at least it did for me). Oct 4, 2018 at 17:34
  • 2
    It's important to notice that if you have a foreign reference to anywhere, the source column must have real index or the performance will suffer. I'm no sure if PRIMARY index is enough but UNIQUE index is definitely not good enough for this purpose. Dec 20, 2018 at 10:51
  • 18
    For those trying to understand why: consider a foreign key from table A to table B. If you delete a row from table B the database has to verify that no rows in table A reference this row. If table A does not have an index on the referencing column, it has to sequentially scan the whole table, which could be very slow if the table is large.
    – kleptog
    Dec 19, 2019 at 17:01
  • 1
    You can have a look at your existing indices with \d+ table_name
    – Alfred Bez
    Jan 20, 2021 at 14:19

You have a few options. 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:

DELETE ...; 

A major key here is you want to minimize the depth of subqueries. In this case you may want to set up temp tables to store relevant information so you can avoid deep subqueries on your delete.

  • 1
    In my case, I started the DELETE FROM command before going to bed and it was still not done when I returned to my computer the next day. 100% CPU use on one core the entire time. After disabling the triggers and trying again it took 3 seconds to delete 200k records. Thank you! Apr 12, 2018 at 7:30
  • @Christ Great response, thanks!!! Feb 28, 2022 at 2:44

The easiest method to solve the problem is to query detailed timing from the PostgreSQL: EXPLAIN. For this you need to find at minimum a single query that does complete but takes longer than expected. Let's say that the slow query would look like

delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';

Instead of really running that query you can do

explain (analyze,buffers,timing)
delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6';

The rollback at the end allows running this without really modifying the database. You still get the detailed timing of what took how much. After running that, you may find in the output that some trigger causes huge delays:

Trigger for constraint XYZ123: time=12311.292 calls=1

The time is in ms (millisecond) so checking this contraint took about 12.3 seconds. You need to add a new INDEX over the required columns so that this trigger can be computed effectively. Note that e.g. the implementation of foreign key references use the trigger mechanism internally so you may have triggers executing without explicitly defining any triggers.

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. As a result, you may have accumulated years of data until you hit the case where DELETE is too slow due missing an index.

The reason the source column needs the index is that when you have tables X and Y, with Y.r having foreign key reference to X.id, deleting any row from table X requires checking if a row with Y.r pointing to that row in table X does exist. Without an index over Y.r PostgreSQL will need to scan whole table Y to check this. With the index the check will be quick because the index can directly tell if such value exists in Y.r.

Once you have fixed performance of that constraint (or some other thing that took overly long time), repeat the query in begin/rollback block so you can compare the new execution time to previous execution time. Continue until you're happy with the single row 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.

Note that if you add a new index and it doesn't improve performance, it might be a good idea to remove that index. Keeping any indexes up to date when new rows are added and removed will cause some performance loss so you shouldn't have indexes without a real need.

(Note that EXPLAIN needs a query that can complete successfully. I once had a problem where PostgreSQL took overly long to figure out that one delete was going to violate a foreign key constraint and in that case EXPLAIN cannot be used because it will not emit timing for failed queries. I don't know any easy to way to debug performance issues in such a case.)

  • 1
    This is clearly the best answer here and I've come back to it multiple times as it shows you how to clearly identify the issue. Thanks!
    – toxaq
    Mar 8 at 1:09
  • Note that in some cases the issue may be inaccurate statistics. For such cases, "CREATE STATISTICS" over most important columns in query may help. May 17 at 14:10

Disabling triggers may be a threat to DB integrity and cannot be recommended; however if you are sure your operation is constraint-failure-proof, you can disable triggers, with the following:

SET session_replication_role = replica;

Run the DELETE here.

To restore triggers, run:

SET session_replication_role = DEFAULT;

Source here.


If you have ON DELETE CASCADE triggers, they are hopefully there for a reason, and therefore should not be disabled. Another trick (still add your indices) that works for me is to create a delete function that manually deletes data starting with the tables at the end of the cascade, and works towards the main table. (This is the same as you would have to if you had a ON DELETE RESTRICT trigger)

    tablea_uid integer

    tableb_uid integer,
    tablea_rid integer REFERENCES tablea(tablea_uid)

    tablec_uid integer,
    tableb_rid integer REFERENCES tableb(tableb_uid)

In this case delete the data in tablec then tableb then tablea

 RETURNS void AS $$

    DELETE FROM tablec;
    DELETE FROM tableb;
    DELETE FROM tablea;


For me the trick was to drop the fk constraint from another referencing table. This referencing table was huge. But be careful, I knew that that constraint for the records I had to delete was not relevant. Therefore I could temporarily drop the constraint to add it afterwards (during which I was sure there was no other database activity).

  • 1
    You could have probably just added index to the source column of the foreign key contraint. If the source column (the table that has the constraint that points to another table) doesn't have real index (plain UNIQUE is not enough), then the constraint check requires doing full sequential scan to the source table for every row to be deleted in the target table. Jul 9, 2022 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.