Data Store Selection Matrix

The question is never "which database is best." It is: for this access pattern, at this cardinality, with this consistency requirement, what does the wrong choice cost me in two years?

That last clause is the one people skip. Storage choices are the hardest architectural decision to reverse. Frameworks get replaced in a weekend. A data store with three years of production data, a schema every service depends on, and an operational runbook the on-call rota has memorised gets replaced in three quarters, if at all.

So the bar for deviating from the boring choice should be high, and this page exists to make the deviation defensible when it is genuinely warranted.

Start here: the honest default

Use PostgreSQL until you have a specific, measured reason not to.

This is not conservatism. A single modern Postgres instance handles tens of thousands of transactions per second, tens of terabytes, JSON documents, full-text search, geospatial queries, vector similarity, time-series partitioning, and queueing. It gives you serializable transactions, mature tooling, a large hiring pool, and managed offerings on every cloud.

Most polyglot persistence in the wild is not the result of a requirement. It is the accumulated residue of individual teams each choosing what they knew, and the bill arrives as five backup strategies, five failure modes, and a dual-write problem nobody owns.

Deviate when you have a number. "Our analytical queries scan 400 GB and take 90 seconds on Postgres" is a number. "Postgres doesn't scale" is not.

The decision sequence

Answer in this order. Each answer eliminates categories.

1. What is the access pattern? Point lookups by key, range scans, ad-hoc multi-table joins, aggregation over billions of rows, traversal of relationships, similarity search, or full-text ranking. This single answer eliminates most of the field.
2. What is the write pattern? Random updates, append-only, bulk load, high-cardinality time-stamped events.
3. What is the working-set size versus total size? If the hot set fits in RAM, most of the differences between engines disappear.
4. What consistency do you actually need? See the consistency models reference. Assume you need less than your instinct says, except for money.
5. What is the operational cost of being wrong? Who runs it at 3 a.m.? Who restores it? Has anyone tested the restore?

Question 5 is the tiebreaker and it is almost never asked in the design review.

---

The matrix

Category	Fits when	Breaks when	Representative	The honest trade-off
Relational (OLTP)	Ad-hoc joins, transactions across entities, evolving query patterns, integrity constraints	Analytical scans over billions of rows; single-node write ceiling	PostgreSQL, MySQL	Best default; write throughput is eventually bounded by one primary
Distributed SQL	You need relational semantics past a single node, or multi-region writes	Latency-sensitive single-key work; small datasets	CockroachDB, Spanner, YugabyteDB	Serializable at scale, paid for in per-transaction latency and operational complexity
Columnar / OLAP	Aggregation over billions of rows, few columns at a time, mostly append	Point lookups, updates, transactions	ClickHouse, BigQuery, Snowflake, DuckDB	10–100x on scans; updates and deletes are second-class or absent
Key–value	Known-key point access, extreme throughput, simple values	Any query you cannot express as a key	DynamoDB, Redis, FoundationDB	Predictable latency at any scale; a new access pattern means a new table or a migration
Document	Aggregates read and written whole, per-document schema variance	Cross-document transactions, joins, ad-hoc analytics	MongoDB, DynamoDB (doc mode)	Schema flexibility now, schema archaeology later — the schema still exists, it just lives in the application
Wide-column	Massive write volume, time-ordered rows within a partition, linear scale-out	Ad-hoc queries; anything needing cross-partition consistency	Cassandra, ScyllaDB, HBase	Write throughput and availability that nothing else matches; you must design the table per query, up front
Graph	Variable-depth traversal is the primary workload — fraud rings, permissions closures, recommendations	Relationships exist but three joins would do	Neo4j, Neptune	Traversals that are elegant here are painful in SQL; the reverse is also true, and most "graph problems" are two joins
Time-series	High-cardinality timestamped metrics, downsampling, retention, time-window queries	General-purpose querying; frequent updates	TimescaleDB, InfluxDB, Prometheus	10–20x compression and automatic retention; cardinality explosions are the standard outage
Search	Relevance ranking, fuzzy matching, faceting, typo tolerance	As a system of record — ever	Elasticsearch/OpenSearch, Typesense, Meilisearch	Ranking quality nothing else matches; it is a derived index, and it will drift from the source
Vector	Semantic similarity over embeddings at scale, hybrid with keyword	Small corpora; exact-match retrieval	pgvector, Qdrant, Pinecone, Weaviate	Sub-10 ms ANN over millions of vectors; recall is a tuning parameter, and re-embedding on model change is a migration
Cache	Recomputation is expensive and staleness is tolerable	Durability required; correctness depends on it	Redis, Memcached	Latency; every cache is a second copy of the truth and therefore a correctness liability
Object storage	Large immutable blobs, cheap durable bulk, data-lake substrate	Small-record random access; low-latency reads	S3, GCS, R2	Effectively infinite and effectively free; per-object latency is tens of milliseconds
Log / stream	Ordered, replayable event history; fan-out to many consumers	Random access by key; mutation	Kafka, Pulsar, Kinesis	Replay and decoupling; ordering exists only within a partition

Before you add a store: can Postgres do it?

For each specialist category, there is a point where the extension stops being enough. Knowing where that point is saves most of the arguments.

You want	Postgres option	Good enough until	Then move to
Document storage	JSONB + GIN index	Documents are a minority of your model; no sharding needed	MongoDB, DynamoDB
Full-text search	tsvector + GIN	Basic relevance is acceptable; no faceting, typo tolerance or custom analysers; corpus under ~10M docs	Elasticsearch, Typesense
Vector search	pgvector + HNSW	Under ~5–10M vectors and you want them transactionally consistent with your rows	Qdrant, Pinecone, Vespa
Time-series	Declarative partitioning, or TimescaleDB	Hundreds of millions of rows with modest cardinality	ClickHouse, InfluxDB
Analytics	Read replica, materialised views, or DuckDB over exports	Scans stay under a few hundred GB and minutes are acceptable	ClickHouse, BigQuery, Snowflake
Job queue	SELECT ... FOR UPDATE SKIP LOCKED	Under a few thousand jobs/second, single-region	SQS, Kafka, Temporal
Graph traversal	Recursive CTE, ltree	Depth is bounded and known	Neo4j
Caching	UNLOGGED tables, or the OS page cache	You have not measured a cache miss problem yet	Redis

The pattern: the extension is right until either scale or a specialist feature breaks it, and you should be able to name which one before you add a system.

The polyglot tax

Every additional store costs you, whether or not it appears in the design document:

• A second failure mode, with its own recovery procedure and its own on-call knowledge requirement.
• A backup and a tested restore. Untested restore is not backup.
• The dual-write problem. Writing to Postgres and then to Elasticsearch is two writes with no transaction between them. When the second fails, they diverge silently. The fix is the transactional outbox or change data capture — never a try/catch around the second write.
• Consistency questions with no good answer, because now "the data" exists in several places at several ages.
• Hiring and rotation, permanently.

A rough rule: adding a store is worth roughly 0.25 to 0.5 of an engineer, indefinitely. If the benefit does not clear that, keep the extension.

Sizing shortcuts

Fast estimates that avoid a spreadsheet. Pair these with the capacity calculator when you need the full derivation.

• Rows per second a single Postgres primary sustains for simple writes: low tens of thousands with tuned WAL and batching; low thousands if every write fans out to five indexes and a trigger.
• When to shard: when your working set no longer fits in RAM and the read replica route is exhausted. Not before. Sharding costs you cross-shard joins, distributed transactions, and every rebalance for the life of the system.
• Index budget: every index is a write tax. Five indexes on a hot write table is usually two too many.
• Retention beats scale. The cheapest way to make a store fast is to hold less in it. Ask what the data is worth at 90 days before designing for it at three years.

Anti-patterns

Search engine as the system of record. It is a derived index. It will lose documents, it will reindex, and it has no transactions. Own the truth elsewhere and rebuild the index from it — and prove you can, on a schedule.

Cache as a database. If losing the cache means losing data or serving wrong answers rather than slow ones, it is not a cache.

A queue used as a database. Replaying a topic to answer a query means you needed a materialised view, not a longer retention setting.

Choosing for peak scale you do not have. Selecting Cassandra for a hundred writes per second buys you the operational profile of a system at a million and none of the querying you actually need today.

Choosing by benchmark. Vendor benchmarks measure the workload the vendor chose. The only benchmark that matters is your access pattern, your data distribution, and your hardware — and it takes two days to run.

One store per microservice, on principle. Service independence is about the schema boundary and deploy independence, not the engine. Twelve services can own twelve schemas in one cluster and remain independent.