systemsadvanced 20m read

Designing for Scale · The Storage Decision Tree

Databases are not religions. Choosing between relational, wide-column, document, and blob storage by mapping the access pattern before looking at the engine.

The problem this post solves

Most storage decisions are made backwards. The engine is chosen based on familiarity, and the access pattern is tortured to fit it.

This post reverses the direction. Storage is a pure consequence of the access pattern: how the data is written, how it is read, and what happens when it grows. The goal is to draw a decision tree that takes an access pattern as input and produces the correct category of storage as output, eliminating the need to debate specific vendors until the category is locked.

First principles

From first principles

Start with the question

Why is the access pattern the only input that matters?

1
Every database is heavily optimized for a specific layout on disk.
forced by · B-trees favor localized reads; LSM trees favor fast writes.
2
Reading data in a way that fights the disk layout requires scanning or network hops.
forced by · If data is not co-located, retrieving it requires seeking across the disk or coordinating across partitions.
3
Scanning and coordination do not scale.
forced by · They consume CPU and network bandwidth at a rate proportional to data size, which guarantees eventual collapse.
4
Therefore, the layout must match the read pattern, and the engine must match the layout.
forced by · There is no engine that can efficiently serve a read pattern that fights its fundamental disk structure.

⇒ Therefore

You cannot choose a database until you know exactly how you will query it.

The decision tree

The choice of storage engine is rarely a choice between ten things. It is usually a series of binary branches.

Relational vs NoSQL

Common misconception

✗ What most people think

NoSQL is for scale, SQL is for consistency.

✓ What is actually true

NoSQL is for predictable access patterns, SQL is for flexible access patterns. Both scale; both offer consistency.

Why the myth is so sticky

Modern relational databases can scale horizontally (NewSQL), and modern NoSQL databases offer ACID transactions. The true distinction is how they lay out data. SQL normalizes data, paying a join cost at read time to allow flexible queries. NoSQL denormalizes data, optimizing for a specific read path at the cost of query flexibility.

Prove it to yourself

If your access patterns will change over time, or you need ad-hoc reporting, NoSQL will punish you. If your access patterns are rigid and high-volume, SQL joins will punish you.

When choosing a relational database, you are buying the ability to ask questions tomorrow that you did not anticipate today. You are paying for it with the CPU cost of joins and the operational cost of scaling a monolithic primary node.

When choosing a NoSQL database, you are buying predictable, single-digit millisecond latency at any scale. You are paying for it by losing the ability to join, aggregate, or query flexibly. The data must be written exactly as it will be read.

Wide-column and Time-series

When the write volume becomes extreme, B-tree based databases (like most relational databases) begin to thrash. They must update indexes and rebalance pages on disk, which creates write amplification.

Wide-column stores and time-series databases use Log-Structured Merge (LSM) trees. They write only sequentially to memory and an append-only log, flushing to immutable files on disk.

The tradeoff

When to use an LSM-based Wide-Column store

B-Tree (Relational, Document)

+ you gain Fast reads, strong consistency, flexible secondary indexes.

− you pay Slow writes, write amplification, expensive to scale horizontally.

pick when Read-heavy workloads where immediate consistency is required.

LSM Tree (Wide-Column)

+ you gain Sustained high write throughput, predictable horizontal scaling.

− you pay Reads can be slower (must check multiple SSTables), complex tombstone management.

pick when Write-heavy workloads: telemetry, IoT, logging, chat history.

Row vs Column Orientation

This distinction is specifically for analytical workloads (OLAP).

In a row-oriented database, all fields for a single record are stored contiguously on disk. Reading a single row is fast. Reading one column across a billion rows requires loading the entire dataset into memory.

In a column-oriented database, all values for a single column are stored contiguously. Reading one column across a billion rows requires touching only a tiny fraction of the disk.

Mental modelThe OLTP vs OLAP divide

OLTP (Transactional) is a sniper rifle: row-oriented, finds one complete record instantly. OLAP (Analytical) is a snowplow: column-oriented, sweeps up millions of values for a single attribute to aggregate them.

Never run analytical aggregates on an OLTP database. It ruins the cache for transactional users.
Never run point lookups on an OLAP database. It is unoptimized for finding single needles.
Move data between them via Change Data Capture (CDC).

🔔 Fires when you see

Use when someone asks to run a dashboard query against the primary production database.

Object vs Block Storage

For unstructured data—images, video, backups—the choice is between block storage and object storage.

Block storage behaves like a hard drive. It is mounted to a specific operating system, supports random access modifications, and is expensive.

Object storage behaves like an API. It is accessible over HTTP, supports only full-file replacement, and is cheap.

For web-scale applications, object storage is the only viable choice for user media. It offloads the bandwidth and durability problem entirely to the cloud provider.

What to carry forward

Key points

You can now

Categorize a storage problem based on its access pattern.
Defend the choice of NoSQL over SQL using read/write paths, not scale myths.
Explain why LSM trees outperform B-trees on write-heavy workloads.
Separate transactional from analytical workloads at the storage layer.