01 · The baseline
One database already makes strong promises.
A PostgreSQL transaction gives us the familiar ACID foundation: changes are all-or-nothing, valid rules remain true, concurrent work is isolated according to a chosen level, and committed work survives. WAL makes durable recovery possible; locks and multi-version concurrency control shape what concurrent transactions can observe.
Isolation is not simply on or off. PostgreSQL offers different views of concurrent work, each trading stronger guarantees for more waiting or more retries.
| Level | What a transaction sees | Application consequence |
|---|---|---|
| Read Committed | Each statement gets a fresh snapshot of committed data. | Two statements in one transaction can see different committed results. |
| Repeatable Read | The transaction keeps one stable snapshot. | Conflicting updates can still require an abort. |
| Serializable | Results behave as if transactions ran one at a time. | PostgreSQL may abort a transaction; the application must retry it. |
See PostgreSQL’s official Transaction Isolation documentation for the exact guarantees and retry behavior.
02 · A race in one row
Read, calculate, write can lose a valid update.
Two workers read inventory 10. Each sells one item, calculates 9, and writes
9. Both transactions can commit, but only one decrement remains. The second assignment
overwrote the first worker’s decision with the same stale calculation.
The best fix is often to express the operation atomically inside the database:
UPDATE inventory SET quantity = quantity - 1 WHERE … AND quantity > 0. PostgreSQL sees
the dependency and serializes the row updates. Other valid tools are SELECT … FOR UPDATE,
or Serializable isolation with a retry loop.
Do not ask the application to protect a rule the database can express in one atomic statement.
03 · When the wire breaks
A network partition forces a choice.
CAP is about the moment connected machines can no longer communicate reliably. The partition is not an option we select; it is the failure already happening. During it, the system must choose between consistency—refuse some operations rather than risk conflicting truths—or availability—continue answering and accept that copies may diverge.
Synchronous or quorum paths may wait or reject work when enough members cannot confirm it.
Asynchronous paths can keep serving, but acknowledged writes or reads may later need reconciliation.
Most days there is no partition, yet replication still crosses a network. PACELC adds the daily question: else, when the network is healthy, do we pay extra latency for stronger consistency or accept weaker consistency for faster responses? Replication’s synchronous-versus-asynchronous choice was already this tradeoff in concrete form.
04 · Name the promise
Different operations deserve different consistency.
| Promise | Meaning | Possible use |
|---|---|---|
| Linearizable | Every operation appears to occur at one real-time point. | Claiming a unique username or spending a balance. |
| Read-your-writes | A user sees their own completed changes. | Showing the address immediately after saving it. |
| Monotonic reads | Once a user sees a version, later reads never go backward. | Progress, status, or feeds routed among replicas. |
| Eventual | Copies converge when updates stop, without a bound on immediate visibility. | Like counts, analytics, and reconstructable projections. |
“The system is eventually consistent” is too broad to guide engineering. State the promise at the operation boundary. Payment creation may need uniqueness and read-your-writes; a recommendation count may safely arrive later. Paying for the strongest guarantee everywhere wastes availability and latency.
05 · Messages repeat
Retries must not repeat the business effect.
Networks fail ambiguously. A client times out after sending a payment request and cannot know whether the server committed it. Retrying is necessary, but blindly retrying can charge twice. An idempotency key gives every logical request a stable identity and lets the database make duplicate attempts harmless.
INSERT INTO payments (idempotency_key, amount)
VALUES ($1, $2)
ON CONFLICT (idempotency_key) DO NOTHING;The key needs a unique constraint and a stored result or status that later retries can return. The rule applies beyond HTTP: consumers, job runners, outbox relays, and migration workers all retry after uncertain failure. “At least once” delivery becomes safe only when the effect is idempotent.
06 · More than one authority
A saga replaces one global rollback with local repairs.
An order workflow may reserve inventory on one shard, charge a payment service, and schedule delivery elsewhere. Holding locks across all three systems with a distributed transaction is fragile and can block progress during failures. A saga commits local transactions one at a time and records how to compensate if a later step fails.
Compensation is a new business action, not time travel: a refund does not erase the fact that a charge was briefly visible. Intermediate states must be modeled, exposed carefully, retried idempotently, and reconciled when automation cannot finish.
07 · The complete map
Every scaling tool buys one thing and sends a bill.
| Tool | What it buys | What it costs |
|---|---|---|
| Indexes | Fewer pages touched per query | Slower writes, storage, buffer-pool competition |
| The write path | Durability at sequential-write speed | Write amplification, dirty-page flushing, checkpoint spikes |
| Partitioning | Pruning, smaller maintenance units, cheap retention | Rigid key, multiplied indexes, planning overhead |
| Sharding | Independent write, storage, CPU, and memory capacity | Routing, fan-out, lost joins, distributed transactions, rebalancing |
| Replication | Read throughput and recovery copies | Lag, loss or reduced availability, failover coordination |
Consistency is the bill beneath every copy and split. It appears as acknowledgement latency, stale reads, retries, fencing, idempotency, compensation, and reconciliation. It cannot be removed; it can only be made explicit and paid where the product truly needs it.
Storage trips dominate; understand the working set before changing architecture.
Replicas add readers; shards add independent writers.
Index prefix, partition key, and shard key help only queries that carry them.
Most “we need sharding” stories still end at an index, a query, or a working-set fix.
The Old Dragon“Now you may split the realm—not because it sounds large, but because you can name the resource that ran out and the price you accept.”