Foreign keys are not a substitute for discipline

Relational schemas accumulate foreign keys over time, often added almost automatically whenever one or more columns in a new table reference another table — a guarantee that the database will refuse data that doesn't line up. The decision to do so has clear precedent.

The challenge is that proliferating foreign keys can bog a database down at scale, creating back-pressure even on read-only queries and eventually resulting in data access gridlock. Often, a perspective change can result in improved performance and scalability — namely, which of these foreign keys could, instead, be handled through disciplined engineering in code rather than an automatic safety net.

Two engagements taught me the cost of leaning on the database instead of the code.

On the first — an earlier Postgres engagement — we were moving a database onto a distributed design so it could scale by tenant. The schema had grown a dense web of foreign keys, most of them added defensively over the years. They did not survive the move. Foreign keys across shards are not supported the way they are on a single node, and a large number had to be unwound by hand. Constraints added to keep the system safe had become the main obstacle to scaling it.

On another, proliferating foreign keys resulted in significant database platform degradation. Every write paid to re-verify relationships the application already knew were valid. At low volume that cost is invisible. At high volume it compounds, and the database spends its time proving relationships the code should have guaranteed by design. This ultimately affects even read-only transactions, as writes consume an increasing share of the DBMS's bandwidth.

In one of our recent projects — an open "pay it forward" project we're building — we made the same call deliberately and wrote it down: raw SQL over an ORM, discipline over automatic safety nets. That judgment isn't a side-project preference. It comes out of roughly a dozen client systems where the automatic safety nets cost more than the discipline would have.

None of this is an argument against foreign keys. On a small, single-node schema where integrity matters more than scale and writes are modest, they are a good default. They become a liability when you expect to shard, when write throughput is high, or when they are used as a substitute for rigor that belongs in code. Use them sparingly, where they are worth their cost, and you will reap the rewards as your database scales.

I've written separately about choosing sharding over active-active multi-master. This is the other half of what that work taught me.