Choosing a database

Name the access pattern first, then pick the store. Postgres covers most patterns on one machine. A specialised store earns its place when a measured access pattern outgrows Postgres.

Not startedSaved in this browser only.

1Choose from the access pattern: query shape, read and write mix, consistency, scale and latency.
2Start with Postgres. JSONB, full text, partitions and pgvector cover most shapes on one machine.
3Add a store only on a measured signal. Name the source of truth; every other store holds a copy fed by change data capture.
4Know each store's guarantees: which reads can be stale, and what one transaction can cover.

From access pattern to store

start in the middle column

Read each row left to right. Move to the right column only when a measurement says so.
A "copy" store never takes writes from the service. Change data capture feeds it from the truth.
Two shapes in one product often means two stores, with one of them the truth.

I name the query shape first. While it fits one machine I keep it in Postgres; past that, each shape has its own store, and search, analytics and vectors become copies.

Status per workload

approved, only if, not approved

workload	approved	only if	not approved	deciding reason
Accounts, orders, payments	Postgres	Distributed SQL Past one primary	Cassandra	Money moves need multi-row transactions and constraints.
Sessions, hot profile reads	Redis in front of Postgres	Postgres alone Reads fit the primary	Redis as the truth	Redis replicates asynchronously; a failover can lose acknowledged writes.
Shopping carts, very large scale	DynamoDB	Cassandra Stale reads are fine	Redis as the truth	Access by key only; partitions split as the data grows.
Chat messages per conversation, very large scale	Cassandra	Postgres Fits one machine	OpenSearch as the store	Write-heavy, one known query: partition by conversation, cluster by time.
Product catalogue, attributes vary	Postgres JSONB	MongoDB Past one machine	One row per attribute	A GIN index answers attribute filters; joins to orders stay possible.
Product search box	OpenSearch, a copy	Postgres full text One machine, simple ranking	Search engine as the truth	Relevance, typos and facets at scale; writes show after a refresh.
Server metrics from a large fleet	TimescaleDB	Postgres partitions Fits one machine	One unpartitioned table	Append by time, compress old chunks, expire by time.
Live dashboards over billions of events	ClickHouse, a copy	Postgres rollups One machine	Scans on the primary	Columnar storage reads only the columns a query names.
Ad hoc analysis over years of data	BigQuery, a copy	ClickHouse Answers under a second	Scans on the primary	Scans run on demand; seconds per query are acceptable.
Fraud rings, many hops	Neo4j, a copy	Postgres recursive Few hops, seconds fine	One query per hop from the service	Each hop follows stored links; cost grows with edges touched.
Semantic search, 1 million embeddings	Postgres pgvector	Vector database Past one machine's memory	A full scan per query	An HNSW index keeps the vectors next to the rows they describe.
Images and video	S3 + CDN		bytea in Postgres	Files in rows grow every backup and every replica.
A ledger larger than one machine	Distributed SQL	Sharded Postgres Each transaction on one shard	Cassandra	Transfers need transactions across rows on different nodes.

Every approved store in this table is the answer the picker below gives for that workload. A lab test checks each row.

For each workload I name the approved store and the condition under which another store works. I also name the store I refuse, and the mechanism that decides.

Guarantees per store

capabilities, from the official documentation

store	a read returns	one transaction covers	source
Postgres	The latest commit on the primary. Replicas are asynchronous by default; synchronous replication is an option.	Any rows in any tables, up to SERIALIZABLE isolation.	isolation, standby
Redis	The latest write on the primary. Replication is asynchronous, so a failover can lose acknowledged writes.	MULTI/EXEC or one script, run with no other command in between.	replication, transactions
DynamoDB	Eventually consistent by default. Strongly consistent reads on tables and local indexes; global secondary indexes are always eventual.	TransactWriteItems: several items, all or nothing.	read consistency, transactions
Cassandra	Set per query: ONE, QUORUM, ALL. Quorum reads and writes overlap.	A lightweight transaction (IF NOT EXISTS, IF): compare-and-set in one partition.	consistency, CQL
MongoDB	Set by read concern; "majority" returns data a majority acknowledged.	One document is always atomic. Multi-document transactions exist, at a cost.	atomicity, transactions
OpenSearch	Near real time: a write is searchable after the next refresh.	One document. No multi-document transactions.	near real time
ClickHouse	Data from completed inserts.	An insert into one partition is atomic. Multi-statement transactions are experimental.	transactional guarantees
BigQuery	Committed table data.	Multi-statement transactions across tables.	transactions
Neo4j	Committed data.	ACID transactions over nodes and relationships.	transactions
Spanner	External consistency: commit order matches real time.	Rows across splits and machines.	TrueTime
S3	Strong read-after-write for PUT and DELETE.	One key. No atomic update across keys.	consistency model
pgvector	The same as Postgres: vectors are columns.	Vectors and rows in one transaction. HNSW and IVFFlat indexes are approximate.	pgvector

Before I put a store on the diagram I say what a read can return and what one transaction can cover. That decides where the truth lives.

Start with Postgres

what one Postgres covers, and the signal to add a store

need	Postgres covers it with	shown	add a store when you see	then add
Key lookups	Primary key index	Tested	Reads take the primary's CPU from its writes.	Redis cache
Documents	JSONB with a GIN index	Tested	Whole-document access past one machine.	MongoDB
Text search	tsvector, GIN, ts_rank_cd; pg_trgm for typos	Tested	Relevance tuning, facets, or index size past one machine.	OpenSearch
Time series	Range partitions; a query reads only its partitions	Tested	Write rate or retention past one machine.	TimescaleDB
Job queue	FOR UPDATE SKIP LOCKED	Tested	Fan-out to many consumers, replay, retention of the stream.	Kafka
Vectors	pgvector, HNSW index	Cited	The index outgrows one machine's memory.	Vector database
Geography	PostGIS: geometry types and spatial indexes	Cited	Rarely; PostGIS is the reference for spatial SQL.
Analytics	Read replica, materialised views, rollup tables	Cited	Dashboards scan more than a replica can read in time.	ClickHouse
Feeding other stores	Logical replication and logical decoding	Cited	Always, once a copy exists.	CDC

Tested: a lab test runs the query on the lab Postgres and checks that the plan uses the index or skips the partitions. Cited: pgvector, PostGIS, logical replication.

Postgres covers documents, full text, time partitions, queues and vectors on one machine. I add a store when a measured signal in the right-hand column appears.

Try it: pick a store

every answer is a recorded row of a tested function

query shape

reads and writes

consistency

scale

read latency target

usePostgresrelational OLTP

source of truthPostgres

A primary key lookup is one index probe. With the working set in memory, it takes well under a millisecond.
The data and the write rate fit one machine, so a second store adds work and no capacity.
Add read replicas when reads outgrow one node. Writes stay on the primary.

change one trait

DynamoDB
Redis

360 combinations of traits, each produced and checked by the lab's decision function. The rules hold for every one. Files go to object storage, the truth is never a cache or an index, and strong consistency never lands on Cassandra.

I set the traits of the access pattern and read off the store and the source of truth. Then I check which one change would move the answer.

The decision rules

pseudo code

recommendpseudo code

recommend(t):
  IF t.shape = files: RETURN S3, + CDN IF under 1 ms
  IF t.shape = key AND under 1 ms AND (mostly reads OR beyond one machine):
    RETURN Redis in front of recommend(t at a few ms).truth¹
  IF fits one machine:
    graph AND faster than seconds  → Neo4j, a copy fed from Postgres
    search AND under 1 ms          → OpenSearch, a copy fed from Postgres
    ELSE → Postgres with the feature for the shape²
           + Redis cache IF mostly reads AND under 1 ms
  beyond one machine:
    key, items under a key:
      mostly writes AND stale is fine³  → Cassandra
      ELSE                             → DynamoDB
    joins and transactions → distributed SQL
    documents → MongoDB;  time series → TimescaleDB;  many hops → Neo4j
    search, analytics, vectors → a copy fed from Postgres by CDC⁴

1A cache never changes where the truth lives. The truth is what the same pattern would use without the latency target.
2One machine: JSONB, full text, partitions, pgvector or a recursive query.
3Cassandra takes writes on any replica. With strong reads required, DynamoDB is the answer.
4The service never writes the copy. Change data capture does, from the log.

Tested source Go: recommend

Go: recommendgo


// Recommend returns the store for an access pattern. It starts from Postgres and moves to a
// specialised store only when a trait needs one.
func Recommend(t Traits) (Recommendation, error) {
  if err := t.valid(); err != nil {
    return Recommendation{}, err
  }
  big := t.Scale == "beyond"
  strong := t.Consistency == "strong"
  pg := func(reasons ...string) Recommendation {
    r := Recommendation{Store: "postgres", Truth: "postgres", Reasons: reasons}
    if t.Mix == "read-heavy" && !slices.Contains(reasons, "pg-replica") {
      r.Reasons = append(r.Reasons, "pg-replicas")
    }
    if t.Latency == "sub-ms" && t.Mix == "read-heavy" {
      r.Add, r.Reasons = "redis", append(r.Reasons, "cache")
    }
    return r
  }
  copyOf := func(store string, reasons ...string) Recommendation {
    return Recommendation{Store: store, Truth: "postgres", Reasons: append(reasons, "pg-truth", "cdc")}
  }

  switch t.Shape {
  case "blob":
    r := Recommendation{Store: "s3", Truth: "s3", Reasons: []string{"blob"}}
    if t.Latency == "sub-ms" {
      r.Add, r.Reasons = "cdn", append(r.Reasons, "cdn")
    }
    return r, nil
  case "key":
    switch {
    case t.Latency == "sub-ms" && (t.Mix == "read-heavy" || big):
      slower := t
      slower.Latency = "ms"
      base, err := Recommend(slower)
      if err != nil {
        return Recommendation{}, err
      }
      return Recommendation{Store: "redis", Truth: base.Truth, Reasons: []string{"redis-offload", "redis-memory", "redis-loses"}}, nil
    case big && t.Mix == "write-heavy" && !strong:
      return Recommendation{Store: "cassandra", Truth: "cassandra", Reasons: []string{"cass-writes", "cass-tunable"}}, nil
    case big:
      r := Recommendation{Store: "dynamodb", Truth: "dynamodb", Reasons: []string{"dynamo-scale"}}
      if strong {
        r.Reasons = append(r.Reasons, "dynamo-strong")
      }
      return r, nil
    }
    return pg("pg-key", "one-node-fits"), nil
  case "partition":
    switch {
    case big && t.Mix == "write-heavy" && !strong:
      return Recommendation{Store: "cassandra", Truth: "cassandra", Reasons: []string{"cass-model", "cass-writes", "cass-tunable"}}, nil
    case big:
      r := Recommendation{Store: "dynamodb", Truth: "dynamodb", Reasons: []string{"dynamo-sort", "dynamo-scale"}}
      if strong {
        r.Reasons = append(r.Reasons, "dynamo-strong")
      }
      return r, nil
    }
    return pg("pg-partition", "one-node-fits"), nil
  case "relational":
    if big {
      return Recommendation{Store: "distsql", Truth: "distsql", Reasons: []string{"pg-relational", "distsql"}}, nil
    }
    return pg("pg-relational", "one-node-fits"), nil
  case "document":
    if big {
      return Recommendation{Store: "mongodb", Truth: "mongodb", Reasons: []string{"mongo-docs"}}, nil
    }
    return pg("pg-jsonb", "one-node-fits"), nil
  case "search":
    if big || t.Latency == "sub-ms" {
      r := copyOf("opensearch", "search-engine")
      if strong {
        r.Reasons = append(r.Reasons, "search-nrt")
      }
      return r, nil
    }
    return pg("pg-fts", "one-node-fits"), nil
  case "timeseries":
    if big {
      return Recommendation{Store: "timeseries", Truth: "timeseries", Reasons: []string{"ts-store"}}, nil
    }
    return pg("pg-time", "one-node-fits"), nil
  case "analytics":
    switch {
    case big && t.Latency == "seconds":
      return copyOf("bigquery", "olap", "warehouse"), nil
    case big:
      return copyOf("clickhouse", "olap", "olap-fresh"), nil
    case t.Latency == "seconds":
      return Recommendation{Store: "postgres", Truth: "postgres", Reasons: []string{"pg-replica", "one-node-fits"}}, nil
    }
    return pg("pg-rollup", "pg-replica"), nil
  case "graph":
    if big {
      return Recommendation{Store: "neo4j", Truth: "neo4j", Reasons: []string{"graph-hops", "graph-scale"}}, nil
    }
    if t.Latency == "seconds" {
      return Recommendation{Store: "postgres", Truth: "postgres", Reasons: []string{"pg-recursive", "one-node-fits"}}, nil
    }
    return copyOf("neo4j", "graph-hops"), nil
  case "vector":
    if big {
      return copyOf("vectordb", "vector-scale"), nil
    }
    return pg("pg-vector", "one-node-fits"), nil
  }
  return Recommendation{}, fmt.Errorf("unhandled shape %q", t.Shape)
}

Files go to object storage. Everything else starts in Postgres and moves only when it passes one machine or needs a capability Postgres lacks.

One truth, many copies

change data capture

method	status	why
Write Postgres; CDC updates the copies	Approved	One write. The log orders every change.
Outbox table in the same transaction	Approved	The event commits with the row; a worker ships it.
The service writes both stores	Not approved	A crash between the writes leaves them different.
Nightly full reload	Small data, stale ok	Simple, but the copy is up to a day old.

A logical replication slot keeps WAL on the primary until its consumer reads it. A stopped consumer fills the primary's disk; alert on slot lag.

The service writes one store. Change data capture reads its log and updates the search index, the warehouse and the cache. A copy can lag, but it never drifts for good.

Failure cases

what breaks, and what keeps it correct

event	result	why it stays correct, or the fix	saved by
A user saves a product, then searches for it	The index has not refreshed; the product is missing.	Show the user's own items from Postgres. Search is a copy.	Read own writes
Redis fails over	Recent cache and session writes are lost.	Reload the cache from Postgres. Keep nothing in Redis that you cannot rebuild.	Truth
The CDC consumer stops	Copies go stale; the slot holds WAL on the primary.	Alert on slot lag. On restart the consumer resumes from its position.	Slot
The service writes Postgres, then crashes before the index write	The stores differ.	Write once; let CDC or an outbox carry the change.	Outbox
A read on a DynamoDB global secondary index right after a write	It returns the old value.	Index reads are always eventual. Read the table with a consistent read.	DynamoDB
A new query on Cassandra that no table models	A full scan, or no answer.	Add a table for the query and backfill it from the truth.	Backfill
One partition key takes most writes	One node runs hot while others idle.	Spread the key: add a high-cardinality part, or salt a hot key.	Key design
Files stored in database rows	Backups, restores and replicas grow with every file.	Move files to S3; keep the key in the row.	S3

Scale ladder

start simple; climb only on a signal

step	add	it handles	move up when you see
1	One Postgres with the feature each shape needs.	104,051 key lookups a second in the lab, plus documents, text, time and vectors.	Reads slow the primary's writes.
2	Read replicas for reads that can lag.	Reads grow with each replica. Writes stay on one primary.	Hot keys are read far more often than they change.
3	A Redis cache in front of the hot keys.	57,047 GETs a second on one Redis thread; a cluster adds nodes.	A shape Postgres serves poorly at this size: relevance, wide scans.
4	Copies fed by CDC: search, columnar analytics.	Each copy scales on its own. Postgres stays the truth.	One access pattern's writes or data pass the largest primary.
5	A second primary store for that pattern: DynamoDB, Cassandra, distributed SQL.	Writes and storage that grow with nodes.	Top of the ladder.

Demand example: 10 million daily users × 50 key reads = 500,000,000 / 86,400 ≈ 5,787 a second. Postgres spread the lookups over all 16 hardware threads of the laptop. Redis runs commands on one thread.

I start with one Postgres, add replicas and a cache for reads, then copies fed by change data capture. A second primary store comes last, for one access pattern that has outgrown one machine.

Drill

predict, then reveal

0 of 9 known

A team wants MongoDB because "the schema will change". What do you ask, and what do you suggest?
Why is the search index a copy and not the source of truth?
You need user profile reads under 1 ms at 50,000 a second. Is Redis required?
When does DynamoDB beat Postgres for shopping carts?
Dashboards scan 5 billion events on the primary. What fails, and what is the fix?
The service writes Postgres, then OpenSearch. What goes wrong?
Cassandra, replication factor 3, QUORUM writes and QUORUM reads. Why does a read see the last write?
Where do user photos go?
What is the signal to add a specialised store?

Numbers to say

measured, derived or cited

Postgres: 104,051 primary key lookups a second; one lookup 0.082 ms (median).
Redis: 57,047 GETs a second on one thread; one GET 0.177 ms (median).
quorum: Replication factor 3: QUORUM is 2; 2 + 2 > 3, so reads meet writes.
DynamoDB: Global secondary index reads: always eventually consistent (documentation).
S3: Strong read-after-write for PUT and DELETE (documentation).
picker: 360 trait combinations, every one tested.

Postgres 16.14 and Redis 8.10.2 on an 8-core laptop shared with other runs, 32 clients, 100,000 keys. Read them as orders of magnitude.