CODEWITHSUNDEEP
postgresql
Wellington Silva Ribeiro
Wellington Silva Ribeiro

Reputation: 201

How does Postgres implement a delete cascade internally?

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?

Upvotes: 1

Views: 533

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324501

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.

Upvotes: 3

Related Questions