Skip to content

Path 1 · lesson 8 of 8

Intermediate25 minEvidence from PostgreSQL 18.7 and Apache Cassandra 5.0.9 runs earlier in this path

Choosing a database

How to choose between Postgres and Cassandra from access patterns, invariants, scale and failure tolerance, backed by the real output from this path.

What you will learn

  • List the questions that decide between a relational and a wide-column database
  • Back each answer with behaviour you have seen on the real engines
  • Recognise when the answer is "Postgres, until measured otherwise" and when it is not
  • Write a short, defensible database decision for a design review

Before you start

The concept

Choosing a database is choosing which problems you want to have. Every engine in this path made trade-offs you have now seen on real output: Postgres gives transactions, joins and a planner on one primary; Cassandra gives writes that keep flowing when nodes fail and capacity that grows by adding nodes, in exchange for designing every table around a query. Neither is better in general. The system's needs decide.

QuestionPoints towards PostgresPoints towards Cassandra
Are the queries known up front?No, or they will keep changingYes, and they are mostly by key
Are there rules across rows or entities?Yes: balances, stock, uniqueness, foreign keysNo, or they fit inside one partition
How much is written?Up to what one primary can absorbMore than one machine can write, continuously
Must writes continue when machines fail?A short failover pause is acceptableYes, including during a node or zone outage
Multiple regions writing?One writable region is acceptableEvery region must accept writes locally
Who operates it?A team that knows Postgres, or a managed serviceA team ready to run repairs, compaction and capacity
No single row decides. Several rows pointing the same way do.

Why it exists

Database choices are the hardest architecture decisions to reverse: migrating data, rewriting every query and retraining a team takes quarters. They are also where reviews most often go wrong in both directions: a distributed database adopted for scale the product never reaches, paying its modelling cost every day, or a single primary pushed past its limits because nobody wrote down when it would run out. A Staff engineer is expected to make the decision explicit, evidence-based and revisitable.

How it works inside

The evidence, question by question

Each question in the table maps to behaviour you saw in this path's labs. Here it is again, side by side.

Rules across rows: transactions

Database outputPostgres Serializable catches write skew (lesson 3)

Real database output
 bob_commit |                                      bob_error
------------+--------------------------------------------------------------------------------------
 ERROR      | ERROR:  could not serialize access due to read/write dependencies among transactions+
A rule that spans rows (at least one doctor on call) was enforced by the database. Cassandra 5.0 has no multi-partition transactions; its compare-and-set works on one partition.

Database outputCassandra compare-and-set on one partition (lesson 3)

Real database output
A: UPDATE ... SET balance = 50 IF balance = 100
 [applied] | balance
-----------+---------
     False |      70
Enough for a single balance or a unique username, if the invariant fits in one partition. Not for moving money between two accounts.

Evolving questions: joins and the planner

Query planA new question answered by the existing Postgres schema (lesson 7)

Real database output
                                 ->  Parallel Hash Join
                                       Hash Cond: (i.order_id = o.id)
                                       ->  Parallel Seq Scan on order_items i
No migration, no new table. In Cassandra, the same question needed a new table and a backfill, and asking it the relational way was a syntax error:

Database outputCassandra and joins (lesson 7)

Real database output
SyntaxException: line 1:47 mismatched input 'o' expecting EOF (...o.order_id FROM shop.orders_by_customer [o]...)
Cassandra's model is fixed by the queries it was designed for; a new question means a new table.

Availability when machines fail

Database outputCassandra with one of three replicas down (lesson 4)

Real database output
ONE:
Consistency level set to ONE.
QUORUM:
Consistency level set to QUORUM.
ALL:
Consistency level set to ALL.
code=1000 [Unavailable exception] message="Cannot achieve consistency level ALL" info={'consistency': 'ALL', 'required_replicas': 3, 'alive_replicas': 2}
QUORUM writes carried on with no failover and no pause. Every node accepts writes.

Database outputPostgres with its only synchronous replica down (lesson 4)

Real database output
 state  | wait_event_type | wait_event |                    query
--------+-----------------+------------+----------------------------------------------
 active | IPC             | SyncRep    | INSERT INTO orders VALUES (45, 'dave', 3.10)
Configured badly, a replica failure stops writes; configured with a quorum of replicas, it does not (lesson 5). And if the primary fails, writes stop until a replica is promoted, usually seconds to a minute with an automated failover tool. That pause is the price of one writable copy.

Scale: spreading data over machines

Database outputCassandra spreads many keys evenly (lesson 6)

Real database output
state node tokens owns
UN c2 16 31.6%
UN c1 16 32.7%
UN c3 16 35.7%
Capacity grows by adding nodes, which take over token ranges. Postgres partitions a table on one server; spreading Postgres over servers needs an extension such as Citus or sharding in the application.

But only many distinct keys spread. One busy key stays on one replica set however many nodes there are (lesson 6), so a distributed database rewards a workload with many independent keys: users, devices, sessions, channels.

Cost of each write

The engines also pay for writes differently. Postgres updates B-tree pages in place and logged 99 MB of WAL, mostly full-page images, for a burst of small updates in lesson 1; each extra index slowed inserts further in lesson 2. Cassandra appends to a commit log and memtable and pays later in compaction, and every denormalised copy is another write. Neither is free; measure with your write pattern.

Architecture

Drawing diagram…

Decision flowchart. If the system has invariants across rows, choose a relational database. Otherwise, if the queries are still evolving, choose relational. If they are known and by key, choose a distributed wide-column store only if a measured requirement is beyond one primary: write volume, data size, multi-region writes or no failover pause. A relational system can later move one outgrown workload to the distributed store and keep the rest.

flowchart TB
  S["New system or workload"] --> Q1{"Invariants across rows or entities?"}
  Q1 -->|yes| PG["Relational, e.g. Postgres"]
  Q1 -->|no| Q2{"Queries known and mostly by key?"}
  Q2 -->|no, still evolving| PG
  Q2 -->|yes| Q3{"Beyond one primary? write volume, data size, multi-region writes, no failover pause"}
  Q3 -->|no| PG
  Q3 -->|yes, measured| CA["Distributed wide-column, e.g. Cassandra"]
  PG -.->|one workload outgrows it| SPLIT["Move that workload, keep the rest"]
  SPLIT -.-> CA
A starting point for the decision, not a substitute for measurement.
  • The decision is per workload, not per company. Many systems run Postgres for orders and accounts and Cassandra for high-volume event or message data.
  • "Measured" matters: estimate writes per second and data growth from real numbers, and compare with what a tuned primary handles in a load test.
  • Other stores answer other questions (search engines for text, column stores for analytics, caches for hot reads); the same approach applies: start from access patterns and invariants.

Step by step

Drawing diagram…

Sequence diagram. The engineer lists access patterns with volumes, invariants and their scope, failure tolerance and regions, and estimated growth and write rate, then brings a proposal with reasons and rejected alternatives to a design review. The review challenges the weakest assumption, the engineer load tests it, and records the decision with a trigger to revisit it.

sequenceDiagram
  participant E as Engineer
  participant R as Design review
  E->>E: list access patterns, with volumes
  E->>E: list invariants and their scope
  E->>E: state failure tolerance and regions
  E->>E: estimate data growth and write rate
  E->>R: proposal: store, reasons, rejected alternatives
  R-->>E: challenge the weakest assumption
  E->>E: load test that assumption
  E->>R: decision record, with the trigger to revisit it
How the decision is made and recorded.
  1. Write the trigger to revisit the decision: for example, "when sustained writes exceed what the primary handled in the load test at 70% CPU".
  2. Rejected alternatives, with why, prevent the same debate every quarter.
  3. The weakest assumption is usually the growth estimate. A load test turns it into a number.

In a real system

  • Start relational for the system of record (accounts, orders, payments, inventory), where invariants and changing questions dominate.
  • Use a distributed store for the high-volume, key-shaped part: events, messages, sensor readings, activity feeds, sessions, where queries are by key and loss of a node must not stop writes.
  • Connect them with events, not dual writes. Write to the system of record and publish changes (the transactional outbox, in the Kafka academy) for the other store to consume.
  • Count the operational cost. A Cassandra cluster needs regular repairs, compaction tuning and capacity planning; a managed Postgres or a managed Cassandra-compatible service changes that equation.
  • Revisit with numbers. Track the metrics that would trigger a change: primary CPU and WAL rate, largest table size, failover time.

Wrong, then right

A startup building a marketplace picks Cassandra for everything, "so we never have to migrate when we scale". A year later it has 40,000 users.

Incorrect implementation

docs/adr/001-database.mdText
Decision: Cassandra for all data (users, listings, orders, payments, messages).Reason:   It scales horizontally, so we will not need to migrate later.Rejected: Postgres, because it does not scale.

Why it is written this way

  • No access patterns, volumes or invariants are stated, so nothing in the decision can be checked.
  • Orders and payments need rules across entities (stock, balances), which Cassandra 5.0 can only enforce within one partition.

Why it fails

  • Every new product question (sales per category, refunds per seller) needs a new table and a backfill; the team ships features slowly.
  • Stock is oversold under concurrency, because the check and the decrement span partitions; the fixes are lightweight transactions on hot partitions and application-level locking.
  • The cluster needs repairs and compaction tuning that nobody on the team has done before, for data that would fit on one server.

Corrected implementation

docs/adr/001-database.mdText
Decision: Postgres (managed, one primary, two replicas, quorum commit) for users,          listings, orders and payments. Messages in Postgres too, partitioned by month.Reasons:  Orders and payments have cross-entity invariants (stock, balances): we use          transactions. Product questions change weekly: joins and indexes absorb them.          Estimated year-1 load: 300 writes/s peak, 200 GB. A load test on the chosen          instance sustained 5,000 writes/s.Rejected: Cassandra for everything: no cross-partition transactions; a table per query          for questions we do not know yet.Revisit:  If messages exceed 2,000 writes/s sustained or 2 TB, move messages (only)          to a distributed store, fed by the outbox.

Why it is written this way

  • Every claim is checkable: volumes, a load-test number, the invariants that ruled Cassandra out for this data.
  • The trigger to revisit names the one workload likely to outgrow the primary, and how it would move.

Trade-offs

  • A primary failure pauses writes for the failover time; the decision accepts that, and should say how long it is.
  • Moving messages later is still a migration, but of one well-understood workload rather than the whole system.
  • If the product had been a global chat service from day one, the same questions would have pointed the other way for messages.

Configuration

Not settings, but the numbers a database decision should record, with where to get them.

Peak and sustained writes per secondDefault Estimate, then load test
What it does
Whether one primary can absorb the writes, with headroom.
When to change it
Measure on the instance size you would run, with your indexes, at 70% CPU or less.
Data size in one and three yearsDefault Estimate from current growth
What it does
Whether one server can hold and back up the data, and how long a restore takes.
When to change it
Include indexes and WAL or commit log; lesson 2's indexes added 198 MB to a 2-million-row table.
Tolerable write outageDefault Agreed with the business
What it does
Whether a failover pause of seconds to a minute is acceptable.
When to change it
If not even that is acceptable, the workload needs a store where every replica accepts writes.
Invariants and their scopeDefault Listed per entity
What it does
Which rules span rows, entities or partitions.
When to change it
Any rule across partitions points to a transactional store for that data.

Hands-on exercise

Write a database decision record

Apply the questions in this lesson to a real or realistic system and produce a decision record that a reviewer could check.

Steps · 0 of 5 done

What can go wrong

Choosing for scale you do not have
Symptom
Feature work is slow; every new question needs a new table and a backfill.
Cause
A distributed store was chosen for hypothetical scale, so the team pays its modelling cost every day.
Fix
Choose for measured needs plus headroom, and record the trigger that would justify a change.
Ignoring cross-entity invariants
Symptom
Overselling, double spending or orphaned records under concurrency.
Cause
Rules spanning several entities in a store without multi-partition transactions.
Fix
Keep data with cross-entity invariants in a transactional store, or redesign so each invariant lives in one partition.
Dual writes to two stores
Symptom
The two stores disagree after failures.
Cause
The application writes to both, and one write fails.
Fix
Write to one system of record and feed the other from its changes (an outbox or change data capture).
No trigger to revisit
Symptom
The primary reaches its limits during a peak, and the migration starts as an emergency.
Cause
Nobody tracked the numbers the decision depended on.
Fix
Record the trigger in the decision and put its metrics on a dashboard with an alert.

How to troubleshoot

Is the Postgres primary approaching its write limit?

Check

SELECT * FROM pg_stat_wal;  and the primary's CPU and disk write throughput over a peak week
How to read it
WAL bytes per second and CPU near the load-tested limit mean it is time to act on the recorded trigger.
Is a Cassandra cluster the right size?

Check

nodetool status and nodetool tablestats on each node
How to read it
Uneven load points to hot partitions (lesson 6), which more nodes will not fix; even load near capacity means adding nodes.
Are design reviews repeating the same database debate?

Check

Look for a written decision record with reasons, rejected alternatives and a trigger
How to read it
Without one, every new person reopens the question. Write it, with numbers.

Key takeaways

  • Decide per workload from access patterns, invariants, write volume, data size, failure tolerance and the team that will operate it.
  • Rules across entities and evolving questions point to a relational database; Postgres enforced them in this path's labs.
  • Many independent keys, very high write volume and writes that must continue through node failures point to a distributed store; Cassandra kept writing at QUORUM with a node down.
  • One busy key does not spread on any store; design keys before choosing machines.
  • Write the decision down with numbers, rejected alternatives and the trigger to revisit it.

Practical check

Small scenarios, one decision each. Pick what you would do, then read why.

Scenario 1 of 3

A ticketing platform sells seats for concerts. Each seat can be sold once; a basket can hold seats from several sections, and payment must succeed for all of them or none. Peak load is 2,000 checkouts a second for a few minutes.

Which store fits the checkout data best?

Scenario 2 of 3

A connected-car company ingests telemetry from 3 million cars, one reading every 10 seconds each, and queries it by car and time range. Ingestion must continue through the loss of a server or an availability zone.

Which store fits the telemetry best?

Scenario 3 of 3

A team's design doc proposes Cassandra for a new internal admin tool with 200 users, a few thousand records, and reports that product managers will define over the next year. The stated reason is "future scale".

What is the most useful review comment?

References

References: PostgreSQL 18 documentation: Transaction isolation ↗ · PostgreSQL 18 documentation: High availability, load balancing and replication ↗ · PostgreSQL 18 documentation: pg_stat_wal ↗ · Apache Cassandra 5.0 documentation: Overview ↗ · Apache Cassandra 5.0 documentation: Introduction to data modelling ↗ · Apache Cassandra 5.0 documentation: Guarantees ↗ · DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store (SOSP 2007) ↗ · Report a problem