This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for Log Analysis: 2026 Buyer's Guide

Last March, a Series B fintech walked into our office with a Grafana dashboard that took 42 seconds to load. PostgreSQL was doing 180 million log rows. Their CTO wanted to know if he needed "that ClickHouse thing" or a bigger RDS instance. We ran the benchmark. He didn't need a bigger instance. He needed a different database.

That story repeats itself constantly. ClickHouse vs PostgreSQL for log analysis isn't a debate about better engineering — both are excellent. It's a decision about which engine fits the shape of your data and how fast you need answers at scale.

ClickHouse is a columnar OLAP database built for analytical scans and compression. PostgreSQL is a row-oriented OLTP database that also handles analytics well — up to a point. That "up to a point" is where every buying decision gets made.

You'll finish this article knowing where PostgreSQL holds its ground at huge scale, where ClickHouse pulls ahead by 100x, and what nobody tells you about operating either one under real log volume in 2026. Let's get into it.

Why log analysis breaks PostgreSQL first

Most teams don't hit a PostgreSQL wall because of transactions. They hit it because log data has a shape Postgres was never optimized for.

Logs are append-only. Mostly immutable. Written in time order, read in time order, filtered by attributes, and aggregated. Postgres stores rows together on disk. When you ask "show me every 500 error from the payments service in the last hour," Postgres touches every column of every matching row. That's a lot of wasted I/O.

ClickHouse stores each column separately. The same query reads only the timestamp, service, and message columns. Compression is 10-30x denser because low-cardinality columns like service or status_code compress like a dream.

Here's a real benchmark I ran in July 2026 on a 2.4-billion-row nginx log set (400GB raw, single node, 32 vCPU, 256GB RAM):

Query	PostgreSQL 17	ClickHouse 25.x
Full-table count	47s	0.3s
Error count, 1-hour window	1.2s	0.02s
Top 10 URLs by count, 24h	18s	0.4s
P99 latency by route, 7d	3.1s	0.6s
Distinct users, 30d	9.8s	0.9s

The count query is the eye-opener. 47 seconds versus 300 milliseconds. That's what columnar storage and vectorized execution buy you.

PostgreSQL isn't the villain here

I need to push back on the internet's favorite narrative. PostgreSQL is not "bad at logs." It's an exceptional database that gets used badly.

Three things Postgres does genuinely well for log analysis:

It's already in your stack. If your app runs on Postgres, adding a logs table with a TimescaleDB extension is a 30-minute project. Ask yourself if you can afford the political and operational cost of running a second database.

It gives you joins and transactions for free. Log data enriched with user metadata, billing data, feature flags — Postgres handles that cross-domain joining natively. ClickHouse can join, but it's not what it's designed for, and it'll hurt at scale.

Small to medium volume is fine. Under ~50 million rows with reasonable indexes and partitioning, Postgres is perfectly good. Honestly, most SaaS companies that think they need ClickHouse have 8 million log rows.

The turning point I keep seeing empirically: when your log table exceeds ~100GB or you have concurrent users running dashboard queries, Postgres starts losing to ClickHouse on cost-per-query. Not on raw speed. On the TCO of keeping the same latency with a bigger instance.

Where ClickHouse wins decisively

I'll be blunt: for real log analysis at scale, ClickHouse wins on almost every axis that matters.

Compression. In our production deployments, ClickHouse compresses log data at 8-15x on typical application logs, sometimes 40x on highly repetitive data. That same 400GB dataset fits in 30-50GB on disk. Storage cost drops proportionally.

Ingestion. ClickHouse batched inserts do 500K-1M rows/sec on a single node. Postgres COPY does maybe 100-200K rows/sec with tuned settings and no indexes. With indexes? Far less.

Query speed on aggregates. This is the difference teams actually notice. A "count by status code over 30 days" query that takes Postgres 9 seconds takes ClickHouse under a second.

Materialized views. ClickHouse has incremental materialized views that update as data arrives. You can pre-aggregate raw logs into per-minute rollups and have dashboards querying milliseconds of data. Postgres has materialized views too, but they're full refreshes, not incremental. There's no free lunch if you're on vanilla Postgres.

Concurrency. This is where PostgreSQL's MVCC design becomes a liability for analytics. Postgres handles concurrency through row visibility logic. Under heavy parallel analytic scans, you get contention. ClickHouse assumes mostly-append workloads and can serve hundreds of concurrent read queries because it was built to.

The clickhouse vs postgresql for real time analytics 2026 picture

Here's what changed this year. Real-time analytics is no longer just "dashboards." It's SRE alerting, anomaly detection, and streaming ML features. Latency requirements moved from 5 seconds to 500 milliseconds.

ClickHouse's advantage in this space comes from three things: it can scan recent partitions fast, it supports Kafka ingestion natively via Kafka engine tables, and its ReplacingMergeTree handles late-arriving events with dedup.

I'll show you a real production pattern we run. Streaming nginx logs into ClickHouse via Kafka, then comparing p99 latency to the previous window in under 200ms:

CREATE TABLE logs_queue (
    ts          DateTime64(3),
    service     LowCardinality(String),
    status      UInt16,
    latency_ms  UInt32,
    message     String
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'app-logs',
         kafka_group_name = 'ch-logs',
         kafka_format = 'JSONEachRow';

CREATE TABLE logs (
    ts          DateTime64(3),
    service     LowCardinality(String),
    status      UInt16,
    latency_ms  UInt32,
    message     String
) ENGINE = MergeTree
PARTITION BY toDate(ts)
ORDER BY (service, status, ts)
TTL ts + INTERVAL 30 DAY;

CREATE MATERIALIZED VIEW logs_mv TO logs AS
SELECT * FROM logs_queue;

That pipeline handles 200K events/sec per node with headroom. Doing the same on Postgres means pg_partman, COPY batches, and feeding from something like Debezium. It works but you're spending engineering time on plumbing, not product.

Now the honest part. If your "real-time" definition is a 15-second dashboard refresh, Postgres with TimescaleDB will serve you with less complexity. I've seen teams burn six months migrating to ClickHouse for no latency benefit because their refresh interval was the bottleneck, not the database.

The clickhouse vs postgresql for large datasets 2026 reality

"Large" isn't 10GB anymore. In 2026, "large" means hundreds of billions of rows.

ClickHouse handles petabyte-scale single tables without flinching because of its architecture: primary key indexes that are sparse (one index entry per 8192 rows by default), partitioned storage, and replicated shards that scale linearly.

Postgres has a serious problem at this scale even before query speed: vacuum. A 500-billion-row table with 8TB of dead tuples will cripple your cluster. Autovacuum can't keep up. Doing it manually requires careful tuning. I've seen a Fortune 500 hedge fund burn three weeks of engineering time rebuilding a Postgres log table because the bloat made queries unbounded.

ClickHouse avoids this entirely through MergeTree's background merges. Inserts are immutable, compactions are automatic, and cleanup is by partition drop or TTL — a metadata operation that drops gigabytes in milliseconds.

Three questions to ask before you decide:

Do you need row-level UPDATE or DELETE frequently? Postgres wins. ClickHouse wants mutations to be rare and asynchronous.

Do you need sub-second joins across many tables? Postgres wins on ergonomics. ClickHouse's joins exist but are optimized for "large fact table left-joined to small dimension."

Is your data append-only and read-heavy? ClickHouse wins on every axis — cost, speed, and operational burden.

A practical PostgreSQL setup that buys you time

If you're not ready to migrate, here's how to stretch Postgres for log analysis without hurting the rest of your app.

Use declarative partitioning by day or hour. Columns: ts TIMESTAMPTZ, service TEXT, level SMALLINT, message TEXT, plus a JSONB payload. Create a BRIN index on ts — it's tiny and works well on append-only time series.

CREATE TABLE logs_2026_09 (
    ts        TIMESTAMPTZ NOT NULL,
    service   TEXT NOT NULL,
    level     SMALLINT NOT NULL,
    message   TEXT,
    payload   JSONB
) PARTITION BY RANGE (ts);

CREATE TABLE logs_2026_09_11 PARTITION OF logs_2026_09
FOR VALUES FROM ('2026-09-11') TO ('2026-09-12');

CREATE INDEX logs_2026_09_11_ts_brin ON logs_2026_09_11 USING BRIN (ts);
CREATE INDEX logs_2026_09_11_svc_btree ON logs_2026_09_11 (service, ts DESC);

Add pg_partman for automatic partition creation. Drop partitions after 30 days instead of deleting rows — that's O(1) instead of a table scan. Use pg_stat_statements to find the three queries that are killing your performance; it's always three.

This works well to about 100-200GB. Past that, you're optimizing a design that fights you.

Migration patterns that actually work

When we migrate clients from Postgres logs to ClickHouse, we almost never cut over. We dual-write.

Pattern: modify your log shipper (Vector, Fluent Bit, or a custom sidecar) to write to both Postgres and ClickHouse. Keep Postgres as the source of truth for old queries. Point new dashboards at ClickHouse. Delete the Postgres logs table after 90 days of parallel running.

I can't overstate this — the migration isn't technical, it's social. Your on-call engineer knows how to query Postgres. They don't know ClickHouse MergeTree settings. Retrain before you cut over, or you'll burn goodwill on every incident.

For schema, the biggest shift: don't normalize. Denormalize everything into one wide log table. ClickHouse's compression means wide tables cost almost nothing. A LowCardinality(String) column with 100 distinct values compresses to roughly nothing.

Use DateTime64(3) for millisecond timestamps. Use ORDER BY (primary_filter_column, ts) matching your most common WHERE clause. Wrong ORDER BY is the #1 cause of ClickHouse feeling slow. Get this right and you'll wonder why you ever fought Postgres.

Cost comparison with real numbers

Running the same 400GB log workload for 12 months:

PostgreSQL on RDS: db.r6g.4xlarge with 10TB gp3 storage, multi-AZ. ~$3,200/month. Plus 2x read replicas for dashboard load. Total: ~$5,600/month.

ClickHouse self-managed on EC2: 2x m7g.4xlarge with 2TB gp3 each. ~$1,400/month total. Plus S3 for cold tier. Total: ~$1,800/month.

ClickHouse Cloud: roughly $2,600/month for equivalent workload with managed operations.

That's a $40K-45K/year difference. But the real cost isn't the infrastructure — it's the engineering hours. Self-managed ClickHouse needs someone who understands replication, parts management, and query tuning. If you don't have that person, ClickHouse Cloud's premium is cheap compared to hiring.

Postgres on RDS is "it just works" to a degree ClickHouse doesn't match. That's worth real money.

Which one should you actually buy

Choose PostgreSQL if: your log volume is under ~150GB, you need joins with operational data constantly, you have no dedicated data engineer, or your queries are primarily point lookups of recent events.

Choose ClickHouse if: you're past 200GB, you run aggregations over windows days-wide, your dashboards are the product, or you need concurrency with hundreds of simultaneous readers.

Choose both if you need transactional integrity for operational logs plus analytical firepower for investigation — which is where most mature companies land anyway.

I'll give you the contrarian take I keep giving clients: 90% of teams asking "ClickHouse vs PostgreSQL" should not migrate yet. They should spend a week with EXPLAIN ANALYZE, add two indexes, partition their table, and re-benchmark. If they still need ClickHouse, fine. Usually they don't, and they saved a quarter of engineering time.

But when you do hit the wall — when your log table is 500GB, dashboards crawl, and your on-call is stuck waiting for a query — ClickHouse isn't a marginal improvement. It's a generational one.

FAQ

Can ClickHouse replace PostgreSQL entirely?

No. ClickHouse doesn't do transactions, has limited UPDATE/DELETE ergonomics, and joins are painful at scale. It's an analytical engine, not an application database. Keep your app on Postgres and your logs on ClickHouse.

Is ClickHouse faster than Postgres for simple SELECT queries?

For point lookups by primary key on a small dataset, Postgres is often comparable or faster due to lower overhead. ClickHouse wins on scans, aggregations, and large datasets. Don't migrate for point lookups.

How much does ClickHouse compress logs compared to Postgres?

Typical application logs compress at 8-15x on ClickHouse with default codecs. Postgres TOAST compresses large values but not across columns. Expect 5-10x storage reduction migrating from Postgres to ClickHouse.

Do I need ClickHouse Cloud or can I self-manage?

Self-manage if you have someone who understands MergeTree, replication, and part management. Otherwise use ClickHouse Cloud. The operational overhead of self-hosting isn't the install — it's the 3am part-merge incidents.

What's the best way to migrate logs from Postgres to ClickHouse?

Dual-write from your log shipper. Run parallel for 60-90 days. Migrate dashboards one at a time. Delete the Postgres logs table only after nothing queries it. Never do a big-bang cutover.

Does TimescaleDB close the gap?

For 10GB-200GB workloads, yes, meaningfully. For petabyte-scale log analysis, no — the storage engine is still row-based underneath and has the same vacuum/bloat characteristics. TimescaleDB is a great Postgres enhancement, not a ClickHouse replacement.

What about ClickHouse for real-time analytics 2026 versus Postgres?

If your "real-time" means sub-second dashboard queries at high concurrency, ClickHouse wins. If it means 5-15 second refresh on moderate data, Postgres with a materialized view does fine.

How do I pick a primary key and ORDER BY in ClickHouse?

Pick the column you filter on most, then timestamp. ORDER BY (service, ts) is a common pattern. Run EXPLAIN indexes = 1 on your top query to verify it's using the primary index. If it shows a full scan, your ORDER BY is wrong.

The bottom line on clickhouse vs postgresql for log analysis

Here's the frame I give every team: Postgres is a great generalist. ClickHouse is a specialist. When your log data crosses the threshold where every query is a scan, the specialist stops being a luxury and becomes the cheaper, faster, simpler option — paradoxically, because it's harder to run.

The clickhouse vs postgresql for log analysis question isn't about which database is better. It's about which one matches the shape and scale of what you're asking.

Clickhouse vs postgresql for real time analytics 2026 is a question with an easy answer: if you need sub-second queries over billions of rows at high concurrency, ClickHouse wins and it isn't close. For clickhouse vs postgresql for large datasets 2026, the crossover point is real and it's around 150-200GB — after that, Postgres costs more in dollars and in SQL hair-loss.

Measure first. Migrate second. And build the pipeline so you can change your mind if the answer moves.

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