Can ClickHouse Replace PostgreSQL for Real Time Analytics

Can ClickHouse Replace PostgreSQL for Real Time Analytics

If you've ever watched a Postgres dashboard crawl at 40 million rows, you already know the feeling. I've been there. In 2021, SIVARO was running a fleet telemetry platform for a logistics client on Postgres 13 — beautiful relational integrity, gorgeous transactions, and a GROUP BY query that took 11 seconds on a Tuesday afternoon. The client wanted sub-second. So we did the boring thing: we benchmarked ClickHouse against the same workload. ClickHouse returned the same query in 180 milliseconds.

That's the story in miniature. But "can ClickHouse replace PostgreSQL for real time analytics" is not a yes/no question. It's a question about which workload you're actually running, how much you've invested in Postgres, and whether your team can stomach running two databases instead of one.

This article breaks down the real differences, gives you migration patterns that actually work, and tells you where I've watched people get burned.

What "Real Time Analytics" Actually Means

Most teams say "real-time analytics" and mean one of three things:

• Dashboards refreshing every few seconds over aggregated data
• Ad-hoc exploratory queries across billions of rows
• Event-level streaming with sub-second latency (fraud detection, anomaly flags)

Postgres can do the first. It struggles with the second. It's the wrong tool for the third. ClickHouse was built for the second and third and is overkill for the first.

The confusion comes from calling all three "analytics." They have wildly different access patterns. OLTP (transactions) wants row-level lookups, strong consistency, and frequent updates. OLAP (analytics) wants columnar scans, compression, and append-heavy writes. Postgres is a world-class OLTP database that happens to have decent analytical extensions. ClickHouse is a purpose-built OLAP engine that has zero interest in being a transactional database.

ClickHouse vs PostgreSQL for Large Datasets — The Real Differences

Let me skip the marketing slides and go straight to what matters when you're choosing.

Storage layout. Postgres stores rows together. ClickHouse stores columns together. If you query SELECT country, SUM(revenue) FROM events, Postgres reads every byte of every row and throws most of it away. ClickHouse reads only country and revenue columns. On a 500GB table, that's often a 20-50x I/O reduction before you even get to vectorized execution.

Compression. ClickHouse columnar storage compresses 5-10x on typical analytics data using Delta, LZ4, and ZSTD codecs. Postgres TOAST compresses large values but doesn't compress your working set the same way. I've watched a 2.4TB Postgres event table become a 380GB ClickHouse table with identical query results.

Indexing. This is the one that surprises people. ClickHouse doesn't have B-tree indexes the way Postgres does. It has a sparse primary index over sorted data (the ORDER BY key) plus skip indexes for secondary filtering. The mental model is totally different. You don't index columns — you design a sort key that matches your query filter patterns. Get this wrong and ClickHouse feels slow. Get it right and it feels like cheating.

Concurrency. Postgres scales to maybe a few hundred concurrent analytical queries on a beefy box before the connection pool becomes a nightmare. ClickHouse handles thousands of concurrent point queries fine because each query is cheap. But it also doesn't have the same MVCC isolation guarantees.

Joins. This is where ClickHouse historically embarrassed itself. For years, joins were the weak spot. As of ClickHouse 23.x (and dramatically better in 24.x and 25.x), hash joins and parallel hash joins work well — but they still want one side to be small. If you're joining two 100M-row tables, you'll feel pain unless you've modeled around it with dictionaries or denormalization. ClickHouse's own join docs are honest about this.

Writes and updates. Postgres loves updates. ClickHouse hates them. ALTER TABLE ... UPDATE in ClickHouse is an asynchronous mutation that rewrites entire parts. If you need row-level UPDATE 1,000 times per second, stop reading and stay on Postgres.

When ClickHouse Wins — Specifics

Here's where I've seen ClickHouse make Postgres look broken:

• Dashboard aggregation over 10B+ rows. Client in 2024: daily active user rolling counts over 8 billion events. Postgres materialized views refreshed in 14 minutes. ClickHouse with a SummingMergeTree did it live in 90ms.
• Time-series slicing. Anything with WHERE timestamp BETWEEN x AND y GROUP BY time_bucket is ClickHouse's home turf.
• Log and event ingestion. 200K events per second is normal for ClickHouse on a 3-node cluster. Postgres tops out around 10-20K/sec on the same hardware.
• Cardinality-heavy GROUP BY. GROUP BY user_id across 50 million distinct users? Postgres chokes on the hash table. ClickHouse chews through it.

When Postgres Wins — Don't Ignore This

• You need transactions. Real ACID with rollback. ClickHouse's transaction support is minimal.
• Data mutates often. Order status updates, user profile edits, inventory decrements. Postgres territory.
• Your data fits. Under 100M rows and under 50GB? Postgres with a couple of indexes and a read replica will serve you fine for years. I've talked to engineers who spent six months migrating to ClickHouse to save 400ms on a dashboard that nobody refreshed more than once an hour.
• You have one database and want to keep it that way. Two systems means two on-call runbooks, two backup strategies, two upgrade calendars. That's real cost.

Can ClickHouse Replace PostgreSQL for Real Time Analytics — The Honest Answer

Yes, if your workload is analytical and your data is large. No, if you also need to run your application's transactional load.

The better framing: ClickHouse replaces the analytics portion of what you might have tried to make Postgres do. It doesn't replace Postgres as your system of record.

Most teams I work with end up with both. Postgres runs the app. ClickHouse runs analytics. Data flows one direction, from Postgres to ClickHouse, via CDC.

Three Architecture Patterns That Actually Work

Pattern 1: Postgres as OLTP, ClickHouse as Analytics

The classic. Your app writes to Postgres. A CDC pipeline (Debezium, PeerDB, or ClickPipes) streams changes into ClickHouse. Dashboards hit ClickHouse.

Postgres (writes) ──CDC──> ClickHouse (reads)
     │                          │
     └───── app queries ────────┘

This is boring and correct. It's the pattern I recommend 80% of the time.

Pattern 2: ClickHouse with Postgres Table Engine (Small Scale)

ClickHouse has a PostgreSQL table engine that queries Postgres directly. Useful for joining small dimensional tables:

sql
CREATE TABLE pg_customers (
    id UInt64,
    name String,
    plan String
) ENGINE = PostgreSQL('postgres-host:5432', 'appdb', 'customers', 'user', 'password');

SELECT c.plan, count() AS events
FROM events e
JOIN pg_customers c ON e.customer_id = c.id
WHERE e.ts >= now() - INTERVAL 7 DAY
GROUP BY c.plan;

Don't pull millions of rows through this. It's for dimensions, not facts.

Pattern 3: Postgres as Write Buffer, ClickHouse as Warehouse

For very high ingest, buffer in Postgres and batch-load into ClickHouse every 30 seconds via a worker. Simpler than Kafka for teams that already have Postgres.

ClickHouse PostgreSQL Migration Best Practices

I've run four of these. Here's what I'd tell a friend.

Model around the query, not the entity. In Postgres you normalize. In ClickHouse you denormalize aggressively. If your analytics query always joins events to users to get country, flatten country into the events table at ingest time. Storage is cheap. Joins are not.

Pick your ORDER BY key thoughtfully. This is the single biggest performance decision. Put your most common filter column first, then your time column. If most queries are WHERE tenant_id = ? AND ts > ?, then ORDER BY (tenant_id, ts) — not (ts, tenant_id).

sql
CREATE TABLE events (
    tenant_id UInt32,
    ts DateTime,
    event_type LowCardinality(String),
    user_id UInt64,
    country LowCardinality(String),
    amount Decimal(18, 4)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(ts)
ORDER BY (tenant_id, ts, event_type);

Partition by time, not by ID. Monthly partitions are usually right. Daily is right for very high volume. Never partition by user_id or anything high-cardinality.

Use LowCardinality(String) for anything with fewer than ~10K distinct values. It's free compression.

Backfill in chunks, then cut over. Don't do a big bang. Run dual-write for a week. Reconcile counts. Then flip reads.

Watch for Nullable columns. They slow everything down. If you can use a sentinel value (0, empty string) instead of NULL, do it.

Test your real queries, not SELECT count(*). Count is a lie. Every database is fast at counting. Run your actual dashboard queries against both and compare.

A Working Ingestion Example

Here's a minimal pipeline I've shipped more than once — Postgres → ClickHouse via a batch worker:

python
import psycopg
import clickhouse_connect
from datetime import datetime, timedelta

pg = psycopg.connect("postgresql://app:pass@localhost/appdb")
ch = clickhouse_connect.get_client(host="ch-host", port=8123)

last_sync = datetime.utcnow() - timedelta(minutes=5)

with pg.cursor() as cur:
    cur.execute("""
        SELECT id, tenant_id, created_at, event_type, user_id, country, amount
        FROM events
        WHERE created_at > %s
        ORDER BY created_at
    """, (last_sync,))
    rows = cur.fetchall()

if rows:
    ch.insert(
        "events",
        rows,
        column_names=["id", "tenant_id", "ts", "event_type", "user_id", "country", "amount"],
    )
    print(f"Synced {len(rows)} rows at {datetime.utcnow()}")

The naive version. For production, track last_sync in a state table, handle idempotency with ReplacingMergeTree on id, and batch in 100K-row chunks.

Query Patterns That Separate the Two

Here's a query that would take Postgres 30+ seconds on a 5B-row table:

sql
SELECT
    toStartOfHour(ts) AS hour,
    country,
    count() AS events,
    sum(amount) AS revenue,
    uniqExact(user_id) AS users
FROM events
WHERE ts >= now() - INTERVAL 24 HOUR
  AND tenant_id = 42
GROUP BY hour, country
ORDER BY hour DESC, revenue DESC
LIMIT 100;

On ClickHouse with ORDER BY (tenant_id, ts, event_type), this scans roughly 1/30th of the table (tenant filter prunes most parts, time range prunes the rest). On a decent single node, this is 200-400ms. Postgres with a B-tree on (tenant_id, created_at) will do it in 8-15 seconds because it has to fetch and reassemble every row.

The uniqExact(user_id) is the killer. Postgres keeps a full hash set. ClickHouse has specialized aggregation states and can parallelize across cores.

Cost Math

I get asked this constantly. Rough numbers from a 2024 build:

• Postgres on db.r6g.4xlarge (16 vCPU, 128GB) with 2TB gp3: ~$1,400/month
• ClickHouse Cloud equivalent (production tier, similar data volume): ~$900-1,300/month
• Self-hosted ClickHouse on 3× c6i.4xlarge: ~$1,100/month plus your time

So ClickHouse isn't automatically cheaper. The savings come from being able to serve 10x the query load on the same hardware, which downstream means you don't scale Postgres horizontally with Citus or read replicas.

At SIVARO we run the numbers honestly for every client. About half stay on Postgres alone. That's fine.

FAQ

Can ClickHouse replace PostgreSQL entirely for my application?
No. ClickHouse doesn't have proper transactions, foreign keys, or efficient row-level updates. It's an analytics engine. Use it alongside Postgres, not instead of it.

How much data before ClickHouse is worth it?
In my experience, around 100-500M rows or 50-100GB of analytics data. Below that, Postgres with good indexes will often be within 2-3x of ClickHouse, and the operational cost of two databases isn't worth it.

Can ClickHouse handle real-time inserts?
Yes. ClickHouse ingests hundreds of thousands of rows per second in batching-friendly patterns. But it batches inserts internally, so a single-row insert per HTTP request is wasteful. Buffer them.

Does ClickHouse support UPDATE and DELETE?
Technically, via mutations, but they're expensive — they rewrite data parts. Use ReplacingMergeTree for upserts and CollapsingMergeTree for deletes. Don't run frequent mutations.

What about ClickHouse's Postgres wire protocol?
ClickHouse has a Postgres-compatible wire protocol (added in 2023), so some Postgres clients can connect. It's not a full Postgres compatibility layer — no transactions, no full SQL semantics. Useful for migrating BI tools.

Is ClickHouse ACID?
Inserts are atomic at the part level. Larger transactions aren't supported. Don't use it for financial ledgers.

What's the best CDC tool from Postgres to ClickHouse?
For most teams I recommend PeerDB (now part of ClickHouse Inc.) or Debezium with Kafka. ClickHouse's own ClickPipes is great if you're on ClickHouse Cloud.

Can I just use Postgres with TimescaleDB instead?
For time-series under a few billion rows, absolutely. TimescaleDB is a genuinely good extension. The crossover point where ClickHouse pulls ahead is around 5-10B rows, or when you need high-cardinality GROUP BY at scale.

The Decision Framework I Actually Use

Ask yourself these five questions in order:

1. Is my data over 100GB or 500M rows of analytics data? No → stay on Postgres.
2. Do I need row-level updates on that data at high frequency? Yes → stay on Postgres, or split the mutable and immutable parts.
3. Do I need sub-second dashboards over the full dataset? No → Postgres with materialized views probably works.
4. Can my team operate a second database? No → stay on Postgres.
5. Does my access pattern match columnar (aggregations, scans, time-series)? Yes → ClickHouse.

If you answer yes to 1, 3, and 5, and no to 2 and 4 — you're a ClickHouse shop.

What I've Learned the Hard Way

Twice I've watched teams try to replace Postgres entirely with ClickHouse. Both rolled back within six months. The application logic assumed transactions. The dashboards were fine. The order processing was not.

Twice I've watched teams hold off on ClickHouse for two years because "we might need Postgres features later." Both ended up migrating anyway, having burned tens of thousands on vertical scaling in the meantime.

So can ClickHouse replace PostgreSQL for real time analytics? Only the analytics half. That half, it replaces spectacularly. The transactional half, it never will. Stop trying to make ClickHouse be Postgres. And stop trying to make Postgres be ClickHouse. Pick the right tool for each job, wire them together with a boring CDC pipeline, and get back to shipping features.

Postgres and ClickHouse aren't competitors. They're coworkers. The teams that figure that out first move faster than everyone else.

---

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