Skip to content

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.

On this page

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 familyShape of the work it fitsTypical 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 familyAccess pattern it fitsWhat you give upWhen 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

In a picture

yesnoyesnoTop queries andtheir ratesJoins ormulti-rowwrites?RelationalLookup orrange by aknown key?Key-value orwide-columnMatch the datashapeCache for hotreads
Start from the query list. Each fork is a question about the access pattern; the cache is added for hot reads, whichever store sits behind it.

How to say it in an interview

The common mistake

Quiz

A new internal tool needs many filters and reports, and nobody can say yet which ones will be used most. Which store fits best?
In a document store, a user's comments grow without limit and are written at different times from the user profile. How should they be stored?
Product pages are read millions of times a day and must return in under a millisecond. The catalog changes a few times an hour. What changes in the design?

Related

Sources

  1. AWS Decision Guides: Choosing an AWS database service
  2. Amazon DynamoDB Developer Guide: NoSQL design for DynamoDB
  3. Apache Cassandra documentation: Data modeling introduction
  4. MongoDB Manual: Best practices for data modeling
  5. PostgreSQL 16 documentation: 3.4. Transactions
Sign in to report a mistake