Choosing a database
Picking a datastore from the reads and writes it has to serve: which queries run, how often, how fast, and what a write must keep together. The store is named with the access pattern that chose it.
What it is
Choosing a database is matching a store to the way the data will be read and written. The brand matters less than the shape of the work: which queries run most, how many per second at peak, how fast each must return, how large a record is, and whether a change has to land in several places as one unit. AWS's decision guide lists the data model, how you access the data, how current it must be, and the record size as the core of the choice, and notes that one system may end up with more than one database.
| Store family | Shape of the work it fits | Typical use the vendor names |
|---|---|---|
Relational (PostgreSQL, MySQL) | Structured rows, queries you can't predict, joins across tables | Workloads with less predictable query patterns |
Key-value (DynamoDB) | Get and put by a known key, very high request rates, single-digit millisecond reads | Session stores, shopping carts |
Document (MongoDB, DocumentDB) | JSON-like records with nested fields, queried across those fields | Semi-structured data with layered attributes |
Wide-column (Cassandra, Keyspaces) | Heavy reads and writes at high throughput, low latency, scaling by adding nodes | High-speed processing at single-digit millisecond latency |
In-memory (Redis, Valkey, Memcached) | Microsecond reads of hot data, usually as a cache in front of a durable store; a durable in-memory database can serve as the primary store | A caching layer in front of another database |
Graph (Neptune) | Walking many relationships between objects | Social networks, fraud detection, recommendations |
Time series (Timestream) | Rows keyed by timestamp, queried as trends | IoT readings, application metrics |
Why the query list comes first. A relational schema is normalized without much thought for which queries will run, and new questions are answered later as they come up. DynamoDB's developer guide describes the opposite discipline: don't lay out the schema until you know the questions it must answer, then shape the data so that the most common and important queries are the cheapest. The guide names three properties to pin down before that: how much data a query touches, what shape the result has, and the peak rate of queries. Cassandra's documentation says the same thing from another angle: joins aren't supported, so each query gets a table that already holds all the columns it needs, and the same fact is written to several tables to make reads cheap at the price of extra writes.
What a transaction buys you. In PostgreSQL 16 a transaction bundles several steps into one all-or-nothing unit: other sessions don't see the half-finished state, a failure partway through leaves none of the steps applied, and once the database acknowledges the commit the changes have been written to permanent storage. When a payment debits one row and credits another, that property is the reason to keep both rows in a store that offers it. Document stores handle this differently: a single document is updated atomically, and MongoDB offers distributed transactions when several documents must change together.
When to use it
Reach past the relational default when a named access pattern is a poor fit for it. The DynamoDB guide puts the trade plainly: a relational database answers flexible queries but each one is relatively expensive and the approach scales poorly under heavy traffic, while a key-value store answers a limited set of queries cheaply and anything outside that set is slow. So a key-value or wide-column store earns its place when you can list the queries up front, they look up or range-scan by a key you control, and the request rate is high. A document store earns its place when the application reads and updates a parent and its children together, which MongoDB's guidance gives as the case for embedding them in one document.
Add an in-memory store when a hot read must return in microseconds rather than milliseconds; the AWS guide suggests a cache such as ElastiCache next to the main database for that case rather than replacing it. Pick a graph store when the question is a path through many relationships, and a time-series store when rows carry timestamps and the queries are trends over time. In each case the sentence to say aloud pairs the pattern with the store: "feed reads are by user and time at 50k per second, so they go to a wide-column table keyed by user". The number is an example, not a target.
Once the store is picked, the key design decides whether it performs. For a partitioned store, the partition key spreads data across nodes, a query that touches few partitions is cheaper than one that fans out to many, and keys that sort related items together let one request fetch them in order. Keys that focus traffic on one partition create hot spots. Those decisions belong to Sharding and partition keys and Database indexes; here it's enough to say which key the top query uses.
When not to
Don't leave the relational store when you can't yet list the queries. Its strength is answering questions nobody planned for, and AWS's guide points to a relational engine for workloads whose query patterns are less predictable. If the design you're asked for is early or its filters are still open, say that, start relational, and name the pattern that would move a piece of data out later.
Don't duplicate data into query-shaped tables or embedded documents when it changes often. MongoDB's guidance is that the extra logic for rare updates costs less than a join on each read, but frequent updates to duplicated data create heavy write workloads. It also lists the cases where embedding is the wrong choice: a child side with high cardinality, data that grows without bound, and parent and child written at different times in a write-heavy workload. A reference, or a relational table, is the simpler option there.
Don't make a cache the system of record. ElastiCache is described as an ephemeral cache for frequently read data; if the data must survive a node loss, it needs a durable store behind the cache, or a durable in-memory database that is designed for that. And don't name three stores for a design that fits in one: each extra store is another thing to keep consistent, operate and explain. Free-text search is a separate access pattern with its own structure, covered in Inverted indexes.
Compared
| Store family | Access pattern it fits | What you give up | When the simpler option is right |
|---|---|---|---|
Relational | Joins, ad hoc filters, all-or-nothing writes across rows | Each query is relatively costly; scaling heavy traffic takes more work | This is the simpler option: start here when queries are not yet known |
Key-value | Get and put by key at very high rates, single-digit millisecond reads | Queries outside the planned set are slow or expensive | Relational, when you still need ad hoc queries or the rate is modest |
Wide-column | Heavy reads and writes by partition key, growth by adding nodes | No joins; one table per query; the same fact written several times | Relational, when the queries aren't known yet or the same fact would be updated often across several query tables |
Document | A parent and its children read and updated together | Embedded children that grow without bound or change often get expensive | Relational, when children have high cardinality or are written separately |
In-memory cache | Microsecond reads of a hot subset | Ephemeral; a lost node loses the copy | No cache, when most reads would miss or the database already keeps up |
Graph | Paths through many relationships | A specialized store to run and learn | Relational with a join or two, when paths are short and fixed |
Time series | Timestamped rows queried as trends | Poor fit for anything that is not time-keyed | Relational with a time index, at small volume |