Choose a database by access pattern, not by hype

Across ten years of backend work I’ve shipped production systems on MySQL, PostgreSQL, DynamoDB, MongoDB, Elasticsearch, Neo4j, Redis, Redshift and BigQuery. The rule I keep coming back to: write down the queries before you pick the database.

“We need something that scales” isn’t a requirement. “Fetch an order by ID in under 10 ms at 5,000 requests per second”, “find products similar to this one by shared attributes”, “sum revenue by region for the last two years” — those are requirements, and each one points at a different kind of store.

Start with an access-pattern list

Before choosing, list for each entity:

Ten minutes on this table saves months of fighting the wrong store.

The main families, by the query they’re good at

Relational (PostgreSQL, MySQL) — the default. Joins, transactions, constraints, flexible queries you didn’t predict. Most systems should start here and leave only when a specific access pattern demands it.

SELECT o.id, o.total, c.email
FROM   orders o JOIN customers c ON c.id = o.customer_id
WHERE  o.status = 'PAID' AND o.created_at > now() - interval '7 days';

Key-value / wide-column (DynamoDB, Cassandra) — known patterns at huge scale. Predictable single-digit-millisecond lookups by key, practically unlimited throughput, and you must design keys around your queries up front. Ad-hoc queries are expensive or impossible.

GetItem   PK = "TENANT#acme"   SK = "PROFILE"
Query     PK = "TENANT#acme"   SK begins_with "EVENT#2026-10"

Document (MongoDB) — aggregates read and written whole. Good when an entity is naturally a nested document (a product with variable attributes, a form submission) and you mostly load it by ID.

Search (Elasticsearch, OpenSearch) — text, filters, ranking. Full-text relevance, faceted filters, fuzzy matching, aggregations for filter counts. Treat it as a derived index, fed from your source of truth, not as the source of truth itself.

{ "query": { "bool": {
    "must":   { "match": { "name": "oak dining table" } },
    "filter": [ { "term": { "in_stock": true } }, { "range": { "price": { "lte": 40000 } } } ]
}}}

Graph (Neo4j) — relationships several hops deep. “Which products share categories and attributes with this one?”, “what depends on this feature?”. In SQL these become recursive joins; in a graph database they’re a short pattern. I’ve used it for product recommendations and for ML metadata lineage.

MATCH (p:Product {id: $id})-[:HAS_ATTRIBUTE|IN_CATEGORY]->(x)<-[:HAS_ATTRIBUTE|IN_CATEGORY]-(other:Product)
WHERE other <> p
RETURN other.id, count(x) AS shared
ORDER BY shared DESC LIMIT 10;

Cache (Redis) — hot reads, counters, short-lived state. Sessions, rate limits, leaderboards, cached responses. Plan for it being empty after a restart.

Warehouse (BigQuery, Redshift, Snowflake) — scans over history. Columnar storage that makes “sum revenue by month and category over two years” cheap. Not for serving user requests.

SELECT DATE_TRUNC(order_date, MONTH) AS month, category, SUM(total) AS revenue
FROM   analytics.orders
WHERE  order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR)
GROUP  BY month, category;

When you need two (or three)

Real systems often combine stores: Postgres for orders, Elasticsearch for product search, a warehouse for reporting. That’s fine — if you decide three things explicitly:

1. Which store is the source of truth for each piece of data. Everything else is derived and can be rebuilt.
2. How derived stores are fed: events, change data capture, or batch loads — and how stale they may be. Search results a few seconds behind are usually fine; a balance a few seconds behind usually isn’t.
3. How you rebuild a derived store from scratch. If you can’t re-index search from the source of truth, it has quietly become a second source of truth.

The anti-pattern is the one in the picture at the end of this post: each new feature adds a store because it’s fashionable, with no answer to those three questions.

A quick decision guide

Takeaways

• Write the access-pattern list before choosing a database.
• Start relational; move a workload out when a specific query demands it.
• Search indexes and caches are derived — feed them from the source of truth and be able to rebuild them.
• When you use several stores, name the source of truth for every piece of data.