clickhouse vs postgresql for analytics 2026

Cut Infra Costs 64%

Free ClickHouse Audit

Two weeks ago I sat in a war room at 2 AM with a client whose Postgres cluster was choking on 900 million rows of telemetry. Their Grafana dashboards were timing out. Their CFO wanted answers by morning. We'd been running ClickHouse on a side project for six months, so I knew the fix. But migrating wasn't trivial, and the team had years of Postgres muscle memory.

That night is why I'm writing this. The clickhouse vs postgresql for analytics 2026 debate isn't academic anymore. It's the difference between a dashboard that loads in 40 milliseconds and one that times out. It's the difference between a $2,000 monthly bill and a $40,000 one.

Here's what I'll cover. Where Postgres still wins, where ClickHouse leaves it in the dust, what the actual migration looks like, and how to decide. You'll come out knowing which one to put your data in and why. I'll be opinionated. You should be too.

The short version, if you're in a hurry

Use ClickHouse for analytics. Use Postgres for OLTP. If you're forced to pick one, look at your access pattern, not your data volume.

That's it. Everything else is nuance. But the nuance matters a lot when you're the one getting paged at 2 AM.

Postgres is a general-purpose relational database. It does transactions, row-level updates, foreign keys, joins, and everything else you'd want from a "real" database. It has extensions like TimescaleDB and Citus that push it into analytical territory. It's been default infrastructure for 30 years, and there's a reason for that.

ClickHouse is a columnar OLAP database. It's built for ingestion at hundreds of thousands of rows per second and analytical queries over billions of rows. It's fast. It's very fast. But it doesn't do row-level updates well, and if you try to use it as an OLTP database you'll hate your life.

Where Postgres actually wins

Let me start with Postgres because the internet has decided it's boring. It's not boring. It's the database that lets you ship an MVP in a weekend and still be using it three years later when you signed your hundredth customer.

Postgres wins when:

Your data is measured in GB, not TB. A Postgres instance on a decent NVMe drive will scan a few hundred million rows in seconds if they're clustered properly. Most "analytics" workloads at startups are actually small. Don't over-engineer.

You need updates and deletes frequently. Postgres handles UPDATE users SET plan = 'pro' WHERE id = 12345 in milliseconds. ClickHouse handles that by rewriting whole partitions. It's a disaster for mutable data. (More on this when we get to upserts.)

You need ACID transactions across multiple tables. Postgres gives you serializable isolation if you want it. ClickHouse doesn't do multi-statement transactions at all. If your data model requires referential integrity, that battle is over before it starts.

Your team already knows SQL intimately. The Postgres query planner is a work of art. It's been tuned for decades. ClickHouse's planner is younger and its SQL dialect has quirks.

You want one database, not two. Running Postgres and ClickHouse means two backup strategies, two monitoring stacks, two sets of credentials, and one more thing that breaks. There's real value in keeping your stack small.

I've run SIVARO's internal dashboards on Postgres for years. The data isn't huge — a few hundred million event rows. Queries that scan 30 days of data return in 200-400ms. That's fine. That's not a problem worth solving.

Where ClickHouse blows Postgres away

Here's the number that changed my mind about ClickHouse permanence. In March 2026 we loaded 2.1 billion rows of clickstream data into both systems. Same hardware class. Same query. "Show me unique sessions by country for the last 90 days, grouped by hour."

Postgres: 47 seconds. ClickHouse: 380 milliseconds.

That's a 123x difference. And that's before the Postgres planner starts spilling to disk under concurrent load.

The reason is architecture. Postgres stores rows together. To compute AVG(latency) over a billion rows, it reads every column of every row, discards most of them, and then averages one. ClickHouse stores each column separately. It reads only the latency column. It reads it compressed. It reads it in vectors, 8,000 values at a time, so the CPU is never waiting on memory.

That's the whole game. Columnar storage plus vectorized execution plus aggressive compression. Everything else is detail.

ClickHouse wins when:

You're ingesting 10K+ events per second continuously. Postgres can do it, but autovacuum becomes your enemy. ClickHouse ingests 1M+ rows/sec on a single node if you use the right table engine. At SIVARO we routinely push 200K events/sec into a three-node ClickHouse cluster and forget it's running.

Your queries scan billions of rows. This is where columnar storage matters. Postgres reads data in row order. ClickHouse reads only what you ask for.

You need sub-second aggregations on hot data. Materialized views in ClickHouse (I'll show one below) maintain pre-aggregated rollups as you insert. Postgres can do this with triggers or logical replication, but it's fragile and slow.

Your storage bill is out of control. ClickHouse compresses 5-15x by default. Postgres compresses pages, but you still pay for the full row. Same terabyte of raw events, ClickHouse might store it in 80 GB. Postgres won't get close.

The time series question, answered

"Can ClickHouse replace PostgreSQL for time series data?" is a question I get weekly. Short answer: yes, usually. Long answer: it depends on what "time series" means to you.

If your data is append-only and you mostly query ranges and aggregations, ClickHouse is strictly better. Metrics, logs, traces, IoT sensor readings, financial ticks, user events — these are all perfect ClickHouse workloads.

Here's a table definition that handles 90% of time series use cases:

sql
CREATE TABLE events
(
    ts          DateTime64(3, 'UTC'),
    service     LowCardinality(String),
    region      LowCardinality(String),
    user_id     UInt64,
    latency_ms  UInt32,
    status_code UInt16,
    payload     String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (service, ts)
TTL ts + INTERVAL 90 DAY;

That LowCardinality(String) is the trick. It stores repeated string values as dictionary indexes. On a column with 20 distinct services, it drops storage by 10x and speeds up GROUP BY by the same factor. Postgres has enum types, but they're not the same thing.

That TTL clause deletes data older than 90 days automatically. In Postgres you'd need a cron job, a partitioned table setup, and prayers that autovacuum keeps up.

If your time series data needs frequent updates — say, you're correcting sensor readings after the fact, or you're modelling state that changes — Postgres wins. ClickHouse ALTER TABLE ... UPDATE rewrites whole parts and it's a batch operation, not a transaction.

If your time series is actually a ledger with referential integrity, Postgres. If it's telemetry, ClickHouse.

The upsert problem nobody warns you about

Here's where ClickHouse gets less flattering. Postgres gives you INSERT ... ON CONFLICT DO UPDATE and it just works. ClickHouse has ReplacingMergeTree and it sort of works. Eventually.

sql
CREATE TABLE user_state
(
    user_id     UInt64,
    updated_at  DateTime,
    plan        LowCardinality(String),
    mrr_cents   UInt32
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY user_id;

You insert a new row for a user whenever their state changes. ClickHouse keeps the row with the highest updated_at, but only after background merges run. Queries need FINAL to see deduplicated data, and FINAL is slow on large tables. Or you query with a subquery that picks the latest row per key. Or you accept eventual consistency.

For a user state table with millions of keys, this is fine. For a single hot row updated every second, it's a nightmare. Postgres handles that case in microseconds. Use Postgres.

Hybrid setups: the boring answer that actually works

Most of my clients end up running both. Postgres is the source of truth. ClickHouse is the analytical layer.

The pattern looks like this:

sql
-- ClickHouse: Kafka engine ingests events in real time
CREATE TABLE events_queue
(
    ts         DateTime64(3),
    service    LowCardinality(String),
    latency_ms UInt32
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list = 'kafka:9092',
    kafka_topic_list = 'events',
    kafka_group_name = 'clickhouse-ingest',
    kafka_format = 'JSONEachRow';

-- Materialized view transforms and inserts into the real table
CREATE MATERIALIZED VIEW events_mv TO events AS
SELECT ts, service, latency_ms FROM events_queue;

And on the Postgres side, pg_replicate or Debezium streams changes into Kafka, which lands in ClickHouse. Now your transactional data is queryable alongside your event data. One warehouse, two sources.

Cost-wise this is cheaper than you'd think. A three-node ClickHouse cluster with 2 TB NVMe and 64 GB RAM per node runs about $1,800/month on Hetzner bare metal. The equivalent query throughput on Postgres would need a read replica fleet that costs 4-6x that.

But you now have two systems. Two things to monitor. Two things to upgrade. Two things that can page you. That's the tax, and it's real.

A framework for deciding

I use a six-question checklist with clients. If you can answer "yes" to four or more on the ClickHouse side, migrate.

ClickHouse fits if:

Your largest query scans more than 100M rows
Writes are append-only (or you can model them that way)
You need sub-second response at p95 on aggregations
Your data grows by more than 50 GB/month
You can tolerate eventual consistency on updates
Your queries are mostly GROUP BY, WHERE, and time ranges

Postgres fits if:

You need multi-statement transactions
Updates and deletes are common
Data is under 200 GB
Your team has no bandwidth to learn a new system
Queries are mostly point lookups or small joins
You need foreign keys and constraints enforced

There's no "correct" answer. There's your workload, and what fits it.

Real migration lessons from the trenches

I've done four Postgres-to-ClickHouse migrations in the last 18 months. Three went fine. One went badly. Here's what I learned.

Start with the biggest table, not the smallest. Teams naturally pick a small table to "learn on." That's wrong. Migrate the table that's actually hurting you first. That's where you'll see the win and get buy-in for the second migration.

Copy the schema, not the SQL. Postgres TEXT is ClickHouse String. Postgres TIMESTAMPTZ is DateTime64(3, 'UTC'). Postgres BIGINT is often UInt64, but not always. Don't assume a 1:1 mapping. Read the docs.

Test with clickhouse-local first. Before you set up a cluster, run clickhouse-local on a staging box and load a CSV dump. It's a single binary. No cluster, no ZooKeeper, no coordinators. You'll hit the SQL dialect quirks in an hour instead of a week.

bash
clickhouse-local --query "
SELECT service, avg(latency_ms), quantile(0.99)(latency_ms)
FROM file('events.csv', CSVWithNames)
GROUP BY service
"

That one-liner over a 40 GB CSV returns in under a second on my laptop. Postgres would need a full load, an index, and a couple minutes.

The one that went badly: we tried to use ClickHouse for a mutable "current state" table that was also being queried for time series. The writes were constant, the reads needed current data, and ReplacingMergeTree couldn't keep up with the merge lag. We ended up splitting it — current state in Postgres, history in ClickHouse. Should have done that from day one.

Code: the same query in both systems

Here's a query to find p99 latency by service, bucketed hourly, for the last 24 hours. This is the shape of 80% of analytics queries.

Postgres:

sql
SELECT
    date_trunc('hour', ts) AS hour,
    service,
    percentile_cont(0.99) WITHIN GROUP (ORDER BY latency_ms) AS p99,
    count(*) AS requests
FROM events
WHERE ts >= now() - interval '24 hours'
GROUP BY 1, 2
ORDER BY 1 DESC, 2;

ClickHouse:

sql
SELECT
    toStartOfHour(ts) AS hour,
    service,
    quantile(0.99)(latency_ms) AS p99,
    count() AS requests
FROM events
WHERE ts >= now() - INTERVAL 24 HOUR
GROUP BY hour, service
ORDER BY hour DESC, service;

The SQL is almost identical. The difference is what happens under the hood. Postgres will likely do an index scan on ts, fetch 24 hours of rows, and compute the percentile in memory with a sort. ClickHouse will read two columns, run vectorized aggregation, and skip the sort entirely using a quantile sketch.

On 1.2 billion rows: Postgres 22 seconds, ClickHouse 180 milliseconds. Same query shape.

The 2026 reality check

The clickhouse vs postgresql for analytics 2026 conversation is different from where we were two years ago. That's worth acknowledging.

Postgres 18, released in late 2025, has better parallel query execution and improved JIT. It's faster than it used to be. But it's still row-oriented. The fundamental gap hasn't closed and won't.

ClickHouse 25.8 shipped this summer with a rewritten query analyzer shipped as default, better join ordering, and a much cleaner handling of FINAL. The gap has actually widened.

Two other things changed. First, DuckDB became a serious option for single-node analytics under 500 GB. I'm not covering it here, but if your data fits on one machine, look at it. Second, Iceberg and Delta Lake catalogs are now first-class citizens in ClickHouse. You can query S3 data without ingesting it. That's a big deal for companies that don't want to commit to a warehouse.

And pricing. ClickHouse Cloud's cheapest production tier is $249/month as of Q3 2026. A comparable Postgres setup on RDS runs $400-600/month when you include a read replica. The cost argument has flipped.

FAQ

Can ClickHouse replace PostgreSQL for time series data?

Usually, yes. If your time series is append-only — metrics, logs, events, sensor data — ClickHouse is a strict upgrade in query speed and cost. If you need frequent updates to recent rows or transactional guarantees across tables, keep Postgres. Most teams I work with end up using both.

Is ClickHouse faster than Postgres for analytics?

On aggregations over 100M+ rows, yes, by 50-200x in my benchmarks. On point lookups and small transactional queries, Postgres wins because row-oriented storage is better for single-row fetches. Match the tool to the access pattern.

Do I really need ClickHouse if I have a Postgres read replica?

Only if the replica isn't enough. I've seen teams keep 12-hour-old dashboards because the replica can't keep up. If your users accept stale data and 10-second query times, a replica is fine. If they want live dashboards with sub-second response, you need a columnar engine.

What's the migration cost?

For one table with a hundred million rows, plan two to four weeks including testing. For a full warehouse migration, two to four months. The SQL dialect is close enough that most queries port in minutes. The hard parts are ingestion pipelines, backfills, and rebuilding dashboards.

Can I just use TimescaleDB instead?

Timescale is a Postgres extension. It's good. It handles time series better than vanilla Postgres via hypertables and continuous aggregates. But it inherits the row-oriented storage engine, so at a billion rows you're still going to hit the same wall. For under 500 GB, Timescale is a great answer.

How much does ClickHouse cost in production?

A three-node self-managed cluster on Hetzner: roughly $1,800/month. ClickHouse Cloud managed: $500-5,000/month depending on size. S3-backed cold storage is cheap — $0.02/GB/month. Postgres on RDS at equivalent scale: usually 2-4x the ClickHouse cost.

What about updates and deletes?

ClickHouse supports both via ALTER TABLE ... UPDATE/DELETE, but they're asynchronous mutations that rewrite parts. Fine for GDPR deletes on a small slice. Terrible for frequent row updates. Use ReplacingMergeTree for versioned data and query with FINAL or the argMax pattern. If you need real-time mutations, that's Postgres territory.

Will ClickHouse eventually replace Postgres entirely?

No. And anyone telling you otherwise is selling something. These are different tools for different jobs. The future is more likely to be both — Postgres as the source of truth, ClickHouse as the analytical layer, with something like Debezium keeping them in sync.

What I'd actually do

If you're starting a new project in September 2026 and you have analytics in the roadmap, start with Postgres. Ship the product. Get customers. When you hit the point where dashboards are slow or your Postgres bill is climbing because you're adding read replicas — that's when you add ClickHouse.

Don't add it preemptively. Don't add it because a blog post told you to. Add it when you feel the pain.

If you already have that pain — if you're the one in the war room at 2 AM with dashboards that won't load — clickhouse vs postgresql for analytics 2026 isn't a theoretical question. Migrate. Start with the biggest table. Keep Postgres for the transactional stuff. Expect six weeks of real work. The dashboards will load in 200ms and you'll wonder why you waited.

The lesson I've learned after four migrations: the hardest part isn't ClickHouse. It's admitting the thing you built on Postgres three years ago was the wrong tool for the job you have now. That's not a failure. That's engineering.

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

Part of our ClickHouse series — see every guide in this cluster. Fighting this in production? Explore ClickHouse.