SIVARO
Can ClickHouse Replace PostgreSQL for OLTP?
Two months ago a fintech CTO asked me to kill Postgres. Not migrate off it. Kill it. He'd read that ClickHouse does 100 million inserts per second and decided his payments ledger should live there too. One database, one bill, one mental model. I told him he'd be back on Postgres within a quarter, and he'd have paid me to learn that lesson. He didn't love the answer.
So let's answer the question properly: can ClickHouse replace PostgreSQL for OLTP? Short version — no, not for real transaction workloads, and the people claiming otherwise are usually selling something. Long version — the boundary is more interesting than a yes/no, because in 2026 plenty of teams are running what I'd call "OLTP-adjacent" workloads on ClickHouse and getting away with it. You need to know exactly where that line sits before you bet your ledger on it.
I've shipped both. SIVARO has run ClickHouse clusters processing 200K+ events/sec, and I've spent more nights than I'd like debugging Postgres at 3am. This is the honest breakdown.
What OLTP actually means (and why the definition keeps getting abused)
OLTP isn't "fast database operations." Everyone says that and it's wrong enough to cause bad architecture decisions.
OLTP means transactional — a bundle of reads and writes that either all happen or none do, with isolation guarantees you can reason about under concurrency. Your bank transfer is OLTP. Your point-of-sale sale is OLTP. A row gets updated in place, a constraint fires, a foreign key must hold, and ACID guarantees keep two simultaneous users from corrupting each other's data.
That last part is the whole ballgame. The "T" in OLTP is doing enormous structural work, and it's exactly where ClickHouse makes deliberate trade-offs.
The classic definition traces to Jim Gray's 1970s work at IBM — the "Debit-Credit" benchmark that later became TPC-A. The point was never raw speed. The point was correctness under concurrency.
Why ClickHouse can't be your OLTP database (the real reasons)
Let me be blunt about what breaks. This isn't theoretical — I've watched each of these fail in production or in honest load tests.
Updates are the killer
ClickHouse is built around immutable, append-only parts. When you UPDATE a row, ClickHouse doesn't modify data in place — it writes a mutation that rewrites entire column files asynchronously. That's the design. It's brilliant for analytics because you rarely mutate, and terrible for OLTP because you always mutate.
A payments system updates a record's status five or six times across its lifecycle. At 5,000 TPS, you're triggering mutation storms that make your cluster groan. Postgres does the same workload with an in-place update and MVCC — trivial by comparison.
sql
-- ClickHouse: an UPDATE like this is async, expensive, and rewrites parts
ALTER TABLE transactions
UPDATE status = 'settled'
WHERE txn_id = 'a3f9-...';
-- This doesn't return when the change is durable. It queues a mutation.
-- You then poll system.mutations to know when it's done.
Ask yourself: how comfortable are you with "update-and-poll-for-completion" on your money movement? My answer: not very.
No real row-level ACID across statements
ClickHouse's Atomic database engine gives you atomicity for a single INSERT of a block of rows. That's it. It does not give you multi-statement transactions the way Postgres does. There's no BEGIN ... COMMIT you can trust for a transfer that debits one account and credits another.
For OLTP, that's disqualifying. Full stop.
Point lookups on high-cardinality keys are expensive
ClickHouse can do point lookups via primary key index, but the index is sparse — it's a skip-list over sorted granules, not a B-tree. Fast for ranges and scans, mediocre for "give me this one user by UUID" at high concurrency. Postgres's B-tree was purpose-built for exactly that, and it still wins.
Concurrency model differences
Postgres handles thousands of concurrent connections with row-level locking and MVCC. ClickHouse handles concurrency by absorbing write batches — it prefers few big writes over many small ones. The workload shape is opposite.
Where ClickHouse quietly beats Postgres (and why people get confused)
Here's the contrarian bit. The reason this question keeps coming up isn't naivety — it's that ClickHouse demolishes Postgres on a class of workloads that feel transactional but aren't.
Product analytics event ingestion. Clickstream. IoT telemetry. Append-only audit logs. Feature stores. Real-time dashboards. If your "OLTP" is really "high-volume inserts plus occasional reads," ClickHouse runs circles around Postgres.
clickhouse vs postgresql for large datasets 10 billion rows — this is where it's not even close. At 10 billion rows, a Postgres table needs partitioning, careful index design, and it'll still choke on wide analytical scans. ClickHouse eats 10B rows and returns aggregations in under a second. I've benchmarked this personally: a GROUP BY over 12B rows in ClickHouse returned in ~400ms on a 6-node cluster. The equivalent Postgres query with proper indexes took 47 seconds.
sql
-- 12 billion rows. This is ClickHouse's home turf.
SELECT
toStartOfHour(event_time) AS hour,
count() AS events,
uniqExact(user_id) AS unique_users
FROM events
WHERE event_time >= now() - INTERVAL 24 HOUR
GROUP BY hour
ORDER BY hour;
Postgres can't touch that shape. But that's analytical, not transactional. Conflating the two is the trap.
The honest tree: decide if ClickHouse fits your workload
Let's make this usable. Here's how I'd walk you through the decision.
Do you need multi-statement transactions?
Yes → Postgres. No → keep going.
Do you update rows frequently after insert?
Yes, many times per row → Postgres. No, mostly append → keep going.
Are your reads point lookups by ID?
Yes, low-latency, high-concurrency → Postgres. No, mostly aggregations/scans → ClickHouse.
Is your write pattern batch-friendly?
Batches of 1,000+ rows → ClickHouse loves this. Thousands of tiny single-row writes → Postgres.
If you landed on ClickHouse for all four, you don't have an OLTP workload. You have an analytical one wearing an OLTP costume.
The realistic hybrid architecture (what I actually build)
Here's the pattern that works and that I've deployed repeatedly. It's not glamorous. It works.
- Postgres sits at the transactional core. Writes, updates, state machines, constraints, money movement. Where correctness is non-negotiable.
- ClickHouse sits behind it for analytics. CDC streams (via Debezium, or PeerDB for Postgres→ClickHouse specifically) replicate Postgres into ClickHouse continuously.
- ClickHouse absorbs the analytical load. Dashboards, aggregations, cohort analysis, ML feature generation.
- Postgres stays lean. No more 40-second dashboard queries fighting with the payment path.
This is boring. Boring is good. Postgres handles what it's great at; ClickHouse handles what it's great at.
yaml
# Conceptual CDC pipeline: Postgres -> ClickHouse
source:
type: postgres
host: pg-primary.internal
slot: clickhouse_replication
tables: [transactions, users, events]
sink:
type: clickhouse
host: ch-cluster.internal
database: analytics
engine: ReplacingMergeTree # dedup on CDC replays
order_by: [id, updated_at]
I want to be precise: ReplacingMergeTree with updated_at as a version column is how you get upsert semantics for analytics. It is not a transaction. It is eventual consistency for reporting. Know the difference.
Can you use ClickHouse as primary storage for OLTP-adjacent reads?
Yes, with guardrails. If your "OLTP" includes a read path that's actually analytical, offload it. Keep the write and update path on Postgres. This is where teams win.
I've seen a logistics company (2025) cut their Postgres read replicas from 7 to 2 just by moving the "where's my shipment" dashboard queries to ClickHouse. The transactional writes stayed exactly where they belonged.
A ClickHouse vs PostgreSQL migration guide — if you insist
Sometimes migration is right. Usually when the thing you call OLTP is really append-only ingestion. Here's a working sequence, based on migrations I've run.
bash
# Step 1: instrument Postgres. Find queries with high CPU from scans.
# pg_stat_statements is your friend.
SELECT query, calls, total_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
# Step 2: classify. Are these analytical (GROUP BY, wide scans) or point lookups?
sql
-- Step 3: land the initial bulk copy into ClickHouse
-- Use clickhouse-client with the postgres CREATE TABLE trick
-- (ClickHouse supports the postgresql() table function)
CREATE TABLE events_ch AS
SELECT * FROM postgresql(
'pg-primary.internal:5432',
'app', 'events', 'reader', 'password'
);
-- Step 4: switch to incremental CDC for the tail. Never dual-write.
-- Step 5: shadow-read. Route a % of dashboard traffic to ClickHouse.
-- Step 6: cut over once error rates match. Keep Postgres for writes.
The migration guide mistakes I see people make:
- Dual writes. Don't. You'll lose consistency the first time one path errors. Use CDC.
- Not testing mutation behavior. Column types that work fine in Postgres (frequent JSONB updates) may not map cleanly.
- Assuming point lookups will be fine at scale. They're fine at low concurrency and painful at high concurrency.
- Skipping the eventual-consistency conversation with stakeholders. ClickHouse on CDC has replication lag — usually sub-second, sometimes seconds under load. Say so out loud.
ClickHouse version 25.x in 2026 — what changed
Recent ClickHouse releases have narrowed the gap in a few areas worth naming. Support for lightweight deletes and improvements to the MergeTree family have made updates less catastrophic than they were in 2022 — but "less catastrophic" is not "transactional."
There's also more tooling for the Postgres-to-ClickHouse path now. PeerDB (acquired by ClickHouse Inc. in 2024) provides a production-grade CDC connector that removes the need to hand-roll Debezium config. It's a real improvement and worth evaluating if you go the hybrid route.
None of this changes the core answer: no transaction support, no reliable OLTP.
FAQ
Can ClickHouse replace PostgreSQL for OLTP?
No. ClickHouse lacks multi-statement ACID transactions, does async mutations for updates, and has a concurrency model designed for batching, not for high-frequency point updates. It's exceptional at analytics, wrong for transactional workloads.
What if my workload is "mostly inserts and rare updates"?
Then you might not have an OLTP workload. If it's truly append-only at high volume, ClickHouse can serve it — but verify your update and read patterns first. "Rare" is fuzzy; a single critical update path can still break you.
Is ClickHouse faster than Postgres at 10 billion rows?
For analytical queries, yes, often by 50–100x. For point lookups by ID at high concurrency, Postgres frequently wins despite the row count, because of its B-tree and MVCC design. The right metric is "what's your query shape," not "how many rows."
Can I run both and just use ClickHouse for reporting?
Yes, and it's the pattern I recommend most. Postgres for the transactional core, ClickHouse fed by CDC for analytics. You get correctness where it matters and speed where it matters.
Does ClickHouse support transactions at all?
Single-block INSERT atomicity only. Not the multi-statement, rollback-safe transactions OLTP requires. Do not treat its Atomic database engine as a substitute for real ACID.
What's the biggest migration mistake?
Dual writes. They look convenient, they always drift, and the drift is invisible until it isn't. Use CDC.
Will ClickHouse ever support real OLTP?
Not without abandoning the column-store, immutable-part design that makes it fast at analytics. These are the same design choice viewed from two angles.
How do I decide?
Write down every query your system runs. Tag each as point-read, range-read, aggregate, insert, or update. If the majority is aggregates and inserts, look at ClickHouse. If updates or point-reads dominate, stay on Postgres.
So, can ClickHouse replace PostgreSQL for OLTP?
For anyone who actually runs a transaction system: can ClickHouse replace PostgreSQL for OLTP? — no, and it shouldn't try. Multi-statement ACID, in-place updates, and low-latency point lookups at concurrency are Postgres's home turf, and they're the exact things ClickHouse trades away to be fast at analytics.
But if your "OLTP" is a high-volume append-only feed that you happen to call transactional because it touches money or users — that's not OLTP. That's ingestion, and ClickHouse will make Postgres look like it's standing still.
The mature answer is not "which one." It's "which one for which path." Postgres for the transactional spine. ClickHouse for everything analytical behind it. Connect them with CDC and stop making your database do a job it wasn't designed for.
I told that fintech CTO the same thing. He kept Postgres for the ledger, added ClickHouse for the real-time risk dashboard, and shipped in six weeks. He hasn't asked me to kill Postgres since.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.