Choosing the right DB

"Just use Postgres" is good advice about 80% of the time. The other 20% is where systems get slow, expensive, or painful to change: a search box running LIKE '%term%' over millions of rows, a leaderboard recomputed with ORDER BY on every request, analytics queries that lock up the production database every Monday morning.

This post is a map of the database families you will actually meet, what each one is good and bad at, and how to decide. The short version: pick by access pattern, not by popularity, and expect a non-trivial system to end up with more than one database.

The families at a glance#

Relational: the default, for good reasons#

Tables, schemas, foreign keys, and ACID transactions. You describe what you want in SQL and the planner figures out how. That last part matters more than it sounds: when the product changes and you need a query nobody planned for, a relational database just runs it.

Use it for the core of most products: users, orders, payments, inventory, anything where correctness matters and records relate to each other.

Watch out for write-heavy workloads that outgrow one primary, full-text search beyond the basics, and analytics on the same instance that serves users.

Modern Postgres also stretches surprisingly far into other families: JSONB for document-style data, built-in full-text search, PostGIS for geo, TimescaleDB for time-series, pgvector for embeddings. Before adding a new database, check whether an extension covers your case well enough.

Distributed SQL: when one primary is not enough#

CockroachDB, Spanner, TiDB and YugabyteDB keep the SQL model and transactions but spread data across nodes and regions. They solve real problems: global users, writes beyond what one machine can take, surviving a region outage.

They are not free. Every write coordinates across nodes, so single-write latency is higher, and they cost more to run and to understand. Most teams that think they need this actually need read replicas, better indexes, or partitioning first.

Document: when records are read as a whole#

MongoDB and Firestore store JSON-like documents. They shine when a record is naturally nested and almost always read and written together: a product with its variants, a user profile with settings, a game character with its stats.

Use it for content with varied shape, fast-moving early products, and mobile apps that sync documents directly (Firestore).

Watch out for data that is really relational. If you keep reaching for $lookup or duplicating the same data across documents and fixing it with background jobs, the model is fighting you.

On game backends I worked on, MongoDB fit player-owned data well: a profile, a clan, a habitat are each one document read in one call. The parts that needed ranking or real-time fan-out lived elsewhere, which is the next section.

Key-value: the speed layer#

Redis (and its fork Valkey) keeps data in memory and answers in well under a millisecond. DynamoDB is a managed key-value and document store that scales almost without limit if you design keys around your queries.

Redis is more than a cache. Its data structures solve specific problems directly:

• Strings and hashes for caching and sessions.
• Sorted sets for leaderboards and rankings: insert a score, ask for the top 100 or a player's rank, both fast at any size. This is how I would build any ranking that updates in real time.
• Pub/sub and streams for real-time fan-out, like chat or activity feeds.
• Atomic counters and TTLs for rate limiting and page view counts.

Watch out for treating Redis as the source of truth without thinking about persistence, and for DynamoDB tables designed before the access patterns are known. DynamoDB rewards a single-table design that matches your queries and punishes the ones you didn't plan for.

Wide-column: write throughput at scale#

Cassandra, ScyllaDB and Bigtable are built for enormous, steady write volume: event logs, messaging, IoT, activity histories. You design one table per query, partitioned so each read hits one partition.

The trade-off is flexibility. There are no joins and no ad-hoc queries, and changing an access pattern often means a new table and a backfill. Choose it when the scale is real and the queries are stable.

Search engines: never the source of truth#

Elasticsearch, OpenSearch, Meilisearch and Typesense build inverted indexes for full-text search: relevance scoring, typo tolerance, stemming, faceted filters, geo queries.

On a construction-materials marketplace I worked on, moving listing search from SQL to Elasticsearch made it several times faster and unlocked filtering by location and material type that SQL could not do well. The rule that kept it sane: the relational database stays the source of truth, and the search index is a projection rebuilt from it. If the index breaks, you reindex; you never lose data.

Meilisearch and Typesense are much simpler to run and are often enough for product search on small to mid-size catalogues.

Time-series: metrics and events over time#

TimescaleDB (a Postgres extension), InfluxDB and Prometheus are optimised for data that arrives in time order and is queried by time range: metrics, sensor readings, prices, logs.

They compress well, downsample old data automatically (keep per-second data for a week, per-hour forever) and drop expired data cheaply. Use them when you write constantly, rarely update, and ask "what happened between T1 and T2". If you already run Postgres, TimescaleDB is the lowest-friction start.

Analytical (OLAP): the questions the business asks#

ClickHouse, BigQuery, Snowflake and DuckDB store data by column, so a query that sums one column over a billion rows reads only that column. Questions like "revenue by country by week for the last two years" go from minutes to seconds.

They are poor at the opposite job: many small inserts and updates per second. That is why the usual pattern is to keep the transactional database for the product and copy data into the warehouse for reporting. On an enterprise cost-allocation platform I worked on, operational data lived in PostgreSQL while BigQuery held the historical cost data for analytics and reporting, so heavy reports never touched the database users were working in.

DuckDB deserves a special mention: an embedded analytical database that runs in-process, great for querying Parquet files or exports without running a server.

Graph: when the relationships are the data#

Neo4j, Neptune and Memgraph store nodes and edges and make multi-hop queries cheap: friends of friends, fraud rings, recommendation paths, dependency graphs.

In SQL, each extra hop is another join, and the query gets slow and hard to write. In a graph database, traversal is the native operation. Use it when the main questions are about paths and connections. For a simple follower list, a relational table is still fine.

Vector: similarity for AI features#

Vector databases store embeddings and answer "find the items most similar to this one". They power semantic search, recommendations and retrieval for LLM apps (RAG).

Start with pgvector if you already run Postgres: your embeddings sit next to the data they describe, and you can filter with normal SQL in the same query. Move to a dedicated engine like Qdrant or Pinecone when the number of vectors or the query rate outgrows it.

How to choose#

Work through these questions in order. Each one narrows the field.

1. What are the access patterns? Write down the five most important reads and writes before choosing anything. "Get user by id", "top 100 by score", "full-text search with filters", "sum by week" each point to a different family.
2. How much does correctness matter? Money, inventory and anything legal need transactions and constraints. Analytics and feeds can usually tolerate eventual consistency.
3. What does the data look like? Strongly related records point to relational, self-contained nested records to document, and connections as the main subject to graph. Append-only events in time order point to time-series or wide-column.
4. What is the read/write ratio and scale? Read-heavy loads scale with caches and replicas. Write-heavy loads at very large scale are where wide-column and distributed SQL earn their cost.
5. Who will operate it? A managed service your team understands beats a theoretically better database nobody can debug at 3 a.m.

Most real systems use several#

Choosing "the" database is often the wrong question. Mature systems use polyglot persistence: each workload goes to the store that fits it, around one source of truth. A typical marketplace might look like this:

               ┌──────────────┐
writes ──────▶ │  PostgreSQL  │  source of truth: users, orders, payments
               └──────┬───────┘
                      │ outbox / CDC (Debezium, Kafka, queues)
      ┌───────────────┼─────────────────┬──────────────────┐
      ▼               ▼                 ▼                  ▼
Elasticsearch       Redis           ClickHouse /          S3
product search   cache, sessions,   BigQuery           images, files,
and filters      rate limits,       reporting and      exports
                 leaderboards       analytics

A health app I worked on followed the same idea on AWS: DynamoDB for the core data, DAX and ElastiCache as caching layers in front of it to cut database load, and S3 for files.

Polyglot persistence has a price, and it is worth naming:

• Synchronisation. Every extra store needs a way to stay in sync. Prefer change data capture or the transactional outbox pattern over writing to two databases from application code, which fails halfway sooner or later.
• Consistency. Secondary stores lag behind. Decide where users may see slightly stale data (search results, dashboards) and where they may not (balances, stock).
• Operations. Each database is another thing to back up, monitor, upgrade and secure.

So add stores for a measured reason, keep one clear source of truth, and make every secondary store rebuildable from it.

Cheat sheet#

The best database is the one that matches how your data is read and written, and that your team can run with confidence. Start simple, measure, and let real access patterns, not trends, decide when the next one joins the system.