This article was originally published at sivaro.in

ClickHouse vs PostgreSQL Real-Time Analytics Use Cases

If you're choosing between ClickHouse and PostgreSQL for real-time analytics, here's the short version: Postgres handles transactional truth. ClickHouse handles analytical scale. Most teams I've worked with need both — and the pain comes from pretending one can do the other's job.

I've migrated three production systems from Postgres to ClickHouse since 2023. Two went smoothly. One cost us six weeks of rework because we underestimated how different the data models are. That's the lesson I want to save you from.

By the end of this piece, you'll know exactly when to pick each, how to think through the clickhouse vs postgresql real-time analytics use cases that actually matter, and what postgresql to clickhouse data migration best practices look like when you've done it a few times.

What Postgres Actually Is (and Isn't)

PostgreSQL is a row-store with MVCC. Every row you insert gets versioned. Every update writes a new tuple. Every index points to those tuples. That design is beautiful for OLTP — it's why your payment ledger, user table, and order state machine live in Postgres and should stay there.

It's also why dashboards slow down at 50 million rows.

At SIVARO, we ran a client's event analytics on Postgres 15 with a partitioned table by day. At 40M rows, queries were fine. At 400M, a 30-second dashboard refresh became 4 minutes. At 2 billion, the autovacuum couldn't keep up, bloat ate 60% of disk, and the replica lag crossed 90 seconds. That's the wall.

Postgres can do analytics. It just doesn't want to.

Columnar extensions like Citus and TimescaleDB help. So does partitioning plus BRIN indexes. But you're still writing rows to a heap and reading them back through the same engine that's also trying to serve your API.

What ClickHouse Actually Is (and Isn't)

ClickHouse is a columnar, MergeTree-based OLAP engine built at Yandex for web analytics. It stores data in parts, sorts by primary key order, and compresses aggressively. Our current production cluster ingests 180K events per second across 6 nodes and answers sub-second aggregations over 14 billion rows.

It is not a database for transactions. There are no real foreign keys. Updates are asynchronous mutations that rewrite parts. Joins are possible but expensive compared to denormalized wide tables. Point lookups by ID work, but they aren't the design goal.

If you try to run your user profile table in ClickHouse, you'll have a bad time.

The Core Trade-Off: Row Store vs Column Store

Here's the fundamental difference, without hedging.

Postgres reads a row by fetching all its columns. ClickHouse reads a column by fetching that column across all rows. For "give me user 4821's email," Postgres wins by a mile. For "give me average session duration by country for the last hour," ClickHouse wins by 100x or more.

Why the 100x? Three reasons.

First, column pruning. If your table has 40 columns and you query 3, ClickHouse touches 3 columns of disk. Postgres reads the whole row from the heap.

Second, compression. Columnar data compresses insanely well. Similar values sit next to each other. We see 8-12x compression versus raw on typical event data. Postgres TOAST helps with large text but doesn't come close.

Third, vectorized execution. ClickHouse processes data in batches of 65K rows using SIMD. Postgres processes one tuple at a time through its executor.

At first I thought the perf gap was a tuning problem — turns out it was architecture.

Where Postgres Still Wins for Real-Time Analytics

I don't want you to read this and rip Postgres out of your stack. There are real clickhouse vs postgresql real-time analytics use cases where Postgres is the correct answer.

Low-latency dashboards under 10M rows. If your analytics tables are small, Postgres with a couple of good indexes and materialized views answers in milliseconds. You don't need another system.

Mixed read/write with transactional guarantees. If you're computing analytics on data that must be consistent with your OLTP state — say, real-time fraud checks against the same rows your app updates — Postgres is the only sane choice. ClickHouse's eventual consistency via ReplicatedMergeTree will bite you here.

Complex joins across many dimensions. ClickHouse can join, but a 6-way join with small dimension tables is more ergonomic in Postgres. We had a customer analytics pipeline with 7 joins that ran in 800ms on Postgres and took 4 seconds on ClickHouse before we denormalized.

Ad-hoc queries by humans. Analysts writing fuzzy WHERE clauses and exploring hit Postgres better. ClickHouse punishes poor query patterns harder — a full scan on a 10-billion-row table is a full scan.

Where ClickHouse Destroys Postgres

Now the flip side.

Sub-second aggregations over billions of rows. Non-negotiable. Postgres can't get there.

High-ingest event pipelines. We've sustained 1M inserts/sec per ClickHouse node using async inserts and batching. Postgres tops out around 10-50K/sec per node before WAL becomes the bottleneck.

Time-series with high cardinality. Device IDs, session IDs, trace IDs. ClickHouse's primary key ordering plus skip indexes handle this. Postgres index bloat on high-cardinality columns is a career hazard.

Real-time dashboards with 5-50 concurrent users on big data. ClickHouse's query cache plus per-query memory limits make this predictable. Postgres under concurrent analytical load wrecks your OLTP traffic.

I'll be blunt: if your analytics queries are p95 above 2 seconds and your data is over 100M rows, you're fighting Postgres. Stop.

A Decision Framework You Can Actually Use

Forget the "it depends" answer. Here's what I tell clients.

Pick Postgres alone if:

• Your total analytical dataset is under ~50M rows
• You need transactional consistency with OLTP state
• You have fewer than 20 concurrent analytical users
• Your queries are point lookups or small aggregations

Pick ClickHouse alone if:

• Your data is append-only events, logs, or metrics
• You don't need row-level updates or deletes
• You can denormalize to wide tables
• Your queries are aggregations, not lookups

Pick both if (and this is 80% of real cases):

• Postgres is your source of truth for entities — users, orders, products
• ClickHouse is your analytics layer for events, telemetry, and derived metrics
• A CDC pipeline (Debezium, PeerDB, ClickHouse's Postgres connector) keeps the dimensions in sync

The PostgreSQL for Analytics vs ClickHouse Reality Check

Let me kill a myth. "Postgres can do everything ClickHouse does if you tune it right" is wrong, and I've paid to prove it.

In 2024, we tried to keep a client on Postgres with TimescaleDB hypertables instead of adding ClickHouse. Dataset was 6 billion events over 18 months. We tuned shared_buffers to 128GB, used columnar compression, partitioned by week, and built continuous aggregates.

Results: continuous aggregates refreshed in 40 seconds for hourly rollups. ClickHouse does the same rollup in 1.2 seconds on 4x less hardware.

Postgres for analytics vs ClickHouse isn't a fair fight at that scale. At small scale, it's not worth adding a second system. The crossover point for us is around 100M rows and 5-10 second query latency requirements.

Each system has a ceiling. Postgres's is lower and you hit it faster than you think.

Postgres to ClickHouse Data Migration Best Practices

This is where teams lose weeks. Here's how I'd do it again.

Model first, migrate second. Don't copy your Postgres schema. ClickHouse wants wide tables, LowCardinality types for repeated strings, and DateTime64 for precision. A table with 8 foreign keys becomes one denormalized table or a ClickHouse dictionary for lookups.

Use CDC, not batch dumps. We use PeerDB or Debezium reading from Postgres logical replication. Batch dumps work for one-time loads but you'll miss the replication lag management and incremental consistency.

Start with a shadow consumer. Run your ClickHouse pipeline in parallel for 2-4 weeks. Compare query results against Postgres daily. We caught three subtle timezone bugs this way.

Backfill in partitions, not one big query. A 2-billion-row backfill done as one INSERT SELECT from Postgres will OOM your Postgres replica. Chunk by day or hour, throttle to avoid overwhelming the source.

Validate row counts and checksums per partition. Sounds obvious. Half the teams we audit don't do it.

Here's a migration pattern we use:

-- ClickHouse target table with proper types
CREATE TABLE events (
    event_time DateTime64(3),
    user_id UInt64,
    event_type LowCardinality(String),
    country LowCardinality(String),
    properties String CODEC(ZSTD(3))
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
PARTITION BY toYYYYMMDD(event_time)
ORDER BY (event_type, user_id, event_time);

# Incremental backfill from Postgres by time window
import psycopg2
from clickhouse_driver import Client
from datetime import datetime, timedelta

pg = psycopg2.connect("postgresql://...")
ch = Client('clickhouse.internal')

window = timedelta(hours=1)
cursor_time = datetime(2025, 1, 1)

while cursor_time < datetime(2026, 9, 1):
    with pg.cursor(name='stream') as cur:
        cur.itersize = 50000
        cur.execute("""
            SELECT event_time, user_id, event_type, country, properties
            FROM events
            WHERE event_time >= %s AND event_time < %s
        """, (cursor_time, cursor_time + window))

        batch = []
        for row in cur:
            batch.append(row)
            if len(batch) >= 100000:
                ch.execute('INSERT INTO events VALUES', batch)
                batch = []

        if batch:
            ch.execute('INSERT INTO events VALUES', batch)

    cursor_time += window

Don't run this on your primary Postgres. Read from a replica and throttle.

Real Query Examples

Here's a dashboard query — average session duration by country, last 24 hours. On Postgres:

EXPLAIN ANALYZE
SELECT country,
       AVG(session_seconds) AS avg_duration,
       COUNT(*) AS sessions
FROM sessions
WHERE started_at > NOW() - INTERVAL '24 hours'
GROUP BY country
ORDER BY sessions DESC;

On 500M rows with proper partitioning and indexes, we measured 12-18 seconds on Postgres. Same data on ClickHouse:

SELECT country,
       avgMerge(session_agg) AS avg_duration,
       count() AS sessions
FROM sessions
WHERE started_at > now() - INTERVAL 24 HOUR
GROUP BY country
ORDER BY sessions DESC;

Sub-400ms.

The ClickHouse version uses an AggregatingMergeTree with avgState so we don't even scan raw rows. That's a design choice Postgres can't match — but it's only possible because the data is append-only and we accepted denormalization.

Gotchas Nobody Warns You About

ClickHouse's mutation model. ALTER TABLE ... UPDATE rewrites entire parts. On a 500GB partition, that's minutes. If you plan to update rows frequently, you've chosen wrong.

Postgres's autovacuum and bloat. Long-running analytical queries on your OLTP replica will block vacuum and bloat indexes. Set statement_timeout aggressively on the analytics connection.

ClickHouse's join memory. Joins spill to disk in recent versions but a bad join can still OOM a node. Cap with max_memory_usage at the user level.

Postgres's planner on partitioned tables. With 500+ partitions, planning itself takes hundreds of milliseconds. Too many partitions will kill you.

Both systems need serious monitoring. We use Prometheus with the standard exporters and alerts on merge queue depth (ClickHouse) and replication lag (both).

Cost Comparison at Real Scale

Hardware-wise, on AWS, we paid roughly:

• Postgres (r6i.4xlarge, 128GB, io2 storage, 3-node cluster with replica): ~$4,800/month to hit 400 QPS of moderate analytics with 2s p95 latency on 2 billion rows.
• ClickHouse (3x r7i.4xlarge, 128GB, gp3 storage): ~$2,900/month for 40 QPS of sub-second analytics on 14 billion rows.

ClickHouse is cheaper per analyzed datum. But you also pay for the pipeline — CDC infra, a metadata store for schemas, and engineers who know MergeTree. For small teams, Postgres wins on total cost until scale forces the choice.

When a Hybrid Beats Either

The mature answer for most companies at scale is hybrid, with clear boundaries.

Postgres holds entities and current state. ClickHouse holds events and derived analytics. A reverse sync (ClickHouse to Postgres) pushes back "user's last seen" or "order count" when the API needs it at request time. We use ReplacingMergeTree for that and a small Kafka topic for the reverse flow.

If you architect this well, Postgres never sees the 14 billion rows. ClickHouse never sees the write-heavy user profile table. Everyone's happy.

For the clickhouse vs postgresql real-time analytics use cases you'll actually face, this pattern covers about 80% of them.

FAQ

Can ClickHouse replace Postgres entirely?
No. No foreign keys, asynchronous updates, eventual consistency across replicas. If your app expects ACID and row-level updates, ClickHouse will hurt you.

Is Postgres fast enough for real-time analytics?
Below 50M rows, usually yes. Above 500M, rarely without serious tuning. Between those, it depends on query patterns and concurrency.

How long does a typical Postgres to ClickHouse migration take?
Our fastest was 3 weeks for a 40M row pipeline. Our slowest was 11 weeks for a 3-billion-row multi-tenant system with hourly backfills and validation. Budget 4-8 weeks realistically.

What's the best CDC tool for Postgres to ClickHouse?
PeerDB (now part of ClickHouse Inc.) is the smoothest we've used in 2026. Debezium works but you're bolting on Kafka, a sink connector, and a lot of ops overhead.

Does ClickHouse support updates and deletes?
Yes, via ALTER TABLE ... UPDATE/DELETE and lightweight deletes. Both are expensive on large tables. If you update frequently, you've picked the wrong engine.

What about DuckDB for this?
DuckDB is excellent for single-node analytical workloads under ~100GB. It's not a server, doesn't do concurrent writes well, and isn't a replacement for either system at production scale.

Can I run ClickHouse in Kubernetes?
Yes. ClickHouse operator works. We run on EKS with local NVMe instances. Watch out for storage class performance — gp3 at scale chokes.

Does Postgres have a columnar option that competes?
Hydra and Citus columnar storage help, but on our 6-billion-row benchmark, ClickHouse was still 10-30x faster for aggregations. Useful if you can't add another database — not a substitute.

The Bottom Line

For the clickhouse vs postgresql real-time analytics use cases most teams face, the decision comes down to scale and query pattern. Under 50M rows with transactional needs: Postgres. Over 100M append-only events with aggregation-heavy dashboards: ClickHouse. Both: the common case at scale, connected by CDC.

Stop trying to make one database do everything. Postgres is your source of truth. ClickHouse is your read-optimized analytical engine. Wire them together, validate the migration partition by partition, and monitor both like your revenue depends on it — because at scale, it does.

---

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