Explain the CAP theorem and what it means for distributed databases
Quick Answer
The CAP theorem states that a distributed data system can only guarantee two of three properties at any given moment during a network partition: Consistency (every read sees the most recent write), Availability (every request gets a response, even if not the latest data), and Partition tolerance (the system keeps working despite network failures splitting it into isolated groups). Since partitions are an unavoidable reality in any real distributed system, the practical choice is really between CP (consistent, but may refuse requests during a partition) and AP (available, but may return stale data during a partition).
Detailed Answer
The three properties
- Consistency (C): every node returns the most recent write for any given read. All nodes see the same data at the same time.
- Availability (A): every request receives a (non-error) response, even if some nodes can't communicate with each other.
- Partition tolerance (P): the system continues operating even when network communication between nodes is disrupted — a "partition," where some nodes can't reach others.
Why it's really "pick 2 of 3" only during a partition
In a system with no network partitions, you can actually have both C and A simultaneously. The theorem's bite only applies during an actual partition event. And because partitions are a real, unavoidable fact of distributed systems (networks fail, nodes get cut off, packets get dropped), partition tolerance isn't really optional for any system that's genuinely distributed across multiple nodes. So the real-world choice collapses to CP vs. AP: when a partition happens, do you sacrifice consistency (keep serving requests, possibly with stale data) or sacrifice availability (refuse requests from the cut-off nodes until the partition heals, to guarantee consistency)?
CP systems — consistency over availability during a partition
When a partition occurs, a CP system will refuse to serve (or will block) requests on the minority/cut-off side rather than risk returning stale or conflicting data.
Examples: traditional relational databases in a synchronous-replication configuration, HBase, MongoDB (in its default configuration, favoring consistency via a single primary that must be reachable for writes), ZooKeeper/etcd (consensus-based coordination systems).
AP systems — availability over consistency during a partition
When a partition occurs, an AP system keeps accepting reads/writes on both sides of the partition, accepting that different sides may temporarily disagree, and reconciling the divergence once the partition heals (see the eventual consistency question).
Examples: Cassandra, DynamoDB (in its default/eventually-consistent read mode), CouchDB.
Why this isn't really about "SQL vs NoSQL"
CAP is a property of a distributed system's design choices, not an inherent property of "relational" vs. "NoSQL." A single-node relational database isn't meaningfully subject to CAP at all (there's nothing to partition), but a multi-region relational deployment absolutely is, and some NoSQL systems (like MongoDB by default) actually lean CP rather than AP. Many modern databases also let you tune this per-operation (e.g., Cassandra's per-query consistency levels, DynamoDB's strongly-consistent vs. eventually-consistent reads) rather than being a single fixed system-wide choice.
A strong answer doesn't just recite "Consistency, Availability, Partition tolerance." It explains why the real tradeoff is CP vs. AP, since P is non-negotiable for a genuinely distributed system, and can name what a specific real system chooses and why that fits its use case — e.g., a banking ledger favoring CP because stale balance reads are unacceptable; a social media "like counter" favoring AP because a few seconds of staleness is an acceptable tradeoff for never showing an error to the user.