Building Robust Systems with ACID and Constraints

ACID is not a suggestion

When Andreas Reuter and Theo Härder coined the acronym ACID in 1983, they refined Jim Gray’s earlier triad of atomicity, consistency, and durability by adding isolation. Decades later, the major relational systems — Postgres, Oracle, MSSQL — still ship with these guarantees intact. That longevity is telling: no better idea has replaced them.

The last decade brought a wave of data stores offering novel capabilities like streaming changesets, JavaScript APIs, and nested JSON documents. Most of them assume that horizontal partitioning is an inevitability and that ACID must therefore be sacrificed. But swapping durable correctness guarantees for novelty features, or for an untested belief that the system will soon need to scale out, is a poor trade.

The reason so many engineers under-value ACID may simply be that they've had it too easy. Coming up through frameworks like Rails, or in environments where an ACID database was present from the start, it's easy to never think deeply about what the database actually guarantees. Powerful features go unused because they're invisible.

Atomicity: the all-or-nothing guarantee

The “A” in ACID ensures that within a transaction, committed changes are all or nothing. If a transaction fails midway, the database is left exactly as it was before. For buggy software — and software is always buggy — this matters because any large program will eventually need an operation that writes two or more objects in sequence. Transactions wrap such operations so that even the worst case leaves state undamaged.

Failing a transaction is never desirable, but atomicity removes the expensive fallout.

Many non-relational products advertise document-level atomicity. In MongoDB, RethinkDB, CouchBase and similar systems, a single row write is atomic. Nothing beyond that is promised.

Consider a GitHub-like service: when a user opens a pull request, several objects must be saved in succession: the pull request model itself, a webhook record, a reviewer assignment, and an audit log event.

Without transactional atomicity, a request that fails after the first two saves has made partial progress. The result is an invalid pull request object, and any later code path that loads it can error trying to read state that was only half-created.

What's the remedy in such systems? Sometimes an automated rollback mechanism exists, but it's more common for the strategy to be an optimistic hope that partial failures will be rare. Code defensively loads data to tolerate combinations of invalid state that accumulate over time. At some scale, incidents require manual intervention or a custom “fixer script.” After enough of these episodes, engineers spend less time building and more time acting as data janitors.

Consistency: one valid state to another

The “C” in ACID dictates that every transaction moves a database between valid states — never into an intermediate one. It is easiest to appreciate through a concrete scenario: user registration.

If you store a single account for [email protected], the naive flow is:

1. Look for any existing user with that email; if one exists, reject the request.
2. Create the new record.

This works until traffic level starts creating races. If two nearly-concurrent registration requests both complete step one successfully, duplicates emerge.

An ACID database solves this in multiple ways:

1. Run transactions under a strong isolation level like SERIALIZABLE, so only one concurrent registration for the same email can commit.
2. Impose a uniqueness constraint on the table or an index, making duplicate insertion impossible at the storage layer.

Without ACID, these races are left to your application. You could build a custom locking scheme to serialize registration per email, but many teams skip that and defer the fix until the pain shows up in production.

Isolation: peers don't collide

The “I” in ACID handles two transactions that target the same data at the same time. Isolation gives each one a consistent view (depending on the level), and reconciles the results at commit. Modern relation databases accomplish this with sophisticated multiversion concurrency control that is both correct and efficient.

Concurrent web traffic is unavoidable in any real application. What does life without isolation look like?

The custom locking trap

The usual workaround is a hand-rolled pessimistic lock that constrains access to a resource set to one operation at a time, forcing others to wait.

This approach is all downsides.

• It’s slow. Operations can wait a long time for locks on contended resources, and the worst pain lands on your largest users.
• It’s inefficient. Lock granularity is typically broad (often an entire account) to keep complexity down, so operations block when they don’t have to.
• It’s a lot of work. A basic lock, easy enough. Making it fast and efficient quickly gets complicated. ACID databases give you a correct, performant locking system for free.
• It’s likely wrong. Locks are prone to bugs. A custom implementation will fail eventually — prematurely and unpredictably.

Durability: committed means committed

Durability is the guarantee that committed work survives crash and power loss. It’s arguably the most universally honored of the four: even systems that skip A and C tend to get the “D” right. Achieving durable writes in MongoDB, for example, took years.

The wrong thing to optimize for

One argument pushed by document stores is speed to prototype — no schema design bogging you down. This conflates Ricth Hickey’s distinction between "easy" (approachable) and "simple" (not complex). Schemaless databases are easy; they aren't necessarily simple.

The speed of first prototypes isn't the deciding factor. Where schemaless design hurts is over a decade of operations. The next ten years of a system's life are spent keeping it correct — reducing bugs and data inconsistencies that lead to user and operator attrition. It is artificially hard to work with systems where a User record isn't guaranteed to carry an id or email field. At some point, even schemaless enthusiasts add ad-hoc constraints, and mature organizations reach for object modeling frameworks to get minimal shape guarantees — well after inconsistency has made migrations difficult and production code globally defensive against thousands of edge cases.

For production software, the better the schema definition and data self-consistency, the more manageable the operational burden. Optimizing for the startup's first days over a decade of maintenance is a pathological trade of long-term sustainability for short-term convenience.

On "scale"

ACID databases carry a reputation of not scaling out, until a certain size is reached. But most products — measured in users, not in tens of petabytes — are well served indefinitely by a single, vertically scalable node. Infrequently accessed junk can be offloaded to alternate stores and old data archived. Many databases that claim to be enormous still contain hundreds of GBs that needn’t be there – and the genuine full-horizontal-scale scenarios are rarer than marketing suggests.

For those that truly hit search-engine scale, post-relational options now offer hybrids: Citus provides per-shard ACID, and Google Spanner gives distributed read-write transactions with locking. Neither forces building on a simplistic key/value substrate just to get some horizontal behavior.

Your Database Isn’t a Nice-to-Have

Every workaround for a missing ACID guarantee or a missing constraint is a tax you pay in engineering time and system reliability. Atomicity gaps lead to cleanup scripts and reconciliation jobs. Consistency gaps force elaborate application-level workarounds. Isolation gaps push you toward building your own locking layer—one that is likely slow, inefficient, and buggy. Skipping constraints and schemas means accepting that your production data has no cohesive structure.

None of these substitutes are better than the original. When you choose a data store without ACID, you end up reimplementing what it already does for you, but inside your application code where it is harder to test, harder to reason about, and more likely to fail.

Let the Database Do Its Job

A database that provides these properties is a foundational substrate for your application. It gives you correctness and speed without requiring you to build and maintain the machinery yourself. These features are not just convenient—they are battle-tested, refined over millions of hours running some of the heaviest production workloads in existence.

For most new projects, the pragmatic advice is straightforward: start with a relational database that offers ACID and robust constraint support. In nearly every case, that means just using Postgres. It gives you the guarantees you need as a solid base, so you can spend your effort building features instead of reinventing transactionality.