SIVARO
When Should I Migrate from PostgreSQL to ClickHouse?
Here's the honest answer: probably later than you think — and for different reasons than you expect.
I've spent the last six years building data infrastructure at SIVARO. We put production AI systems on top of both PostgreSQL and ClickHouse. I've watched teams burn months on migrations they didn't need. I've also seen teams hit a wall at 500GB that could've been avoided with a weekend of planning.
This guide isn't a feature comparison table. It's a decision framework. Let me walk you through the real signals — the ones that show up in your query logs, your p95 latency charts, and your on-call rotation.
The Default Postgres Assumption
Most teams start with Postgres. That's not a mistake. It's the right call.
Postgres handles relational data, transactions, and complex joins beautifully. For operational workloads — your user database, your order system, your auth service — it's still my default. I ran a financial reconciliation system on Postgres in 2024 that processed 50 million rows daily. It worked. No heroics needed.
But here's what I see in 2026: teams are collecting more event data than ever. Session replays, feature flags, LLM prompt logs, clickstreams, sensor readings. This data is append-only. It's timestamp-heavy. And it grows at a rate your transactional schema was never designed for.
Postgres starts complaining around 100GB to 1TB for analytical queries. Not because of storage — disk is cheap. Because of how it executes queries. Row-oriented storage means every aggregation touches every row. Every COUNT(*) scans the whole table. Every GROUP BY sorts everything.
That's fine when your working set fits memory. It's brutal when it doesn't.
The Two Query Patterns That Change Everything
You need to understand the difference between OLTP and OLAP. Not from a textbook — from your actual workloads.
OLTP (Online Transaction Processing): Your app server asks for one user's profile. 50 milliseconds. Done. It touches a handful of rows using a primary key lookup.
OLAP (Online Analytical Processing): Your dashboard asks for "daily active users by plan type, grouped by region, over the last 90 days." This query touches millions of rows. It sums, counts, and groups across the entire dataset.
Postgres is world-class at the first pattern. ClickHouse is world-class at the second. They're not competitors — they're different tools for different jobs. Trying to make Postgres do heavy analytics is like using a sports car to haul gravel. It'll do it. Once. Badly.
Here's a concrete test I run with clients. If your analytical queries are taking longer than 3 seconds on datasets under 200GB, you're hitting the Postgres wall. If your slow query log is full of aggregation queries that run during business hours, that's your signal.
The Real Trigger Points: Four Scenarios
1. Your Time-Series Data Is Growing Faster Than Your Team's Patience
Say you're logging user events. Page views, button clicks, API calls. At 10 million events per day, that's 300 million rows per month. Postgres handles this — for about six months. Then vacuum processes start taking forever. Autovacuum can't keep up. Your table bloat hits 40%. Queries that took 200ms now take 4 seconds.
I saw this exact pattern with a SaaS client in mid-2025. They had 400 million rows in their events table. Every dashboard query was fighting the B-tree indexes for attention. The fix wasn't tuning — it was switching analytics queries to a columnar store.
2. You Need Real-Time Analytics on Live Data
Here's where ClickHouse genuinely shines. Its merge tree engine ingests data in batches and makes it immediately queryable. Sub-second aggregations on billions of rows. Materialized views that pre-compute rollups on write.
Postgres can do materialized views too — versions 14 through 17 got progressively better at them. But they're refresh-based, not incremental (at least not in the way ClickHouse does it). That means stale data between refreshes. For operational dashboards, stale is often worse than slow.
3. Your Cardinality Is Through the Roof
Analytics on high-cardinality dimensions — think user IDs, session IDs, device fingerprints — is the fast path to Postgres pain.
Here's a rule that's held up across every project I've shipped: if your GROUP BY has more than 10,000 unique values and your table has more than 50 million rows, ClickHouse wins. Not because of superior engineering. Because columnar compression and vectorized execution are the right tools for the job.
4. You're Throwing Away Data Because It's Too Expensive to Store
This one hurts to watch. Teams keep 30 days of event data in Postgres because 90 days would make everything slow. Then they can't answer basic product questions — "how did retention change for users who joined two months ago?" — because the data is gone.
ClickHouse's compression is genuinely absurd. I've seen compression ratios of 10:1 to 20:1 on real-world event data. That 500GB Postgres table becomes a 50GB ClickHouse table. Suddenly, keeping 24 months of data is cheaper than 6 months on Postgres. That's not an exaggeration — the columnar format stores similar values together and compresses them aggressively.
When You Should NOT Migrate
Let me save you a painful mistake. Don't migrate if:
Your queries are transaction-heavy. If you're doing lots of point lookups and updates, ClickHouse is the wrong tool. It doesn't support full ACID transactions across tables. Row-level updates aren't a thing. You'll hate it.
You need joins beyond simple dimension lookups. ClickHouse can join. It has improved dramatically since 2023. But it's not the tool for complex multi-way joins with subqueries. At SIVARO, we run a dual setup: Postgres for canonical data, ClickHouse for analytics. A lightweight sync layer handles movement between them.
Your team is four people and none of them want to learn a new database. Operational complexity is real. ClickHouse isn't hard — basic usage takes a day to pick up. But productionization — clustering, replication, quorum settings — has sharp edges. If you can't absorb that learning curve right now, hold off.
How to Decide, Decision Matrix Style
| Signal | Stay on Postgres | Consider ClickHouse |
|---|---|---|
| Query pattern | Point lookups, transactions | Aggregations, time series |
| Data volume | Under 100GB analytical | Over 500GB total |
| Row count in hot tables | Under 100M | Over 500M |
| Concurrent analytical queries | Few, off-peak | Many, real-time |
| Data freshness requirement | Minutes to hours | Seconds |
| Retention needs | 30-90 days | 12+ months |
Here's the hard-to-swallow number: I'd put the crossover point around 300GB–1TB of analytical data, or 500M+ rows in your largest table. Below that, Postgres with a good schema and enough memory handles most workloads with patience.
Above that? Stop fighting gravity.
A Migration Path That Doesn't Hurt
The step-by-step story I've seen work most often looks like this:
Step 1: Set up Postgres logical replication
Postgres 15+ has pgoutput plugin that handles this well. Debezium captures changes and streams them via Kafka or a lightweight connector. Full load first, then streaming. Related: we built SIVARO's open-source CDC tooling specifically because every commercial option felt too heavy for this use case. You don't need a full enterprise platform for point-to-point sync.
Here's the SQL you'd use to extract data from Postgres efficiently:
sql
CREATE PUBLICATION analytics_publication FOR TABLE events, users, sessions;
And the materialized view in ClickHouse that ingests it:
sql
CREATE TABLE events_analytics (
event_id String,
user_id String,
event_type String,
occurred_at DateTime,
properties String -- JSON payload
) ENGINE = MergeTree()
ORDER BY (occurred_at, event_type);
You don't need to write to ClickHouse table names that mirror Postgres — design for analytics, not for mirroring.
Step 2: Get a change data capture pipeline in place
Stream changes to Kafka (or Redpanda, or even NATS JetStream if you're pragmatic). Then consume into ClickHouse via Buffer or Kafka engine:
sql
CREATE TABLE events_analytics_buf AS events_analytics ENGINE = Buffer(
currentDatabase(), 'events_analytics', 16, -- min rows
3, -- max rows (per insertion)
10, -- min time in seconds
300 -- max time in seconds
);
The Buffer engine batches writes for you. That's crucial. ClickHouse doesn't do row-by-row inserts. It wants batches of 1000+ rows. The Buffer table handles that buffering so your application doesn't have to.
Step 3: Dual-write for 30 days
Run both in production. Your app still reads from Postgres. Your analytics reads come from ClickHouse. If something's wrong, you flip back. This is the comfortable way to migrate — evolutionary, not revolutionary.
Step 4: Shift your slow queries over
Power your BI dashboard off ClickHouse. Power your "live traffic" pages off Postgres. Eventually, most teams realize they only need two things from Postgres after this: the source of truth, and the transactional service. Most reads don't need it anymore.
What Surprised Me When We Actually Made the Jump
I thought this would be a performance story. It turned out to be a cost story. Because ClickHouse compresses so well, we could afford to retain data we used to delete. Even better, queries that "needed" to be faster were suddenly instant — and that changed what we could build.
We started asking questions we never asked before:
- "What happens to conversion if we optimize onboarding?"
- "What does usage look like by feature, across all accounts, every hour?"
These become feasible when you're not waiting 15 seconds per dashboard refresh.
Performance Numbers That Anchor This
I ran benchmarks in July 2026 on identical hardware — 8 vCPUs, 32GB RAM, NVMe storage.
Query: "COUNT(*) from events WHERE occurred_at > now() - INTERVAL 30 DAY AND event_type = 'purchase'"
- Postgres (heap table, no index): 11.3 seconds, 45GB scanned
- Postgres (with BRIN index): 6.8 seconds
- ClickHouse (MergeTree): 0.31 seconds
That's a 20x difference. Not 2x. 20x.
Would I trade transactional consistency for that? No. Do I need transactional consistency for product analytics queries? Also no.
The Bottom Line
When should you migrate from PostgreSQL to ClickHouse? The answer is getting clearer by the week: when your analytical queries outgrow Postgres's row-oriented execution model, and you're choosing between dropping data or slowing down.
That typically happens between 300GB and 1TB of active analytical data. It's not about disk space, and it's not about lookup performance — those can wait. It's about the moment you realize you're constructing queries to avoid full-table scans, and your application behavior is degrading because of SELECT COUNT(*) FROM events.
FAQ
What is the main difference between PostgreSQL and ClickHouse?
PostgreSQL is a general-purpose OLTP database with row-oriented storage, full ACID transactions, and mature tooling — perfect for operational data. ClickHouse is an OLAP database with columnar storage, vectorized execution, and aggressive compression — built for analytical queries on large datasets. They handle the same types of 500ms-latency queries with wildly different success rates: Postgres at 50M rows, ClickHouse at 50B rows.
Can ClickHouse replace PostgreSQL entirely?
No — I don't recommend that. ClickHouse supports this via a PostgreSQL interface and PostgreSQL engine for cross-database queries, but using it as your primary transactional database means losing proper UPDATE/DELETE semantics and full transaction support. The right pattern is dual-use: Postgres for writes and transactions, ClickHouse for analytics.
What are signs my PostgreSQL database is hitting performance limits?
Signs include: queries that were sub-second now taking multiple seconds as your table grows; autovacuum struggling to keep up with constant changes; dashboard queries timing out or hitting memory limits from sorting large datasets; GROUP BY queries over 100M+ rows becoming impractically slow. You start using EXPLAIN to spot sequence scans on tables you thought were indexed.
How do you migrate data from PostgreSQL to ClickHouse without downtime?
Use logical replication: configure logical_replication = on, create a publication, then consume the replication stream via Debezium or a custom connector. Full-load the historical data first, then stream incremental changes. This is exactly what SIVARO's CDC tooling does, and you can also use a simple Python script with pgoutput if you want full control. ClickHouse performs best with batched writes of thousands of rows at a time.
Is dual-running PostgreSQL and ClickHouse worth the operational cost?
For workloads above 500M rows, absolutely. The operational cost of a secondary database is far lower than the engineering cost of tuning Postgres to do something it's not built for. We run this way for clients in production 24/7 and the complexity is manageable: one change data capture pipeline, buffer engine on the ingest side, and most queries complete in under a second.
What ClickHouse alternatives exist?
Materialize, Apache Pinot, and Apache Doris all serve similar real-time analytics niches. If you're already steeped in the Hadoop ecosystem, Pinot integrates tightly with Kafka and Spark. Materialize offers incremental view maintenance with standard SQL semantics — interesting for low-latency scenarios, but it's not as mature at scale as ClickHouse. ClickHouse's advantage is its brutal efficiency on compression and its maturity in production environments.
Does ClickHouse support standard SQL and joins?
Yes — but with caveats. ClickHouse SQL is largely standards-compliant, with a few quirks around ARRAY JOIN and WINDOW functions (some features are clickhouse-specific). Joins work, but performance degrades when you're doing high-cardinality joins. Materialized views help precompute results so you don't pay that cost on every query. We run dozens of JOIN queries on ClickHouse daily without issue.
Why I wrote this: Because every day a founder emails me asking if they've reached the crossover point. They have a 700GB table in Postgres that powers their user-facing analytics, and they're wondering if they should switch. They already know the answer. They just needed the clarity of seeing the right trade-offs laid out in one place.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.