255: Storage Model Selection: Relational, Document, Key-Value, Wide-Column, Graph, and Time-Series

Learning outcomes

By the end of this lesson, you can:

explain and apply relational databases in a realistic implementation;
explain and apply document databases in a realistic implementation;
explain and apply key-value stores in a realistic implementation;
explain and apply wide-column stores in a realistic implementation;
explain and apply graph databases in a realistic implementation.

Prerequisites and retrieval

This lesson builds on the 01–06 foundation and the earlier lessons in this module. Before you start, retrieve one concrete example from a previous project where this same concern appeared. Perhaps you chose a database for user data, a cache for sessions, or a store for events without first writing down the access pattern. The point is not to memorize product categories. It is to make a defensible decision for a large-scale distributed service, where requirements, traffic, failure modes, cost, and operational constraints all need to be explicit.

Terminology

Relational databases: Strong schemas and constraints, transactions, joins, and mature query optimizers make relational databases a good fit for connected business data and invariants.
Document databases: Documents work well for aggregate-oriented data that is normally retrieved or updated together, especially when nested shapes evolve over time.
Key-value stores: Key-value systems optimize access by a known key. They commonly power caches, sessions, counters, and high-scale simple lookups, while their support for secondary or ad-hoc queries varies widely.
Wide-column stores: Partition-key and clustering-key models support very large distributed datasets with predictable query patterns. In exchange, they require partition-aware design and give up much of the flexibility of joins and ad-hoc access.
Graph databases: Graph stores optimize relationship traversal and graph queries when edges and path patterns are the primary concern. At moderate scale, however, many graph-shaped workloads still fit well in a relational database.
Specialized stores: Time-series databases, search engines, object stores, vector stores, and analytical warehouses target distinct workloads rather than serving as interchangeable general-purpose databases.

Mental model

Treat Storage Model Selection: Relational, Document, Key-Value, Wide-Column, Graph, and Time-Series as a design problem with observable inputs, outputs, invariants, and failure modes. Database selection starts with access patterns, invariants, consistency expectations, scale, and operations. “NoSQL scales” and “SQL is relational” are not decision criteria by themselves. A sound implementation makes its assumptions visible, narrows uncertainty at its boundaries, and leaves evidence such as tests, types, constraints, metrics, or diagrams that explains why the design is safe.

A useful sequence for both interviews and production design is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not leap from a requirement straight to a library call. First state what must remain true. Then choose the storage mechanism that makes those guarantees enforceable and observable.

Deep dive

1. Relational databases

Start with the data relationships and the rules that must hold. Relational databases provide strong schemas and constraints, transactions, joins, and mature query optimizers, so they fit connected business data and important invariants particularly well. They can scale vertically, through replicas and partitioning, and in some products through distributed variants. Those scaling options still have different costs and operational behaviors, so “relational” does not describe one fixed capacity or deployment model.

Decision rule: Use a relational database deliberately when it makes the contract or invariant easier to prove. If the choice only reduces typing while hiding an assumption, prefer the more explicit design.

2. Document databases

If an application usually reads and updates one aggregate as a unit, a document can keep that data together and avoid reconstructing it through multiple joins. Documents are also useful when nested shapes evolve. The trade-off appears when data crosses aggregate boundaries: relationships between documents, consistency across them, and multi-document transactions may become more complicated than they would be in a relational model.

Decision rule: Use a document database deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

3. Key-value stores

Key-value systems are strongest when the application already knows the lookup key. That makes them a natural fit for caches, sessions, counters, and other simple lookups that need to operate at high scale. They are not automatically a good fit for data that users will query in several unplanned ways. Secondary-index and ad-hoc query capabilities vary widely, and the access pattern must be designed around the actual store rather than assumed after the fact.

Decision rule: Use a key-value store deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

4. Wide-column stores

Wide-column stores organize data around a partition key and, often, clustering keys. This model can support huge distributed datasets when the query patterns are known in advance. It also means partition size, key distribution, and the queries the system must serve are design inputs, not implementation details. The model trades flexible joins and ad-hoc access for scale and predictable, partition-aware reads.

Decision rule: Use a wide-column store deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

5. Graph databases

Graph databases earn their complexity when relationship traversal is the workload: for example, following connected entities or evaluating path patterns. They optimize graph queries when the edges are more important than isolated records. That does not mean every domain containing relationships needs a graph database. At moderate scale, a relational model can represent many graph-shaped workloads clearly and efficiently.

Decision rule: Use a graph database deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design.

6. Specialized stores

Some workloads have requirements that are not well represented by a general-purpose application database. Time-series stores target timestamped measurements, search systems target text and relevance queries, object stores target large blobs, vector stores target similarity search, and analytical warehouses target aggregations over large datasets. Polyglot persistence can be the right answer, but only when the benefit outweighs the synchronization work and additional operational complexity.

Decision rule: Use specialized stores deliberately when they make the contract or invariant easier to prove. If the choice only reduces typing while hiding an assumption, prefer the more explicit design.

Worked example

Consider a large-scale distributed service. Before selecting storage, write the requirement in one sentence, list the input and output contracts, and identify which concept above owns each failure mode. This forces the decision to start from behavior rather than from a favorite database.

The useful separation is between layers. Parsing and validation belong at the boundary. Domain rules belong in the domain or service layer. Persistence rules belong in the database or repository. Presentation rules belong in the client. Mixing those concerns can make a happy-path demo look shorter, but it makes retries, malformed values, missing records, and partial failures much harder to reason about.

Client
  |
DNS -> CDN / Edge
  |
Load Balancer -> API instances -> Cache
                          |          |
                          +------> Primary datastore
                          |
                          +------> Queue / Stream -> Workers

Walk through at least four cases:

the normal path;
an empty or missing value;
a duplicate, retry, or concurrent path where that behavior is relevant;
a dependency failure.

For every case, state which layer detects the problem and what the caller observes. For example, boundary validation may reject malformed input before the repository is called, while a datastore timeout is a dependency failure that needs a defined error and retry policy. This is the level of explanation expected in a senior code review or technical interview: not just which component exists, but which component owns each guarantee.

Production perspective

“The code works on my machine” is only the beginning of production correctness. Ask how the design behaves during deploys, retries, partial failures, stale clients, concurrent requests, malformed data, schema changes, and high-cardinality workloads. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after evidence identifies a bottleneck or risk.

Whenever the design calls an external dependency, define its timeout and cancellation strategy. Whenever it persists data, define transaction and consistency expectations. Whenever it exposes user-visible state, define loading, empty, error, stale, and success states. Whenever security is involved, assume the client can be modified and all network input is untrusted.

Guided lab

For an ecommerce platform, choose storage for orders, sessions, catalog documents, product search, images, and metrics. For each choice, state the access pattern, the invariant, the consistency expectation, and the operational cost.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

The lab is deliberately open-ended. A good answer is not the one that names the most specialized technologies; it is the one that connects each choice to the workload and makes its trade-offs visible.

Edge cases and failure modes

Relational databases: Test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Document databases: Test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Key-value stores: Test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Wide-column stores: Test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Graph databases: Test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.

The exact test differs by storage model, but the questions remain consistent: what happens when the value is missing or invalid, when the same operation arrives twice, when requests overlap, and when the data grows beyond the size used in development?

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern may be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and discovering the real contracts only after integration.
Optimizing before measuring, or choosing a scalable mechanism without an actual scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

When debugging, reproduce the smallest failing case first. Inspect the actual value or execution plan, trace the boundary where the invariant first becomes false, and fix the layer that owns the rule instead of adding a downstream patch. Depending on the failure, that boundary may be the request parser, the server route, the repository, the database query, or the deployment configuration.

Interview questions

What problem do relational databases solve, and what trade-off or failure mode would make you choose a different approach?
What problem do document databases solve, and what trade-off or failure mode would make you choose a different approach?
What problem do key-value stores solve, and what trade-off or failure mode would make you choose a different approach?
What problem do wide-column stores solve, and what trade-off or failure mode would make you choose a different approach?
What problem do graph databases solve, and what trade-off or failure mode would make you choose a different approach?

Checkpoint

Without notes, explain Storage Model Selection: Relational, Document, Key-Value, Wide-Column, Graph, and Time-Series to another developer in five minutes. Include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code. Your explanation should make clear not only what storage model you selected, but why its access patterns and guarantees fit the requirement.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.