Very slow DELETE in PostgreSQL, workaround?

Question

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.

Answer 1

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.

Answer 2

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

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

The rollback at the end allows running this without actually modifying the database. You still get the detailed timing of what took how much. After running the above EXPLAIN with the transaction, 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 for this example. 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. Every index will have a small performance penalty when new rows are added and removed, so if adding an index doesn't improve performance for your workload, you should remove that index to avoid overhead caused by maintaining that index. It's also worth mentioning that you should optimize for the workload you actually need. Optimizing for some imaginary workload that doesn't actually happen is a sure way to get suboptimal performance in practice.

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

Answer 3

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.

Answer 4

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.

Answer 5

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

Answer 6

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)

CREATE TABLE tablea (
    tablea_uid integer
);

CREATE TABLE tableb (
    tableb_uid integer,
    tablea_rid integer REFERENCES tablea(tablea_uid)
);

CREATE TABLE tablec (
    tablec_uid integer,
    tableb_rid integer REFERENCES tableb(tableb_uid)
);

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

CREATE OR REPLACE FUNCTION delete_in_order()
 RETURNS void AS $$

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

$$ LANGUAGE SQL;

Answer 7

I had a similar case and I got the EXPLAIN plan of the delete using:

EXPLAIN (ANALYZE, BUFFERS, TIMING)

In my case I detected that one trigger executed to check one FK was taking a lot of time.

Planning Time: 0.074 ms
Trigger for constraint fk_preparation_brochure_publication_profile_mapping_brochure_id: time=0.239 calls=1
Trigger for constraint preparation_brochure_custom_linkout_brochure_fk: time=3.737 calls=1
Trigger for constraint preparation_brochure_linkout_mapping_brochure_fk: time=18901.130 calls=1
Trigger for constraint fkks411c7rnuajq50lxutlfjygb: time=26.569 calls=1
Trigger preparation_brochure_history_trigger: time=37.139 calls=1
Execution Time: 18972.871 ms

Finally I found the reason: the original column was type varchar(200) and the referenced column in the FK was var(200).

Once I have aligned the referenced column with the type of the original one the deletion of the row was decreasing from around 20 seconds to few milliseconds.