ON DELETE CASCADE, shards and replicas: three database decisions people regret later

PlanetScale published three Postgres texts in one week: why you shouldn't trust ON DELETE CASCADE, how to design shards, and what is new in their sharded Neki. A thirty-person company won't need shards for a long time. But cascading deletes and the question of what counts as deleted apply to any database, even one that sits on a single virtual machine and stores requests, estimates and customers.

What came out

On October 21, PlanetScale explained why it doesn't trust ON DELETE CASCADE. The temptation is clear: declare the foreign keys, and Postgres deletes the child rows itself. The problems come later. A big cascading delete holds locks on every affected table, and a routine migration like adding a column queues up behind it and drags all new queries along: for them this ended with writes stopping in production. A single DELETE like that in large environments wrote hundreds of gigabytes of WAL per transaction. A foreign key without an index turns deleting one row into a full table scan. And with logical replication the subscriber doesn't run cascades, so if it has a child table of its own, orphans stay there without a single error. Their answer is soft delete. One row gets a deleted_at, and the real DELETEs run later, in batches, during quiet hours, for example after 30 days.

On October 23 the same team broke down shard design in Neki. The unit of design there is the shard group: tables that hash the key the same way put rows with the same customer_id on the same shard, so a customer's orders, line items and addresses are always together. Lookup tables like countries and currencies are declared reference tables and copied to every shard. A catalog can be sharded by lower(sku) so that AB-1042 and ab-1042 land in the same place. One caveat: a transaction inside a single shard keeps Postgres semantics, a transaction across two shards has no atomic commit.

On October 20, in a roundup of Neki updates, they wrote that in a month of preview they added read-only replicas for shards, Metal nodes from $50 a month going down to 1/8 vCPU, 1 GB of memory and 10 GB of NVMe, a launch in Azure, support for materialized views, LISTEN/NOTIFY and cursors, and COPY TO STDOUT now works on sharded tables, so pg_dump can read the data. Distributed INSERT ... SELECT, by their account, got roughly 76 times faster than at the start of the preview.

Why this matters to a business

Take a car rental company. A manager deletes a customer, because the customer asked or by mistake. If the database has cascades on, the customer's bookings, handover reports, signatures and inspection photos go with them. A month later a damage dispute comes in, and there is nothing to prove it with. Without the cascade, with a “deleted” mark instead, the customer disappears from the lists, but the history is intact and the decision can be reversed within 30 days.

In a business system, deletion should be a status that can be undone. Shards and replicas are for when one machine stops coping, and most companies are a long way from that. I come across databases with no index on a foreign key far more often than databases that have outgrown one server.

How I do it

MariaDB is in almost all of my projects, and I prefer not to turn cascading deletes on: the relations are declared, and the logic on the server decides what counts as deleted.

At a manufacturing company the item catalog has tens of thousands of entries, and the parts and materials calculation program checks them against 1C. If an item is missing from the catalog, or a spec or a documentation file is missing, the program doesn't let it through silently: the flag is visible in the result. That is the same orphan problem from the PlanetScale text, solved at the level of rules. The program handles one order in 65 seconds on average, where it used to take 4 to 5 hours, and no shards were needed for that.

At the PDR company in the US, in 2023 I proposed moving from Google Sheets to a SQL database for speed and a single data format. The idea was supported, and after the move to MariaDB we connected services one after another: document parsers, telephony, a leaderboard. Every call there is recorded, every real lead goes through the funnel to the end, and conversion grew by 30%. I wouldn't build that on cascading deletes: a lead with its call recording and documents is needed later for reports, even if the deal is closed or the customer has left.

I haven't tested Neki and its shard groups myself: none of my clients' data has grown to the point where one database on Debian in Google Cloud stops coping.

What I would do

Check whether the database has ON DELETE CASCADE. If it does, write out the chain of tables that will go with one row and show it to the business owner. Usually that is where the cascade gets turned off.

Introduce soft delete for customers, orders and documents: a deleted_at field and a filter in the queries. Run the physical cleanup as a separate job, in batches, outside working hours.

Check the indexes on foreign keys. It is one command and often the cheapest fix for a slow database.

Don't think about shards while one machine copes. If it stops coping, first a read replica for reports, and only then a conversation about shard groups.

How the server holds the rules and the state of the system, I wrote in the article The server: data, access and process execution. Services: internal systems and data processing in Python. Cases: order calculation for manufacturing and technical partner to a PDR company. On the same topic: schema migrations without fear and a backup nobody has restored. Earlier I wrote about heavy queries and large values in a database. If something in your database is deleted by cascade and you're not sure what exactly, tell me how it is set up: Discuss your task.