Choosing a data store
Generated by Claude (Fable 5.1) by hand, Scout learning center, format v2 on 2026-10-15, reviewed by a second reader (Claude Fable 5.1), Scout learning center, format v2 C1 on 2026-10-15.
Concept: Relational and non-relational data stores · also covers Data systems (schemas, indexing, partitioning), Storage engines and indexes, System design and architecture · Lesson 5 of 33 in System design · version 1
The data store is the decision a design cannot easily revisit. This lesson reads an access pattern into a kind of store, puts eight criteria against relational, key-value, wide-column, document, search, time-series, graph and object stores in one decision, explains the guarantees that separate them, prices the case where one store is not enough, warns about the migration a wrong choice forces, maps each kind to its AWS managed service from the AWS database decision guide, and walks the news feed through the whole choice.
On this page
- Why the data store is the decision
- Reading an access pattern into a store
- The decision: which store for which workload
- The criteria, store by store
- Guarantees, transactions and the shape of the answer
- When one store is not enough, and what two cost
- The migration trap
- On AWS: the managed service for each store
- Worked example
- In practice
Why the data store is the decision
Most architecture decisions can be revisited in a sprint. The data store cannot: it fixes the data model, the queries that are cheap, the guarantees the application may assume, and the migration that will one day be needed to leave it. For decades the choice was not made at all, because the only option was a relational database and every application's data was modelled as relational whether it fitted or not; Werner Vogels's account of why Amazon built purpose-built stores makes the point that the database was driving the data model rather than the use case driving the database.[4]
The original evidence is in the same account and in the Dynamo paper. Amazon found that about 70 percent of its operations were key-value lookups that used only a primary key and returned a single row, and the paper lists the services that need only primary-key access, from shopping carts to session management and best-seller lists, for which a relational database would limit scale and availability. A store that offers only that access path can promise what those services need, including staying writeable while disks fail and network routes flap.[4] [5]
| store kind | unit of data | the question it answers best | examples |
|---|---|---|---|
| Relational | rows in tables with a schema | any query over several tables, with transactions and joins | PostgreSQL, MySQL, Aurora |
| Key-value | a value by its key | get or put one item by key, at any scale | DynamoDB, Redis, Dynamo-style stores |
| Wide-column | rows grouped in a partition, sorted by a clustering key | a range within one partition, writes at high rate | Cassandra, ScyllaDB, Bigtable, Keyspaces |
| Document | a JSON-like document | fetch or update one document and query its fields | MongoDB, DocumentDB, PostgreSQL jsonb |
| Search index | an inverted index over text and fields | full-text and faceted search, relevance ranking | Elasticsearch, OpenSearch |
| Time series | samples labelled by metric and tags | ranges over time, aggregation, downsampling | Prometheus, Timestream, TimescaleDB |
| Graph | nodes and typed, directed relationships | traversals across many relationships | Neo4j, Neptune |
| Object store | an immutable object by key | put and get large blobs cheaply and durably | Amazon S3 and other object stores |
The lesson on storage engines explained the mechanism under each kind; this lesson is about the choice. It reads an access pattern into a store kind, puts the criteria against the kinds in one decision, prices the case where one store is not enough, warns about the migration that a wrong choice forces, and maps each kind to the managed service that runs it on AWS. The worked example walks the news feed from this module through the decision.[1]
Reading an access pattern into a store
The DynamoDB design guide states the discipline for every non-relational store in one sentence: do not start designing the schema until you know the questions it will need to answer. A relational schema can be normalised first and queried later; a key-value, wide-column or document schema is the queries, so the queries come first. Cassandra's documentation says the same of its model: modelling is query-driven, each query is best served by a single table, and entities are duplicated across tables to make that true.[2] [3]
Writing down an access pattern
- List every read and write the product makes, as a sentence: fetch a user's timeline, append a post to each follower's timeline, search posts by text.
- For each one, name the key it arrives with: a user id, a post id, a text query, a time range. A query with no key is a scan or a search, which alone narrows the store.
- Estimate its peak rate per second and the size of what it reads or writes.
- State what it must see: its own last write, every write before it, or anything recent.
- Mark the ones that must happen together or not at all. Those are the transactions, and they decide whether one store can hold the data.
- Sort by rate times size. The top two or three are the dominant paths; the store is chosen for them.
The DynamoDB guide names three properties to know before modelling: the size of the data stored and requested at once, its shape, and its velocity. Those three plus the consistency need sort most patterns into a kind. A key with a small value at a high rate is key-value. A key that owns a growing sorted list is wide-column. A text query is a search index. A time range over labelled samples is time series. A question that walks relationships several hops deep is a graph. A question that joins entities in ways nobody predicted is relational, and so is any set of writes that must be atomic across entities.[2]
| query | key | peak rate | must see | sorts into |
|---|---|---|---|---|
| create a post | post id | 230 per second | its own write | any durable store |
| read my home timeline | user id, newest first | 11,600 per second | recent | a sorted list per key: wide-column or an in-memory list |
| append a post to each follower's timeline | follower id | 46,000 per second | eventually | the same list per key, write-heavy |
| list who I follow and who follows me | user id | low | recent | key-value or relational |
| search posts by text | text | low | recent | search index |
| store and serve media | media id | high bytes, low count | immutable | object store |
The decision: which store for which workload
The decision block is the centre of the lesson: one row per kind of store, the workload it is built for, the workload that breaks it, and named examples with the AWS managed service after them, mapped from the AWS database decision guide and taken up again at the end.[1]
| Option | Pick when | Avoid when | Example |
|---|---|---|---|
| Relational | queries are varied and will change, writes must be atomic across entities, and the data fits one primary with replicas, which is most products | the dominant path is a single-key lookup at a rate one primary cannot serve, or a sorted list per key that grows without bound | PostgreSQL, MySQL; Aurora or RDS, Aurora DSQL for multi-region |
| Key-value | almost every access is get or put by a known key, the rate or size is beyond one machine, and the store must stay writeable through failures | the product needs ad hoc queries, joins or transactions across many items, or the questions are not yet known | Redis, Dynamo-style stores; DynamoDB |
| Wide-column | each key owns a sorted, growing list read by range, writes dominate, and the partition can be bounded, as a chat channel or a timeline is | queries cross partitions, a partition can become hot, or the team has nobody to run the cluster | Cassandra, ScyllaDB, Bigtable; Keyspaces |
| Document | each entity is read and written whole, its fields vary by record, and queries are on the fields of one document | data is relational underneath and the joins are hidden in application code, or multi-document transactions are common | MongoDB; DocumentDB, or jsonb columns in PostgreSQL |
| Search index | the question is a text query or a faceted filter ranked by relevance, over data owned by another store | it would be the only copy of the data; it is a derived index and must be rebuildable | Elasticsearch; OpenSearch Service |
| Time series | samples arrive at a high rate with a timestamp and labels, are queried by range and aggregated, and age out | every sample must be exact and complete, as for per-request billing, or the queries are not over time | Prometheus, TimescaleDB; Timestream |
| Graph | the questions traverse many relationships several hops deep: fraud rings, social paths, recommendations, knowledge graphs | the graph is shallow and the questions are one hop, which a join or a key lookup answers | Neo4j; Neptune, Neptune Analytics |
| Object store | objects are large, written once, read whole or by range, and durability per byte must be cheap | the application needs small updates, listing by attributes or transactions across objects; keep those in a metadata store | Amazon S3 and other object stores; S3 |
The criteria, store by store
The decision rows compress eight criteria. The two tables expand them so that a reviewer can disagree with one cell rather than a row; the cells follow the AWS decision guide, the vendor documentation cited in the captions, and the mechanism taught in the storage-engines lesson.[1]
| criterion | relational | key-value | wide-column | document |
|---|---|---|---|---|
| access pattern | any; joins across tables | one item by key | a range inside one partition | one document by key, filters on its fields |
| read to write ratio | either; reads scale with replicas | either; both scale by partition | write-heavy; appends are cheap | read-mostly, whole documents |
| consistency need | transactions across rows and tables | per item; always writeable in Dynamo-style stores | tunable per query; per partition | per document; multi-document transactions are the exception |
| data shape | regular, normalised | opaque value | sorted rows per partition | nested, varying fields |
| scale and growth | one primary; shard by hand past it | linear by partition | linear by partition; bound the partition size | shard by key |
| query flexibility | highest; ad hoc SQL | lowest; by key only | by partition and clustering key only | fields of one document; secondary indexes |
| operational cost | well understood; managed forms everywhere | lowest when managed | a cluster to run and repair unless managed | moderate; managed forms exist |
| team familiarity | nearly universal | common | specialist | common |
| criterion | search index | time series | graph | object store |
|---|---|---|---|---|
| access pattern | text and faceted queries ranked by relevance | ranges over time per metric and label set | traversals across relationships | put and get whole objects, range reads |
| read to write ratio | read-heavy; indexing is batched | write-heavy ingest, range reads | read-heavy traversals | write once, read many |
| consistency need | near real time; a derived copy | approximate is acceptable; not for billing | per transaction in most engines | per object; immutable |
| data shape | documents with analysed text fields | samples with timestamps and labels | nodes, typed directed relationships, properties | opaque bytes with a few metadata fields |
| scale and growth | shards and replicas | retention and downsampling bound it | grows with relationships; traversals get costly | effectively unbounded, cheap per byte |
| query flexibility | high for text; weak for joins | high over time; weak across metrics | high for paths; weak for scans | none beyond key and prefix listing |
| operational cost | a cluster and its reindexing | retention management | specialist | lowest when managed |
| team familiarity | common | common for monitoring | rare | universal |
Two cells deserve a sentence, and one row a caveat: the team-familiarity row is the lesson's own judgement, not a source's. Query flexibility is the hidden price of every non-relational choice: the DynamoDB guide describes data that can be queried efficiently in a limited number of ways, outside of which queries are expensive, against relational data that can be queried flexibly at a cost that does not scale under high traffic. The time-series consistency cell is the Prometheus project's own warning: where every sample must be exact, as in per-request billing, another store must hold the record.[2] [11]
Guarantees, transactions and the shape of the answer
Three guarantees separate the kinds more sharply than throughput does. The first is atomicity across entities: a relational store commits several rows in one transaction as a matter of course, while a key-value or document store makes a single item the unit of atomicity and offers cross-item transactions, where it offers them at all, as a bounded special case. A product whose core operation moves value between two records, a ledger or an inventory, has this guarantee as its dominant path.[2]
The second is what a read may see. The Dynamo paper's design target is an always-writeable store: a shopping cart must accept an add even while servers and network paths fail, and the price is that two replicas may hold divergent versions to be reconciled later. The relational default is the opposite: one primary accepts writes and a read on it sees every committed write. Neither is better; the question is which failure the product would rather have, a rejected write or a stale read.[5]
The third is exactness of the answer. A search index ranks by relevance, which is a judgement rather than a match; a vector index returns approximate neighbours, and pgvector's documentation states the trade plainly: exact search by default with perfect recall, or an approximate index, HNSW or IVFFlat, that trades some recall for speed and changes the results a query returns. A time-series store may drop or round samples by design. Each is the right answer for its question and the wrong one for a question that needs the exact row.[10] [11]
| kind | unit of atomicity | what a read sees | exactness of the answer |
|---|---|---|---|
| relational | a transaction over many rows and tables | every committed write, on the primary | exact |
| key-value, document | one item or document; bounded multi-item transactions where offered | configurable; eventually consistent reads are the cheap default in many stores | exact for the item |
| wide-column | one partition | tunable per query | exact |
| search index | one document | after the next refresh | ranked, not exact |
| vector index | one row | after indexing | approximate unless exact search is chosen |
| time series | one sample | recent | approximate by design in monitoring stores |
| object store | one object | the object as written | exact bytes |
When one store is not enough, and what two cost
Most products end up with a primary store plus derived ones: a cache for the hot reads, a search index for the text queries, a time-series store for the metrics, an analytics copy for the reports, an object store for the files. The rule that keeps this sane is that exactly one of them is the source of truth and every other is rebuildable from it. Discord's search is the pattern done right: messages live in Cassandra, Elasticsearch holds an index that is built lazily, only when someone first searches a server, and even the mapping of servers to index shards is kept in Cassandra as the source of truth with the index as a derived copy.[8]
| second store | what it adds | what it costs |
|---|---|---|
| cache | cheap repeated reads | invalidation, cold starts, a stale-read story |
| search index | text and faceted queries | a pipeline that feeds it, a lag, and a reindex when the mapping changes |
| time series | metrics over time at high ingest | retention policy, cardinality limits, a second query language |
| analytics copy | reports without loading the primary | a batch or change-data pipeline and a freshness lag |
| object store | cheap, durable blobs | a metadata table to keep in step; orphaned objects |
| any of them | the right shape for one query | two systems to run, two failure modes, two skill sets, and a dual-write path that can disagree |
The cost is rarely in the software. It is in the pipeline between the stores, which is a second write path that can fail half-way; in the lag, which the product must tolerate and the support team must explain; in the on-call surface, which doubles; and in the people, since each kind of store has its own failure modes and tuning. The AWS decision guide's advice on vector search generalises: a team that already runs a hundred PostgreSQL databases should first evaluate the vector extension on the engine it knows, because the same engine lets developers use the skills and tools they have rather than learn a new system.[1]
So the second store has to earn its place with a query the first cannot serve at the required rate or shape, not with a preference. The order of preference is: the primary store's own feature, such as a JSON column, full-text search or a vector extension in PostgreSQL; a managed derived store fed by change capture; and only then a second primary with its own writes.[1] [10]
The migration trap
The wrong store is not discovered at design time; it is discovered when the dataset outgrows it, and leaving it then means migrating live data under full traffic. Discord's message store shows both halves. The first version was a single MongoDB replica set, chosen for speed of iteration, and the team planned from the start for an easy migration out. At about 100 million messages the data and its index no longer fitted in RAM, latency became unpredictable, and the messages moved to Cassandra with a partition key of channel and time bucket sized to stay under 100 MB. Six years later the Cassandra cluster had grown from 12 nodes to 177, was high toil with hot partitions that hurt the whole cluster, and was migrated again, to ScyllaDB, behind a new data-services layer.[6] [7]
Stripe's account of moving hundreds of millions of subscription objects states the method most teams use and the constraint that makes it hard: the migration had to run with every service at full traffic and with users expecting consistency throughout. The four-step dual-writing pattern is the answer, one codebase-wide change at a time.[9]
The four-step dual-writing migration, as Stripe describes it
- Dual write: every new write goes to the existing table and the new one, so they stay in sync; then backfill the missing history into the new table, in Stripe's case hundreds of millions of subscription objects, until the two hold identical information.
- Change every read path in the codebase to read from the new table.
- Change every write path in the codebase to write only to the new table.
- Remove the old data that relies on the outdated data model.
The trap is the cost of the choice that forces this, not the migration itself. A store chosen by preference rather than by the access pattern tends to be left at the worst time, when the dataset is largest and the product busiest. The defence is Discord's: choose the store for the questions you have, but from day one keep the application's data access behind a narrow interface, keep ids and keys that any store can use, and know what the next store will be and what number triggers the move.[6] [9]
On AWS: the managed service for each store
The neutral decision comes first; the product mapping is the last step. On AWS the database decision guide gives it in one table: each managed engine, its data model, when to use it and what it is optimised for. The table below carries that mapping for the eight kinds of this lesson; the alternative column is the lesson's own reading of the guide's other rows, and two cells the guide does not cover are marked; the exercises in this module link back here when they take a design down to AWS.[1]
| store kind | AWS service | the guide's use-when | alternative on AWS |
|---|---|---|---|
| Relational | Amazon Aurora (MySQL and PostgreSQL compatible) | migrating or modernising a relational workload, or less predictable query patterns | Amazon RDS for six engines; Aurora DSQL for active-active multi-region with strong consistency; Aurora PostgreSQL Limitless for writes beyond one instance |
| Key-value | Amazon DynamoDB | session stores and shopping carts; very high throughput for millions of simultaneous users; serverless | ElastiCache or MemoryDB for in-memory latency |
| Wide-column | Amazon Keyspaces (for Apache Cassandra) | Cassandra workloads, heavy reads and writes at single-digit-millisecond latency with linear scalability | DynamoDB, when the Cassandra model is not required |
| Document | Amazon DocumentDB (with MongoDB compatibility) | JSON-like documents with rich querying across their fields | jsonb in Aurora PostgreSQL |
| Search index | Amazon OpenSearch Service | from the guide's vector table: native k-NN; RAG at scale, hybrid search, log analytics | none in the guide; OpenSearch is the search service |
| Time series | Amazon Timestream | large amounts of time series data from many sources: IoT, metrics, asset tracking | a managed Prometheus for monitoring metrics (the lesson's own; not in the guide) |
| Graph | Amazon Neptune | complex networks of objects: social graphs, fraud detection, recommendations | Neptune Analytics for graph analytics and GraphRAG |
| Object store | Amazon S3 | not a row in the guide; the lesson's own mapping: large, immutable objects at low cost per byte | none; the metadata belongs in one of the stores above |
Vector search is where the guide argues most clearly from team familiarity. Rather than a separate vector database, it lists vector capability inside the existing services, ranked by latency and fit, and advises a team that already runs relational databases to evaluate the vector extension on the engine it has before adding a new system.[1]
| service | vector capability | latency, per the guide | best for, per the guide |
|---|---|---|---|
| OpenSearch Service | native k-NN | low to medium | retrieval-augmented generation at scale, hybrid search, log analytics |
| Aurora PostgreSQL | pgvector extension | low | relational applications that need vectors and transactional data in one database |
| MemoryDB | native vector search | sub-millisecond | semantic caching, real-time inference, lowest-latency retrieval |
| Neptune Analytics | vector similarity plus graph analytics | medium | GraphRAG and knowledge-graph-enhanced retrieval |
| DocumentDB | native vector search with HNSW and IVFFlat indexes | low | MongoDB-compatible applications with vectors |
Worked example
Worked example: choosing the stores for the news feed
The news feed exercise in this module sizes a product with 10 million daily active users, where each user follows about 200 accounts. Its estimates are the inputs here; every number is an assumption from that exercise, not a measurement. The decision is made per access pattern, and the stores are then counted.[2]
From the access patterns to the stores
- Posts: 230 creates per second at peak. Any durable store serves this; the posts table and its author index stay relational, because posts are also read by id, edited, deleted and joined to their authors.
- Home timeline reads: 11,600 per second at peak, each wanting the newest entries for one user. A sorted list per key, read by range: a wide-column partition or an in-memory capped list, not a relational query that sorts at read time.
- Fan-out appends: 46,000 per second at peak, each adding one entry to one follower's list. Write-heavy, by key, eventually consistent: the same per-key list, on a store that makes appends cheap.
- Timeline size: 800 entries per user at 16 bytes is 12.8 kB, and the cache for all active users is about 128 GB; an in-memory store holds it, with the wide-column store or a rebuild from the posts table behind it.
- Social graph: who follows whom, read when a post is created to find its followers and when a timeline is rebuilt. One hop, by user id: a key-value or relational table, not a graph database, because no question walks further than one hop.
- Search: posts by text, low rate, ranked by relevance. A search index derived from the posts table through its change stream, rebuildable, never the only copy.
- Media: images and video, large and immutable. An object store, with the media id and attributes in the posts row.
- Metrics: the fan-out lag, the cache hit ratio and the request latencies, at a high ingest rate and queried by range. A monitoring time-series store, approximate by design.
- Count: one relational primary as the source of truth, one in-memory timeline cache, one wide-column or list store for timelines, one derived search index, one object store and one metrics store, each with a query the primary could not serve at that rate or shape.
| data | store kind | on AWS (the mapping in the on-AWS subtopic) | why this kind |
|---|---|---|---|
| posts, users, follows | relational, the source of truth | Aurora PostgreSQL | varied queries, joins, transactions across entities; the rates fit one primary with replicas |
| home timelines | a sorted list per user: in-memory cache in front of a wide-column store | ElastiCache in front of Keyspaces or DynamoDB | 46,000 appends and 11,600 range reads per second at peak, eventually consistent |
| post search | search index, derived | OpenSearch Service | text queries ranked by relevance; rebuildable from the posts table |
| media | object store | S3 | large immutable blobs, cheap per byte; metadata stays in the posts row |
| service metrics | time series | a managed Prometheus or Timestream | high ingest of labelled samples, range queries, approximate is acceptable |
The decision that changes the architecture is the timeline. If it were kept relational, every home timeline read would sort and merge posts from about 200 followed accounts at 11,600 requests per second, and the choice of fan-out on write would be pointless. Everything else in the table is a derived store that earns its place with one query shape; the relational primary stays the only source of truth, and each derived store can be rebuilt from it.[2] [3]
In practice
Amazon built Dynamo, and later DynamoDB, because it measured its own access patterns: about 70 percent of operations were key-value lookups by primary key returning one row, and services such as the shopping cart needed a store that stayed writeable through disk, network and data-centre failures. The Dynamo paper lists the services that need only primary-key access and states that a relational database would have limited their scale and availability.[4] [5]
| MongoDB (2015) | Cassandra (2017) | ScyllaDB (2023) | |
|---|---|---|---|
| chosen because | fastest to iterate on; a single replica set | linear scalability, automatic failover, no manual resharding | same model without the garbage-collection toil; hot partitions still possible |
| left because | at about 100 million messages the data and index no longer fit in RAM and latency became unpredictable | 177 nodes, frequent pages, hot partitions that slowed the whole cluster | current |
| key | compound index on channel and created time | partition (channel, 10-day bucket) sized under 100 MB, clustered by message id | the same, behind data services with request coalescing |
| scale | 100 million messages | 12 nodes in 2017, 177 by 2022, trillions of messages | 72 nodes at 9 TB each |
| tail latency for history reads | unpredictable | p99 of 40 to 125 ms | p99 of 15 ms |
Discord's search is a derived store by design: Elasticsearch was chosen over Solr for its self-contained discovery and automatic shard rebalancing, messages are indexed lazily only when a server is first searched, and the mapping from servers to index shards is stored in Cassandra, the primary store, as the source of truth with a cache in front of it.[8]
Stripe migrated hundreds of millions of Subscriptions objects to a new data model with every service at full traffic, using the four-step dual-writing pattern: dual write to the existing and new tables and backfill the history, change every read path to the new table, change every write path to write only to the new table, then remove the old data that relies on the outdated model.[9]
Technologies
Sources
- docAWS Decision Guides, Choosing an AWS database service (docs.aws.amazon.com, read 2026-10-15): the table of engines by data model with when to use each, the vector-capability table, and the team-familiarity guidance (docs.aws.amazon.com) checked 2026-10-15
- docAmazon DynamoDB Developer Guide, NoSQL design for DynamoDB: access-pattern-first modelling, know the questions before the schema, as few tables as possible (docs.aws.amazon.com) checked 2026-10-15
- docApache Cassandra documentation, Data modeling, Introduction: query-driven modelling, one table per query, denormalisation (cassandra.apache.org) checked 2026-10-15
- articleWerner Vogels, A one size fits all database doesn't fit anyone (All Things Distributed, 2018-06-21): the use case drives the database; about 70 percent of Amazon's operations were key-value lookups (www.allthingsdistributed.com) checked 2026-10-15
- paperGiuseppe DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store, SOSP 2007 (PDF): services that only need primary-key access, the always-writeable shopping cart (www.allthingsdistributed.com) checked 2026-10-15
- articleDiscord Engineering, How Discord Stores Billions of Messages (2017): MongoDB until the data and index outgrew RAM, the move to Cassandra, the (channel, bucket) partition key sized to stay under 100 MB (discord.com) checked 2026-10-15
- articleDiscord Engineering, How Discord Stores Trillions of Messages (Bo Ingram, 2023-03-06): 177 Cassandra nodes, hot partitions, the move to 72 ScyllaDB nodes, the data services layer with request coalescing, before-and-after tail latencies (discord.com) checked 2026-10-15
- articleDiscord Engineering, How Discord Indexes Billions of Messages (2017): Elasticsearch chosen over Solr, lazily indexed, Cassandra kept as the source of truth (discord.com) checked 2026-10-15
- articleStripe Engineering, Online migrations at scale (2017): the four-step dual-writing pattern applied to hundreds of millions of Subscriptions objects with every service at full traffic (stripe.com) checked 2026-10-15
- repopgvector README (GitHub, maintained): exact search by default with perfect recall, HNSW and IVFFlat approximate indexes that trade recall for speed, vectors beside the rest of the data (github.com) checked 2026-10-15
- docPrometheus documentation, Overview: the multi-dimensional time series model, designed for reliability, and when it does not fit (per-request billing) (prometheus.io) checked 2026-10-15
- docNeo4j documentation, What is a graph database: nodes, relationships with a type and a direction, properties on both (neo4j.com) checked 2026-10-15