Choose Relational Document Key-Value and Wide-Column Stores
Purpose: pick the store that matches the access pattern
In system design, storage choice is not a brand preference. It is a decision about how the system will read, write, update, index, and recover data under load. Relational, document, key-value, and wide-column stores all persist records, but they organize bytes differently and therefore make different operations cheap or expensive. The outcome of this lesson is a practical decision method: start from entities, query shapes, consistency needs, data growth, and operational failure modes, then choose the store whose internal model fits those pressures.
The four families are easiest to separate by their primary unit of organization. A relational database stores rows in tables and uses schemas, constraints, joins, and transactions to protect relationships. A document database stores nested documents, usually JSON-like, and optimizes for fetching an aggregate in one read. A key-value store maps an opaque key to an opaque value and is fastest when the application already knows the key. A wide-column store partitions rows by a key and stores sparse columns in sorted order, which supports very large write-heavy datasets when queries are designed around partition and clustering keys.
How the storage models work internally
A relational engine treats tables as logical relations. Rows have fixed columns, types, indexes, and optional foreign keys. The query planner turns SQL into an execution plan: scan this index, join these rows, filter predicates, aggregate results, and return a set. B-tree indexes keep ordered keys so point lookups and range scans are efficient. Transactions use isolation rules and write-ahead logging so a committed update can survive a crash and related changes can succeed or fail together. This makes relational storage strong for money movement, inventory, permissions, and other domains where invalid relationships are more dangerous than slower writes.
A document store centers the aggregate. A user profile document might contain addresses, preferences, and recent devices in one object. The database can index selected fields inside that object, but the application usually reads or replaces a whole document or subdocument. This reduces join work for screens that show one aggregate, but duplication is common. If an address is embedded in three order documents, changing it later may require three updates. Document stores fit catalogs, profiles, content, and event payloads where fields vary across records and reads usually retrieve one logical object.
A key-value store has the smallest data model. The storage engine hashes or routes a key to a shard and stores a byte string, JSON blob, counter, set, or similar value depending on the product. It generally cannot answer ad hoc questions such as "find all users in Ohio" unless the application created a separate key or index for that lookup. This simplicity gives excellent latency for sessions, cache entries, rate limits, feature flags, and precomputed results. The trade-off is that modeling discipline moves into application code.
A wide-column store is often misunderstood as a table database. It has tables, but the table is designed around a partition key and sorted clustering columns. Rows with the same partition key are stored together; columns may be sparse and families can be stored separately. The engine can append high write volumes and replicate partitions across nodes. Queries must be predictable: "all readings for sensor 9 between 10:00 and 11:00" works well if sensor id is the partition key and timestamp is the clustering key. "all sensors with temperature above 80" is expensive unless modeled as another table.
Syntax and schema anatomy
The anatomy of a storage decision is a set of constraints, not only a schema. Name the primary entity, the read path, the write path, the required consistency, the expected cardinality, and the failure behavior. Then map those requirements to the native API of each store.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
status TEXT NOT NULL,
total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
created_at TIMESTAMP NOT NULL
);
CREATE INDEX orders_customer_created
ON orders (customer_id, created_at DESC);This relational schema declares types and invariants. The check constraint rejects negative totals before bad data spreads. The index matches a common access path: recent orders for one customer.
{
"collection": "products",
"document": {
"sku": "bag-219",
"name": "Commuter bag",
"attributes": { "color": "black", "capacity_liters": 18 },
"variants": [
{ "id": "bag-219-s", "stock": 14 },
{ "id": "bag-219-l", "stock": 3 }
]
},
"indexes": ["sku", "attributes.color"]
}The document version keeps product details and variants together because product pages normally need the full aggregate. Indexing selected fields allows lookups by SKU or color without requiring a fixed column for every possible attribute.
def choose_store(needs_join, reads_by_known_key, write_volume, flexible_fields):
if reads_by_known_key and not needs_join:
return "key-value"
if write_volume == "very_high" and not needs_join:
return "wide-column"
if flexible_fields and not needs_join:
return "document"
return "relational"
print(choose_store(True, False, "moderate", False))
print(choose_store(False, True, "high", True))The output is deterministic: the first case returns relational because joins and constraints matter; the second returns key-value because direct lookup dominates and relationships do not.
Worked example 1: checkout orders
An ecommerce checkout creates an order, reserves inventory, records payment intent, and later reconciles shipment. These entities are related and correctness matters. If an order total is negative, a payment references the wrong order, or inventory is decremented without an order, the business has data corruption. A relational database is the default choice.
The expected behavior is transactional: inserting the order and its order lines commits as one unit. If the inventory update fails, the transaction rolls back and the order remains uncreated or marked as failed according to the workflow. The database can enforce foreign keys between orders and order_lines, unique constraints on payment provider ids, and indexes for customer support queries. A document store could keep an order aggregate, but cross-order reporting and strict relationships become harder. A key-value store is wrong as the system of record because it does not naturally protect relationships. A wide-column store may serve analytics or order-event history, but not the core checkout transaction.
Worked example 2: product catalog with varied attributes
A product catalog may include shoes with sizes, laptops with processor details, furniture with dimensions, and groceries with nutrition fields. Forcing every possible field into relational columns creates sparse tables and frequent migrations. A document store fits the read path: product pages, search indexing jobs, and merchandising tools usually fetch one product aggregate at a time.
The expected behavior is flexible evolution. Adding "battery_watt_hours" to laptop documents does not require adding that field to shoes. The application can validate required fields by product category, while the database indexes common fields such as sku, brand, and category. The main trade-off is update duplication. If brand metadata is copied into every product document, a brand rename becomes a batch update. If brand is normalized into a relational table, the read path may need joins or application-side composition. The design choice depends on whether display speed or centralized reference integrity is more important.
Worked example 3: time-series readings at large scale
Consider a fleet of devices sending a reading every second. The main query is "show readings for device D between start and end," and writes are continuous. A wide-column store is strong when the table is modeled as partition key device_id and clustering key timestamp. Rows for one device are colocated and sorted, so range reads are efficient.
The expected behavior is fast append and bounded range scan. A query for one device over one hour reads a contiguous slice. A query for all devices over one hour touches many partitions and should be handled by a separate analytics pipeline or a second table designed for that query. A relational database can handle smaller volumes and richer queries, but hot indexes and write amplification may become bottlenecks. A key-value store could store one key per reading, but range queries require naming conventions and manual fan-out. A document store could bucket readings into documents, but concurrent updates to hot buckets can create contention.
Design choices and trade-offs
Choose relational when the data model has durable relationships, multi-row invariants, ad hoc querying, and a need for strong transactional semantics. The cost is schema migration work and careful scaling once a single primary database becomes hot. Vertical scaling, read replicas, partitioning, and eventually sharding are common steps, each adding operational complexity.
Choose document when the application reads and writes aggregates that naturally fit in one document and fields vary by record. The cost is duplicated data and weaker protection against cross-document inconsistency. You must decide which facts are authoritative and which are cached copies.
Choose key-value when access is by exact key and low latency matters more than query flexibility. The cost is limited secondary querying and the need to design key names, expiration, serialization, and invalidation carefully. A cache key such as user:42:permissions:v3 can be excellent; using the same store as the only source for complex account relationships is usually brittle.
Choose wide-column when writes are enormous, queries are known in advance, and partition-aware modeling is acceptable. The cost is that each important query may need its own table. Poor partition keys create hot partitions; overly broad partitions create slow reads and compaction pressure.
Failure modes and troubleshooting
Relational symptom: checkout requests slow down and database CPU spikes. A likely cause is a missing composite index for a high-volume query such as recent orders by customer. Diagnose by inspecting query plans and checking whether the database scans many rows. Correct by adding an index that matches equality columns first and range or sort columns next, then re-test write overhead because every index also slows writes.
Document symptom: user profile screens show mixed old and new address values. The cause is duplicated address data embedded in profile, order, and shipment documents. Diagnose by tracing which document owns the canonical value and listing update paths. Correct by normalizing the frequently changed fact, or by using an asynchronous repair job with version fields so stale copies can be detected.
Key-value symptom: cache hit rate is high but users see stale permissions after role changes. The cause is key invalidation that does not include the permission version. Diagnose by comparing the source-of-truth permission row with the cached value and key name. Correct by deleting affected keys on role change or embedding a monotonically increasing version in the key.
Wide-column symptom: one node receives disproportionate traffic and latency rises for a single tenant. The cause is a hot partition, often a partition key with too little cardinality such as tenant_id for a very large tenant. Diagnose by checking per-partition read and write metrics. Correct by adding a bucket component, such as tenant_id plus day or tenant_id plus hash bucket, while preserving the query’s ability to read a bounded number of partitions.
Security, performance, and reliability implications
Security follows the data model. Relational databases often centralize sensitive records, so row-level permissions, least-privilege roles, audited migrations, and encrypted backups matter. Document stores require attention to field-level exposure because one read can retrieve a large nested object. Key-value stores often hold sessions and tokens, so expiration, transport encryption, and avoiding sensitive data in keys are important. Wide-column stores may replicate data broadly; retention policies and tombstone cleanup affect both compliance and performance.
Performance is a modeling property. The fastest store used with the wrong query shape will still be slow. Reliability is also model-dependent: relational transactions protect invariants, document version checks prevent lost updates, key-value compare-and-set operations protect counters or locks, and wide-column quorum settings trade latency against consistency. In a mature architecture these stores are often combined: relational for orders, document for catalog, key-value for cache, and wide-column for telemetry. The design challenge is making one store authoritative for each fact.
Hands-on lab: classify storage needs
Prerequisites: Python 3 and a terminal. This lab uses an in-memory script, so there is no database to install. The goal is to practice mapping requirements to store families and verifying the recommendation.
- Create a file named storage_choice_lab.py in a scratch directory.
- Paste the script below.
- Run python3 storage_choice_lab.py.
- Compare each printed recommendation with the reason and decide whether you would override it in a real design review.
cases = [
{"name": "checkout", "joins": True, "known_key": False, "huge_writes": False, "flexible": False},
{"name": "product_catalog", "joins": False, "known_key": False, "huge_writes": False, "flexible": True},
{"name": "session_cache", "joins": False, "known_key": True, "huge_writes": False, "flexible": False},
{"name": "device_readings", "joins": False, "known_key": False, "huge_writes": True, "flexible": False},
]
def recommend(case):
if case["joins"]:
return "relational: relationships and invariants dominate"
if case["known_key"]:
return "key-value: exact-key lookup dominates"
if case["huge_writes"]:
return "wide-column: partitioned append and range reads dominate"
if case["flexible"]:
return "document: aggregate shape varies by record"
return "relational: general querying is still useful"
for case in cases:
print(f"{case['name']}: {recommend(case)}")Verification: the script should print four lines. The expected recommendations are checkout as relational, product_catalog as document, session_cache as key-value, and device_readings as wide-column. Cleanup: delete the scratch file. There is no persistent state or rollback step because the lab does not create a database.
Assessment exercises
- A messaging app needs conversation history by conversation id, unread counts by user, and moderation searches by keyword. Which facts would you store in which family, and which store is authoritative for each fact?
- A document model embeds customer tier inside every order for faster support screens. Customer tier changes monthly. Describe the failure mode and a repair strategy.
- A wide-column table uses country as the partition key for click events. Explain why this creates hot partitions and propose a better key for queries by customer and time range.
- A team wants to replace a relational billing database with a key-value store for speed. Identify the invariants they would lose and one safer architecture that still improves read latency.
Summary
Relational, document, key-value, and wide-column stores differ because their internal organization differs. Tables, documents, keys, and partitioned column families make different operations natural. The practical system-design move is to begin with access patterns and invariants, then choose the store that makes the important path simple: relational for constrained relationships, document for flexible aggregates, key-value for exact-key latency, and wide-column for partitioned high-volume writes and range reads.
