Pick the Database That Matches the Workload, Not the One on Your Resume

There is a separate question from the one about relationship shape. Even after you decide your data is mostly relational, or mostly aggregates, or mostly a graph, you still have to answer: what does the workload look like? Same data can be served by very different engines depending on how it is read, how fast it is written, and how long it is kept.

A short tour of the workload buckets that actually matter.

OLTP. Many small reads and writes by primary key or narrow indexes. Latency in single-digit milliseconds. Strong consistency inside a row, transactions when needed. Postgres and MySQL are the boring, correct answer for almost every line-of-business system. They do not lose data, the tooling is mature, and you can run them for a decade without surprises.

OLAP. Few queries, but each one scans hundreds of millions of rows over a handful of columns, aggregates, and returns in seconds. Running this on your OLTP database competes with real users for IO. ClickHouse, Snowflake, BigQuery, DuckDB. Column-oriented, vectorized, optimized for scans. Data flows in from OLTP via CDC or batch.

Key value. You only ever look the thing up by an exact key. Sessions, feature flags, rate limit counters, idempotency keys. Redis for microseconds when you can lose recent state. DynamoDB for managed, durable, and predictable at scale. In Postgres these become hot-table problems the day you grow.

Document. The unit of work is an aggregate you read and write whole. Mongo if the schema really will drift. Postgres with jsonb if you want the document model and SQL when you eventually need it. Pick the second one more often than you think.

Time series. Append-only, very high write rate, queries by time window with aggregations and downsampling, automatic retention. Influx, Timescale, Prometheus. These engines compress time-ordered data 10 to 100 times better than a generic row store. Keeping five years of per-second metrics in Postgres turns your storage bill into a meeting.

Columnar files. Parquet on object storage with Trino or DuckDB on top. The right answer for cold analytics and data lake patterns where you want compute and storage to scale independently.

Search. Elasticsearch, OpenSearch, or a vector index. The right answer when the query is "find everything matching this fuzzy phrase, ranked." Postgres full-text search works for a while. Switch when ranking quality matters more than operational simplicity.

The failure mode I keep watching: a team picks one engine because it is what they know, then bolts every workload onto it as the system grows. The metrics workload ruins the OLTP database. The search workload ruins it again. The analytics workload finishes the job. You did not need one database. You needed three, talking through CDC, each doing the job it is built for.

Map the workload first. The engine names are just labels.