/system-and-data-design

name: system-and-data-design
description: >
  Decide whether the system will hold, and where the data lives: requirements and load
  first, then back-of-the-envelope numbers, building blocks (cache, queue, load
  balancer, CDN), and the data layer in depth — storage engines, indexes, replication,
  partitioning, transactions and consistency, batch vs stream. Use when sizing or
  scaling anything; choosing a database, cache, queue or index; when asked "will this
  hold", "how many machines", "which database", "do we need a queue", "read replica",
  "sharding", "eventual consistency", "why is this query slow at scale"; when designing
  an ingestion or processing pipeline; or when a service is slow under load rather than
  wrong. Do NOT use for module layout, dependency direction or domain boundaries (use
  architecture-first), for function- and naming-level quality (use code-complexity), for
  restructuring code that is already too large (use refactoring-safely), or for a
  low-traffic internal tool where the honest answer is one process and one database.

System and data design — will it hold, and where does the data live

Two questions that are usually asked together and answered separately, badly. Capacity without storage internals gives a diagram that cannot be built; storage internals without capacity gives a database choice with no reason behind it.

Scope guard — read first

The most common failure here is answering at the wrong scale.

| Situation | Honest answer | |---|---| | Internal tool, tens of users | One process, one database, no cache. Stop. | | Product with real traffic, single region | Estimate first; add a cache and a queue only where the numbers say | | Multi-region, or data outgrowing one machine | Full pass: estimation → building blocks → replication/partitioning → consistency |

Adding a cache before measuring is the canonical way to convert one problem into two.

Step 1 — requirements before architecture

• **Functional**: what must it actually do? Write it as verbs, not components.
• **Non-functional, with numbers**: users, requests/sec at peak, payload size, growth,

read:write ratio, acceptable latency, acceptable staleness, retention.

• **Constraints**: budget, team size, existing stack, compliance, where data may live.

"Acceptable staleness" is the single most useful number and the one nobody asks for. It decides caching, replication and consistency all at once.

Step 2 — back-of-the-envelope, before any diagram

Estimate storage/day, bandwidth at peak, QPS, and working-set size. The point is not precision; it is discovering that the answer is "one machine" or "this cannot work as described" before drawing anything.

Anchors worth remembering: memory reads are ~100ns, SSD ~100µs, a same-region round trip ~0.5ms, cross-continent ~150ms. Anything crossing a network is ~1000× a memory access — which is why one N+1 query pattern outweighs most micro-optimisation.

Step 3 — building blocks, each with a reason

| Block | Add it when | Cost you accept | |---|---|---| | Cache | Read-heavy, tolerable staleness, measured hot set | Invalidation becomes your problem | | Queue | Work can be async; spikes must be absorbed | Ordering, retries, duplicate delivery | | Read replica | Reads dominate; stale reads acceptable | Replication lag becomes visible to users | | Partitioning | One machine cannot hold data or throughput | Cross-partition queries and transactions get hard | | CDN | Static or cacheable content, geographically spread | Purge and versioning discipline |

Each row is a trade, not an upgrade. A block added without its reason written down is a future mystery.

Step 4 — the data layer

• **Storage engines**: log-structured (LSM) favours writes and compaction; B-tree favours

predictable reads and in-place updates. Choose by the workload's read:write shape, not by brand.

• **Indexes** are a write-cost you pay for a read-benefit. An index nobody's query plan

uses is pure cost.

• **Replication**: single-leader is the default and enough for most systems; multi-leader

and leaderless buy availability and pay in conflict resolution you must then design.

• **Partitioning**: choose the key by access pattern, not by what looks even. Hot keys

and unbounded partitions are the two failures; both are visible in a histogram before they are visible in production.

• **Transactions**: know which isolation level you actually get. Read-committed does not

prevent lost updates; "we use transactions" is not a consistency argument.

• **Batch vs stream**: batch for correctness and reprocessing, stream for freshness.

Streams that cannot be replayed lose the ability to fix a bug retroactively.

Review pass

• Is every component justified by a number, or by habit?
• What happens at 10× — which part breaks first, and is that acceptable?
• Where can it lose data, and is that written down?
• What is the failure mode of each dependency: degrade, queue, or fail loudly?
• Does any user-visible read cross a replication lag nobody bounded?

References — load on demand

• `references/system-design/four-step-process.md`, `estimation-numbers.md`,

`building-blocks.md`, `database-scaling.md`, `common-designs.md`, `reliability-operations.md`

• `references/ddia-systems/storage-engines.md`, `data-models.md`, `replication.md`,

`partitioning.md`, `transactions.md`, `batch-stream.md`, `fault-tolerance.md`

• `*-original.md` — the source skills' own framework prose, kept verbatim

Gotchas

• **Designing for a scale you do not have.** The cost is paid now, the benefit maybe

never. Estimate first; the estimate frequently says "one machine".

• **Cache as a fix for a slow query.** It hides the query and adds invalidation. Fix the

query, then decide about the cache.

• **Eventual consistency chosen by accident.** A read replica added for speed silently

makes some reads stale. Decide which reads may be stale, and say so.

• **The queue that became a database.