When Databases Outgrow Their Best Features

Databases have a tendency to degrade as they grow. A schema that started small, clean, and well-constrained often becomes large, bloated, and full of compromises. The causes are familiar: the underlying technology can’t scale certain operations, high-risk maintenance is banned for stability reasons, back-migrations are too costly, or simply that cutting corners ships code faster. Whatever the reason, the features that made the database worth using in the first place end up on the chopping block.

Transactions and Foreign Keys Are the First to Go

ACID transactions are frequently sacrificed early. Their value isn’t obvious in a low-traffic system, they add friction to rapid development, and they can cause locking headaches in production. Losing them, however, undermines the application’s long-term operability in ways that are well documented.

Referential integrity usually follows. Foreign keys guarantee that a referenced object actually exists, and they give developers controlled deletion semantics via ON DELETE RESTRICT or ON DELETE CASCADE. Without them, every query that loads a referenced object must defensively handle the case where the object is missing:

user = User.load(api_key.user_id)
if !user
  raise ObjectNotFound, "couldn't find user!"
end

Teams rationalize dropping foreign keys for various reasons—technological limits, fear of a large cascade on a hot table, or simply the ongoing discipline required to maintain proper relations. The result is that correctness logic migrates from the database into application code, where it's harder to enforce consistently.

Nullable Columns Multiply

Large schemas end up with a disproportionate number of nullable fields, and that's a burden on application code: every nullable field demands independent handling and a fallback plan, adding time and bug surface. Several dynamics drive this. Nullable is the DDL default unless you explicitly write NOT NULL. Making a column non-nullable usually requires migrating existing data, which is slow and risky on hot nodes. And even platform limitations get in the way—in Postgres, running SET NOT NULL requires a full table scan to verify no nulls exist, blocking other operations. On a large table, that can take down production. A CHECK constraint on null values can work around the issue, but it would be better to be able to use the native DDL safely.

Indexes Become Expensive and Restrictive

Index maintenance is trivial on small systems and painful on large ones. In larger deployments, indexes must be built on multiple clusters, builds on hot nodes risk interfering with production and require throttling tools, and the sheer data volume makes builds slow and storage costly. Reduced query performance is the visible cost, but there are hidden ones too: product decisions can end up constrained by whether a feature would require an index on an enormous collection—one that takes weeks to build and costs a significant chunk of change annually in storage.

Complex queries suffer a similar fate. The expressive power of SQL is valuable, but complex statements can bring unpredictable performance and unanticipated locking. Storage teams respond by restricting what developers can run, reducing the interface to single-row selects with index hints, single-row updates, and single-row deletes. The source article gives two concrete examples: one DBA banned all multi-row updates due to replication concerns (arguably helping production, but spawning heinous workarounds and tech debt), and another system requires every query to be named, statically defined, and pre-approved against an existing index so that correctness is verified at build time.

# a simplified storage API
def insert(data:); end
def delete_one(id:); end
def load_many(predicate:, index:, limit:); end
def load_one(id:); end
def update_one(id:, data:); end

Toward Scalable Sanity

There’s a disconnect between operators of large production systems and developers of open-source database tooling. The former often adopt a nihilist view that every mature installation inevitably degrades into a key/value store; the latter don't always prioritize features that would help large installations. That fatalism isn’t inevitable—systems like Citus, Spanner, and CockroachDB already support features like cross-shard transactions that were previously impossible. More movement in that direction is needed.

A few operations-friendly features would go a long way toward slowing the entropy:

  • Index builds that can be paused or throttled in an emergency.
  • A safe, non-blocking path to turn a nullable column into NOT NULL, without an immediate full table scan.
  • A “strict” SQL dialect that defaults to NOT NULL and requires foreign keys.
  • A protocol that lets queries signal out-of-band when they didn’t perform well—for example, returning results without using an index—so test suites can catch problems before production does.
  • A migrations framework built into the database itself, allowing long-lived migration queries to be deprioritized and paused as needed.

The goal isn’t unreasonable: large databases should keep the correctness guarantees of small ones, and applications built on them should get more stable as a result.