05 — Consistency Trade-offs — CQRS, ACID, and the CAP Theorem

Exam trivia was how I filed ACID/CAP and CQRS — until the first time I had to choose between a consistent read and an available one. The model that made them click: they're the formal names for trade-offs you're already making, whether you admit it or not [1][2]. Once I could name the trade-off, the question stopped being "which is correct" and became "which guarantee am I willing to give up, and on purpose."

These two ideas are the foundation for reasoning about any system that spans more than one process or one database. Almost every "why is our data weird" incident traces back to a trade-off someone made without realizing it was a trade-off.

ACID: what a transaction guarantees

ACID describes the guarantees a database transaction provides — atomicity, consistency, isolation, and durability — ensuring reliable operations even under failure [1]:

• Atomicity — all of the transaction happens, or none of it does. No half-written state.
• Consistency — the transaction takes the database from one valid state to another, respecting all constraints.
• Isolation — concurrent transactions don't interfere with each other as if they ran one at a time (modulo the isolation level).
• Durability — once committed, the change survives a crash.

ACID is the default way of thinking for a single relational database — PostgreSQL, MySQL, and friends deliver these guarantees by default. It's also the model I had to unlearn the moment a system grew past one database.

CAP: the distributed-systems constraint

The CAP theorem states that a distributed system can guarantee only two of three properties at once: consistency, availability, and partition tolerance [2]. Because network partitions are a fact of life — cables fail, switches fail, nodes go briefly unreachable — partition tolerance isn't really optional in a distributed system. So the practical choice is between consistency and availability _when a partition happens_:

• CP (consistency + partition tolerance) — during a partition, the system refuses requests it can't verify are consistent. You'd rather be unavailable than wrong. Think: a banking ledger.
• AP (availability + partition tolerance) — during a partition, the system keeps answering, possibly with stale data that reconciles later. You'd rather be eventually consistent than unavailable. Think: a social feed [2].

Architects use CAP to reason about database and system trade-offs deliberately. A system that silently picks AP for data that needed CP is where data-corruption bugs live.

Eventual consistency

Eventual consistency is the consequence of choosing AP — updates propagate across the system over time instead of instantly [3]. All replicas _eventually_ converge to the same value if no new updates arrive, but at any given moment, two readers might see different values. This is acceptable for a like-count on a social post and unacceptable for a balance on a withdrawal. Eventual consistency isn't a bug; it's a chosen property — and it's the one that lets distributed systems scale.

CQRS: splitting reads from writes

CQRS — Command Query Responsibility Segregation — separates the operations that change data (commands) from the operations that read data (queries), often using different models for each [3][4]. The read side might be a denormalized projection optimized for fast lookups, while the write side is a normalized model optimized for validation and consistency.

Write path (commands)   →   Write model (optimized for validation)
                                         │
                                         ▼  (events / sync)
Read path  (queries)    →   Read model  (optimized for fast lookups)

This separation pairs naturally with eventual consistency — the read model often lags the write model by milliseconds or more, updated asynchronously [3]. The trade-off is real: CQRS adds complexity (two models, a sync mechanism) in exchange for the ability to scale reads and writes independently and to shape each model for its actual workload. It's common in systems where reads vastly outnumber writes, or where the read shape differs wildly from the write shape.

The single thread

ACID is what you get in a single box. CAP is the wall you hit the moment there's more than one box. Eventual consistency and CQRS are deliberate responses to that wall — choosing availability and scaling reads/writes separately, at the cost of immediate consistency and added complexity. The architect's job is to know which side of the wall each piece of data lives on, and to make the choice deliberately.

How I use this

For each piece of data, I ask: does the reader need to see every write immediately (CP/ACID), or is "eventually correct" fine (AP/eventual)? Balances and inventory lean CP; feeds, counters, and search indexes lean AP. When read and write shapes diverge sharply, CQRS earns its complexity — otherwise a single well-indexed model is simpler and I don't reach for the split. The discipline is making the trade-off explicit in the design doc rather than discovering it during an incident.

References

[1] "ACID," Wikipedia. [Online]. Available: https://en.wikipedia.org/wiki/ACID

[2] "What is CAP Theorem?," BMC Blog. [Online]. Available: https://www.bmc.com/blogs/cap-theorem/

[3] M. Fowler, "CQRS," martinfowler.com. [Online]. Available: https://martinfowler.com/bliki/CQRS.html

[4] Microsoft, "CQRS pattern," Azure Architecture Center. [Online]. Available: https://learn.microsoft.com/en-us/azure/architecture/patterns/cqrs