Designing for Scale · The Storage Decision Tree
Storage selection has several independent axes. A source-bounded lesson with worked reasoning, failure analysis and explicit runtime limitations.
Original publication date unrecordedUpdated
Lesson preparation & details
Level: advanced
By the end, you should be able to
- Storage selection has several independent axes
- A workload has heavy writes plus strict cross-account transfers and ad hoc support queries. Is “LSM means NoSQL” sufficient?
Bring with you
Sources checked: . A source check does not change the publication or update date.
Editorial review: · What review means
In this article · 10 sections
The problem this post solves
Start a storage decision with the operations and invariants, not a preferred database logo. A media upload, an atomic account transfer and an analytical scan ask different questions of the data system. Access patterns matter, but they are not the only input: transaction scope, latency/freshness requirements, retention, recovery, security, cost and operating skill can eliminate a storage choice that looks attractive on a throughput chart.
The goal is a shortlist with testable reasons, not a decision tree that always returns one correct vendor category. Logical data model, physical engine, replication protocol and API are separate axes. A relational database can store JSON and denormalized projections; a nonrelational product may offer transactions or secondary queries within specific boundaries. Check those boundaries rather than importing guarantees from a category name.
First principles
For each important operation write down the lookup/filter/order keys, result size, frequency, concurrent writers and atomicity requirement. Then ask what work the proposed layout must do: which pages/files/partitions it reads, what it rewrites on an update, and how an index or materialized view is maintained. A scan can be the right operation for a large analytical query; coordination can be necessary to preserve an invariant. Neither inherently guarantees collapse, and avoiding all joins is not a substitute for correct data modelling.
A storage plan also needs an authoritative copy and a recovery path. A search index, cache or analytical projection may be rebuildable from a transactional ledger or append-only log. State the replay/correction mechanism and acceptable lag. Adding more stores creates a cross-system consistency problem; it is not automatically more scalable or more reliable.
The decision tree
Figure text equivalent — labels and relationships
- Node “Operations and invariant” (square).
- Node “Atomic scope and recovery satisfied?” (diamond).
- Node “Reject candidate or redesign boundary” (square).
- Node “Lookup, range, scan and update patterns” (square).
- Node “Choose logical model and indexes” (square).
- Node “Compare physical layouts and partitioning” (square).
- Node “Test failure, restore and retention” (square).
- Node “Measure cost and operating burden” (square).
- Node “Document shortlist and trade-offs” (square).
- “Operations and invariant” leads to “Atomic scope and recovery satisfied?”. Line: normal; ends: arrow point.
- “Atomic scope and recovery satisfied?” leads to “Reject candidate or redesign boundary”: No. Line: normal; ends: arrow point.
- “Atomic scope and recovery satisfied?” leads to “Lookup, range, scan and update patterns”: Yes. Line: normal; ends: arrow point.
- “Lookup, range, scan and update patterns” leads to “Choose logical model and indexes”. Line: normal; ends: arrow point.
- “Choose logical model and indexes” leads to “Compare physical layouts and partitioning”. Line: normal; ends: arrow point.
- “Compare physical layouts and partitioning” leads to “Test failure, restore and retention”. Line: normal; ends: arrow point.
- “Test failure, restore and retention” leads to “Measure cost and operating burden”. Line: normal; ends: arrow point.
- “Measure cost and operating burden” leads to “Document shortlist and trade-offs”. Line: normal; ends: arrow point.
For large media objects, consider an object API plus metadata records. For transactional relationships and ad hoc joins, consider a relational system with the required isolation. For key-addressed state, a key-value API may simplify access. Time-series data needs an explicit entity/time partitioning and retention policy; it does not automatically imply one wide-column or LSM product. A document model may suit aggregate-shaped records but still needs indexes and a defined transaction boundary. Revisit the shortlist when the workload changes rather than forcing new queries through an obsolete layout.
Relational vs NoSQL
“NoSQL is for scale, SQL is for consistency” is a myth, not a premise. SQL is a query language and relational modelling is a logical organization; neither mandates one monolithic primary, normalization level or disk structure. “NoSQL” groups quite different document, key-value, wide-column and graph products. A category name does not promise single-digit-millisecond latency at any scale or forbid joins/aggregation.
Normalization helps represent dependencies without contradictory duplicated facts. A derived read model may denormalize for a known access pattern, but its update and reconciliation policy must be explicit. Joins have costs and can also use effective indexes and algorithms. Do not replace a measured query with a cross-service manual join merely to avoid the word SQL.
Consider a transfer between two accounts: debit and credit must commit together with the invariant the product requires. Two individually atomic key writes are not automatically an atomic transfer. A product's transaction scope, isolation/conflict handling and recovery behavior matter more than whether the API returns documents or rows. Separately assess support queries and history reporting; a store ideal for one point lookup may need an additional projection for another access pattern.
Wide-column and Time-series
B-trees organize ordered keys into pages and support equality/range/ordered access. Updates maintain the relevant pages/indexes and logging according to the engine. LSM-based engines buffer writes in memory, persist through a commit log and flush immutable sorted files; later compaction merges files and reclaims obsolete data under its rules. Cassandra 4.1 documents this commit-log, memtable and SSTable path. Not every time-series or wide-column product uses the same implementation, and a SQL interface can sit above an LSM engine.
LSM does not mean “writes only once” or “only sequential work.” Compaction rewrites data, reads may inspect multiple files, filters/indexes consume space, and tombstones have retention and replica-repair implications. B-trees do not imply strong consistency, nor do LSM trees imply eventual consistency: replication/transaction protocols establish those contracts. Compare write amplification, read amplification and space amplification on the actual workload, including steady-state compaction and recovery—not only a short empty-database load.
For telemetry keyed by (device, time), a time-bucketed partition can bound size and make
retention manageable. But a query across all devices for one region is a different access
pattern. A highly active device can become a hot partition even if total capacity looks
adequate. Choose bucket duration, key distribution and secondary projections using the actual
range queries, write skew and deletion obligations. TTL and compaction policy are not free
substitutes for capacity planning.
Row vs Column Orientation
Row/column orientation is independent of relational/NoSQL and B-tree/LSM classifications. A row-oriented layout keeps a record's fields together at its storage unit; a columnar layout organizes values by column, often within row groups. Parquet format 2.10.0 describes row groups, column chunks and pages. This supports reading selected columns and efficient encoding, but not a universal promise that every analytical query touches a tiny fraction of the dataset.
As an uncompressed payload model, one million rows with twenty 8-byte fields contain 160 MB. A query needing two such columns has a 16 MB selected-value payload. Actual I/O depends on page/row-group boundaries, metadata, compression, filtering, cached data and encoding. A row store can stream a scan; it need not load the entire dataset into memory. A covering index or projection may avoid reading every row field. Conversely, fetching one complete row from a columnar file may require several column reads.
OLTP and OLAP describe workloads, not forbidden combinations. An occasional indexed aggregate may be acceptable on a transactional system; an unbounded dashboard scan may disrupt it. Measure resource contention and isolate workloads with replicas, limits or a derived analytical store when justified. CDC is one way to maintain that projection, not the only possible path. The projection's lag, schema changes, deletes and reconciliation remain part of the design.
Object vs Block Storage
Block storage exposes addressable blocks used by a filesystem or database. It does not itself supply the application's schema, transactions or backup policy. Object storage exposes named objects and metadata through an API. Amazon S3's user guide describes objects stored in buckets; object-level behavior is not the same as a mounted filesystem's random-write interface. Provider APIs may include multipart upload, range reads, versioning or specialized append capabilities, so “all object stores only replace full files” is too broad.
For user media, object storage is often useful because application servers need not carry all payload bytes. It is not the only viable choice: workload locality, existing infrastructure, filesystem requirements and cost can justify alternatives. Compare requests, bandwidth/egress, storage classes, replication, restore time and operational work rather than declaring object always cheap and block always expensive. IAM, public access, key management, lifecycle, versioning and recovery configuration remain customer responsibilities; a cloud durability claim does not protect against every accidental authorized deletion.
An object store also does not automatically make a multi-file dataset a transactional table. A table format such as Iceberg publishes snapshots through its metadata commit protocol. Readers must use the committed table metadata rather than interpreting a directory listing of partially uploaded files as a complete new dataset. Source-object durability and table transaction semantics are separate guarantees.
Worked selection exercise
A service stores account balances, receipt PDFs and hourly revenue dashboards. Transfers must preserve total funds; PDFs are immutable once issued but access may be revoked; the dashboard may lag by five minutes. Is one “write-heavy NoSQL” choice sufficient?
Choose boundaries before multiplying databases
No. First select a transaction mechanism whose atomicity/isolation preserves the transfer invariant. Store receipt metadata and a protected object reference under an explicit issuance contract; enforce current access when downloading even if the object bytes are immutable. A dashboard can initially use bounded indexed queries if measured contention allows it, or a rebuildable analytical projection with a five-minute freshness objective and reconciled totals. Explain the source of truth and failure recovery for each. Additional engines earn their complexity only when the correctness/workload/operating requirements justify them.
What to carry forward
Compare independent axes: logical model, physical layout, transaction/replication contract, API and operations. LSM and columnar techniques offer specific mechanisms, not blanket wins. Use access patterns to propose a design, then challenge it with skew, partial failure, retention and restore. No database performance or cloud-storage deployment was reproduced here.
Sources and review boundary
Versioned sources checked 2026-10-07: PostgreSQL 16 multicolumn indexes, Cassandra 4.1 storage engine, Parquet format 2.10.0, and Iceberg 1.9.2 reliability. The S3 object-storage overview is access-dated, not a pinned API deployment. Layout arithmetic and the account/media example are explicit teaching models, not vendor benchmark results.
Pause / Recall / Apply
Can you explain it without the page?
Close the example. Reconstruct the core idea, then change one assumption. Mark complete when you’re ready; you can always undo it.
Stored in this browser only. No account, no sync. Clearing browser data removes your record.