03Building blocksDatabases
03 · Building block
Databases
Choose relational, key-value, document, wide-column, graph, or time-series storage from access patterns and guarantees.Architecture map
See where the component sits in a real system.
Write
Application clients enters through Application service. Transaction boundary owns validation and commits the durable record to Primary database.
Propagate
WAL / CDC log separates the committed write from background work. Projection workers can retry safely while it builds Replicas + indexes.
Read
Query layer serves from Replicas + indexes, then checks authoritative state whenever freshness, policy, or correctness requires it. It also consults Cold archive as an explicit dependency.
Say this first: One primary store owns truth; replicas, indexes, and archives serve explicit derivative workloads.
Open the full whiteboard ↗Explain every boundary before adding more boxes.
One primary store owns truth; replicas, indexes, and archives serve explicit derivative workloads.
Choose relational, key-value, document, wide-column, graph, or time-series storage from access patterns and guarantees.
State read/write ratio · working set · transaction boundary · retention · shard count. State average and peak load, stored bytes, bandwidth or open connections, and the growth horizon before choosing a partitioning strategy.
End-to-end walkthrough
Trace the architecture in this order.
- 01
Enter and classify the request
Application clients → Application serviceCommands + queries enters over HTTPS / RPC. Application service handles identity, admission, routing, and request context; it deliberately does not own domain truth.
- 02
Validate, then cross the commit boundary
Application service → Transaction boundary → Primary databaseTransaction boundary receives the command, checks invariants and retry identity, then uses ACID / conditional write to update Primary database. The user-visible mutation is accepted only after this boundary succeeds.
- 03
Move replayable work off the request path
Transaction boundary → WAL / CDC log → Projection workers → Replicas + indexesTransaction boundary emits tail commit log; Projection workers uses consume and project / update to build Replicas + indexes. Consumers must tolerate duplicate delivery and stale retries because this path is asynchronous.
- 04
Serve reads from the right authority
Application service → Query layer → Replicas + indexes / Primary databaseQuery layer uses stale-tolerant read for the common, read-optimized path and strong read when correctness or repair requires authoritative state. The API must state the freshness promise instead of hiding it.
- 05
Contain the dependency boundary
Projection workers → Cold archivesnapshot / tier crosses into Cold archive. Treat timeouts as ambiguous, use a deadline and idempotent retry or reconciliation, and keep the core state recoverable when the dependency is unavailable.
Ownership ledger
Why each box exists—and what it must defend.
| Component | Owns | Why it exists | Interviewer probe |
|---|---|---|---|
| Application serviceModel access patterns | Identity, admission, routing | Protects the system edge and attaches trusted context before domain work begins. | Timeout budgets, quotas, regional routing |
| Transaction boundaryValidate invariants | Write invariants and retry identity | Serializes or conditionally applies state changes before acknowledging success. | Concurrent writes, deduplication, hot ownership |
| Primary databaseAuthoritative records | Authoritative durable state | Provides the one record used to resolve disputes, recover, and rebuild projections. | Partition key, replication, consistency |
| WAL / CDC logOrdered committed changes | Durable asynchronous handoff | Absorbs bursts and lets slow or optional work retry independently of the request. | Ordering key, lag, retention, dead letters |
| Projection workersReplay + version checks | Replayable processing | Runs expensive, fan-out, or side-effecting work with leases and bounded retries. | Idempotency, poison work, autoscaling |
| Replicas + indexesRead/search/analytics views | Rebuildable query state | Shapes data for the dominant reads without weakening the write-side invariant. | Freshness, versioning, rebuild time |
| Query layerRoute by consistency need | Read composition and freshness policy | Chooses authoritative or derived state and returns a stable client contract. | Fan-out, cache policy, partial results |
| Cold archiveSnapshots + retention | External capability, not local truth | Keeps a specialized or third-party concern behind a replaceable contract. | Ambiguous timeout, circuit breaking, fallback |
Physical design
Name the database, shard key, indexes, and guarantees.
- Database + storage
- Reference deployment: PostgreSQL for transactional truth, Redis for cache/session data, and OpenSearch/ClickHouse only as derived stores.
- Partitioning / sharding
- Start unsharded. When one primary fails writes/storage, hash by tenant or aggregate owner; use range/time shards only for dominant range scans.
- Indexes
- Build indexes from named queries: equality columns, then range/sort; use covering/partial indexes selectively and measure write amplification.
- Replication + consistency
- Use transactions for money/uniqueness. Multi-AZ primary/standby protects commits; read replicas scale reads; derived stores are eventual.
- Cache, queue + recovery
- Cache-aside with versioned keys and an outbox/CDC for derived stores. Consumers apply source versions idempotently and expose lag.
- Capacity math
- Estimate rows, bytes/row, read/write QPS, working set, growth years, contention, and replica lag before sharding.
- Alternative rejected
- Do not pick NoSQL because it 'scales'; key-value/document/wide-column/graph must be earned by query shape and guarantee.
Deep-dive candidates
Pick one risk and explain the mechanism, alternative, and cost.
Relational stores
Use transactions, constraints, joins, and mature indexing when entities and correctness rules are tightly related.
Tie the mechanism back to Primary database, Replicas + indexes, and the stated state read/write ratio · working set · transaction boundary · retention · shard count envelope.Key-value and document stores
Prefer simple key access or aggregate-shaped documents when horizontal partitioning and flexible schema outweigh joins.
Tie the mechanism back to Primary database, Replicas + indexes, and the stated state read/write ratio · working set · transaction boundary · retention · shard count envelope.Wide-column and time-series stores
Use them for very high append volume, predictable partition keys, retention, and range scans over time.
Tie the mechanism back to Primary database, Replicas + indexes, and the stated state read/write ratio · working set · transaction boundary · retention · shard count envelope.Failure pressure test
Show detection, containment, recovery, and evidence.
The topic-specific correctness risk
A poor partition key creates hot shards, cross-shard transactions, and painful migration.
Track failed promises at Primary database and Replicas + indexes.WAL / CDC log or Projection workers falls behind
Bound admission, scale on oldest-work age, retry with jitter, and isolate poison work before lag becomes unbounded.
Oldest event age · retry rate · dead-letter volume · projection freshnessPrimary database is slow or unavailable
Apply a deadline, preserve retry identity, fail over only within the stated consistency model, and reconcile any ambiguous result.
Commit p99 · timeout rate · replication lag · recovery time- Functional requirements and non-goals
- Peak traffic, storage, bandwidth, and growth
- Entities, APIs, idempotency, and pagination
- Source of truth and consistency promise
- Partition key, replicas, caches, and hot spots
- Retries, backpressure, failover, and reconciliation
- Latency, saturation, correctness, and recovery metrics
- Security, migration, cost, and multi-region evolution
Read the solid request path first, stop at the source of truth, then follow the dashed event path into workers and rebuildable read models. Every arrow names a contract you should be ready to defend.
- 01
Service — Issues modeled reads and writes Define the output contract before moving to the next owner.
- 02
Primary store — Owns committed records Define the output contract before moving to the next owner.
- 03
Replica — Scales tolerant reads Define the output contract before moving to the next owner.
- 04
Change log — Publishes mutations Define the output contract before moving to the next owner.
- 05
Derived store — Serves search or analytics Define the output contract before moving to the next owner.
- 06
Archive — Retains cold data Confirm the result and emit the evidence needed to reconcile it.
Lesson spine
What you need to understand.
Choose a database from access patterns, invariants, and operational needs—not from a product label.
Relational stores
Use transactions, constraints, joins, and mature indexing when entities and correctness rules are tightly related.
Key-value and document stores
Prefer simple key access or aggregate-shaped documents when horizontal partitioning and flexible schema outweigh joins.
Wide-column and time-series stores
Use them for very high append volume, predictable partition keys, retention, and range scans over time.
Graph stores
Earn them with multi-hop traversal that is central to the product; an adjacency list in SQL is often enough.
Replication and sharding
Replicas add read capacity and availability; shards add write and storage capacity while making resharding and cross-shard work explicit.
Polyglot persistence
One authoritative database plus derived caches, indexes, and analytics stores is safer than treating every copy as truth.
Before the boxes
Frame the decision.
What must work
Choose relational, key-value, document, wide-column, graph, or time-series storage from access patterns and guarantees.
What changes the design
State read/write ratio · working set · transaction boundary · retention · shard count
What owns the truth
Identify the component that commits authoritative state, then separate synchronous confirmation from derived work.
What stays simple
Do not add global coordination, multi-region writes, or a specialized store until a requirement earns the complexity.
Decision table
Make the trade-offs explicit.
| Decision | Defensible position | Cost to acknowledge |
|---|---|---|
| Primary mechanism | Choose relational, key-value, document, or wide-column storage from access patterns and transaction boundaries. | The stronger guarantee usually adds coordination, latency, state, or operational work. |
| Sync vs. async | Keep only correctness-critical confirmation synchronous. Move derived views, notifications, analytics, and cleanup behind a durable boundary. | Async work needs idempotency, lag monitoring, replay, and a product definition for partial completion. |
| Simple vs. scaled | Begin with one logical owner and a clear API. Partition or replicate only the resource proven to be the first bottleneck. | Migration requires stable identities, versioned contracts, backfill, and a rollback path. |
Failure review
Design the recovery path.
Topic-specific risk
A poor partition key creates hot shards, cross-shard transactions, and painful migration.
ResponsePersist enough identity and state to distinguish retry, resume, compensation, and operator repair.
Dependency timeout
A timeout is ambiguous: the remote side may have failed, succeeded, or still be running.
ResponseUse deadlines, bounded backoff with jitter, idempotency keys, and a status or reconciliation path.
Overload or skew
Average capacity can look healthy while a tenant, key, partition, region, or expensive request saturates one owner.
ResponseExpose queue depth and hot-key share, apply backpressure, isolate tenants, and degrade optional work before correctness.
Evidence + level bar
Prove the design can be operated.
Health of the promise
Measure user-visible latency or freshness, correctness drift, saturation, retry volume, and time to recover. Alert on the failed promise—not only CPU.
Complete and clear
Finish the happy path, identify the state owner, choose reasonable building blocks, and explain one scale mechanism.
Trade-offs and failure
Separate read and write paths, define consistency, explain partitioning, and make duplicate or partial failure safe.
Evolution and operations
Discuss multi-region boundaries, migration, tenant isolation, capacity, observability, and how the architecture changes over time.
Interview language
Open the deep dive with a claim.
“For Databases, the decision I want to make explicit is this: Choose relational, key-value, document, or wide-column storage from access patterns and transaction boundaries. I’ll trace the state-changing path first, show where the result becomes durable, then test the design against the highest-risk failure and our target scale.”
08 · Retrieval check
Can you defend it without the page?
- For Databases, where is the correctness boundary and which failure would you test first?
- Which component owns committed truth, and what event or response proves the commit?
- Where is the first scaling or coordination bottleneck under the stated envelope?
- What happens after an ambiguous timeout or duplicate operation?
- Which complexity would you remove at one hundredth of the scale?