Can ClickHouse Replace PostgreSQL as Primary Database?

Cut Infra Costs 64%

Free ClickHouse Audit

I got this question three times last week. Once from a fintech CTO, once from a Series B founder, and once from a developer who'd just watched a YouTube benchmark where ClickHouse crushed Postgres on a 10-billion-row query.

The short answer: No, not as a drop-in replacement. But the longer answer is more interesting, because it's not about which database is "better" — it's about whether your application actually needs what Postgres gives you, or whether you've been paying for transactional guarantees you never use.

Let me walk you through what I've learned building data systems at SIVARO since 2018. We've run both in production. We've migrated workloads between them. We've made mistakes.

What Each Database Actually Is

PostgreSQL is a relational database management system built for online transaction processing (OLTP). It guarantees ACID compliance. It handles concurrent writes from thousands of users. It supports complex joins, foreign keys, and constraints. It's been around since 1996 and it's the default choice for most web applications.

ClickHouse is a columnar OLAP database released by Yandex in 2016. It's built for online analytical processing. It stores data column-by-column instead of row-by-row, which makes it dramatically faster for analytical queries that scan large datasets. It's designed for append-heavy workloads, not frequent updates.

Can ClickHouse replace PostgreSQL as primary database? If your primary database needs to handle user authentication, order processing, or any workload where a single row matters, the answer is no. ClickHouse isn't built for that. But if your "primary database" is actually storing event logs, metrics, or analytical data, Postgres might be the wrong tool you've been forcing into service.

The Query That Started This Conversation

A client came to us in early 2025. They had a Postgres database that had grown to about 4 terabytes. Their analytics queries were taking 30+ seconds. They'd tried indexing, partitioning, read replicas. Nothing helped.

The problematic query looked something like this:

sql
SELECT 
    user_id,
    COUNT(*) as event_count,
    AVG(session_duration) as avg_duration
FROM events
WHERE timestamp >= now() - INTERVAL '90 days'
GROUP BY user_id
ORDER BY event_count DESC
LIMIT 100;

On a table with 2.3 billion rows, Postgres was scanning everything. Every index strategy failed because the query needed to aggregate across almost the entire dataset.

We moved that table to ClickHouse. Same query took 0.8 seconds. That's a 37x improvement. Not because ClickHouse is magic — because it's purpose-built for exactly this pattern.

But here's what we didn't do: we didn't move their users table. We didn't move their orders table. We didn't move anything that required per-row updates or transactions.

The Architecture That Actually Works

Most people who ask "can ClickHouse replace PostgreSQL as primary database" are really asking about architecture. The answer in production is a hybrid approach.

Here's the pattern we've used successfully across multiple clients:

Application → PostgreSQL (source of truth, transactions)
                ↓
            CDC pipeline → ClickHouse (analytics, reporting)

The application continues to use Postgres for everything operational. Changes are streamed to ClickHouse in near-real-time using tools like Debezium or ClickHouse's own integration with Kafka. Analytics queries hit ClickHouse, which never has to compete with production traffic.

We've built this at SIVARO for clients processing 200K events per second. The pattern holds up.

When ClickHouse Makes Sense as Your Primary Database

There's a specific scenario where I'd say yes, ClickHouse can replace your primary Postgres. It's when your application is fundamentally write-once, read-many.

Think about these use cases:

Time-series data. IoT sensor readings, financial market data, application metrics. Data is appended, queried by time range, rarely updated.

Event logging. User behaviors, system events, audit trails. Append-only, queried for analysis.

Analytics platforms. If your product IS an analytics tool, ClickHouse might be all you need.

We built an internal observability platform at SIVARO on ClickHouse alone. No Postgres anywhere. It handles:

200K events/second ingestion
30 days of retention across 300 billion rows
Sub-second queries on 90-day aggregates

For that workload, Postgres would've failed. ClickHouse is the right primary database.

But notice what we're not doing: user logins aren't stored there. Billing data isn't there. No transactions.

The Technical Differences That Matter

Storage Engine

Postgres stores data row-by-row. ClickHouse stores column-by-column.

This changes everything about query performance. A query that needs only 2 columns out of 20 can skip reading 90% of the data. On a 10-billion-row table, that's the difference between 10GB and 1GB of I/O.

Compression

Columnar storage compresses better because similar data sits together. We routinely see 8-10x compression ratios in ClickHouse versus 3-4x in Postgres, depending on the dataset. That means less storage cost and better cache efficiency.

Indexing

Postgres uses B-trees by default. Great for point lookups, terrible for range scans over large datasets. ClickHouse uses sparse primary indexes — instead of indexing every row, it indexes groups of rows. That's why analytical queries are fast.

Updates and Deletes

Postgres handles updates natively. ClickHouse supports mutations (UPDATE/DELETE) but they're implemented as rewrites. They work, but they're slow and shouldn't be frequent.

This is the fundamental trade-off.

Operation	PostgreSQL	ClickHouse
Point lookup by primary key	1-5ms	10-50ms
Insert 1000 rows	10-50ms	5-20ms
Aggregate 1B rows	30-120s	1-5s
Update 1M rows	2-10s	30-120s (mutation)
Concurrent heavy read/write	Excellent	Good for reads, weaker mixed

Numbers from our own load testing at SIVARO on comparable hardware: 8 cores, 64GB RAM, NVMe storage, both databases configured with default settings.

When Postgres Wins, and Why You Should Keep It

Postgres is the right choice when your data has relationships that matter at write time.

Here's a concrete example. A client ran a marketplace platform. Their Postgres schema had:

sql
CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id),
    status TEXT NOT NULL,
    total_cents INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id UUID PRIMARY KEY,
    order_id UUID NOT NULL REFERENCES orders(id),
    product_id UUID NOT NULL,
    quantity INTEGER NOT NULL,
    price_cents INTEGER NOT NULL
);

Every order creation involved writing multiple rows across multiple tables within a transaction. If the order_items insert failed, the order had to be rolled back. That's ACID enforcement — and ClickHouse doesn't do that.

Sure, you could normalize the data into a wide table in ClickHouse. But then you lose referential integrity. ClickHouse won't check whether an order_id references an existing order. It won't prevent orphaned rows.

I asked one advocate of ClickHouse-as-primary how they handled this. Their answer: "We just don't have that problem." And honestly, for their use case that was correct. But if your application needs foreign keys, constraints, or transactions, Postgres wins. Full stop.

Migration Path: How to Actually Do This

If you've decided to try ClickHouse, here's the practical path we've used with multiple clients.

Step 1: Identify Read-Only or Append-Only Workloads

Go through your Postgres tables. Find the ones that:

Are written once and never modified
Are queried primarily by aggregates (COUNT, SUM, AVG)
Are growing so large that queries are getting slow

These are your migration candidates.

Step 2: Set Up Replication (Not a One-Time Export)

Don't do a one-time data dump. Build a pipeline that keeps ClickHouse in sync with Postgres.

bash
# Using Debezium for CDC
docker run -d \
  --name debezium \
  -p 8083:8083 \
  debezium/connect:2.7

Configure the Postgres source connector, pointing at your WAL. Then set up a sink to ClickHouse. We use this pattern:

Postgres WAL → Kafka → ClickHouse Kafka Engine → MergeTree tables

Step 3: Rewrite Queries

ClickHouse uses almost-SQL. There are differences:

sql
-- Postgres: LIMIT/OFFSET
SELECT * FROM events LIMIT 100 OFFSET 20;

-- ClickHouse: LIMIT with offset (works slightly differently)
SELECT * FROM events LIMIT 20, 100;

Array functions, JSON handling, and date functions differ. Plan for rewrite time. Budget 2-3 weeks for a moderately complex application.

Step 4: Test, Load, Test Again

Run your full analytical workload against ClickHouse with production-sized data. Don't trust benchmarks you read online. Benchmark against YOUR data and YOUR queries.

At SIVARO we built a query harness that replays production traffic against both databases and compares latency percentiles. We do this for every client before recommending a migration.

Real Numbers from Our Production Systems

Some concrete figures from systems we've built. Client names omitted, but these are real deployments.

Client A (Fintech): 800GB of transaction history. Query time for monthly revenue reports went from 45 seconds on Postgres to 1.2 seconds on ClickHouse. Kept Postgres for the transaction processing layer.

Client B (SaaS analytics): 12 billion rows of product usage events. Postgres couldn't handle the ingestion rate — we were hitting CPU limits at 10K events/sec. ClickHouse ingests 200K events/sec on the same hardware. Primary database now ClickHouse.

Client C (E-commerce): Tried to go all-in on ClickHouse. Moved their orders table. Failed within 2 weeks because their inventory tracking required row-level locks during concurrent updates. Rolled back to Postgres for that workload. Kept ClickHouse for analytics.

The lesson: the answer to "can ClickHouse replace PostgreSQL as primary database" is workload-dependent. It's not about the database being better or worse. It's about matching database characteristics to access patterns.

ClickHouse Configurations That Matter

If you do migrate, these settings matter more than people think:

xml
<yandex>
    <merge_tree>
        <!-- Only merge if partition is large enough -->
        <max_bytes_to_merge_at_min_space_in_pool>107374182400</max_bytes_to_merge_at_min_space_in_pool>
    </merge_tree>
</yandex>

Set this if you're storing large partitions. Without it, ClickHouse will constantly merge small parts, wasting CPU.

For ingestion-heavy workloads, tune insert settings:

sql
SET async_insert = 1;
SET async_insert_threads = 4;
SET wait_for_async_insert = 0;

This gives you higher throughput at the cost of slightly delayed visibility of inserted rows. For most analytics, that's fine. For transactional systems, it's not.

The Cost Question

ClickHouse uses less storage due to compression. On a 5TB Postgres database we migrated to ClickHouse, we ended at 800GB. That's real savings if you're paying for cloud storage.

But ClickHouse requires more memory for large aggregations. We recommend at least 32GB RAM for production nodes, and it doesn't handle tiny workloads well. If you're under 100GB of data, Postgres is probably fine. Don't add ClickHouse complexity without a compelling reason.

What About the Ecosystem?

Postgres has been around for almost 30 years. It has connectors for everything. ClickHouse now supports the PostgreSQL wire protocol, which means many Postgres clients can connect directly. That's helpful, but not complete — you'll still find client libraries that assume Postgres-specific error formats or transaction protocols.

ClickHouse is actively developed, and the community is growing. I'd argue it's past the "maybe it's a fad" stage — companies like Uber and Cloudflare run it at massive scale. But Postgres has a bigger ecosystem of experienced engineers. That matters for hiring and for troubleshooting issues at 2am.

Final Verdict on "Can ClickHouse Replace PostgreSQL as Primary Database?"

Here's my honest position after 8 years of building data systems:

For the vast majority of applications: No. Your primary database should be a transactional database. PostgreSQL is the right choice. ClickHouse is not a replacement for it.

For a specific subset of applications: Yes, absolutely. If your workload is append-only or analytical, ClickHouse is superior. It's not just "good enough" — it's an order of magnitude better for the right queries. The trend of storing everything in Postgres out of habit, with no actual transactional requirements, is a mistake.

Most people asking this question are actually asking whether they can solve their performance problems by swapping databases. You can't. You solve performance problems by matching the data store to the access pattern. Sometimes that means ClickHouse. Sometimes that means keeping Postgres and adding a secondary analytical store. Sometimes it means both.

At SIVARO, we've built the hybrid pattern into our default architecture. Postgres for transactions. ClickHouse for analytics. Debezium for the pipeline in between. It's not the simplest architecture, but it's the most honest one — each database does what it's best at, and neither has to compromise.

Try it. Run your own benchmarks. Question your assumptions about which database is "primary."

FAQ

1. Can ClickHouse handle high-concurrency read/write workloads like Postgres?

No. ClickHouse is optimized for analytical queries, not high-frequency point lookups or transactions. It has a single-threaded mutation model, so concurrent updates are bottlenecked. For OLTP workloads, Postgres remains the better choice.

2. Does ClickHouse support ACID transactions?

ClickHouse supports full ACID transactions only for single-part inserts. Multi-row transactions that span multiple tables aren't supported as in Postgres. You'll need to consider eventual consistency patterns.

3. What's the best way to replicate data from Postgres to ClickHouse?

Set up Change Data Capture (CDC) with Debezium, stream to Kafka, then ingest into ClickHouse using the Kafka engine or ClickHouse Connect. This provides near-real-time replication for analytical queries.

4. Can I use ClickHouse for an application with user authentication and sessions?

Not recommended. Those workloads require point lookups and frequent updates — exactly what ClickHouse is not built to optimize. Keep auth data in Postgres.

5. What hardware do you need to run ClickHouse in production?

Minimum 32GB RAM per node for moderate workloads, 128GB+ for large datasets. Storage should be NVMe SSD for WAL and data files. For a 10-billion-row table, expect to provision at least 512GB NVMe.

6. Is there a risk of ClickHouse being discontinued or losing maintenance?

ClickHouse is an open-source project with commercial support from ClickHouse Inc. It's used at scale by major tech companies. The project is actively maintained with releases every few months. Any open-source tool carries some risk, but ClickHouse is significantly backed.

7. How fast can I migrate from Postgres to ClickHouse?

For a 1TB analytical workload with a working CDC pipeline, plan for 4-6 weeks including data validation, query rewriting, and performance tuning. Faster migrations are possible with simple schemas, but budget full time for the migration process.

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

Part of our ClickHouse series — see every guide in this cluster. Fighting this in production? Explore ClickHouse.