ACID, BASE and CAP: the promises a database makes

When somebody says a database is "eventually consistent" or "ACID compliant" or "AP not CP", they are making a precise claim about what it promises when things go wrong. This lesson is what those claims mean, because they are the actual difference between the families and they are used loosely by almost everybody.

ACID

Four promises, made by relational databases and by some others. The example throughout: a member borrows the last copy of a book.

BEGIN;
  UPDATE books  SET available = available - 1 WHERE id = 10 AND available > 0;
  INSERT INTO loans (member_id, book_id, due_date) VALUES (1, 10, DATE '2026-10-14');
COMMIT;

Atomicity — all of it, or none of it

Either both statements take effect or neither does. If the power fails between them, you do not get a loan for a book that was never marked unavailable.

This is the one people mean when they say "transaction". It is also the one that is genuinely hard without a database: the temp-file-and-rename trick from the previous lesson gives you atomicity for one file, and cannot give it to you across two.

Consistency — the rules still hold afterwards

The database will not let a transaction leave the data breaking your constraints. If available has a CHECK (available >= 0), a transaction that would make it -1 fails and rolls back.

This is the most misunderstood letter. It does not mean "correct" in any general sense, and it is not the "consistency" in CAP — that is a different word with a different meaning, which is the single largest source of confusion in this subject. Here it means: your declared constraints are not violated.

Isolation — concurrent transactions do not see each other's mess

While your transaction is running, another one should not see your half-finished work.

This is the letter with a dial on it. Isolation is not one thing; it is a spectrum of levels, each allowing certain anomalies, and the default in PostgreSQL is not the strictest one. That has real consequences — the lost update from the previous lesson can still happen inside a transaction at the default level, which is exactly the kind of thing you should find surprising. Module 6 demonstrates it against a real server and then fixes it.

Durability — committed means committed

Once COMMIT returns, the data survives the machine losing power a microsecond later. It is in the write-ahead log, flushed to disk, before you were told it worked.

This is why a database is slower than writing to memory, and it is not a flaw.

BASE

The deliberate counterpart, coined as a pun. Basically Available, Soft state, Eventually consistent.

The claim is: for some systems, staying up matters more than every reader seeing the same value at the same instant, so relax the second to guarantee the first.

Eventually consistent means: if writes stop, all copies converge on the same value — eventually. In the meantime, two readers can legitimately see different values.

Whether that is acceptable is entirely a business question, not a technical one:

A "likes" count that is briefly stale on one server: fine.
A product page showing an old description for two seconds: fine.
A bank balance: not fine.
Whether the last copy of a book is available: not fine, and this is worth noticing because it looks trivial and is not.

The mistake is treating this as a property of good engineering rather than a trade. BASE is not "modern" and ACID is not "legacy". They are different guarantees for different costs.

CAP

The theorem everybody cites and most people state wrongly.

For a distributed system — data on more than one machine — you can have at most two of:

Consistency: every read sees the most recent write.
Availability: every request gets a non-error response.
Partition tolerance: the system keeps working when the network between machines drops messages.

The part that is usually stated wrongly

You do not get to choose to give up P. Networks partition. Cables are cut, switches fail, a data centre becomes unreachable. Partition tolerance is not a design option; it is a fact about the world.

So the real choice, and only during a partition, is between C and A:

CP — choose consistency. When the network splits, refuse to answer rather than answer possibly-wrong. The minority side stops accepting writes. Examples: PostgreSQL with synchronous replication, MongoDB by default, HBase, etcd.

AP — choose availability. Keep answering on both sides, accept that they will diverge, reconcile afterwards. Examples: Cassandra, DynamoDB, CouchDB.

And when there is no partition — which is almost all the time — you get both. CAP is a statement about failure, not about normal operation. A system described as "AP" is not inconsistent on a normal Tuesday.

PACELC, which is the more useful version

CAP only talks about partitions. PACELC adds the rest of the time:

If there is a Partition, choose A or C. Else, choose Latency or Consistency.

That second half is what you actually experience daily. Reading from a replica is faster and might be slightly stale. Reading from the primary is current and slower. You make that trade constantly, and it has nothing to do with partitions.

Anyone who has served a read from a read replica and seen a user's own just-saved change missing has met the E half of PACELC. The usual fix — read your own writes from the primary — is a consistency choice made per query.

What this means in practice

Four things worth carrying forward.

ACID is not a synonym for slow, and eventual consistency is not a synonym for fast. PostgreSQL on one machine does hundreds of thousands of ACID transactions a second. The trade-offs bite at scale across machines, which most applications never reach.

The interesting choices are per operation, not per database. PostgreSQL lets you choose an isolation level per transaction and synchronous or asynchronous replication per commit. Cassandra lets you choose a consistency level per query. "Is it consistent?" is usually the wrong question; "is this operation consistent?" is the right one.

Being explicit about what may be stale is a design skill. Write it down: this counter may be a minute old; this balance may not be. Teams that never make that list end up assuming the strictest guarantee everywhere and are surprised in both directions.

The consistency you need is usually less than you assume, and the places you need it absolutely are fewer and more specific than you assume. Getting those few right matters far more than making everything strict.

Check your work

Atomicity. All of the transaction or none of it — and it is the one you cannot get across two files by hand.

Consistency, in ACID. Your declared constraints are not violated. Not the same word as the C in CAP.

Isolation, and why it is special. It has a dial: levels allowing different anomalies, and the default is not the strictest.

Durability. Committed survives power loss, because of the write-ahead log.

What BASE trades. Every reader seeing the same value at the same instant, in exchange for staying available.

What eventually consistent means. If writes stop, copies converge.

Who decides whether that is acceptable. The business, per piece of data — not the engineer in general.

The part of CAP everybody states wrongly. You cannot choose to give up P; networks partition whether you like it or not.

So what the real choice is. C or A, and only during a partition.

What CAP says about a normal day. Nothing — you get both.

What PACELC adds. Else: latency or consistency, which is the trade you make daily with read replicas.

Where the choices actually live. Per operation — isolation level per transaction, consistency level per query.

Practice

Write the four ACID letters from memory with one sentence each.
Explain the difference between the C in ACID and the C in CAP to somebody. If they are confused, try again — it is the point of the exercise.
For each of these, decide whether eventual consistency is acceptable and say why: a like count, a bank balance, a username's uniqueness, a product description, the last seat on a flight, a search result.
Find the CAP theorem stated on three websites. Count how many say you "choose two" without saying that P is not optional.
Read Eric Brewer's own 2012 retrospective ("CAP Twelve Years Later"). Note what he says people got wrong.
Look up whether PostgreSQL, MongoDB, Cassandra and DynamoDB are CP or AP. Note that several are configurable.
Work out the PACELC classification of a system you have used.
Describe a bug you have seen that was really a read replica being stale.
List the data in a project of yours that may be stale, and the data that may not.
Look up PostgreSQL's isolation levels and note which is the default. Note that it is not the strictest.
Find out what synchronous_commit = off does in PostgreSQL, and which ACID letter it trades away.

Official documentation

PostgreSQL — Transactions — Atomicity, with worked examples.
PostgreSQL — Transaction isolation — Every level and the anomalies each one permits. The authoritative reference for module 6.
PostgreSQL — Write-ahead logging — How durability is achieved, and what synchronous_commit trades.
Brewer, "CAP Twelve Years Later: How the Rules Have Changed" — The author of CAP, correcting how it is usually stated. The most useful thing on this page.
MongoDB — Read concern and write concern — Consistency chosen per operation, in practice.
Cassandra — Consistency levels — The same idea from the AP side.

Next: PostgreSQL, MySQL or SQLite — and why this course starts where it does.