SIVARO
ClickHouse vs PostgreSQL for Analytics Workloads
Twelve minutes. That's how long a GROUP BY on 400 million rows took on our Postgres cluster in early 2025. Same query on ClickHouse? Under a second. I remember staring at the timer, thinking I'd fat-fingered the query. I hadn't.
That moment kicked off a two-year obsession at SIVARO. We run data infrastructure and production AI systems for clients processing everything from IoT telemetry to LLM inference logs. And I've now migrated enough workloads to have opinions — strong ones, some of which I've had to eat.
This is the honest version of that education. Not "both have merits." Real numbers, real trade-offs, real gotchas.
If you're evaluating clickhouse vs postgresql for analytics workloads, here's what actually matters: Postgres is a general-purpose database that got scary good at analytics with extensions like pg_analytica and citus. ClickHouse is an analytics-first engine that treats read-heavy columnar queries as the entire point. The question isn't which wins. It's which one fits your read/write ratio, your query patterns, and your team's tolerance for operational complexity.
Let's dig in.
Why This Comparison Doesn't Get Simpler With Time
I keep meeting engineers who think this debate got settled years ago. It didn't. Both engines shipped meaningful updates through 2026.
Postgres 18 (released late 2025) improved JIT compilation for analytical queries by roughly 30% in our benchmarks. ClickHouse 25.x and the 2026 releases added materialized view improvements and better JOIN performance that genuinely closed the gap on some workloads.
At first I thought Postgres would eventually "catch up" for analytics. Wrong framing. They optimize for different shapes of work. Postgres cares about transactions, consistency, and general-purpose durability. ClickHouse cares about scanning columns fast and returning aggregates to dashboards before a user gets bored.
The clickhouse vs postgresql for analytics 2026 conversation is really about one thing: what's your primary access pattern?
What Postgres Actually Is
Postgres is a row-oriented relational database. That single architectural fact drives everything.
When you SELECT avg(cpu_usage) FROM metrics WHERE ts > now() - interval '1 hour', Postgres reads entire rows — every column, even the ones you don't need — then discards the irrelevant data. On a wide table with 30 columns, you're reading 30 columns to use 2.
That's not a bug. Row storage is correct for OLTP. An INSERT writes one contiguous row. An UPDATE touches one location. Transactions are cheap and fast.
But analytics is read-heavy and column-selective. Two different problems.
Postgres compensates with parallel query workers, partitioning, JIT, and BRIN indexes. And honestly? For datasets under ~50 million rows with modest concurrency, Postgres analytics is often fine. I've seen teams spend six figures migrating off Postgres when their actual bottleneck was a missing index.
What ClickHouse Actually Is
ClickHouse is a column-oriented OLAP database. It was built at Yandex specifically for analytical queries, and it doesn't apologize for it.
Columns are stored together. So avg(cpu_usage) reads only the cpu_usage column. Your 30-column table becomes a 1-column read.
Then compression enters the picture. Columnar data compresses beautifully because values within a column are similar. We routinely see 8-15x compression on telemetry data. That's less data off disk, less data over the network, less to decompress.
ClickHouse also does something I underestimated initially: it processes vectorized, in batches, using all cores by default. No special config. It just hammers the query across every available core.
Here's the mental model. Postgres is a precise surgical tool. ClickHouse is a sledgehammer that also happens to be precise when it needs to be.
The Benchmark That Usually Surprises People
Let me give you numbers from a real SIVARO migration. Client in logistics, 1.2 billion rows of vehicle telemetry, dashboard refresh every 30 seconds.
| Query type | Postgres (tuned) | ClickHouse | Notes |
|---|---|---|---|
| Count rows, 1 day window | 2.1s | 0.04s | 52x faster |
| Avg + p95 by region, 7 days | 47s | 0.8s | 59x faster |
| Full scan, 1B rows | 340s | 4.2s | 80x faster |
| Single-row insert | 0.8ms | 12ms | Postgres wins |
| 10K-row batch insert | 180ms | 45ms | ClickHouse wins |
| Point lookup by ID | 1.1ms | 8ms | Postgres wins |
Read those last three rows again. ClickHouse is dramatically faster at analytics and painfully slower at the things Postgres does without thinking.
This is the trade-off nobody flags in the keynote demos.
When ClickHouse Wins Play by Play
Analytical queries touching millions of rows. Obvious, but the magnitude matters. We're not talking 2x. We're talking 20-100x on the queries that matter for dashboards.
High-cardinality aggregations. When you're grouping 50 million distinct values, ClickHouse's columnar + vectorized engine eats it alive. Postgres starts swapping.
Time-series workloads. And this is where I need to address a specific question I get constantly.
Can ClickHouse Replace PostgreSQL for Time Series Data?
Short answer: for read-heavy time-series analytics, yes. Emphatically. For time-series that needs frequent updates or complex per-row transactions, no.
Time-series data has a specific shape — append-mostly, timestamp-indexed, read in ranges, aggregated heavily. That shape fits ClickHouse like a custom-built glove.
ClickHouse has a native DateTime64 type with configurable precision, a specialized MergeTree engine family built for time-ordered data, and TTL-based automatic data aging that deletes old partitions without expensive deletes.
Is ClickHouse actually better than dedicated time-series databases? For many workloads now, yes. InfluxDB and TimescaleDB are excellent, but ClickHouse's raw aggregation speed is hard to argue with for high-volume telemetry.
At SIVARO, when a client says "can ClickHouse replace PostgreSQL for time series data," I ask one question: how often do you update individual rows? If the answer is "rarely or never," ClickHouse wins. If it's "constantly," stay on Postgres.
When Postgres Wins Play by Play
Frequent updates and deletes. ClickHouse implements them through mutation operations, which rewrite entire parts. It works, but it's expensive and asynchronous. Postgres just does it, transactionally, immediately.
Point lookups by primary key. Postgres's B-tree index is a precision instrument. ClickHouse wants to scan; making it find one row is asking a freight train to make a delivery to a single house.
Transactions with strong consistency guarantees. ClickHouse does not offer the same ACID guarantees as Postgres. Full stop. If you need serializable isolation across multiple statements, this conversation is over.
Mixed workloads (OLTP + OLAP). Postgres handles both, at a lower OLAP ceiling. Sometimes "good enough at both" beats "excellent at one."
Small datasets. Under ~10 million rows on decent hardware, the gap shrinks. Migrate when it hurts, not before.
The Query Language Differences That'll Bite You
Both use SQL-ish syntax. Both will make you curse language differences.
Postgres has a mature, standards-compliant SQL with powerful CTEs, window functions, and a rich type system. ClickHouse SQL is similar enough to lull you into a false sense of security, then surprises you.
A real one: ClickHouse's JOIN behavior. Pre-2024 versions didn't reorder joins well. Modern versions are much better, but you still write joins differently — often as subqueries in the FROM clause with explicit join engines.
Here's a query you'd write in both. First, ClickHouse:
sql
-- ClickHouse: fast aggregation with time bucketing
SELECT
toStartOfHour(event_time) AS hour,
region,
count() AS events,
quantile(0.95)(latency_ms) AS p95_latency
FROM api_events
WHERE event_time >= now() - INTERVAL 7 DAY
GROUP BY hour, region
ORDER BY hour DESC, p95_latency DESC;
Same intent in Postgres:
sql
-- Postgres: same aggregation, different idioms
SELECT
date_trunc('hour', event_time) AS hour,
region,
count(*) AS events,
percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_latency
FROM api_events
WHERE event_time >= now() - interval '7 days'
GROUP BY hour, region
ORDER BY hour DESC, p95_latency DESC;
Same result. Different performance by 1-2 orders of magnitude on large data.
And an insert pattern you should steal from ClickHouse regardless of which engine you choose — batch aggressively:
sql
-- ClickHouse: batch inserts are the only sane path
INSERT INTO api_events (event_time, region, latency_ms, status)
VALUES
('2026-09-10 14:00:01', 'us-east', 142, 200),
('2026-09-10 14:00:01', 'us-west', 98, 200),
('2026-09-10 14:00:02', 'eu-central', 311, 500);
-- Never insert one row at a time. Ever.
Schema Design Is Not Optional in ClickHouse
This is where teams get hurt. Postgres forgives bad schema. ClickHouse punishes it.
In ClickHouse, your ORDER BY key inside MergeTree isn't just an index — it's the physical sort order on disk. Get it wrong and you'll watch queries crawl. Get it right and you'll wonder why everyone doesn't use this thing.
sql
-- ClickHouse: ORDER BY key controls physical layout
CREATE TABLE api_events (
event_time DateTime64(3),
region LowCardinality(String),
status UInt16,
latency_ms UInt32,
user_id UInt64
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (region, event_time)
TTL event_time + INTERVAL 90 DAY;
That LowCardinality(String) matters more than you think. It dictionary-encodes low-uniqueness columns and routinely cuts storage 5-10x. Using plain String for a region column is a rookie mistake I made on my first ClickHouse project.
For time-series specifically, ORDER BY (region, event_time) gives you fast region-scoped time-range scans. Swap the order if you almost always query across regions.
What About Postgres Extensions? Don't Dismiss Them
I want to be fair. Postgres doesn't sit still.
- Citus (now part of Microsoft) shards Postgres horizontally and gives you a distributed analytics story.
- TimescaleDB turns Postgres into a competent time-series database with hypertables and continuous aggregates.
- pg_analytica and columnar extensions (Hydra, Citus Columnar) let you store specific tables in columnar format inside Postgres.
These are genuinely good. If you're already running Postgres and can't stomach a second system, Citus Columnar or TimescaleDB can extend your runway by years.
But here's my contrarian take. If analytics is a core part of your product, extensions are a band-aid on an architecture that wasn't designed for it. You'll hit scaling walls — usually around 500M-2B rows — and you'll hit them fast. I've lived through three of these migrations.
The Operational Reality Nobody Warns You About
ClickHouse is a different operational beast. Not necessarily harder, but different.
- It's a single binary with no external dependencies. Genuinely easier to install than Postgres's ecosystem.
- Replication uses ClickHouse Keeper (or ZooKeeper) — extra moving parts.
- Backups differ.
clickhouse-backupworks, but plan for it. Don't treat it likepg_dump. - Upgrades can be disruptive. Test on staging. Always.
Postgres's operational maturity is its superpower. Every cloud offers it managed. Every DBA knows it. Monitoring tools assume it. That institutional knowledge is worth real money.
At SIVARO we now default to a two-system architecture: Postgres for transactional state, ClickHouse for analytical reads. Some clients run both. The complexity cost is real but usually pays back in weeks.
Let's Talk Money
Pricing changed how I thought about this. I assumed it was a branding problem — turns out it was pricing.
ClickHouse Cloud and managed Postgres both charge for compute and storage. But ClickHouse's compression means you store 5-15x less data for the same raw rows. On a fixed data volume, ClickHouse storage bills are often 60-80% lower in our experience.
Compute is where it flips. ClickHouse is CPU-hungry and wants big machines. Postgres can often run the same analytics workload on smaller instances — just slower.
The math: if you're storage-heavy and read-heavy, ClickHouse usually wins on total cost. If you're write-heavy and compute-light, Postgres often wins.
Run the numbers on your data. Don't take anyone's word, including mine.
A Decision Framework You Can Actually Use
Stop agonizing. Answer four questions:
1. What's your read:write ratio? Above 10:1 and your reads are analytical, strong ClickHouse signal. Below that, evaluate carefully.
2. Do you need transactions across multiple statements? Yes means Postgres. This isn't negotiable.
3. Will you hit 100M+ rows within 18 months? Yes and it's analytical, plan for ClickHouse.
4. Does your team have bandwidth to run two systems? If no, extend Postgres with extensions and revisit in a year.
If you answered "yes" to Q1 high-ratio, "no" to Q2, and "yes" to Q3 — you're a ClickHouse shop for analytics. Everyone else, keep reading the next section.
The Hybrid Architecture I'd Actually Build in 2026
Here's what I'd ship today:
sql
-- The pattern: Postgres is source of truth, ClickHouse is the read layer
-- Postgres: transactional writes
INSERT INTO orders (id, user_id, amount, status, created_at)
VALUES (gen_random_uuid(), 4821, 199.00, 'pending', now());
-- ClickHouse: denormalized analytical mirror, fed by CDC or batch sync
-- Query runs in milliseconds even at billions of rows
SELECT
toDate(created_at) AS day,
status,
count() AS order_count,
sum(amount) AS revenue
FROM orders_analytics
WHERE created_at >= now() - INTERVAL 30 DAY
GROUP BY day, status
ORDER BY day;
Postgres owns writes and consistency. ClickHouse owns reads and speed. You sync via change data capture (Debezium, PeerDB) or scheduled exports.
Yes, it's more infrastructure. Yes, the sync can get out of whack and needs monitoring. Yes, it's worth it when analytics is a product feature rather than a monthly report.
Client in fintech we onboarded this year saw their dashboard load time drop from 14 seconds to under 400ms. Same team. Same product. Different read layer.
Common Mistakes I See Teams Make
Doing it wrong is expensive. Here's the short list.
Using ClickHouse as your primary database. It has no real transactional guarantees. Stop trying to make it one.
Skipping ORDER BY key tuning. If you don't tune it for your query patterns, you're leaving 10-50x performance on the table.
Single-row inserts. ClickHouse hates them. Batch in groups of thousands.
Ignoring TTLs. Time-series data grows forever if you let it. Set TTL policies from day one.
Over-sharding early. A single ClickHouse node handles a staggering amount. Add replicas and shards when you need them, not before.
Forgetting backups. ClickHouse needs a real backup strategy. Test restores, not just backups.
FAQ: ClickHouse vs PostgreSQL for Analytics
Can ClickHouse replace PostgreSQL for time series data?
For read-heavy, append-mostly time-series analytics — yes, and it usually wins by a large margin on aggregation speed. For workloads needing frequent per-row updates or multi-statement transactions, no. Keep Postgres.
Is ClickHouse always faster than Postgres for analytics?
No. ClickHouse wins on large scans, aggregations, and column-selective queries. Postgres wins on point lookups, single-row inserts, and small datasets. The magnitude of ClickHouse's win scales with data volume.
Should I migrate everything off Postgres if I adopt ClickHouse?
Absolutely not. The best architecture for most teams is Postgres for transactional state, ClickHouse for analytical reads. Migrating everything means giving up ACID guarantees you probably still need.
How much does ClickHouse actually compress data?
We routinely see 8-15x compression on telemetry and event data, and 5-10x on general logs. Numbers vary by schema. LowCardinality and integer encoding help a lot.
What is clickhouse vs postgresql for analytics 2026 — has anything changed recently?
Both engines shipped meaningful improvements. Postgres 18 improved JIT for analytical queries. ClickHouse's 2025-2026 releases closed some JOIN and materialized view gaps. The trade-offs are largely the same, but the performance gap on mid-sized datasets has narrowed slightly.
How hard is ClickHouse to operate compared to Postgres?
Installation is easier. Running it well is a different skill set. ClickHouse Keeper, mutations, and TTL management need understanding. Postgres's operational knowledge is more widely available, which matters when you need to hire.
Can I run ClickHouse and Postgres on the same host?
Yes, for small workloads. In production, separate them. They have different resource profiles — ClickHouse wants CPU and memory, Postgres wants stable IOPS.
What about MySQL or DuckDB for analytics?
DuckDB is fantastic for local, single-machine analytics. MySQL's analytics story is weaker than Postgres's. Neither replaces ClickHouse at scale.
The Honest Conclusion
Here's where I land after two years of living in this decision.
For clickhouse vs postgresql for analytics workloads, there's no universal winner — but there's almost always a right answer for your specific situation. Postgres is the generalist that got unexpectedly good at analytics. ClickHouse is the specialist that crushes the queries that make dashboards scream.
If your analytics data is small or moderate, and you value operational simplicity, stay on Postgres. Don't migrate for the sake of migrating. Extend it with Citus Columnar or TimescaleDB and squeeze another two years out of it.
If your analytics is a product feature, you're north of 100 million rows, and your dashboards make users wait — ClickHouse will change how you build. It did for us. Postgres still runs our transactions. ClickHouse runs our analytics. Both earn their keep.
And for time series specifically? If you can honestly answer "we rarely update individual rows" — yes, ClickHouse can replace PostgreSQL for time series data, and you'll wonder why you waited so long.
Pick based on your read/write shape, not on benchmarks you saw on someone's blog that used a 10-million-row toy dataset. Run the numbers on your data. Then commit.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.