This article was originally published at sivaro.in
ClickHouse vs PostgreSQL for Real Time Analytics 2026
Most teams pick Postgres for analytics because it's already running. That's the whole reason. It's sitting there, the app writes to it, the ORM knows it, and nobody wants a second database in the stack.
I get it. I've done it too.
Then the dashboard query hits 40 million rows and takes 90 seconds, and suddenly you're explaining to a VP why "real time" means "check back after lunch." That's the moment the ClickHouse conversation starts. And in 2026, that conversation is louder than ever — Postgres 18 shipped parallel query improvements and better columnar-ish execution paths, while ClickHouse keeps eating the OLAP world from the inside out.
So here's the honest comparison. When I say clickhouse vs postgresql for real time analytics 2026, I mean: which one do you actually run for your analytics workload, and what breaks if you pick wrong.
This isn't a feature checklist. It's a decision guide built from systems I've shipped — including one pipeline at SIVARO handling 200K events/sec where we ran both, side by side, for eight months. I'll tell you what won and where Postgres genuinely held its ground.
The core difference nobody explains properly
Postgres is a row store. It writes a row, it reads a row, it keeps that row's columns together on disk. That's perfect for OLTP — "give me user 4471's latest order" touches one page.
ClickHouse is a column store with a different religion. It writes columns together, compresses the hell out of them, and reads only the columns your query touches. For "average latency by region over the last 30 days," that's a 50x to 500x reduction in bytes read.
Most people think this is a query optimizer difference. It's not. It's a physics difference. You can't tune Postgres into being a column store.
But — and this is the part the ClickHouse evangelists skip — column storage is terrible at point updates. Updating a single row in ClickHouse means rewriting a part. If your workload is "insert one order, update its status, delete it when refunded," ClickHouse will fight you.
The real question isn't "which is faster." It's "what shape is my data access."
Where Postgres still wins (and it's not a consolation prize)
I need to kill a myth: Postgres is not slow at analytics. It's slow at large-scale analytics on wide tables with high concurrency, which is a narrower problem than Twitter would have you believe.
My rule of thumb, stress-tested across maybe 20 client deployments:
Postgres handles real time analytics fine up to roughly 50–100 million rows, if you do three things:
- Partition your big tables by time
- Build the right indexes (BRIN on timestamps is criminally underused)
- Use
pg_stat_statementsand actually read it
Here's what a decent Postgres analytical query looks like after partitioning:
-- Partitioned events table, monthly partitions
CREATE TABLE events (
id BIGSERIAL,
event_time TIMESTAMPTZ NOT NULL,
user_id BIGINT,
event_type TEXT,
latency_ms INTEGER
) PARTITION BY RANGE (event_time);
CREATE INDEX ON events USING BRIN (event_time);
-- Query that stays sub-2s on ~80M rows
SELECT
date_trunc('hour', event_time) AS hr,
event_type,
percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95
FROM events
WHERE event_time >= now() - interval '7 days'
GROUP BY hr, event_type
ORDER BY hr DESC;
That runs in under 2 seconds on a beefy instance at 80M rows. I've watched it happen. Postgres 18's improved parallel hash aggregate made this meaningfully faster than the 16 days.
And Postgres gives you things ClickHouse can't easily: foreign keys, transactions across tables, UPDATE ... WHERE that doesn't require a PhD, and a JSONB story that's genuinely good for semi-structured data.
If your analytics is "operational analytics" — dashboards on fresh transactional data, small joins, moderate row counts — Postgres is the right answer. Full stop.
Where ClickHouse wins, brutally
Now the other side. I first got serious about ClickHouse in 2023 when a client's log pipeline — 4 billion rows, Elasticsearch chugging — needed to answer a simple question: "show me the p99 latency for checkout across services, last 24 hours, sliced by deployment version."
In Elasticsearch: 20+ seconds, sometimes timeout.
In ClickHouse: 400ms. Same hardware class. I rewrote the ingestion path that weekend.
That's the clickhouse vs postgresql for log analysis story in one anecdote. Logs are append-only, high-cardinality, and queried in aggregations over time ranges. That's ClickHouse's home turf.
Look at what a log analytics query looks like:
CREATE TABLE logs (
ts DateTime64(3),
service LowCardinality(String),
level LowCardinality(String),
trace_id String,
message String,
duration_ms UInt32
) ENGINE = MergeTree()
PARTITION BY toYYYYMMDD(ts)
ORDER BY (service, level, ts)
TTL ts + INTERVAL 30 DAY;
-- p99 by service over last 24h, with version slicing
SELECT
service,
quantile(0.99)(duration_ms) AS p99,
count() AS requests
FROM logs
WHERE ts >= now() - INTERVAL 24 HOUR
AND level = 'error'
GROUP BY service
ORDER BY p99 DESC
LIMIT 20;
LowCardinality(String) alone compresses service names to dictionary codes. The ORDER BY (service, level, ts) means ClickHouse can skip entire data blocks for queries filtering on service. That's why it's fast — not magic, just data layout matching access pattern.
The clickhouse vs postgresql for large datasets 2026 comparison really lands here. At a billion rows, Postgres needs serious partitioning discipline and hardware. ClickHouse just... gobbles it. 1 billion rows on a single mid-tier node is a Tuesday for ClickHouse. For Postgres, it's a project.
The 2026 twist: ClickHouse got more Postgres-shaped, Postgres got more analytical
Two things happened in the last 18 months that complicate the old advice.
ClickHouse added more transactional-ish features. Lightweight deletes are now genuinely usable (they were awful in 2022). ReplacingMergeTree handles upsert patterns without the old pain. And the Postgres-compatible wire protocol has gotten real — you can point some tools at ClickHouse and they just work. I've had clients move reporting workloads off Postgres with almost no application changes.
Postgres got columnar help. Postgres 18's parallel query improvements and the continued maturity of extensions like pg_analytics (which embeds DuckDB-style columnar execution) and Citus for sharding mean Postgres can push further before it breaks. Neon and Supabase both shipped better analytics paths in 2025–2026.
So the gap narrowed. It didn't close.
Here's the way I actually think about it now:
| Dimension | PostgreSQL 18 | ClickHouse 2026 |
|---|---|---|
| Sweet spot rows | < 100M | 100M – 100B+ |
| Point updates | Excellent | Painful |
| Aggregations | Good | Exceptional |
| Concurrent users | 100s (with pooling) | 100s–1000s |
| Joins | Full, flexible | Limited, plan carefully |
| Compression | 2–4x | 10–20x typical |
| Transactions | Full ACID | Single-table, eventual |
| Operational cost | Low if you have it | Real (separate system) |
| Ops familiarity | High | Medium |
Notice the ops cost row. That's where most "ClickHouse is better" arguments quietly lose. It's not free to run two databases.
A decision tree that actually decides something
Stop reading vendor benchmarks. Answer these four questions.
Question one: What's your row count in 12 months?
Under 100M → Postgres. Keep your life simple.
Over 500M and growing → ClickHouse. Don't fight physics.
Between → depends on the rest.
Question two: How many writes per second?
Postgres handles 10K–50K inserts/sec with tuning. ClickHouse handles 1M+ inserts/sec via batch inserts. If you're at 200K events/sec (my current world), that's ClickHouse territory, no debate.
But batch matters. ClickHouse hates single-row inserts. You must batch. Here's the pattern I use:
# Correct ClickHouse insert: batched, not row-by-row
import clickhouse_connect
client = clickhouse_connect.get_client(host='localhost')
def flush_events(buffer):
if len(buffer) < 10000:
return
client.insert(
'events',
buffer,
column_names=['ts', 'user_id', 'event_type', 'latency_ms']
)
buffer.clear()
# Events accumulate in memory; flush every 10K or every second.
# Never call insert() inside a hot loop.
That single change — batching — is the difference between ClickHouse being 20x faster than Postgres and being 5x slower. Teams that miss this blame ClickHouse. They're blaming the wrong thing.
Question three: Do you need transactions or updates?
If yes, and it's central to the workload → Postgres, or a hybrid.
If updates are rare and updates-as-inserts works → ClickHouse fine.
Question four: What's your team's operational bandwidth?
One database is a decision. Two is a commitment. Be honest about whether you can run ClickHouse well — backups, replication, TTLs, schema migration discipline. ClickHouse migrations are not Postgres migrations. There's no ALTER TABLE ... ADD COLUMN DEFAULT that's instant on huge tables. It's better than it was, but plan around it.
The hybrid pattern I keep recommending
Here's my actual advice for most mid-to-large teams in 2026: run both, but not for the same thing.
Postgres is the source of truth. It handles writes, transactions, application state. ClickHouse is the analytical mirror — fed by CDC (Debezium), Kafka, or a nightly + streaming hybrid.
I've shipped this at three companies now. It's the pattern that scales without forcing one database to be something it's not.
# Rough CDC topology for a Postgres -> ClickHouse mirror
# Debezium tails Postgres WAL -> Kafka -> ClickHouse Kafka engine
source:
connector: debezium-postgres
tables: [orders, users, events]
sink:
engine: kafka
topics: [pg.public.orders, pg.public.events]
clickhouse:
# Kafka engine table -> materialized view -> MergeTree
- kafka_table: orders_queue
- mv: orders_mv
- target: orders_replacing # ReplacingMergeTree dedupes on order_id
The catch: replication lag. Right now, in my setups, it's 200ms–2s depending on load. If your "real time" means sub-second, measure it. Don't assume.
And the failure mode nobody warns you about: schema drift. Postgres gains a column, ClickHouse doesn't know, CDC silently drops it. I've lost a day to this. Set up a check.
Cost, because that's why you're really here
Numbers, roughly, for a workload at 2 billion rows and 50K inserts/sec:
Postgres-only path: a well-tuned r8g.4xlarge instance + read replicas for analytics, roughly $2,500–4,000/month on AWS. You'll fight it by month six.
ClickHouse Cloud: comparable workload runs $1,800–3,500/month, but you'll want a small Postgres alongside for app state — add $300–600. Net similar, with far more headroom.
Self-hosted ClickHouse: cheapest at scale, most operational weight. If you have a platform team, this wins. If you don't, ClickHouse Cloud earns its markup.
The contrarian take: ClickHouse is often not cheaper than Postgres for small data. Its per-query cost model and minimum cluster sizes mean below ~200M rows you're paying for headroom you don't need. I've talked three clients out of migrating for exactly this reason.
FAQ
Is ClickHouse harder to operate than Postgres?
Yes, meaningfully. Backups, replication topology, and schema migrations all demand more expertise. If you can't dedicate someone to it, use ClickHouse Cloud.
Can Postgres handle real time analytics in 2026?
Absolute yes, up to a point. Partitioning, BRIN indexes, and Postgres 18's parallel execution get you further than the old advice suggests — roughly 100M rows for interactive dashboards. Past that, it's a losing battle.
When should I pick ClickHouse over Postgres for log analysis?
The moment your log volume crosses a few hundred million rows or you need sub-second aggregations across high-cardinality fields. Below that, Postgres + a time partition is genuinely fine and I've built it that way many times.
Does ClickHouse support updates?
Yes, but differently. Lightweight deletes and ReplacingMergeTree cover most cases. It's not row-level OLTP, and pushing an update-heavy workload onto ClickHouse will hurt.
What about DuckDB?
Different tool. Excellent for single-node local analytics and embedded use. Not a server, not built for concurrent production dashboards. I use DuckDB for analyst work and ClickHouse for production.
Can I migrate from Postgres to ClickHouse later without a rewrite?
The ingestion path changes; the query path often doesn't. Most dashboard SQL ports with minor tweaks. The bigger cost is running two systems, not the code.
What's the ClickHouse wire-protocol Postgres compatibility actually good for?
BI tools that speak Postgres can often connect to ClickHouse directly now. I've used it for Metabase and a couple of internal tools. It's convenient, not a replacement for native clients.
My actual recommendation
If you're under 100M rows and your team is small: stay on Postgres. Don't add a database to feel sophisticated. Postgres 18 is genuinely capable at real time analytics in this range, and you'll sleep better.
If you're above 500M rows, doing heavy log analysis, or ingesting over 100K events/sec: add ClickHouse. Keep Postgres as your source of truth. Feed ClickHouse via CDC or Kafka. This is the clickhouse vs postgresql for real time analytics 2026 answer that holds up in production, not just in a benchmark.
The middle zone — 100M to 500M — is where you have to actually think. Profile your queries. Measure your p95. Try both. If Postgres holds and your team refuses to run two systems, that's a legitimate reason to stay. Operational simplicity compounds.
The clickhouse vs postgresql for large datasets 2026 comparison doesn't have a universal winner. It has a right answer per workload shape, and the fastest way to get it wrong is to pick based on which database has better marketing.
One last thing. Whatever you pick, instrument it. I've seen teams spend six figures migrating to fix a performance problem that turned out to be a missing index and a query doing a full table scan on every dashboard load. Fix the query before you migrate the database.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.
Top comments (0)