07. Storage Decision Framework — Pick by access pattern, not ideology¶
~17 min read. SQL versus NoSQL becomes easy once you ask what questions the data must answer.
Built on the ELI5 in 00-eli5.md. The kitchen needs shelves, fridges, and freezers for different jobs — and the house rules decide which storage shape keeps the restaurant honest.
1) Stop asking "SQL or NoSQL?"¶
See. That question is too vague. Storage is about fit. What is the access pattern? What is the correctness bar? What is the scale? What is the query shape? That is the real interview answer.
┌────────────────────┐
│ first ask │
├────────────────────┤
│ get by key? │
│ join many tables? │
│ nested documents? │
│ append by time? │
│ traverse edges? │
└─────────┬──────────┘
│
├──→ relational → Postgres
├──→ key-value → DynamoDB / Redis
├──→ wide-column → Cassandra
└──→ document → MongoDB
2) The four decision axes¶
We can make the framework mechanical. Ask these in order.
Access pattern¶
Do you mostly fetch by primary key? Do you query many related entities together? Do you read the newest events by partition? Do you store one large nested object?
Consistency need¶
Can two users briefly see different values? Can you afford lost updates? Can one stale read create money loss or oversell?
Scale profile¶
Are writes modest and queries rich? Are writes huge and uniform? Are reads tiny but millions per second?
Query complexity¶
Do you need joins, constraints, aggregates, and ad hoc filters? Or do you need only key lookups and range scans?
┌──────────────┬──────────────────────────────┬──────────────────────┐
│ axis │ if answer looks like this │ lean toward │
├──────────────┼──────────────────────────────┼──────────────────────┤
│ access │ user_id → cart │ DynamoDB / Redis │
│ access │ order + items + payments │ Postgres │
│ access │ convo_id → newest messages │ Cassandra │
│ access │ page document with embeds │ MongoDB │
│ consistency │ exact invariants matter │ Postgres │
│ consistency │ stale reads are tolerable │ Cassandra / DynamoDB │
│ complexity │ joins and reporting matter │ Postgres │
└──────────────┴──────────────────────────────┴──────────────────────┘
So what to do?
Never start from vendor names.
Start from verbs.
Get.
List.
Join.
Count.
Append.
Expire.
Those verbs expose the right store faster than brand comparison does.
---
## 3) Quick fit guide for common stores
Use this as a first-pass table.
It is not perfect.
It is very useful.
| Store | Best fit | Be careful when | Mental note |
|---|---|---|---|
| **Postgres** | relational data, transactions, joins, constraints, secondary indexes | write scale is enormous and query pattern is simple key-based only | safest default for core business data |
| **Cassandra** | huge write volume, partition-key reads, append-heavy timelines, multi-region availability | you need joins, global secondary queries, or strict single-row semantics across many partitions | model queries first, not tables first |
| **DynamoDB** | key-value or key-range access with predictable latency at very high scale | you need flexible ad hoc queries or cross-item transactions everywhere | excellent when access pattern is known up front |
| **Redis** | cache, counters, rate limits, locks, short-lived sessions | you need durable system-of-record guarantees or rich historical querying | memory-first speed, not a free relational database |
| **MongoDB** | evolving document shapes, nested objects, content records, per-document reads/writes | relationships are deep and joins dominate the workload | great when one document answers one request |
Now what is the problem?
Many systems need more than one store.
That is normal.
Orders may live in Postgres.
Hot counters may live in Redis.
Activity events may live in Cassandra.
Flexible profile blobs may live in MongoDB.
This is called polyglot persistence.
It is not architecture cosplay.
It is matching **prep stations** to the job.
---
## 4) Worked example: choosing storage for an order system
Suppose we are designing an e-commerce order platform.
Peak checkout is 600 successful orders per second.
Each order has:
- order header = 2 KB
- line items = 2 items on average × 0.6 KB = 1.2 KB
- payment reference = 0.4 KB
- status history seed row = 0.4 KB
Total primary data per order is:
2 + 1.2 + 0.4 + 0.4 = 4 KB.
Daily primary write volume is:
600 orders/sec × 4 KB × 86400 sec.
First multiply orders per day.
600 × 86400 = 51,840,000 orders/day.
Now bytes.
51,840,000 × 4 KB = 207,360,000 KB/day.
That is about 207.36 GB/day before replicas and indexes.
Good.
Now query shapes.
- get order by order_id
- list recent orders for user_id
- join order with payment and refund state
- update status with strong correctness
- run finance reconciliation queries
Consistency need?
Strong.
A duplicate or lost state transition is expensive.
Query complexity?
Medium to high.
Relationships matter.
Scale?
Serious, but not beyond relational possibility if partitioned and indexed well.
So the core order record leans Postgres.
Why not Cassandra?
Because our dominant pain is not write bandwidth alone.
Our pain is correctness plus relational queries.
Why not DynamoDB as the only store?
Possible for some shapes, yes.
But our read surface includes joins, reporting, and reconciliation.
Those become awkward fast.
Why not MongoDB?
Because the hard part is not nested JSON.
The hard part is cross-entity integrity.
Look at the decision table in sentence form.
```text
query shape: join-heavy + transactional
consistency: strict
write rate: 600/sec
main choice: Postgres
supporting choice: Redis for hot reads, maybe warehouse/search elsewhere
5) Common traps in storage decisions¶
Trap one. Choosing Cassandra because traffic is large. Large traffic alone proves nothing. If the query model is relational, you still suffer. Trap two. Choosing Postgres because teams know SQL. Familiarity helps. It does not erase a billion simple writes per day. Trap three. Using Redis as the primary truth store for user-critical data. Fast is not the same as durable. Trap four. Using MongoDB for everything because the schema evolves. Schema flexibility is valuable. But it does not replace transactional modelling. Trap five. Pretending one store must solve every problem. Look. The core rule is this. Store primary truth where the hardest requirement is easiest to satisfy. Then project that truth outward for speed. That is how mature systems grow. First pick the system of record. Then pick accelerators around it.
Where this lives in the wild¶
- Stripe ledger service — finance platform engineer keeps money movement on a relational core because reconciliation, constraints, and audits are non-negotiable.
- Netflix viewing history — data platform engineer benefits from wide-column storage when writes are huge and reads are mostly by member and recent time.
- Amazon shopping cart — retail backend engineer likes DynamoDB-style key access because most requests are get-cart and update-cart by customer.
- GitHub rate-limit counters — platform engineer uses Redis-like in-memory storage for hot counters and expiry windows, not for durable issue history.
- Notion page content — product engineer benefits from document-shaped storage when blocks and nested properties evolve faster than a strict schema.
Pause and recall¶
- Why is "SQL or NoSQL?" the wrong first question?
- Which four axes should you check before naming a database?
- In the worked example, which requirement pushed the core order store toward Postgres?
- When is polyglot persistence a sensible move rather than needless complexity?
Interview Q&A¶
Q: Why choose Postgres instead of Cassandra for a payments or order ledger? A: Because correctness, constraints, and relational queries dominate. Cassandra shines with huge partition-oriented writes, but that is not the hardest part here.
Common wrong answer to avoid: "Because SQL databases are always safer" — safety comes from fit to invariants and access patterns, not from the label alone.
Q: Why choose DynamoDB instead of MongoDB for a pure key-value workload at very high scale? A: If the access pattern is mostly primary-key gets and updates, DynamoDB gives predictable scaling without pretending you need document flexibility.
Common wrong answer to avoid: "Because MongoDB cannot scale" — it can scale, but the question is which model matches the workload more directly.
Q: Why choose MongoDB instead of Postgres for some content-heavy product records? A: When one request usually reads or writes one nested document and fields evolve often, document storage lowers modelling friction.
Common wrong answer to avoid: "Because joins are bad" — joins are useful when relationships are central; they are just not always the main shape.
Q: Why use Redis around a primary database instead of making Redis the system of record? A: Redis is excellent for speed, TTL, and counters. It is usually a poor place to anchor the most durable, auditable business truth.
Common wrong answer to avoid: "Because Redis loses data" — durability settings exist, but the broader issue is mismatch between memory-first access patterns and core-record needs.¶
Apply now (5 min)¶
Exercise: Pick one domain: chat, ecommerce, ride-hailing, or analytics. Write the top three query verbs. Then write the hardest correctness rule. Then pick one primary store and one sentence defending it. Sketch from memory: Draw a two-column table. Put access patterns on the left. Put storage choice on the right. Circle the single requirement that dominated your decision.
Bridge. You picked storage. But reads are slow. 80% of traffic is reads. The kitchen is making the same dish from scratch every time. We need a shortcut. → 08-caching-where-and-why.md