One PostgreSQL server gives ACID transactions (Chapter 14). Large systems keep copies of the data, called replicas, on many machines, often in several countries. Copies make the system faster and keep it working when a machine fails. They also make a new problem.
Copies can differ
A write goes to one replica first and reaches the others a moment later:
| Time | EU replica | US replica |
|---|---|---|
| 1 | stock = 40 | stock = 40 |
| 2 | sell 1: stock = 39 | stock = 40 (old) |
| 3 | the change arrives: stock = 39 |
At time 2, a read on the US replica returns old data.
CAP
A network partition happens when replicas cannot reach each other. The CAP theorem says that during a partition, a distributed database can keep only one of these:
| Letter | Property | Meaning |
|---|---|---|
| C | Consistency | Every read returns the newest write, or an error. |
| A | Availability | Every request gets an answer, maybe with old data. |
| P | Partition tolerance | The system keeps working while the network is split. |
Networks do fail, so P is not optional. The real choice is C or A while the partition lasts:
- CP: a replica that cannot reach the others refuses requests. No old data, but some requests fail.
- AP: every replica answers. No request fails, but some answers are old.
BASE
Many NoSQL stores choose availability. Their promise is called BASE:
| Letter | Meaning |
|---|---|
| BA | Basically Available: the system answers, even during failures. |
| S | Soft state: the data on a replica can change with no new write, when changes arrive from other replicas. |
| E | Eventually consistent: if the writes stop, all replicas become the same after some time. |
| ACID | BASE | |
|---|---|---|
| Goal | correct data | always answer, at a large scale |
| Reads | newest committed data | can be old for a short time |
| Typical systems | PostgreSQL, MySQL | Cassandra, DynamoDB (default reads) |
The line is not sharp. Many NoSQL stores now offer transactions or strongly consistent reads, and relational databases have replicas that lag behind. Ask what each system promises, read by read.
The C in ACID and the C in CAP are different: ACID's C means that the rules hold; CAP's C means that every read sees the newest write.