TIL: Be careful with Postgres cascades

It doesn't matter what kind of database you use, eventually you will learn to fear it.

With Val Town we've been using Postgres, and a lot of our foreign keys have cascade constraints set. So, for example, a user in the users table might have some settings in the settings table, and because the settings table has a user_id foreign key and a cascade relationship, if that user is deleted then the settings will be too, automatically.

We were deleting some ancient data from a table like this, and the deletes took forever. Deleting a single row would hang for minutes and take down the prod database. Any guesses?

ON DELETE CASCADE is kind of like an implicit select, on a different table, that runs every time that you delete a row. So, if you have a foreign key relationship that has a huge or poorly-indexed table on one side of it, guess what: each and every deletion will have huge unexpected overhead.

I'm not a big fan of CASCADE relationships for this reason, but the foreign key-ness might be enough to trigger this performance bottleneck.

It is a good reminder that a lot of the very good, helpful magic in relational database is also implicit behavior. Not only can triggers and constraints affect the behavior of operations on a table, the triggers and constraints on another table's foreign keys can affect the performance of a table.