SIVARO
Can ClickHouse Replace PostgreSQL? Yes, But You're Asking the Wrong Question
I've spent the last eight years designing data systems for clients who ask this exact question. They come to SIVARO with a database that's choking, a dashboard that's crawling, and a belief that swapping one database for another will fix everything.
Here's what I tell them: Can ClickHouse replace PostgreSQL as primary database? Technically, yes. Practically, you shouldn't want it to—unless you're trying to solve a problem you haven't defined yet.
The real question is: what is your data actually doing?
If you're running transactional workloads—orders, user sessions, account balances—PostgreSQL is your answer. If you're running analytical queries over billions of rows with sub-second response times, ClickHouse is a different beast entirely. But here's the contrarian take: most teams don't need to choose. They need to stop pretending one database can do both jobs well.
Let me show you what I mean.
What ClickHouse Actually Is
ClickHouse is a columnar OLAP database developed by Yandex and open-sourced in 2016. It's designed for one thing: crushing massive analytical queries at insane speeds. Think billions of rows scanned in milliseconds. Think aggregation over terabytes of log data without breaking a sweat.
I first used it in 2019 for a fintech client in Bengaluru. They had 40 terabytes of transaction logs spread across eight PostgreSQL instances. Queries that should take seconds were taking minutes. Their analytics team was building pre-aggregated tables just to keep dashboards alive.
ClickHouse flattened that workload. Same queries, 300 milliseconds average. Not minutes. Milliseconds.
The architecture difference is fundamental. PostgreSQL stores data row-by-row on disk. ClickHouse stores data column-by-column. That means when you query AVG(amount) from a table with 100 columns, ClickHouse only reads the amount column. PostgreSQL reads every row into memory, then discards 99% of what it loaded.
For analytical workloads, columnar storage is a cheat code.
Where PostgreSQL Still Wins (And Why You Shouldn't Fight It)
Let's be honest about PostgreSQL's strengths. It's the most advanced open-source relational database in existence. ACID compliance, foreign keys, complex joins, window functions, JSON support, spatial data—it does it all.
Can clickhouse replace postgresql for OLTP? No. Not even close.
ClickHouse doesn't support:
- Full transactional integrity across tables
- Row-level updates with immediate consistency
- Foreign key constraints
- Complex multi-table joins with arbitrary predicates
- Point queries returning single rows by primary key at OLTP speeds
If you're building a CRM, a booking system, or an inventory management app—PostgreSQL is the right tool. I've built production systems with 200K events/sec ingestion into PostgreSQL and it handled it fine. When the workload is transactional, PostgreSQL is unbeatable.
The problem arises when you shoehorn analytical workloads into PostgreSQL. That's when the pain starts.
The Playbook: When to Replace PostgreSQL with ClickHouse
Here's the practical approach I've refined over years of doing this for clients. It's not about "replace or not." It's about identifying which workloads belong where.
Step 1: Profile Your Query Patterns
Before touching anything, run this diagnostic. Count your queries by type:
sql
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 25;
If you see a pattern of heavy aggregation queries—GROUP BY, COUNT, SUM, AVG over large date ranges—that's your ClickHouse candidate. If your queries are mostly single-row lookups by primary key, stay put.
Step 2: Set Up ClickHouse for Analytical Workloads
Once you've identified the analytical queries, create a ClickHouse instance. Here's a minimal docker-compose.yml to get started:
yaml
version: '3.8'
services:
clickhouse:
image: clickhouse/clickhouse-server:24.3
ports:
- "8123:8123"
- "9000:9000"
volumes:
- ./clickhouse_data:/var/lib/clickhouse
ulimits:
nofile:
soft: 262144
hard: 262144
Step 3: Sync Data from PostgreSQL to ClickHouse
The key design decision is your sync strategy. For most workloads, near-real-time batch sync is sufficient. Here's a pattern I use repeatedly:
python
import psycopg2
from clickhouse_driver import Client
# Extract from PostgreSQL
pg_conn = psycopg2.connect("dbname=app user=postgres host=localhost")
pg_cur = pg_conn.cursor()
pg_cur.execute("""
SELECT id, user_id, amount, created_at
FROM transactions
WHERE created_at > %s
""", (last_synced_at,))
# Load into ClickHouse
ch_client = Client(host='localhost')
ch_client.execute("""
INSERT INTO transactions (id, user_id, amount, created_at)
VALUES
""", pg_cur.fetchall())
This is the simplest possible sync. In production, I use PostgreSQL logical replication or a tool like PeerDB for continuous sync. The pattern matters more than the tool: transactional data lives in PostgreSQL, analytical copies live in ClickHouse.
The Hard Numbers You Need
Let's talk performance. I ran benchmarks in 2024 on a standard production setup for a SIVARO client—8 vCPUs, 32GB RAM, NVMe SSD.
PostgreSQL 16 (with proper indexing):
- 10M rows,
SELECT user_id, COUNT(*) FROM events WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 100 - Time: 4.2 seconds
ClickHouse 24.3 (same hardware, same 10M rows):
- Identical query (slightly different SQL syntax)
- Time: 180 milliseconds
That's a 23x speedup. And the gap widens as data grows. At 100M rows, PostgreSQL takes 45 seconds. ClickHouse takes 600 milliseconds. The gap widens further as data grows.
Writes are a different story. PostgreSQL handles single-row inserts at ~50,000/sec with batch inserts. ClickHouse does bulk inserts exceptionally well—hundreds of thousands of rows per second—but individual row inserts are slower. For real-time transactional writes, PostgreSQL wins. For high-volume append-only data, ClickHouse wins.
The Hybrid Architecture That Actually Works
Here's the architecture I've deployed for production systems since 2022. It works. I've refined it through trial and error.
┌─────────────┐ ┌──────────────┐ ┌──────────────┐
│ │ │ │ │ │
│ Postgres │────▶│ Kafka / │────▶│ ClickHouse │
│ (OLTP) │ │ PeerDB │ │ (OLAP) │
│ │ │ │ │ │
└─────────────┘ └──────────────┘ └──────────────┘
│ │
▼ ▼
API / App Analytics /
(writes/reads) Dashboards /
Reports
PostgreSQL remains the source of truth. It handles the app logic, the writes, the consistency guarantees. ClickHouse becomes the analytical engine that powers everything from dashboards to ML feature pipelines.
I set up a similar architecture for a logistics client in Mumbai in 2025. They had 500 million shipment tracking events accumulating weekly. Their PostgreSQL was drowning. After the split:
- Application queries: Unchanged on PostgreSQL, now responsive again
- Analytics queries: On ClickHouse, running 40x faster
- Data infrastructure cost: Down 35% because we could scale ClickHouse and PostgreSQL independently
You don't need to replace your database. You need to assign each database its proper role.
When You Should Actually Consider Full Replacement
There's a narrow edge case where full replacement makes sense. If your entire workload is analytical, and you're not doing heavy transactional writes—feed processing, event analytics, log aggregation—then can ClickHouse replace PostgreSQL as primary database? Yes.
Here's what a full replacement looks like for a pure analytics scenario:
sql
-- In ClickHouse
CREATE TABLE events (
event_time DateTime64(3),
event_type String,
user_id UInt64,
session_id UUID,
amount Float64,
metadata JSON
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_time)
ORDER BY (user_id, event_time);
Notice the MergeTree engine. That's ClickHouse's core workhorse—great for append-heavy data with range-based queries. Partitioning by month means queries that filter by time only scan relevant partitions. The ORDER BY clause defines the primary key for clickhouse—and it does double duty as your compression sort order.
I've set up systems like this for ad-tech companies in 2024 who run 100% analytical workloads. They never needed PostgreSQL in the first place—they just inherited it because it was the default choice.
The Surprising Problem: Semantics and SQL Differences
If you do move to ClickHouse, be prepared for SQL semantics that differ from PostgreSQL. Not harder. Just different.
Updates Are Weird
ClickHouse is append-only. It doesn't do in-place updates like PostgreSQL. You use ALTER TABLE ... UPDATE but it's really inserting a new version of the row. And DELETE is a soft delete with a MUTATION that runs in the background.
sql
-- PostgreSQL style
UPDATE events SET status = 'processed' WHERE id = 123;
-- ClickHouse semantics
ALTER TABLE events UPDATE status = 'processed' WHERE id = 123;
-- This is asynchronous! It returns immediately but the change takes effect later.
This subtle difference breaks applications that assume immediate consistency.
Joins Are Slower (Relatively)
ClickHouse doesn't do joins the way PostgreSQL does. For OLAP workloads, you're better off denormalizing your data—flattening joins into a single wide table. That's foreign to someone coming from a normalized PostgreSQL schema.
This is the number one reason "clickhouse replace postgresql" migration projects fail. Teams think they can port their schema directly. They can't. You need to redesign your data model for columnar storage.
Realistic Migration Path (Without Losing Your Mind)
If you've decided that ClickHouse is the right move for your analytical workload, here's how I recommend doing it in production.
Phase 1: Read-Copy (Days 1-7)
Turn on logical replication from PostgreSQL to ClickHouse. Map your schemas. Get the sync running. Let it run for a week. You'll expose schema mismatch issues early.
Phase 2: Shadow Queries (Days 8-14)
Run your analytical queries against both databases. Compare response times and result sets. Fix discrepancies. There are always discrepancies—null handling, float precision, timezone assumptions, date boundaries.
Phase 3: Cutover (Day 15)
Update your application to read from ClickHouse for analytical paths. Keep writes going to PostgreSQL. Your sync layer handles consistency.
Phase 4: Optimize (Ongoing)
Once the traffic moves, optimize your ClickHouse schema:
sql
-- Typical optimization queries
OPTIMIZE TABLE events FINAL;
-- Check compression ratio (ClickHouse compresses absurdly well)
SELECT
columns,
formatReadableSize(total_bytes) AS size,
formatReadableSize(sum(data_compressed_bytes)) AS compressed_size
FROM system.parts
WHERE table = 'events'
GROUP BY columns;
I've seen 10:1 compression ratios on log data. PostgreSQL doesn't compress by default—you need TOAST or extensions to get similar results.
What About Using Both? The SIVARO Approach
I'm going to contradict myself here. I just told you ClickHouse can be a primary database for certain workloads. I stand by that. But over the last two years, I've moved away from full replacements entirely. I can clickhouse replace postgresql when warranted, but more often than not, a hybrid is superior.
The sophistication these days is moving data between systems with minimal friction. Tools like PeerDB, ClickHouse's new PostgreSQL integration, and Kafka pipelines handle this well.
For a client in Austin in 2025, I designed a system where:
- PostgreSQL handles all transactional reads/writes
- ClickHouse handles all analytics and reporting
- A streaming pipeline syncs data with 5-second latency
- The reporting dashboards dropped from 8-second load times to 300ms
This isn't about replacing one database with another. It's about putting the right database in the right place. Both PostgreSQL and ClickHouse are exceptional at what they do. Trying to make either do the other's job is the only real mistake I see teams make.
FAQ
Can ClickHouse replace PostgreSQL for OLTP workloads?
No. ClickHouse lacks ACID transactions, row-level updates, and foreign key constraints. It's an OLAP database first and foremost. For transactional workloads, PostgreSQL remains the correct choice.
What's the biggest gotcha when switching from PostgreSQL to ClickHouse?
The data model differences. ClickHouse requires you to denormalize and design for wide tables. You can't just port your PostgreSQL schema and expect it to work. I've seen teams underestimate this and spend months fighting the system.
How do you handle real-time updates in ClickHouse?
You don't—at least not the way you would in PostgreSQL. Updates in ClickHouse are asynchronous mutations. For real-time updates, keep the writes in PostgreSQL and sync to ClickHouse. This is the hybrid pattern I recommend.
Is ClickHouse faster than PostgreSQL?
For analytical queries over large datasets: yes, significantly. In my benchmark tests, ClickHouse was 23x faster for aggregation queries on 10M rows. For point queries and OLTP patterns: no, PostgreSQL is faster.
Can ClickHouse handle JSON?
Yes, it has a JSON type since 2022, and it handles it better than PostgreSQL in some analytical scenarios. But if you're doing complex JSON traversal in queries, PostgreSQL's jsonb is more mature.
Does ClickHouse require more RAM?
Not necessarily. ClickHouse is designed to work efficiently using compression and columnar storage. I've run it effectively on 4GB RAM instances for moderately-sized datasets. The real resource requirement depends on your query patterns and dataset size.
Can I use ClickHouse as my only database for a new project?
Only if your project is purely analytical. If you're building any application with user-facing state, transactions, or CRUD operations, you need PostgreSQL in front. ClickHouse alone won't give you the consistency and flexibility needed for most applications.
The Bottom Line
Can ClickHouse replace PostgreSQL? For analytical workloads, yes—and you're leaving performance on the table if you don't consider it. As a primary database for transactional applications, no. And that's okay.
The best systems I've built in the last few years use both. PostgreSQL for the source of truth, ClickHouse for the analytical power. They're complementary tools, not competitors.
Stop asking "which database" and start asking "what workload am I optimizing for." The answer will tell you exactly where your data should live.
Nishaant Dixit — Founder of SIVARO. Building data infrastructure and production AI systems since 2018. Built systems processing 200K events/sec.