Storing Data: Choosing the Right Store

Why one database isn't enough

A kitchen has a knife, a pot and a fridge, because no one tool does every job.

Pick a store, step by step

Walk the tree. come first, since they are hard to add later, and most paths end at Postgres.

Job	Store	Why
10M profiles, 500 reads a second	Postgres	Small, needs joins and transactions
Product search with typos and filters	Elasticsearch	Ranks results, forgives typos
200M click events a day, dashboards	ClickHouse	Scans a few columns fast
2 GB product videos	S3	Blobs bloat a database and its backups

Signal	Rough line	Move
Sustained writes on one primary	About 10K a second	Shard it, or Cassandra and DynamoDB
Reports beside live traffic	Scans of 100M rows	A replica, then ClickHouse
Search with typos and facets	1M+ documents	Elasticsearch
Files	Over about 1 MB	S3, with the key in Postgres
Lookups by one key	100K+ reads a second	Redis in front

Stores compared

Every store is a trade: the second column is why you buy it and the third is what it costs you.

Read more · 1458 words

Store	Strong at	Weak at	How it scales
Relational (PostgreSQL, MySQL)	Transactions, constraints, joins, ad-hoc queries	Horizontal write scale	Replicas, then sharding (Citus, Vitess)
Document (MongoDB)	Flexible entities read together	Cross-document transactions and joins	Built-in sharding
Key-value (Redis, DynamoDB)	Single-key lookups at huge scale	Queries other than by key	Partition by key
Wide-column (Cassandra, ScyllaDB)	Write-heavy, time-ordered partitions	Ad-hoc queries, joins	Partition key + clustering
Object storage (S3, GCS)	Large blobs, cheap and durable	Low-latency small reads	Effectively unlimited
Search (Elasticsearch, OpenSearch)	Full text, relevance, facets	Being the source of truth	Shards + replicas
Time-series / columnar (ClickHouse, TimescaleDB)	Aggregations over time and events	Point updates	Partition by time
Graph (Neo4j)	Multi-hop relationship queries	Bulk analytics	Hard to shard well

For core entities the choice is about how the data is read: with rules and joins, as a whole, or by one key.

Relational (the default). Tables with constraints, and changes wrapped in , so money, stock and users stay correct. Use it when the data has relationships, rules or ad-hoc questions: orders, payments, bookings, users. It is also the answer when you do not yet know the access patterns, because lets you ask a new question tomorrow. One serves thousands of simple queries a second, and add read capacity. Bad at: write scale. Every write goes to one primary, so past roughly 10K sustained writes a second or a few terabytes you or move that data elsewhere. Also a poor home for blobs, with no typo-tolerant search.
Document. One self-contained, JSON-like record per entity, so what is read together is stored together. Use it when each entity is read whole and its shape varies: a product catalog where a shirt has a size and a laptop has RAM. Embed children that are bounded and read together (an order and its 5 to 10 line items); reference children that grow without limit (reviews), since a document is capped at 16 MB. A catalog of 2M items at 5 KB each is 10 GB, easy for a document store or Postgres. Bad at: joins across documents. Multi-document transactions exist but cost more, so data with many cross-references wants Postgres. For an occasional flexible field, a Postgres JSONB column is enough.
Key-value. Answers one question, the value for this key, fast at any size. Use it when access is always by a known key, at a scale or speed a relational database cannot match. keeps data in memory (about 1 ms): sessions, carts, counters, ; 1M sessions at 1 KB is 1 GB of RAM. DynamoDB is durable at any size: design the table around the question ("latest 20 orders for a user"), and it answers in single-digit milliseconds. An item is capped at 400 KB. Bad at: any question you did not design a key for. No joins, no ad-hoc reports, and the fallback is a full Scan. Redis also costs RAM, so it is not for data you only read once a day.

Play Checkout commits, then Out of stock, to see what a transaction buys you.

For volume, time and links three stores serve shapes that strain Postgres.

Wide-column (Cassandra, ScyllaDB). Spreads a huge write stream across many machines by partition key and keeps each partition sorted, so appends are cheap and one entity's history is one sequential read. Use it when sustained writes outgrow one primary (about 10K a second and up) and reads are "latest N for this entity". 5 billion chat messages a day is 5,000,000,000 / 86,400, about 58K writes a second, and about 174K at a 3x peak. Partition by (chat, day), sort by time, and keep each partition well under about 100 MB. Bad at: everything ad hoc. No joins, weak , one table per query. At 600 writes a second Postgres does the same job and gives you transactions as well.
Time-series and columnar (ClickHouse, TimescaleDB). Each column is stored together, so a query that touches 2 of 8 columns reads about a quarter of the bytes. Use it for append-only events and questions that aggregate millions of rows: dashboards, metrics, audit analytics. 200M events a day at 100 B is 20 GB a day, about 7.3 TB a year. An hourly dashboard that needs 2 of 8 equal columns reads 5 GB instead of 20 GB, before compression. Bad at: point updates, deletes and transactions. Insert in batches of thousands of rows, never one at a time. If your volume is modest and you already run Postgres, TimescaleDB keeps time-series inside it.
Graph (Neo4j). Relationships are first-class links, so walking friends of friends of friends follows pointers instead of joining a table to itself three times. Use it when the query is the traversal and runs 3 or more hops deep: fraud rings, recommendations, permission inheritance. With 200 friends each, 2 hops is 40,000 paths and 4 hops is up to 1.6 billion before . Bad at: whole-graph analytics, and , because traversals cross machines. Most social features are one or two hops, and an edge table in Postgres handles those. Do not buy a graph store for a friends list.

For finding things and holding files two stores serve users directly but never hold the truth. Both hang off a row in Postgres.

Search (Elasticsearch, OpenSearch). An from words to documents, so it ranks results and forgives typos in a way a SQL LIKE never can. Use it when users type free text and expect relevance, typo tolerance and facet counts, usually over a million or more documents; below that, try Postgres full-text search first. 10M products at 2 KB is 20 GB, which fits in one or two shards (a common guide is 10 to 50 GB per shard). New documents appear after the next refresh, about 1 second. Bad at: being the source of truth. No transactions, and a bad mapping means a reindex. Feed it from the database with CDC or an , and treat it as a copy you can rebuild.
(S3, GCS). Files by key for little money, almost never lost: S3 is designed for 99.999999999% durability. Use it for blobs: images, video, backups, exports, logs. 1M photos at 3 MB is 3 TB; in Postgres that bloats every backup and the buffer , in S3 it is one key per photo and the row stores the key. Put a in front for reads. Bad at: small, low-latency reads (first byte in tens of milliseconds, not one) and in-place edits, since an object is replaced whole. You cannot query inside it, so keep the searchable fields in Postgres.

Play A typo search, then A price change.

Tap any box to see what it is and what it talks to.

Client
Service
Database

Open in playground

Text description

A shopper buys 2 units of SKU a1 for 998.
BEGIN. Decrement stock where qty is at least 2, then insert the order.
COMMIT. Stock and order land together, or neither does.
The log streams to the replica, usually within milliseconds.
Order confirmed.

Bytes read for one day of events (assumed 100 B rows, 8 equal columns)

Show data table

Bytes read for one day of events (assumed 100 B rows, 8 equal columns)
	GB
Row store	20 GB
Columnar, 2 columns	5 GB

Tap any box to see what it is and what it talks to.

Client
Service
Search index
Database
Worker

Open in playground

Text description

A shopper types runnin shoos.
Words are split, matched with typo tolerance and scored by relevance.
Ranked ids come back, with facet counts (brand, size) in the same call.
The app loads the live price and stock for those ids from Postgres, so the page never shows a stale price.
Results rendered.

Quick checkWhere do uploaded photo bytes go?

A real product, many stores

Take a food-delivery app and ask the tree's questions of each fact, one at a time, and the architecture falls out.

Fact	Deciding question	Store
Orders and payments	Transactions? Yes. 200K a day is 2.3 a second	Postgres
Menus	Read whole, varying shape?	Postgres JSONB, or MongoDB
Driver location now	Key lookup, 50K pings / 4 s = 12.5K writes/s	Redis
Driver location history	Aggregated later?	ClickHouse
Dish search with typos	Full text and facets?	Elasticsearch
Restaurant photos	A file over 1 MB?	S3 and a CDN

What goes wrong, and the principles

Storage mistakes are quiet: they pass the demo and fail in month six, usually at the worst moment.

Mistake	What happens	Fix
One store for everything	At 9:00 on a Monday an analyst scans 100M rows in the same Postgres that takes payments, and checkout times out	Stream changes to a store built for scans (a replica for a small report, ClickHouse for the real thing), so reports and checkout stop sharing disk and CPU
Dual writes that drift	The order commits, the search index call times out, and the order never appears in search	Write one source of truth and derive the rest through CDC or an outbox. A relay retries until the index catches up; a full reindex is the repair of last resort
Picking by hype	A team picks DynamoDB "to scale", then the first report is revenue by category	Name the access patterns first, then the store. If you cannot name them yet, that is the argument for Postgres, which answers questions you have not thought of
The hot partition	A partition key that is the same for most writes sends them all to one partition: with the date as the key, 5,000 writes a second hit one partition that takes about 1,000	Pick a key that varies on every request (user id, chat id plus day). For a single hot counter, split it into N keys and add them on read. The code section shows both

Code

Relational (PostgreSQL)

place-order.js

// Relational (PostgreSQL): stock and the order change together or not at all.
// Decrement stock first; if no row changed, the item is out of stock, so roll back instead of creating an order
export class OutOfStockError extends Error {
  constructor(sku) { super(`out of stock: ${sku}`); this.name = 'OutOfStockError'; this.sku = sku; }
}

export async function placeOrder(pool, { userId, sku, qty, total }) {
  const c = await pool.connect();
  try {
    await c.query('BEGIN');
    const { rowCount } = await c.query('UPDATE stock SET qty = qty - $2 WHERE sku = $1 AND qty >= $2', [sku, qty]);
    if (rowCount === 0) throw new OutOfStockError(sku);
    await c.query('INSERT INTO orders (user_id, sku, qty, total) VALUES ($1, $2, $3, $4)', [userId, sku, qty, total]);
    await c.query('COMMIT');
  } catch (e) {
    await c.query('ROLLBACK');
    throw e;
  } finally {
    c.release();
  }
}

Document (MongoDB)

document.js

// Embed bounded children that are read together: an order and its line items are one document
await db.collection('orders').insertOne({
  userId, status: 'paid',
  items: [{ sku: 'a1', qty: 2, price: 499 }],   // embedded line items
  total: 998,
});

// Reference unbounded children: a product can collect millions of reviews, far past the 16 MB document limit
await db.collection('reviews').insertOne({ productId, userId, stars: 5, text, createdAt: new Date() });
await db.collection('reviews').createIndex({ productId: 1, createdAt: -1 });   // "latest reviews for a product"

Key-value (Redis, DynamoDB)

// Key-value: one key, one value, about a millisecond. No joins, so the key IS the query.
await redis.set('session:' + id, JSON.stringify(sess), 'EX', 1800);   // expires after 30 idle minutes
await redis.incr('views:' + postId);                                   // atomic: no read-modify-write race

Wide-column (Cassandra)

-- Wide-column (Cassandra CQL): partition by (sensor, day) so no partition grows without bound;
-- rows inside a partition are sorted by time
CREATE TABLE readings (
  sensor_id text, day date, ts timestamp, value double,
  PRIMARY KEY ((sensor_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC);

Partition keys that stay even

partition-keys.js

// Keys that keep a partition small and a write load even. Pure functions: easy to test, safe to copy.

// Wide-column: one partition per chat per day, so no partition grows without bound.
export function dayBucket(chatId, date) {
  return `${chatId}#${date.toISOString().slice(0, 10)}`;
}

// Key-value: one very hot key becomes n keys. Writes pick one at random...
export function spreadKey(base, n, rnd = Math.random) {
  if (!Number.isInteger(n) || n < 1) throw new RangeError('n must be a positive integer');
  return `${base}#${Math.floor(rnd() * n)}`;
}

// ...and a read asks all n and adds the answers up. That is the price of spreading.
export function readKeys(base, n) {
  if (!Number.isInteger(n) || n < 1) throw new RangeError('n must be a positive integer');
  return Array.from({ length: n }, (_, i) => `${base}#${i}`);
}

Object storage (S3)

// Object storage (S3): the server only signs a short-lived URL; the file itself never passes through it
import { PutObjectCommand, S3Client } from '@aws-sdk/client-s3';
import { getSignedUrl } from '@aws-sdk/s3-request-presigner';
const s3 = new S3Client({});
const ALLOWED = new Set(['image/jpeg', 'image/png', 'image/webp']);

app.post('/api/uploads', async (req, res) => {
  if (!ALLOWED.has(req.body.type)) return res.status(415).end();
  const key = `u/${req.user.id}/${crypto.randomUUID()}`;            // the server picks the key, never the client
  const url = await getSignedUrl(s3,
    new PutObjectCommand({ Bucket: process.env.BUCKET, Key: key, ContentType: req.body.type }),
    { expiresIn: 300 });                                               // valid for 5 minutes
  res.json({ url, key });                                              // the client PUTs the file to url
});

Search and time-series

search-events.js

// Producer: publish the change after the database commit (better still, the outbox or CDC, so it can't be lost)
export async function onProductSaved({ id, name, price, version }) {
  await queue.publish('product.updated', { id, name, price, version });
}

// Indexer: consumes the topic and upserts the search document. Replays and out-of-order events are safe:
// with external versioning, Elasticsearch rejects any version not newer than the one it already has.
export async function indexProduct({ id, name, price, version }) {
  try {
    await es.index({ index: 'products', id, document: { name, price }, version, version_type: 'external' });
  } catch (err) {
    if (err.meta?.statusCode !== 409) throw err;    // 409 = already have this or a newer version: skip
  }
}

-- ClickHouse: append-only events, partitioned by month, sorted for the common filter
CREATE TABLE events (
  ts DateTime, user_id UInt64, type LowCardinality(String), sku String, price Decimal(10, 2)
) ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (type, ts);

-- Orders per hour over the last day: scans two columns, not whole rows
SELECT toStartOfHour(ts) AS hour, count() AS orders
FROM events
WHERE type = 'order_placed' AND ts > now() - INTERVAL 1 DAY
GROUP BY hour ORDER BY hour;

The chooser, as code

Choosing a data store in the interview

Bring storage up when you reach the data model, and let each store earn its place with a number.

If the interviewer says	You raise
"Billions of chat messages a day"	Cassandra, partitioned by chat and day, sorted by time
"Users upload photos and video"	S3 with a presigned URL and a CDN; the key in Postgres
"Search by keyword, with typos"	Elasticsearch fed by CDC
"A dashboard over events"	ClickHouse, never the transactional database
"Carts and sessions"	Redis, with a TTL
"Friends of friends of friends"	Postgres to 2 hops, a graph store beyond

Interview questions

Where do chat messages live?

Wide-column (Cassandra), partitioned by conversation and sorted by sequence.

Why not store images in Postgres?

Blobs bloat the database and its backups. Use , with the key in Postgres.

How do you keep Elasticsearch in sync?

Change events feed an indexer, and a full reindex rebuilds it from the source of truth.

SQL or NoSQL?

Let the access patterns decide. or ad-hoc queries mean . Known-key access at huge scale means .

When would you pick DynamoDB over Postgres?

When access is few, known and key-based at huge scale (carts, sessions). Not for joins or ad-hoc reports.

600 writes a second of messages: Cassandra or Postgres?

Postgres. That rate is far below its limit, and you keep transactions and ad-hoc queries. Cassandra pays off from tens of thousands of writes a second.

A DynamoDB table is throttled but mostly idle. Why?

A : the partition key sends most writes to one key. Pick a key that varies, or split one into N.

Check yourself

Question 1 of 5

Score 2 out of 3 or better to mark this page complete.

1. Which store for a like counter that's read constantly?PostgresRedisS3

Storing Data: Choosing the Right Store

Trade-offs

Bring it up before they ask

Why one database isn't enough

Pick a store, step by step

Stores compared

A real product, many stores

What goes wrong, and the principles

Model by access pattern first

One source of truth per fact

OLTP and OLAP don't share a database

Boring by default

Code

Relational (PostgreSQL)

Document (MongoDB)

Key-value (Redis, DynamoDB)

Wide-column (Cassandra)

Partition keys that stay even

Object storage (S3)

Search and time-series

The chooser, as code

Choosing a data store in the interview

Write down the access patterns

Start with Postgres and say why

Name the number that breaks it

Keep one source of truth

Own the cost of each store

Interview questions

Check yourself

Deep dives