How does Postgres implement a delete cascade internally?

Question

I've implemented a manual delete cascade in Postgres recently. I used pg_depend and DFS over this table to get the hierarchy from the objects, but it is not as fast as Postgres' own implementation. So how does Postgres implement this internally?

Check the source code: postgresql.org/ftp/source

user330315
– user330315

2016-04-13 05:58:44 +00:00
Commented Apr 13, 2016 at 5:58 — user330315
– user330315, Commented Apr 13, 2016 at 5:58

Craig Ringer · Accepted Answer · 2016-04-13 06:37:34Z

3

PostgreSQL's implementation of update/delete cascade is very simple. It's basically a for each row ... on delete ... execute procedure ... trigger that reaches out and does a delete from only [othertable] where [foreign_key_col] = OLD.[primary_key_col]. It has a few tricks that aren't available to user-level triggers, but that's the gist of it.

See RI_FKey_cascade_del in src/backend/utils/adt/ri_triggers.c for details.

Performance is OK when there's an index on the foreign (referencing) side of a FK relationship, and truly awful when the referencing side is a large table with no index on the referencing column.

PostgreSQL (or at least 9.6 and oler) is not smart enough to batch up keys to delete and do a single big DELETE FROM. It can't accumulate the pending-deletion keys in a tuplestore. It has to dispatch each delete immediately, or (if the FK relationship is deferred) accumulate it in a queue of triggers to fire, which are still fired individually.

You should be able to easily beat the performance of a cascade delete by using DELETE ... FROM ... USING ... or a DELETE ... WHERE EXISTS (...) to batch delete rows you will then delete from the parent table.

edited Apr 13, 2016 at 6:37

answered Apr 13, 2016 at 6:34

Craig Ringer

329k84 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Elad Over a year ago

9.6? The latest version is 9.5

Craig Ringer Over a year ago

@Elad 9.6 just hit feature freeze, with beta planned. So 9.6 won't have any such capability either.

Wellington Silva Ribeiro Over a year ago

@CraigRinger thanks for help, i already had thought in something like that is really a good and easy trick.

Collectives™ on Stack Overflow

How does Postgres implement a delete cascade internally?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related