26

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.

  • 3
    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 '18 at 4:53
24

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.

  • 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 '18 at 21:01
  • 1
    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). – Justin Workman Oct 4 '18 at 17:34
  • Same, had to delete on cascade 600k rows and at the beginning it was taking between 2-10 per operation with 100% CPU usage. Now it took only few minutes to delete them all with 80% CPU usage. – fillobotto Oct 18 '18 at 8:43
  • 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. – Mikko Rantalainen Dec 20 '18 at 10:51
24

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:

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. In this case you may want to set up temp tables to store relevant information so you can avoid deep subqueries on your delete.

  • 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! – Nick Woodhams Apr 12 '18 at 7:30
  • 2
    Wow this as answer save my day. It took only 9 seconds to delete 2M records. – Mankeomorakort May 21 '18 at 14:10
  • Holy cow, it works for foreign keys too! I had no idea! – expert Jul 13 '18 at 16:14
10

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 this line would look like

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

Instead of really running that command you can do

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

The rollback in the end allows running this without really modifying the database but 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. 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.

Once you have fixed performance of that constraint (or some other thing that took overly long time), repeat the command in begin/rollback block so you can compare the new execution time to previous. 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 simply by adding different indexes). Then you can proceed to complete your full delete without any hacks.

(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.)

  • 2
    buffers should have an 's' in explain (analyze,buffers,timing) – Jordan Jul 24 at 18:35
  • This is great. Solved my problem instantly (well after I added that one missing index). Be good to fix the buffers typo as per @Jordan's comment. – toxaq Aug 8 at 22:09
  • 1
    @Jordan @toxaq thanks, fixed the typo about buffers. – Mikko Rantalainen Aug 9 at 12:50
8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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