Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
Is it possible that you have ON DELETE triggers somewhere down the cascading chain? –  dezso Mar 19 '13 at 16:49
2  
Are there indexes on the foreign key columns of the child tables? I haven't tested it with postgresql, but with some databases (cough oracle cough) that can make a huge difference. –  gsiems Mar 19 '13 at 16:53
    
@dezso Yes, there are triggers actually. I didn't think of that. Any way to suspend them? –  jd. Mar 19 '13 at 16:59
1  
@jd. You can ALTER TABLE tablename DISABLE TRIGGER ALL; DELETE ...; ALTER TABLE tablename ENABLE TRIGGER ALL; –  dezso Mar 19 '13 at 17:08
3  
Verify that your business logic won't be compromised by disabling the triggers! Perhaps batch deleting would be a better idea. –  Jon Seigel Mar 19 '13 at 17:22

1 Answer 1

up vote 2 down vote accepted

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.

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.

share|improve this answer
    
Batch delete: is that what I proposed in the question, deleting related rows in the child tables first? The query I'm running now is a simple DELETE FROM table WHERE id = 1;. –  jd. Mar 20 '13 at 13:28
    
yes, delete from child tables and disable triggers. You can do this entirely within a transaction and no outside applications will see triggers disabled. –  Chris Travers Mar 20 '13 at 13:42
    
OK. Is there any tool that will write the DELETE query? That sounds like something that should be automated. Anyway, taking your advice and that in the comments, I truncated the largest tables that would have been empty after the DELETE, added indexes on foreign keys (at least on all large tables) and disabled triggers. That brought the query down to 20 minutes (I also deleted less rows, but that's still a huge decrease). Thanks for your help. –  jd. Mar 20 '13 at 14:15
1  
For those that find this thread later: kirk.webfinish.com/?p=360 "create a list of delete statements that follow referential integrity in PostgreSQL" –  Kirk Roybal Dec 20 '13 at 15:28
2  
Also, check this thread: postgresql.org/message-id/16186.1150464632@sss.pgh.pa.us –  Kirk Roybal Dec 20 '13 at 15:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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