- Book: Database Playbook
- My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
- Me: xgabriel.com | GitHub
Picture the IoT telemetry team that spends weeks moving multi-TB workloads from MongoDB to ClickHouse, watches a Grafana dashboard drop from 14 seconds to under 200 ms on an hourly aggregate, and confirms the migration was the right call. (Numbers illustrative; the shape of the win is the common one.)
Picture the other team, the one that moved customer profile data from Postgres to MongoDB after reading a blog post about "schema flexibility," and discovered every customer-facing query now needed two round-trips and a manual join in application code. An engineer I know rolled that exact migration back. The bill was paid in developer-weeks and one Friday-night incident.
The difference between those stories isn't which database is "better." It's whether the team asked the five questions below first. ClickHouse is excellent for IoT telemetry and bad for customer profiles. Postgres handles profiles cleanly and stays fine for telemetry up to a few TB. MongoDB shines on shape-flexible documents; cross-entity reporting will eat your weekend. Every "X is faster than Y" benchmark is true for that benchmark and silent about your project.
One independent comparison (sanj.dev, 2026) reports ClickHouse ingestion at 2-3M points/sec versus TimescaleDB at 100k-500k, and compression 10-30x better. The same comparison puts ClickHouse point-lookup latency at 15 ms versus TimescaleDB at 10 ms. Treat these as one data point, not consensus. The five questions:
Question 1: what's the dominant write shape?
The single most important question. Two extreme shapes:
Append-only timestamped events. A new row arrives every second (or every microsecond), no row is ever updated, and old rows are aged out by retention policy. IoT, application logs, financial ticks, observability metrics. Time is part of the primary key.
Mutable rows keyed by entity. A user updates their profile, an order moves through statuses, an inventory count adjusts. Rows have lifetimes measured in months or years and change tens of times. The primary key is an entity id, not a timestamp.
If your write shape is append-only and high-volume, time-series stores (TimescaleDB, ClickHouse, QuestDB, InfluxDB) are designed exactly for this. They use columnar storage, time-based partitioning, and aggressive compression that assumes rows are immutable. ClickHouse in particular achieves the compression and ingestion ratios it does by treating updates as a hostile case.
If your write shape is mutable, you want a row-store. Postgres and MariaDB are the boring correct answer for most workloads. They handle updates as a first-class operation, support transactions across rows, and won't punish you with a multi-second OPTIMIZE TABLE when an HR manager fixes a typo in a name field.
There's a middle case: mutable but mostly insert. Order events, audit trails, append-with-occasional-correction. As a rough rule of thumb, Postgres handles this until you cross the low-TB range of hot data, at which point TimescaleDB (Postgres-compatible, hypertable-partitioned) gives you append-shape performance without a migration.
Question 2: what's the read pattern?
Three rough buckets:
Range scans over time. "Average CPU per host over 24 hours, bucketed by minute." Time-series engines crush this. Columnar layout reads only the columns you aggregate, partition pruning skips data outside the range, pre-aggregations give sub-second response on million-point buckets.
Key lookups. "Get the user with id X." Row or KV stores: DynamoDB, Redis, Postgres with a btree index. ClickHouse is bad at this. Single-row lookups pay the columnar tax without any wins. Per the comparison cited above, the 15 ms vs 10 ms gap is real, and on a 200-lookup endpoint it's 1 second of latency you didn't budget.
Full-text or graph traversal. Postgres with pg_trgm or tsvector for moderate full-text. Elasticsearch or OpenSearch for serious search. Neo4j or Memgraph for traversal. Don't ask MongoDB's $graphLookup to scale: it tends to struggle at large dataset sizes — every team I've seen rely on it for cross-collection traversal at scale ended up moving the workload to a graph engine or denormalising into a single collection.
The trap: most projects have more than one read pattern. A telemetry platform needs range scans for dashboards and point lookups for "latest reading from this sensor." The hybrid setups described in the TimescaleDB/ClickHouse comparison split the workload: Postgres for hot operational data, with a ClickHouse replica for analytics. They exist because real systems can't pick one bucket.
Question 3: what are the consistency requirements?
Money: ACID, no exceptions. A relational database with transactions. Postgres, MariaDB, MySQL with InnoDB. The consistency story matters more than the analytics story. You can replicate to a warehouse later.
User-generated content where eventual consistency is fine: most document stores work. MongoDB, DynamoDB. The "I posted a comment and didn't see it for 200 ms" experience is acceptable.
Telemetry: you can usually tolerate the occasional dropped row, and "exactly-once" insert is a research project. ClickHouse and most time-series stores are eventually consistent on replicas; you tune for throughput, not correctness, and you handle dedup at ingest.
The honest version of this question: what's the cost of one wrong row? If the answer is "lawsuit" or "rollback the deploy," you need transactions. If the answer is "a noisy 5-minute bucket on a dashboard," you don't.
Question 4: what's the cardinality of unique series?
This question is specific to time-series and it's where teams get blindsided.
A "series" in time-series terminology is a unique combination of tags or labels. {host="web-01", region="us-east", service="api"} is one series; changing host to web-02 is a different series. Per CrateDB's comparison, tag-indexed time-series engines like InfluxDB degrade once active series climb into the millions, with the practical wall arriving earlier on modest hardware.
If you're tagging by user id, request id, or any high-cardinality dimension, you have a cardinality problem. ClickHouse handles billions of distinct series fine because tags are just columns. TimescaleDB handles tens of millions before you start tuning chunk intervals. Tag-indexed engines hit their wall well before that — your safe ceiling depends on hardware and tag layout, not a single magic number.
Easy check: estimate cardinality as roughly the product of distinct values across all tag dimensions. If users × endpoints × regions is above 10 million and one of those is "users", you do not want a tag-indexed time-series engine. You want a columnar store like ClickHouse, or a wide-column architecture, or to drop the user-id tag and join against a row store at query time.
Question 5: what's your operational headcount?
The question nobody asks until production wakes them up.
As a rough industry rule of thumb: managed Postgres on RDS or Cloud SQL runs at about 0.1 FTE. A self-hosted ClickHouse cluster with proper backup, monitoring, and version management lands at 0.5 to 1 FTE. Self-hosted Cassandra has historically been closer to 2. DynamoDB costs roughly nothing operationally, but it costs you the freedom to ever JOIN. Calibrate against your own ops surface; these are SRE-forum estimates, not measurements.
For dollar costs, one third-party pricing comparison (illustrative, sourced ~2024; confirm current vendor pricing before you act on it) puts 10M reads / 2M writes / 50 GB at roughly: DynamoDB on-demand ~$105/mo, MongoDB Atlas M30 ~$330, DynamoDB reserved ~$45. Postgres pricing was not in that source — for managed Postgres, expect a comparable 50 GB instance on RDS or Cloud SQL to land in the same low-to-mid hundreds, depending on instance class. Add engineer-weeks and the picture shifts.
The pattern that's killed more startups than wrong technology: a 4-person team adopts ClickHouse because it's "right" for telemetry and discovers the on-call rotation is one of the four, full-time.
If you're a 3-person team building anything that doesn't demonstrably break Postgres, the answer is Postgres. With JSONB. With TimescaleDB if you have time-series. Postgres isn't best at any one thing. One team member can run it.
A small decision script
Here's the decision flow as runnable pseudocode. Change the inputs to match your project. Read the output. Argue with it before you migrate.
from dataclasses import dataclass
from typing import Literal
@dataclass
class Workload:
write_shape: Literal["append_only", "mutable", "mixed"]
read_pattern: Literal["range", "key", "fulltext", "mixed"]
consistency: Literal["acid", "eventual"]
series_cardinality: int # only meaningful for time-series
ops_headcount: float # FTE you can dedicate to the DB
def recommend(w: Workload) -> str:
# Branch 1: ACID money/orders/accounts. Stop trying to be clever.
if w.consistency == "acid":
if w.write_shape == "append_only" and w.ops_headcount >= 0.5:
return "Postgres + TimescaleDB extension"
return "Postgres (managed)"
# Branch 2: append-only telemetry. Cardinality and ops budget pick the engine.
if w.write_shape == "append_only":
if w.series_cardinality > 10_000_000:
return "ClickHouse" if w.ops_headcount >= 1 else "BigQuery"
if w.ops_headcount < 0.5:
return "TimescaleDB on managed Postgres"
return "ClickHouse" if w.read_pattern == "range" else "TimescaleDB"
# Branch 3: mutable rows. Read pattern decides the row store.
if w.write_shape == "mutable":
if w.read_pattern == "key" and w.ops_headcount < 0.2:
return "DynamoDB"
if w.read_pattern == "fulltext":
return "Postgres + tsvector (or OpenSearch if heavy)"
return "Postgres"
# Branch 4: mixed write shape. Default to Postgres unless you have ops budget.
if w.ops_headcount < 1:
return "Postgres + TimescaleDB extension"
return "Postgres for hot data, ClickHouse replica for analytics"
print(recommend(Workload(
write_shape="append_only",
read_pattern="range",
consistency="eventual",
series_cardinality=2_000_000,
ops_headcount=0.3,
)))
# -> TimescaleDB on managed Postgres
Three example app shapes through that function:
IoT telemetry, 50k devices, 1 reading/sec, dashboards over hours-to-weeks. append_only, range, eventual, ~50k cardinality, 0.5 FTE. Output: ClickHouse. The cardinality is comfortable, the read pattern is range-heavy, and the ingestion rate (50k points/sec) sits in ClickHouse's sweet spot. TimescaleDB would handle it too, but expect noticeably higher storage cost — the same independent comparison cited earlier puts ClickHouse compression 10-30x ahead.
E-commerce: customers, orders, products, returns, with admin dashboards. mutable, mixed (key for product page, range for "orders this week"), acid, cardinality N/A, 0.5 FTE. Output: Postgres. Everything you need is in Postgres: JSONB for product attributes, partial indexes for hot queries, materialized views for the dashboards. Don't move to MongoDB, don't move to DynamoDB. You are not at the scale where the migration pays off. You may never be.
Analytics warehouse for a 200-person SaaS, daily ETL from app DB plus event tracking. append_only from the events side, mixed overall, eventual, low-to-moderate cardinality, 0.3 FTE. Output: managed BigQuery, Snowflake, or ClickHouse Cloud. The 0.3 FTE rules out self-hosted. Pick by pricing model: BigQuery for spiky workloads, Snowflake for steady ones, ClickHouse Cloud if your SQL team is already used to ClickHouse syntax.
The pattern under all five questions
The five questions are really one meta-question: does my workload fit the assumptions the database was designed under? Time-series engines assume immutable, time-keyed data and reward you for it; punish them with updates and they crawl. Relational engines assume normalized rows and reward you with consistency; punish them with 50 TB of telemetry and they'll need babysitting.
Every clean migration started by asking the five questions and getting a different answer than the team's gut. Every horror story skipped that step.
If this was useful
The Database Playbook covers each store family (relational, document, time-series, KV, search) with the workload signatures, the migration cost, and the operational quirks that don't make it into the marketing pages. If you're picking storage for a new project this quarter, it's the short read that saves you from the migration you'll otherwise do twelve months in.
Top comments (0)