This article was originally published at sivaro.in

ClickHouse vs PostgreSQL for Large Datasets: A Practitioner's Buying Guide

We had 4.2 billion rows in PostgreSQL and a dashboard that took 38 seconds to load.

That was 2021, at a fintech company where I was running data infrastructure. The CEO was watching the query spin. I remember staring at pg_stat_activity thinking: Postgres is a fantastic database. It's just not built for this.

We migrated the analytics workload to ClickHouse. Queries dropped to 400ms.

But here's the thing nobody tells you: we kept Postgres running for everything else. Transactions, user accounts, billing, config. Both databases stayed. Both still run today.

So when someone asks me clickhouse vs postgresql for large datasets, my answer isn't "pick one." It's "know which problem you're actually solving." That's what this guide is about. I'll walk you through when each wins, what breaks during migration, and whether ClickHouse can actually replace Postgres for real-time analytics.

Let's be specific.

What each database actually is

PostgreSQL is a relational database built for correctness and transactions. It's ACID-compliant. It handles concurrent writes beautifully. Row storage means it reads entire rows even when you only need one column. MVCC keeps reads consistent while writes happen.

ClickHouse is a columnar OLAP database built for analytical throughput. Columns are stored separately. Compression ratios hit 10:1 or better on real data. It reads only the columns you query. Vectorized execution means it processes millions of rows per core per second.

That's the whole game. Row store vs column store. OLTP shape vs OLAP shape.

Most people think you pick the "better" database. You don't. You pick the one matching your access pattern. A columnar engine is terrible at fetching a single user by email. A row store is terrible at scanning 4 billion events grouped by time bucket.

Where PostgreSQL breaks on large datasets

Postgres doesn't fail at 100 million rows. It fails at a specific shape of workload.

Plain B-tree indexes still work fine at 100M rows if you're doing point lookups. The problem shows up when you need aggregation across millions of rows per query. Then Postgres does sequential scans, spills to disk, and parallel workers fight each other for I/O.

The other killer is write amplification. Every UPDATE in Postgres writes a new row version. It's how MVCC works. On a high-write append workload — event streams, logs, metrics — that means the table bloats constantly and VACUUM runs forever.

I've watched a Postgres instance process 40K inserts/sec on good hardware. That same instance dropped to 5K/sec after 6 months because of dead tuple accumulation nobody was managing.

Partitioning helps. It doesn't save you. You're still doing row-based scans and row-based storage.

Where ClickHouse wins and where it bites you

ClickHouse eats append-heavy analytical workloads for breakfast. 200K events/sec on a single node is routine. MergeTree engine stores data sorted by your primary key, so range queries over time are absurdly fast.

But.

ClickHouse deletes are expensive. ALTER TABLE ... DELETE triggers asynchronous mutation that rewrites entire parts. That's not a bug, it's the design. If you need frequent row-level deletes or updates, ClickHouse fights you every step.

Joins in ClickHouse are also weaker than Postgres. Small-to-large joins are fine. Large-to-large joins will kill you. Postgres has decades of join optimization work; ClickHouse has a distributed join problem.

And ClickHouse has no real transactions across tables. You don't get ACID the way Postgres gives it to you. If your app relies on that, don't move.

The benchmark that matters

Here's what we actually ran at SIVARO for a client last year. 800 million events, 12 columns, mixed query patterns.

Workload	PostgreSQL 16	ClickHouse 24.x
Point lookup by ID (p99)	2.1 ms	40 ms
Insert 1M rows	22 sec	1.8 sec
Aggregation over 90 days	14.2 sec	0.6 sec
Group-by 3 dims, 800M rows	31 sec	1.1 sec
Update single row	1.4 ms	900 ms

Read that last row again.

That's the trade-off in one table. Postgres is 20x faster at point lookups. ClickHouse is 25x faster at analytical aggregations. The workloads don't overlap.

Don't pick ClickHouse "because it's faster." It's faster at some things. And slower at others.

Setting up ClickHouse for real analytical speed

If you're going to run ClickHouse, get the schema right. It's not optional.

CREATE TABLE events (
    event_time DateTime64(3),
    user_id UInt64,
    event_type LowCardinality(String),
    country LowCardinality(String),
    amount Decimal64(2),
    properties String
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_time)
ORDER BY (event_type, event_time, user_id);

Three decisions in that DDL matter more than any tuning:

• ORDER BY is your actual index. It's the sort key on disk. Getting it wrong means every query scans more than it should.
• LowCardinality(String) on repeated values is a huge win. I've seen 40% storage reduction just from this.
• PARTITION BY toYYYYMM() lets ClickHouse skip entire months when you filter by time.

Compare that to what we'd need in Postgres:

CREATE TABLE events (
    event_time TIMESTAMPTZ NOT NULL,
    user_id BIGINT,
    event_type TEXT,
    country TEXT,
    amount NUMERIC(12,2),
    properties JSONB
) PARTITION BY RANGE (event_time);

CREATE INDEX ON events (event_type, event_time);

Postgres can do this. It handles the workload. It's just going to be 10-20x slower at the aggregations and it'll bloat like crazy on high write volume.

Can ClickHouse replace PostgreSQL for real-time analytics?

Short answer: for analytics, yes. For your application database, no.

I get asked this at almost every architecture review. "Can we just use ClickHouse for everything?"

No.

ClickHouse isn't designed for the reads and writes a transactional app does. It doesn't have proper row-level locks. It doesn't have referential integrity. Foreign keys exist as a syntax thing but don't enforce anything meaningful in production.

But for real-time analytics — dashboards, event aggregations, monitoring, usage metering — ClickHouse is a straight-up replacement. Often a better one than whatever you're running today.

Here's the pattern I recommend and have implemented for multiple clients:

flowchart LR
    App[App writes] --> PG[(PostgreSQL)]
    App --> Stream[Kafka / Redpanda]
    Stream --> CH[(ClickHouse)]
    PG -->|CDC via Debezium| Stream
    BI[Dashboards] --> CH
    App -->|point lookups| PG

Postgres is the source of truth. ClickHouse is the analytical mirror. Kafka connects them. If you don't want Kafka in the stack, use a tool like PeerDB or ClickHouse's own postgresql() table function to pull periodically.

That last option looks like this:

CREATE TABLE pg_events AS
SELECT * FROM postgresql(
  'pg-host:5432', 'analytics_db', 'events', 'user', 'password'
);

-- Then refresh on schedule
INSERT INTO events SELECT * FROM pg_events WHERE event_time > now() - INTERVAL 1 HOUR;

Not the fanciest setup. Gets the job done for many teams.

ClickHouse PostgreSQL migration best practices

I've done three of these migrations in production. Two went fine. One was a mess. Here's what separated them.

Don't migrate. Mirror first. Stand up ClickHouse alongside Postgres. Dual-write or CDC into it. Run both in parallel for 4-6 weeks. Only cut the analytics dashboards over once you've validated query results match. We skipped this on the messy migration and found decimal rounding differences three weeks after cutover.

Normalize nothing. Postgres schema designs are famously normalized. Don't carry that into ClickHouse. Denormalize. Widen. Duplicate columns. ClickHouse compression makes redundant storage cheap, and joins are expensive.

Fix your ORDER BY before you ingest a single row. The primary key in ClickHouse is not unique. It's a sort key. Putting the wrong column first means every query is slower forever. You can change it later via ALTER TABLE ... MODIFY ORDER BY, but it rewrites the whole table on disk.

Backfill in batches by partition. A single INSERT INTO ... SELECT from Postgres with 2 billion rows will time out and maybe OOM the ClickHouse node. Insert month by month. Let merges settle between batches.

Test deletes and updates explicitly. Most teams forget this. If your app does occasional row updates, decide where they happen — probably Postgres — and how they propagate. Don't assume ClickHouse will handle them fine.

Watch out for NULL semantics. They're subtly different. Postgres treats NULLs as unknown; ClickHouse has nullable types but the behavior in aggregates isn't identical. Test with real data.

The one team that did this well spent two months on migration planning. The one that went badly tried to do it in two weeks. You can guess which approach scaled.

Cost and operations reality check

Postgres gets expensive at scale because you scale up. Bigger instance, more RAM, faster disk. AWS RDS will happily charge you $12K/month for a db.r6g.8xlarge with 10TB of io2.

ClickHouse scales out. Add nodes. Cheap NVMe. The compression alone saves 70-90% of storage on typical event data. One client replaced $8K/month of Redshift with a 3-node ClickHouse cluster running on $2K/month of Hetzner. Same query latency.

But.

ClickHouse ops is harder. It's not harder than running Cassandra or running your own Postgres at scale — but it's harder than managed Postgres. Managed ClickHouse options exist (ClickHouse Cloud, Altinity, Tinybird), and they cost more than raw VMs at scale, but they save you from learning merge behavior the hard way.

Postgres managed services are mature. ClickHouse managed services are catching up fast — ClickHouse Cloud launched on AWS in 2022 and expanded considerably through 2024-2025. But if you're allergic to vendor lock-in and don't have a dedicated data infra person, think twice.

When to actually pick each

Pick Postgres for large datasets when:

• You have <500M rows and predictable access patterns
• You need transactions and referential integrity
• You do frequent updates and deletes
• Your team already knows Postgres (this matters more than benchmark numbers)
• Your workload is mixed read/write, not analytical

Pick ClickHouse for large datasets when:

• You're appending millions+ rows per day
• Your queries are aggregations, not point lookups
• You do the same analytical queries repeatedly
• You can denormalize your schema
• You have someone who can own the cluster

Pick both — which is what most mature data teams do — when:

• Your app needs Postgres and your analytics needs ClickHouse
• You can afford the operational complexity for 10-100x faster analytics
• You're willing to invest in CDC or streaming infrastructure

I've never regretted running both. I've regretted trying to force one to do the other's job.

The decisions that matter more than the database choice

Here's my honest take after eight years of building data infrastructure.

The clickhouse vs postgresql for large datasets debate gets too much oxygen. The bigger questions are:

What's your write pattern? Append-only or mutable? This single question decides 60% of the answer.

What's your query pattern? Point lookups or scans-and-aggregates? The other 40%.

Who owns operations? If you have one backend engineer doing everything, use managed services. If you have a data platform team, you can run ClickHouse yourself.

How much data will you have in 3 years, not today? A 50GB database doesn't need ClickHouse. A 5TB one probably does.

I've watched teams spend six months migrating to ClickHouse for a workload that Postgres handled fine with proper indexes. I've also watched teams try to scale Postgres to 20TB and burn out their team doing it.

Start with your access pattern. The database choice follows from there.

FAQ

Is ClickHouse faster than PostgreSQL?
For analytical aggregations over large datasets, yes — often 10-100x faster. For point lookups and transactional workloads, no. Postgres is typically 10-20x faster at single-row reads and updates.

Can ClickHouse replace PostgreSQL for real-time analytics?
Yes. For dashboards, event aggregations, and monitoring, ClickHouse often replaces Postgres entirely. It doesn't replace Postgres as your application's source of truth — that needs transactions and referential integrity.

How do I migrate from PostgreSQL to ClickHouse?
Mirror first, switch later. Set up CDC (Debezium or PeerDB) or dual-writes. Run both systems in parallel for weeks. Backfill in partition-sized batches. Validate query results before cutting dashboards over.

Does ClickHouse support updates and deletes?
Yes, technically. But they're asynchronous mutations that rewrite entire parts. Expect second-level latency. If your workload is update-heavy, stay on Postgres.

What's the cost difference for large datasets?
ClickHouse compresses data 5-10x compared to Postgres, and scales horizontally on cheap NVMe. For 10TB+ analytical workloads, expect 3-5x lower total cost. Below 1TB, savings usually don't justify the migration.

Do I need both databases?
Most production systems that exceed a few hundred million events do, eventually. Postgres for the app, ClickHouse for analytics. It's more infrastructure but each system does what it's designed for.

What about ClickHouse joins versus Postgres joins?
Postgres has decades of join optimization. ClickHouse joins work well for small-to-large but badly for large-to-large. Denormalize before you rely on joins in ClickHouse.

Can I run ClickHouse and Postgres on the same machine?
Yes, for development or small workloads. In production, run them on separate nodes — ClickHouse will happily consume all available memory on merge operations if you let it.

The bottom line on ClickHouse vs PostgreSQL for large datasets

There's no universal winner. I've run both in production at scale, migrated between them, and watched teams try to skip the analysis and just "pick the fast one."

ClickHouse wins on analytical throughput, storage cost, and append-heavy workloads. Postgres wins on transactions, point lookups, updates, and operational familiarity. The clickhouse postgresql migration best practices I keep coming back to are: mirror before you cut over, denormalize, fix your ORDER BY first, and test deletes and updates explicitly.

Can ClickHouse replace Postgres for real-time analytics? Yes — and for most teams running serious dashboards today, it should. But replacing Postgres at the application layer is a different question with a clear "no."

If your workload is append-heavy analytics at scale, ClickHouse pays for itself within months. If it's mixed transactional, stay on Postgres and stop reading benchmarks. And if it's both, run both. That's what we do.

---

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