DatabasesArchitectureNoSQL

Database engines: when to use which

By Harry Osmar Sitohang·Jun 2026·9 min

Every app eventually stores data, and the first question is almost always the same: which database? The honest answer isn't "the fastest" or "the most popular" — it's the one that makes the question you ask most often a single, cheap lookup.

Picture a warehouse. You can organise it two ways. Way one: put everything a single customer needs into one labelled box — their order, address, payment — and shelve the whole box together. To serve that customer, grab their one box. Way two: keep separate shelves for orders, customers, and products, and follow string between them to assemble an answer.

Almost every "SQL vs NoSQL" argument is really about that: how much data you clump into one box, and how often your questions have to cross between boxes. Let's tour the six engine families — each one's read/write flow, when to reach for it, and the real production system that proves it.

One flow, all six families: fall down the questions and the first 'yes' picks your engine — fall through to the bottom and relational is the sensible default.

Key-value — the coat check

You hand over a coat, get a numbered ticket. Later, show the ticket, get the exact coat back. The coat check doesn't know or care what's in your pockets — blisteringly fast, trivially scalable, and useless if you ever need to ask "which coats are blue?".

The coat check: hand over a coat, get a numbered ticket; the ticket fetches that exact coat — but you can't ask 'which coats are blue?'.

The client hashes the key to one node + N replicas. No master — any node can coordinate.

When to use it: caches, sessions, shopping carts, user preferences — anything fetched by key. Netflix built EVCache (an in-memory cache on top of memcached) as its caching tier: the data is ephemeral, has a TTL, and can be evicted at any time. Amazon built Dynamo precisely because the relational pattern was a poor fit for high-availability services like carts and sessions — which are only ever fetched by key — and Dynamo deliberately trades strong consistency for availability.

Document — labelled folders

Each customer gets a manila folder holding a structured form — name, a list of orders, nested line-items. You open any folder by its label and read or edit specific fields without unpacking the whole thing.

Labelled folders: open any folder by its label and read or edit specific fields inside — including the nested list of orders.

A router sends each document to one shard via the shard key; writes hit the primary, the oplog replicates to secondaries.

Unlike key-value, every field can be indexed and queried, and you can update part of a document. The limit: horizontal scale forces you to pick a shard key up front, and the model strains when your data is actually highly connected.

When to use it: self-contained tree-shaped records — a product, a CMS page, a profile, an order.

Wide-column — a filing cabinet of sparse rows

Picture one gigantic spreadsheet where each row is keyed by something you always look up by — a user, a channel, a sensor — and each row can have millions of columns but fills in only the few it needs. You never ask "show me every row where column X is blue" across the whole sheet; you ask "give me the columns for this row key, in order". That's the price; the reward is that the sheet splits across thousands of machines and absorbs a firehose of writes without flinching.

A filing cabinet of sparse rows: look up by row key; each row is wildly wide but fills only a few cells, and the cabinet splits across many machines.

Writes append to a commit log + memtable (RAM) then flush to immutable SSTables; reads merge the memtable + SSTables via bloom filters.

The write path is LSM-tree based: a write hits a memtable (RAM, sorted) + commit log (append) and is acked immediately with no read-before-write, then flushed to immutable SSTables that are periodically compacted. That's why it's write-optimised. Deletes write tombstones, so delete-heavy and hot-partition loads slowly degrade.

Discord moved its message store three times: MongoDB → Cassandra (2017) → ScyllaDB (2022).

— Discord — How Discord Stores Trillions of Messages

The model is textbook query-driven design: messages are partitioned by channel_id + a time bucket, clustered by Snowflake IDs, with replication factor 3 — the partition key is the access pattern. Discord left Cassandra for hot partitions, JVM garbage-collection pauses, and compaction maintenance; ScyllaDB (a C++ rewrite of Cassandra, no garbage collector, a shard-per-core architecture) cut the cluster from 177 nodes to 72 and dropped read p99 from 40–125 ms to 15 ms. Note: they didn't change the model, only the engine — most of the pain was runtime.

When to use it: a firehose of writes keyed by entity + time (messages, events, metrics, feeds).

Columnar (OLAP) — columns ripped apart

Picture every spreadsheet column ripped apart into separate lists — every "price" in one place, every "country" in another. Terrible for "give me one whole row", amazing for "average price across 2 billion sales, grouped by country".

Columns ripped apart: each column is stored on its own, so a query lifts out only the column it needs — here it rips out 'price' and aggregates it (SUM = 42.49).

A query reads only the columns it references, then aggregates them vectorised across nodes (MPP).

Each column is stored and compressed separately (same-type values adjacent compress 10×+). A query touches only the columns it references, in tight vectorised loops, split across many nodes (MPP). Great for SUM/GROUP BY over billions of rows; wrong for "fetch one whole row".

When to use it: scan + aggregate millions of rows — dashboards, BI, ad-hoc analysis whose queries you don't know up front.

Columnar engines power analytics dashboards — aggregating billions of rows (here GROUP BY country, AVG price) into a few fast charts.

Graph — the relationship map

Instead of self-contained boxes, you store the string between things as a first-class object. "Alice → friend → Bob → works-at → Acme" is stored as real links you can walk one step at a time. Questions like "friends-of-friends who work where my friends work" — which make aggregate stores cry — are exactly what graphs are built for.

The relationship map: people and links are first-class, so 'friends-of-friends' is just walking the edges one hop at a time.

An index finds the start node once; after that each hop is a stored pointer — O(1) per hop.

Nodes and relationships are first-class, physically stored citizens (index-free adjacency), so traversing one relationship is a pointer hop, not a join. Cost is proportional to the subgraph you touch, not the total dataset size — the technical basis for the claim that as the number of "hops" grows, aggregate stores slow down while graphs stay flat.

Relational — linked tables with rules

Normalised tables you assemble with JOINs, guarded by integrity rules, committed transactionally. Flexible for moderate relationships, strong on correctness.

Linked tables with rules: normalised tables joined by keys, kept correct by integrity rules and all-or-nothing (ACID) commits.

The optimizer builds a plan; indexes + JOINs assemble rows; the WAL makes commits durable + atomic (ACID).

When to use it: cross-entity joins + aggregation that must stay correct. And this isn't a legacy choice — Uber deliberately did not build NoSQL. Its Docstore/Schemaless is built on MySQL, wrapping every operation in a MySQL transaction replicated via Raft for ACID guarantees. They evaluated Cassandra and rejected it — its eventual consistency hurt developer productivity — and chose a CP (consistency-over-availability) design instead. The lesson: "NoSQL at scale" is not automatic.

Time-series — points on a timeline

A heart-rate monitor, a stock ticker, a server's CPU graph: the same shape of data — a value stamped with a time, appended forever, read back as ranges and roll-ups. It's append-mostly, time-ordered, and you almost never rewrite the past.

Time-series: timestamped points stream in and append on the right; old points roll off (retention); you read them back downsampled — AVG/MAX per window.

The twist: time-series isn't a seventh storage model — it's a specialization of the families above for this one workload. Under the hood:

TimescaleDB is a PostgreSQL extension — relational plus columnar compression and automatic time-partitioning.
InfluxDB 3 stores Apache Arrow / Parquet, so it's columnar; older InfluxDB used a TSM (LSM-tree) engine, much like wide-column.
Prometheus ships its own local LSM-style store, and the classic pattern of partitioning by series + time is exactly the wide-column firehose.

So reach for a purpose-built TSDB when you want the time ergonomics out of the box — retention policies, downsampling, gap-filling, time-bucket functions. Reach for wide-column or columnar directly when you'd rather own the model and already run one.

Need OLTP + OLAP? Run both

Most companies need both, and forcing one tool to do both is the classic mistake. The pattern is standard:

Make the OLTP store (key-value / document / wide-column / relational) the system of record, then stream its data into a columnar copy for analytics — via CDC or a batch export. Append a change log, and compute "latest" at query time.

Don't

Don't run a "GROUP BY across everything" analytical query straight against a wide-column store keyed by channel_id. That forces a full-cluster scan and can knock over the nodes serving live traffic. And don't make a columnar store update rows one at a time — it hates that.

The one-line rule: if a human or a dashboard is asking, send it to columnar; if your application is asking "give me this one thing" at scale, send it to OLTP. When both are true, run both and pipe one into the other.

Sources