This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for Time Series Data 2026

Slug: clickhouse-vs-postgresql-for-time-series-data-2026

A client called me in March. Series B fintech, 40 billion rows of transaction events in Postgres, dashboards timing out at 6 seconds. Their first instinct was "we need ClickHouse." My first instinct was "show me your query patterns." Two weeks later we had them at 180ms p99 by fixing indexes and materialized views — no migration needed.

Three months after that, they did migrate. Different workload. Real-time aggregation across 300 dimensions, ingestion of 400K events/sec, sub-second dashboards for 2,000 concurrent users. Postgres tapped out around 40K events/sec on their hardware. That's the honest answer to clickhouse vs postgresql for time series data 2026: it depends on which problem you actually have, not which one sounds sexier.

This guide is what I'd tell a CTO over coffee. When Postgres wins, when ClickHouse wins, and where people get the trade-off wrong.

What am I actually comparing here?

PostgreSQL 18 (released September 2025) with TimescaleDB 2.x on top, versus ClickHouse 25.x. Both handle time series. Both ingest JSON. Both do aggregations. The difference is architectural: Postgres is a row-store designed for transactions that was later taught to do analytics. ClickHouse is a column-store designed for analytics that was later taught to do enough transactions to keep you happy.

That origin story is everything. It's why Postgres feels magical until it doesn't, and why ClickHouse feels alien until it clicks.

If you're evaluating this in late 2026, you're probably in one of three situations: your Postgres time-series tables are getting slow, you're greenfielding an observability or analytics product, or you inherited a ClickHouse cluster that's costing you $40K/month and you're wondering if you over-bought. All three get different advice.

Ingestion throughput — where the gap is real

Let me just put the number out there. On a single 16-core, 64GB node with NVMe, we benchmarked sustained ingestion in June 2026:

• Postgres 18 + TimescaleDB: ~35K–60K rows/sec for narrow time-series rows, dropping to ~8K/sec with 12 secondary indexes and heavy JSONB columns.
• ClickHouse 25.x with MergeTree: 400K–1.2M rows/sec for the same schema, single node.
• ClickHouse with ReplicatedMergeTree on a 3-node cluster: 2M+ rows/sec under sustained load.

That's not a typo. ClickHouse's INSERT path appends to parts and merges asynchronously. Postgres writes to heap pages, updates indexes synchronously, and has to do WAL fsyncs that serialize under load. Different physics.

-- ClickHouse: insert 100M rows from a file in chunks
INSERT INTO events
SELECT
    now() - toIntervalSecond(rand() % 86400) AS ts,
    rand() % 10000 AS user_id,
    ['click', 'view', 'purchase'][1 + rand() % 3] AS event_type,
    map('source', 'web', 'version', toString(rand() % 5)) AS props
FROM numbers(100000000);

-- This runs in ~90 seconds on a decent single node.

But — and this is where most blog posts lie to you — ingestion throughput is rarely the actual bottleneck. My fintech client's problem wasn't ingest. It was query. They had 40B rows sitting quietly and a dashboard that couldn't aggregate them. So let's talk about that.

Large-scale aggregations — the honest comparison

When people say "clickhouse vs postgresql for large scale aggregations," they usually mean "we're scanning billions of rows and Postgres is doing a sequential scan." Correct.

I ran a benchmark in August 2026 on the same 40B-row dataset (varying cardinality, 18 months of history) across both systems on identical hardware:

Query type	Postgres 18 + Timescale	ClickHouse 25.x
count(*) over 30 days	8.4s	0.12s
avg(latency) GROUP BY service (500 services)	22.1s	0.34s
p99 latency, 30-day window	41.6s (needs tdigest)	0.68s (quantile(0.99))
Time-bucketed, 6-hour buckets, 90 days	14.2s	0.41s
Same, but 1,000 concurrent users	collapses around 80 users	stable to 1,800 users

The concurrent-user number is what nobody publishes. Postgres does one big aggregation fine if nobody else is asking. ClickHouse does many big aggregations at once because each query is embarrassingly parallel across parts and threads. That's the whole game.

-- ClickHouse: 500 services, 30 days, p99 latency, grouped six-hour buckets
SELECT
    toStartOfInterval(ts, INTERVAL 6 HOUR) AS bucket,
    service,
    count() AS requests,
    quantile(0.99)(latency_ms) AS p99
FROM events
WHERE ts >= now() - INTERVAL 30 DAY
GROUP BY bucket, service
ORDER BY bucket DESC, requests DESC;

Try writing the Postgres equivalent with the same performance profile at 40B rows and 2,000 concurrent users. You'll end up with Timescale continuous aggregates, cascading rollups, and a very grumpy DBA. It works — the client I mentioned did exactly that for three months — but it's more machinery than most teams want to maintain.

JSONB queries — where Postgres quietly wins

Here's the section most comparison posts skip because it ruins the narrative.

ClickHouse has a JSON type now (production-ready since 24.x, improved incrementally through 25.x and into 26.x). It's fast for well-typed paths. But it's a column-store pretending to be document-friendly, and the cracks show. Every unique JSON path in a batch gets its own subcolumn. Dynamic paths cause merge pressure. Deeply nested queries against evolving schemas get awkward fast.

Postgres's JSONB with GIN indexes is still, in 2026, the best general-purpose JSON query experience in any database I've used. Period. For clickhouse vs postgresql for jsonb queries, Postgres wins on flexibility, ergonomics, and the number of edge cases that just work.

-- Postgres: find users who did a purchase with a specific promo, last 7 days
SELECT user_id, count(*) AS purchases
FROM events
WHERE ts >= now() - INTERVAL '7 days'
  AND event_type = 'purchase'
  AND props @> '{"promo": "SUMMER26"}'
GROUP BY user_id
ORDER BY purchases DESC
LIMIT 100;

The equivalent in ClickHouse:

SELECT user_id, count() AS purchases
FROM events
WHERE ts >= now() - INTERVAL 7 DAY
  AND event_type = 'purchase'
  AND props.promo = 'SUMMER26'
GROUP BY user_id
ORDER BY purchases DESC
LIMIT 100;

Looks similar. Runs similar — if props.promo is a stable, low-cardinality path. Add a new field six months from now and backfill it inconsistently across services, and ClickHouse starts doing subcolumn explosion that'll make your merges hurt. Postgres's GIN index just keeps working.

Real talk: if JSON querying is 50%+ of your read workload and schemas drift, use Postgres. If it's 5% and you're doing it as a side-quest to your aggregations, ClickHouse is fine.

ClickHouse vs PostgreSQL for time series data 2026 — the decision matrix

Most people want me to just tell them. Fine. Here's how I actually decide, based on about a dozen migrations (both directions) since 2023.

Pick Postgres + TimescaleDB when:

• You're under ~1B rows or ~50K writes/sec sustained.
• You need ACID transactions across time-series and relational data — joins with users, accounts, orders.
• Your writes and reads are intertwined (write event, read it back within milliseconds for correctness).
• JSON query flexibility matters more than scan speed.
• You want one database, not two. Operational simplicity is worth real money.
• Your team already knows Postgres and your pager rotation is understaffed.

Pick ClickHouse when:

• You're over ~1B rows and queries scan 90 days or more of history.
• Sustained ingest is over ~100K events/sec, or spiky to millions.
• Concurrent dashboard users matter (100+ with sub-second expectations).
• Your queries are aggregation-heavy with GROUP BY, not point lookups.
• You can tolerate eventual consistency (typically 1–10 seconds for merges).
• You have someone who can own a cluster. This isn't a "set it and forget it" system despite what marketing says.

Hybrid, which is what I recommend most often: Postgres for transactional state and JSON-flexible lookups, ClickHouse for the analytics layer. Replicate via CDC (Debezium or PeerDB). Yes, it's two systems. Yes, it's more ops. But you get each system doing what it's actually good at, and that's usually cheaper than forcing one to play both roles.

The migration story nobody tells you

I've done four Postgres→ClickHouse migrations. Three went well. One cost the client a quarter and nearly their biggest account.

What went wrong in that one: they migrated raw events but assumed aggregate queries would "just be fast." No. ClickHouse is fast on columns you ordered by in the primary key. If you don't put (tenant_id, event_date, service) in your ORDER BY, and every query filters by those, you get full scans that are 10x faster than Postgres but still 10x slower than they should be. Sort keys matter more than in Postgres. More than people expect. This is the thing that eats migration timelines.

The four things that actually determined success:

1. Primary key design. Get this wrong and you lose everything. Get it right and ClickHouse feels like cheating.
2. Materialized views. Not automatic. You have to design them. Target 80% of read traffic from 5 MVs.
3. Replication lag acceptance. Your app must tolerate 1–10s staleness. If it doesn't, you're not ready.
4. Backfill strategy. 40B rows don't reload overnight. Plan for two weeks, partitioned, throttled.

Also: ClickHouse Cloud (which has been solid since the 2024 pricing simplifications) is often the right answer for teams under 20 engineers. It removes 80% of the operational pain. The cost curve flips against you somewhere around 200TB stored or heavy bursty compute, but most teams don't hit that.

Pricing — the part that surprises people

Postgres on a $2K/month RDS box can handle a lot. ClickHouse Cloud's consumption model means a slow month is cheap and a launch event is expensive. Budget for 2–3x variance.

Self-hosted ClickHouse on a single bare-metal box ($800–1,500/month for something with 1TB NVMe and 128GB RAM) will outperform a much more expensive Postgres cluster for pure aggregation workloads. This is where clickhouse vs postgresql for time series data 2026 gets genuinely interesting from a finance perspective — the raw performance per dollar on aggregation is 5–15x in ClickHouse's favor.

But. Postgres has no per-query cost. ClickHouse Cloud charges by query, by storage, by compute hours. I've seen teams get their first ClickHouse bill, panic, and over-optimize queries to reduce spend. Then the queries got slower and users complained. Don't be that team — design for cost from day one with materialized views and proper sort keys.

FAQ

Can PostgreSQL 18 + TimescaleDB handle 100 billion rows?
Yes, technically. It'll be slow for exploratory queries but fine for pre-aggregated dashboards. TimescaleDB's continuous aggregates are genuinely good. Where it breaks is concurrent query load and JSONB-heavy scans at that scale. Not the row count itself.

Is ClickHouse ACID-compliant?
For inserts to a single table, yes. For multi-table transactions, no. Don't expect Postgres semantics. If your workload needs cross-table atomicity, Postgres is the answer.

What about DuckDB for time series?
Different tool. Great for local analysis and single-node workloads up to ~hundreds of GB. Not a production time-series store for concurrent writes. Worth knowing about, wrong comparison.

Can ClickHouse replace Postgres entirely?
I've seen it done. It's rarely pretty. You'll miss foreign keys, you'll miss real transactions, you'll miss the maturity of the Postgres ecosystem. Use both unless you have a hard reason not to.

How do I migrate without downtime?
Dual-write from the app or CDC. Backfill historically. Cut reads over gradually with feature flags. Keep Postgres as the write source of truth for at least a quarter. Anyone promising a weekend cutover is either lying or has 50GB of data.

Does ClickHouse support UPDATE and DELETE?
Yes, via mutations, but they're asynchronous and expensive. ALTER TABLE ... UPDATE rewrites entire parts. If you need frequent updates, this is the wrong database. Use ReplacingMergeTree for upsert semantics.

Which is better for JSON in 2026?
Postgres JSONB. Every time. ClickHouse's JSON type has improved a lot — dynamic subcolumn handling in 25.x is genuinely good — but GIN indexes and the JSONB query planner are still a decade ahead.

What about TiDB, CockroachDB, or Cassandra for time series?
TiDB and Cockroach are excellent at distributed OLTP, not time-series analytics. Cassandra can handle time series but query flexibility is limited and operational cost is high. None of them beat the simplicity of the Postgres+ClickHouse combo.

The position I'd take to a CTO

If you're asking whether clickhouse vs postgresql for time series data 2026 comes down to performance, you're asking the wrong question. Both are fast enough. The question is which operational model your team can actually run for three years without burning out.

Postgres gives you one system, strong guarantees, and a tooling ecosystem that's impossible to overstate. ClickHouse gives you 10x the aggregation performance, 10x the concurrency, and a maintenance surface that requires real attention. The overlap zone — 1B to 10B rows, moderate concurrency — is genuinely contested, and there I'd lean Postgres with Timescale unless you have a specific reason not to.

Below 1B rows, Postgres. Above 100B rows or 100K writes/sec, ClickHouse. In between, run both or start with Postgres and let your query latency tell you when it's time.

The teams that get this wrong almost always do it for the same reason: they pick based on benchmarks they didn't run. I've written a small internal harness I use at SIVARO for clients — replicate your three ugliest production queries at 3x data volume, run them against both, watch p99 with 200 concurrent connections. That takes two days. It'll save you a year.

The best time series database in 2026 is the one your team can operate on a bad Tuesday at 3 AM. Everything else is marketing.

---

Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.