Path 1 · lesson 8 of 8
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.
| Question | Points towards Postgres | Points towards Cassandra |
|---|---|---|
| Are the queries known up front? | No, or they will keep changing | Yes, and they are mostly by key |
| Are there rules across rows or entities? | Yes: balances, stock, uniqueness, foreign keys | No, or they fit inside one partition |
| How much is written? | Up to what one primary can absorb | More than one machine can write, continuously |
| Must writes continue when machines fail? | A short failover pause is acceptable | Yes, including during a node or zone outage |
| Multiple regions writing? | One writable region is acceptable | Every region must accept writes locally |
| Who operates it? | A team that knows Postgres, or a managed service | A team ready to run repairs, compaction and capacity |
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 outputbob_commit | bob_error ------------+-------------------------------------------------------------------------------------- ERROR | ERROR: could not serialize access due to read/write dependencies among transactions+
Database outputCassandra compare-and-set on one partition (lesson 3)
Real database outputA: UPDATE ... SET balance = 50 IF balance = 100
[applied] | balance
-----------+---------
False | 70Evolving 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 iDatabase outputCassandra and joins (lesson 7)
Real database outputSyntaxException: line 1:47 mismatched input 'o' expecting EOF (...o.order_id FROM shop.orders_by_customer [o]...)
Availability when machines fail
Database outputCassandra with one of three replicas down (lesson 4)
Real database outputONE:
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}Database outputPostgres with its only synchronous replica down (lesson 4)
Real database outputstate | wait_event_type | wait_event | query --------+-----------------+------------+---------------------------------------------- active | IPC | SyncRep | INSERT INTO orders VALUES (45, 'dave', 3.10)
Scale: spreading data over machines
Database outputCassandra spreads many keys evenly (lesson 6)
Real database outputstate node tokens owns UN c2 16 31.6% UN c1 16 32.7% UN c3 16 35.7%
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- 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
- Write the trigger to revisit the decision: for example, "when sustained writes exceed what the primary handled in the load test at 70% CPU".
- Rejected alternatives, with why, prevent the same debate every quarter.
- 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
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
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 you should see
For the delivery app: orders, payments, menus and accounts in a relational store, because of cross-entity invariants and evolving questions. Courier locations, a high-volume key-by-courier stream where only the latest values matter, are a candidate for a distributed or in-memory store if volumes exceed what the primary absorbs; chat can start relational and move later. The record states volumes, the load-test result and a trigger. Your choices may differ; what matters is that every reason can be checked.
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