Choosing a Data Store by Shape

Ask for the access pattern first

"Which database should we use?" is the wrong first question, because a database is a bundle of trade-offs and you cannot weigh them until you know what the data will be asked to do. The first question is "what is the access pattern?" Five answers decide most of the choice.

• The reads and writes. A point lookup (give me the row for this key), a range scan (all events for this key between two times), a full scan or aggregation (sum a column across a billion rows), a text search (documents containing these words), or a traversal (friends of friends). Each has an index or layout built for it, and each is slow on the others' layouts.
• Query flexibility. Will you know every query in advance, or will someone write one you did not plan? A store that only answers "by key" is fast because it refuses everything else.
• Consistency. Whether a write must be visible to the next read, and whether several rows must change together. Chapter 5 priced these; many stores sell their speed by weakening them.
• Scale and growth. Gigabytes or terabytes, hundreds of writes a second or hundreds of thousands, and how fast each doubles. The numbers chapter gives you the arithmetic.
• What you can operate. A store you cannot restore, upgrade or debug at 3 a.m. is a liability, however well it fits the pattern.

The history supports this order. Key-value, wide-column, search and columnar stores were each built because a team hit one pattern a relational database served badly at their scale. None replaced it. Each took one pattern off it.

Relational: the default

Postgres and MySQL store rows in tables, index them with B-trees, and let you ask questions you did not plan for. That last property is the one to value. A relational database answers a join, a filter on any indexed column, a group-by and a multi-row transaction from the same data, with no schema change and no second system. When the access pattern is unknown or will change, which describes most products in their first two years, nothing else buys as much flexibility.

It is strongest when data has relationships (orders have items, users have teams), when several rows must change atomically, and when people need ad hoc queries. With the tools of chapter 4 (replicas, pooling, then sharding) it goes a long way.

Its limits are the ones chapter 4 already named. All writes go through one primary until you shard, and once you shard, joins and transactions that span shards become expensive or impossible. Those limits matter only for the access patterns that hit them: a point lookup by key never needs a join, so it pays for relational power it does not use. That is the opening for the next family.

Key-value: one question, answered fast

A key-value store maps a key to a value and offers little else: get, put, delete, sometimes an atomic increment or a time-to-live. There is no join, no secondary index, and often no way to find a value except by its key. In return the lookup is O(1) in the key (a hash, then one read), data partitions cleanly across machines by that same hash, and latency stays flat as the dataset grows. Redis keeps everything in memory and answers in well under a millisecond; DynamoDB keeps data on SSDs across partitions and documents single-digit-millisecond reads at almost any size.

This is Snip's redirect exactly: given a 7-character code, return a URL. The query never changes, it needs no join, and it makes up 100 million requests a day. A relational table serves it fine, and chapter 3 put Redis in front of it for that reason. The question is whether a key-value store should be the primary store, not only the cache.

It should when the only query is by key, the data must scale past one machine without you running a sharding layer, and you can live with the store's consistency options. A managed store such as DynamoDB shards, replicates and rebalances for you, which is its main selling point at Snip's 1 billion links and 500 GB. It should not when you will need other queries. "All links created by user 42, newest first" is not a lookup by code. In DynamoDB you answer it with a secondary index, which is a second copy of the data keyed differently, or with a table designed around that query up front. You choose your queries at design time, and changing them later is a migration, not a new WHERE clause.

Redis as a cache may lose everything and be rebuilt. Redis as a primary store must survive a restart, which means persistence settings, replication and a plan for the failover window.

Document and wide-column: grouping by unit and by partition

Document stores such as MongoDB keep a self-contained record, usually JSON, with nested fields and arrays. The design idea is the aggregate: store together what you read together, so an order with its line items and shipping address comes back in one read with no join. Records in a collection need not share a schema, and you can query and index fields inside the document. This fits catalogs and content, where the unit of access is the whole object. It fits poorly when you constantly query across documents or when one fact lives in many of them, because you must update every copy.

Wide-column stores (Cassandra, ScyllaDB, Bigtable) are a different bet, and they are the right one for Snip's click stream. Data is addressed by a partition key, which picks the node, and within a partition rows are kept sorted by a clustering key. You can read one row, or a slice of a partition in order, such as "clicks for link aB3xK9z, newest 100". You cannot join, and you cannot query without the partition key except by scanning every node. As with sharding in chapter 4, the key picks your queries; the difference is that here the store forces the choice on you.

Their speed on writes comes from the storage engine. A relational B-tree updates pages in place: to insert a row, find its page, read it from disk if it is not cached, modify it, and write it back, plus a log entry for durability. Writes land at random places on disk, so sustained write throughput is bounded by random I/O. An LSM tree (log-structured merge tree) never updates in place.

A write appends to a commit log and inserts into a sorted in-memory table. When that fills, the store flushes it to disk as one immutable sorted file, and a background compaction merges files. Every disk write is sequential, which is why one node can ingest tens of thousands of small writes per second and why the cluster scales by adding nodes. The price falls on reads and on background work. A read may need to check the memtable and several files, which Bloom filters (a compact structure that says "definitely not here" for most files) limit, and compaction consumes I/O and CPU that you have to budget for. Overwrites and deletes leave old versions and tombstones until compaction removes them.

Click events match this profile: append-only, never updated, high volume, and read in key order. At 100 bytes each, 100 million a day is 10 GB a day, about 3.6 TB a year, with a write rate of 1,200 per second on average and several times that at peak. One Postgres primary could take that rate, but it would hold 3.6 TB of rows nobody updates in a structure designed for updates. Partitioned by link and day, a wide-column store takes the same stream across several nodes with room to add more.

Specialized stores: one pattern each

Search (Elasticsearch, OpenSearch). The access pattern is "find documents containing these words, ranked by relevance, filtered by facets." The mechanism is an inverted index, a map from each term to the list of documents that contain it, so a query intersects a few lists instead of scanning text. A relational LIKE '%word%' cannot use a B-tree and scans the table. Treat it as a derived index fed from your primary store, not a source of truth.

Time-series (Prometheus, TimescaleDB, InfluxDB). The pattern is a steady stream of timestamped measurements, queried by recent range and aggregated over windows. These stores compress runs of similar values heavily, expire old data by retention rules, and downsample (keep one-minute averages after a week and drop the raw points).

Blob and object storage (S3, GCS). The pattern is large, immutable files: images, backups, exports, log archives. An object store is not a database. It has no query beyond key prefix and no partial update: you replace the whole object. In exchange it is very cheap per gigabyte and extremely durable. Keep the bytes there and a pointer to them in your database.

Analytical columnar stores (ClickHouse, BigQuery, Snowflake). The pattern is scanning and aggregating a few columns across billions of rows: "clicks per country per day for last quarter." Storing each column contiguously means a query reads only the columns it names, compresses them well because neighbors are alike, and spreads the scan over many cores or nodes. They are poor at single-row lookups and frequent updates, so they sit beside your operational store, not in place of it. The design of that analytical side is the subject of the data systems design guide.

Graph (Neo4j). The pattern is traversal: friends of friends, shortest path, "who is connected to this account within three hops". In a relational database each hop is a join, and cost grows with the number of hops. A graph store keeps direct pointers from node to node, so a hop costs about the same however large the graph is. Reach for it when traversal depth is the query; most relationships are one or two joins deep and Postgres handles them.

Polyglot persistence and what it costs

Using several stores, each for the pattern it fits, is polyglot persistence. It is the right end state for many large systems, and the cost is easy to underestimate. Each store is one more thing to deploy, monitor, back up, upgrade and staff, with failure modes of its own.

The deeper cost is that there are no transactions across stores. If a link is created in the primary database and must also reach the search index and the analytics store, you have a write to two systems and no mechanism making both succeed or both fail. The naive version, writing to each in turn, is the dual-write problem: a crash between the writes leaves them disagreeing, and nothing notices. The fix is the one chapter 6 gave: commit the change and an outbox row in one local transaction, and let a relay publish to the other stores, which must then tolerate duplicates. That is correct, and it is also machinery you now own.

Third, data is duplicated and denormalized. The same link exists as a row, a cache entry, a search document and an analytics record, in different shapes, updated at different times. Decide before you build which copy wins when they disagree.

Start with one well-run store, usually Postgres, and add a specialized one only when a specific access pattern proves too expensive on it. "Proves" means a measurement: query time, write saturation, a storage bill, or an index that cannot be built. It does not mean a blog post. Postgres does full-text search, JSON documents and time-partitioned tables well enough for a long time, and one store means one backup and one transaction boundary. The exception is when the pattern is known to exceed the general store from the start and the numbers show it. Ingesting terabytes a year of events is not a pattern you wait to discover; you can see it in the arithmetic on day one.

Snip's stores, pattern by pattern

Each of Snip's data sets has its own access pattern, and the store follows from it.

On links I would choose the relational shards unless the team has no appetite to run them. At 1,200 reads per second, almost all absorbed by the cache, and 1 million writes a day (about 12 per second), the database is lightly loaded. Its size is the only reason to shard, and a managed key-value store trades query flexibility for not owning that work. Either is defensible, and the deciding factor is whether you will want queries beyond "by code".

The click path has the strongest case for a separate store, and it is a case from the arithmetic, not from fashion. The redirect handler publishes an event to a log and returns. A consumer writes events to a wide-column store for per-link history and rolls them into counters, the aggregation chapter 6 described. For "which countries clicked this campaign last quarter" across all links, a columnar OLAP engine reads two columns instead of whole rows. How that analytical layer is built, and when a warehouse belongs beside the product database, is covered in the data platform guide.

That is four kinds of store, and it stays tolerable because only the links database is a source of truth. The cache is disposable, the log can be replayed, and the sink can be rebuilt from the log. The next problem is not where data lives but how slow the slowest requests are: tail latency.

✓ Check yourself

Answer from memory before opening each one.

1. Click events are append-only at about 1,200 writes per second on average, yet your team's Postgres primary could probably absorb that rate. Give the mechanism that makes an LSM-based store a better fit anyway, and the cost you accept.

   Show answer

   An LSM tree appends to a log and a sorted in-memory table, then flushes immutable sorted files, so every disk write is sequential and the cluster scales out by adding nodes. A B-tree updates pages in place with random I/O, built for data that changes, while clicks are never updated. The cost is on reads, which may check several files (Bloom filters limit this), and on background compaction I/O. See document and wide-column.

2. A colleague proposes making Redis the primary store for links because the redirect is a pure key lookup. What do you ask before agreeing?

   Show answer

   Ask whether the data can be rebuilt from elsewhere: if Redis is the only copy, it needs persistence, replication and a failover plan, not cache-style settings. Ask which other queries will appear, such as listing a user's links; Redis has no secondary index, so each needs a second structure you maintain. Ask whether 500 GB in memory is worth its cost against a disk-based store with a cache in front. See key-value.

3. Product wants "search links by words in the title". Someone suggests adding Elasticsearch. What do you measure or decide first, and how would the data reach it safely?

   Show answer

   First check whether the existing database's full-text indexes serve the load and latency; if they do, a second store is cost with no gain. If not, treat the search index as a derived copy fed from the source of truth, not a second place to write. Writing to both in turn is the dual-write problem; use an outbox plus a relay, with idempotent index updates. See polyglot persistence.

4. Why is a graph store the wrong answer for "users and the links they own", but plausibly right for "accounts linked by shared devices within four hops"?

   Show answer

   Users to links is one join, which a relational database does well. A graph store pays off when traversal depth is the query: in a relational database each hop is another join and cost grows with depth, while a graph store follows direct pointers at about constant cost per hop. Having relationships in the data is not enough; the question is how many hops the query takes.

5. A document store holds each campaign with its links embedded. Marketing now asks for the total clicks per country across all campaigns. Where does the model strain, and what would you do?

   Show answer

   The aggregate model optimizes for reading one campaign as a unit. A query across every document is a scan, and it competes with the operational workload. Move the analytical question to a columnar store fed from the same events, so the scan reads only the columns it needs and leaves the document store alone. See specialized stores.

6. Snip adds three specialized stores in a year and the click counts in the dashboard begin to disagree with the sink. Which design rule was probably broken, and how do you restore trust?

   Show answer

   Some store other than the source of truth was written independently, or by dual writes, so it stopped being a pure projection. Make one store (or the event log) authoritative, feed every other store from it with an outbox or log consumer, and rebuild the diverged one by replaying from the source. A projection you cannot rebuild is a second source of truth.

Takeaway

• Choose a store from the access pattern (point, range, scan, text, traversal), query flexibility, consistency, scale and what you can operate, in that order of questions, not from the product's reputation.
• Relational is the default because it answers queries you did not plan; its limits are one write primary and expensive cross-shard joins and transactions.
• Key-value gives O(1) lookup by key at any size by refusing other queries, which fits Snip's redirect; as a primary store it needs durability and a planned set of queries, and as a cache it needs neither.
• Wide-column stores use LSM trees, which turn writes into sequential appends and so suit append-only event data such as Snip's 3.6 TB a year of clicks, at the cost of slower reads and background compaction.
• Search, time-series, object, columnar and graph stores each take one pattern off the relational database, and each is a derived copy you must keep in step with the source of truth.
• Start with one well-run store, usually Postgres, and add another only when a measured access pattern is too expensive on it, or when the arithmetic shows it will be before launch.

A store chosen to fit the pattern still has a slow tail: a few requests in a hundred will take many times the average, and that tail sets what users feel. → Tail Latency & Capacity

Further reading

• Designing Data-Intensive Applications — Martin Kleppmann, 2017. Chapter 2 compares relational, document and graph models; chapter 3 explains B-trees against LSM-trees and row against column storage.
• Dynamo: Amazon's Highly Available Key-value Store — DeCandia et al., SOSP 2007. Why Amazon gave up query power and strong consistency for availability, in the paper that shaped most key-value stores since.
• Bigtable: A Distributed Storage System for Structured Data — Chang et al., OSDI 2006. The origin of the wide-column model and of the memtable and sorted-file write path.
• Cassandra storage engine — Apache Cassandra documentation. The commit log, memtable, SSTable and compaction path described concretely.
• Polyglot Persistence — Martin Fowler. The short case for several stores, and the argument that the operational cost is the thing to weigh.