I don't trust ON DELETE CASCADE

Postgres ON DELETE CASCADE is very tempting to use at first. Fully integrity checked, atomic deletion of your data. You don't need to write any application code to clean up related rows, you simply set up your foreign keys, then issue a single DELETE statement, Postgres takes care of the rest. What could be simpler? CREATE TABLE customer_organizations ( id SERIAL PRIMARY KEY , name text NOT NULL ); CREATE TABLE customer_users ( id SERIAL PRIMARY KEY , name text NOT NULL , organization_id INT NOT NULL , FOREIGN KEY (organization_id) REFERENCES customer_organizations(id) ON DELETE CASCADE ); How cascades grow Over time, your application grows, you add more tables, all doing a ON DELETE CASCADE pointing at customer_organizations . To keep the simplicity of ON DELETE CASCADE , you even have this happening recursively - tables that point at tables that point at tables, all doing ON DELETE CASCADE . Your application doesn't need to think about the relationship tree when deleting, it just picks the record to delete (an organization, a user etc etc) and Postgres takes care of things. Cascade plus migration equals outage Then one day, you get paged. Your production database is not responding properly, nearly all writes to most of your tables are timing out, and your customers are complaining. The cause: very large ON DELETE CASCADE interacting with a simple, innocent schema change adding a column to one of your tables. You fire up the PlanetScale live connections CLI and cancel the offending transaction, restoring service, outage over. Let's dig in to what happened: When Postgres processes a delete that causes a cascading delete like this, it has to issue a DELETE for matching rows, generating dead tuples, index updates and WAL, all in one transaction. While doing that, it holds RowExclusiveLock on every table involved. Your simple migration (e.g. adding a nullable text column to one of the tables involved) requests AccessExclusiveLock . The AccessExclusiveLock then blocks subsequent INSERT behind it. The ALTER TABLE can't start until the cascading DELETE finishes, so every new query on customer_users piles up behind it. You can work around this by setting a short lock_timeout on your migrations, and retrying them - instead of breaking things, your migration gets retried until there's a period where it can work. But that DELETE CASCADE causes other problems. In larger environments we've seen hundreds of gigabytes of WAL written by a …