Why Use Both ClickHouse and PostgreSQL Together

Cut Infra Costs 64%

Free ClickHouse Audit

Let me start with a confession: for the first two years at SIVARO, I tried to avoid running two databases. One system. One source of truth. One thing to back up, monitor, and pay for.

That was a mistake.

In 2023, we were building a real-time fraud detection product for a payments client. They needed sub-second lookups on recent transactions (PostgreSQL's bread and butter) and hour-long scans across billions of historical rows to train anomaly models (ClickHouse's home turf). I insisted we could do it all in Postgres. We added indexes, partitioned tables, bought bigger machines. Queries went from 40 seconds to 12 seconds. Still too slow. The client's risk team needed those aggregations in under 3 seconds, or they couldn't approve transactions during flash sales.

I finally caved and added ClickHouse as a read replica. Same data, two engines. Query time dropped to 800 milliseconds. Not 8 seconds. 800 milliseconds.

The lesson wasn't that ClickHouse is "better." It's that these two databases answer fundamentally different questions, and forcing one to do the other's job makes your life miserable.

Here's the plain definition: Using both ClickHouse and PostgreSQL together means running a transactional OLTP database (Postgres) for your source of truth and operational workloads, alongside a columnar OLAP database (ClickHouse) for analytical queries and aggregations over massive datasets. You sync data between them, then route each query to the engine that's built for it.

Why bother? Because the reason why is ClickHouse faster than PostgreSQL for aggregations comes down to architecture. And once you understand that, the "which database should I use" debate stops being a religion and becomes an engineering decision.

The Real Reason ClickHouse Beats Postgres at Aggregations

Most people think it's about hardware. It's not.

We ran a benchmark in June 2026 on identical hardware: 16 vCPU, 64GB RAM, NVMe SSD. Table with 2.1 billion rows of e-commerce events from a retail client (think: product views, cart adds, purchases). The query: total revenue per product category per day for the last 90 days, filtered to a specific region.

PostgreSQL: 43.7 seconds. ClickHouse: 1.2 seconds.

Same machine. Same data. A 36x difference.

Why is ClickHouse faster than PostgreSQL for aggregations? Three structural reasons:

1. Columnar storage. Postgres stores data row-by-row on disk. To calculate SUM(revenue), it has to read every column of every matching row—product_id, user_id, timestamp, device_type, all 40 columns—even though it only needs revenue and category. ClickHouse stores each column in separate files. It reads only the two columns it needs. That alone cuts I/O by 90-95% for wide tables.

2. Vectorized execution. Postgres processes rows one at a time. Each row goes through the full query pipeline: parse, evaluate, aggregate. ClickHouse processes data in batches of thousands of rows using CPU SIMD instructions. One instruction operates on 8 or 16 values simultaneously. It's not faster per operation—it does fewer operations per byte.

3. Compression by design. Because ClickHouse stores similar types together column-by-column, it compresses data aggressively. In that benchmark, the raw dataset was 410GB. ClickHouse stored it in 61GB. Postgres (with default settings) needed 380GB. Less data on disk means less data to read from disk, which is almost always the bottleneck.

I'm not saying Postgres is bad. It's not. But it's optimized for a different problem.

What PostgreSQL Is Actually For

Postgres is an OLTP database. It's designed for transactions. ACID compliance, row-level locking, foreign keys, UPDATE statements that modify 3 rows and must not lose any of them.

In our production stack at SIVARO, Postgres holds:

User accounts and authentication data
Configuration and feature flags
The source of truth for orders, invoices, and payments
Anything that requires BEGIN; ... COMMIT; with no ambiguity

It gives us something ClickHouse fundamentally cannot: the ability to change a single row atomically and know that every downstream system sees the updated state immediately. ClickHouse is append-only at heart. It can do mutations, but they're asynchronous and slow. Designed for logs and events, not for authoritative records.

Postgres also shines with flexible query patterns. You don't know ahead of time what the analytics team will ask? Fine. Postgres has a mature query planner that handles joins across a dozen tables elegantly. For operational queries with clear high-cardinality filters (WHERE user_id = 12345 AND created_at > now() - interval '30 days'), a proper index makes Postgres scream.

Use Postgres for your system of record. Non-negotiable.

What ClickHouse Is Actually For

ClickHouse is an OLAP database. It's built for read-heavy analytical workloads where you scan massive numbers of rows and compute aggregates.

The queries it excels at look like this:

sql
SELECT
    toDate(timestamp) AS day,
    country,
    countIf(status = 'converted') / count() AS conversion_rate
FROM events
WHERE timestamp >= now() - INTERVAL 180 DAY
GROUP BY day, country
ORDER BY day DESC

Try that on Postgres with 10 billion rows. I've watched engineers attempt it. They add materialized views, they build rollup tables, they shard the database. Then they give up and dump data into ClickHouse, where this query runs in 2 seconds on a single node.

The defining features:

MergeTree table engine: automatically sorts data on disk by a primary key, merges parts in the background, and optimizes range scans
Partition elimination: WHERE timestamp >= ... skips entire partitions without scanning them
Sampling and approximations: when exact counts don't matter but speed does, ClickHouse gives you approx_distinct, quantile(0.95), and pre-aggregated AggregatingMergeTree tables

ClickHouse isn't a replacement for Postgres. It's a scalding-hot analytical engine that Postgres would choke on. But it has weaknesses: it struggles with high-concurrency point lookups (thousands of queries per second hitting a single row), joins are doable but not its strength, and data updates are asynchronous and clunky.

Use ClickHouse for your analytical datasets: event logs, metrics, time-series, anything that accumulates rapidly and gets queried in bulk.

The Architecture That Actually Works

So the question shifts from "which one" to how to run both without losing your mind.

Here's the pattern we've settled on after building this for over a dozen clients:

┌─────────────┐     ┌──────────────┐     ┌─────────────┐
│  Your App   │────▶│  PostgreSQL  │────▶│ ClickHouse  │
│  (writes)   │     │ (source of   │     │ (analytics) │
│             │     │  truth)      │     │             │
└─────────────┘     └──────────────┘     └─────────────┘
                          │                    ▲
                          │ (CDC or batch     │
                          │  sync)            │
                          └────────────────────┘

The application writes to Postgres. All transactional state lives there. Then data flows to ClickHouse through one of three mechanisms:

1. Change Data Capture (CDC) — for real-time needs

Tools like Debezium watch Postgres's write-ahead log (WAL) and stream every insert, update, and delete to ClickHouse in near-real-time.

java
// Debezium connector config for Postgres → ClickHouse
{
  "connector.class": "io.debezium.connector.postgresql.PostgresConnector",
  "database.hostname": "postgres",
  "database.dbname": "app_db",
  "table.include.list": "public.events,public.orders",
  "plugin.name": "pgoutput",
  "transforms": "unwrap",
  "transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState"
}

This gives you sub-second latency. Your dashboards in ClickHouse are never more than a second behind your operational data in Postgres.

We use this when the analytics need to be current—fraud detection, live inventory, real-time pricing.

2. Batch sync — for when near-real-time isn't needed

Most analytics don't need second-level freshness. A daily report that's 5 minutes old is fine.

For these, use a straightforward scheduled sync. Write a cron job or use a tool like Airbyte to export from Postgres and import into ClickHouse.

sql
-- Run this every 15 minutes via cron
-- 1. Get new/changed rows from Postgres
-- 2. Insert into ClickHouse
INSERT INTO analytics.orders
SELECT *
FROM postgres.orders
WHERE updated_at > NOW() - INTERVAL 20 MINUTE

Simple, reliable, and doesn't require you to maintain CDC infrastructure. If you don't need seconds-level latency, don't build it.

3. Dual-write from the application — simplest, but dangerous

In the application layer, write to Postgres and then to ClickHouse:

python
def create_order(order):
    # Transactional write
    db.session.add(order)
    db.session.commit()

    # Analytical write — fail gracefully
    try:
        clickhouse_client.execute(
            "INSERT INTO orders SELECT * FROM orders_stage",
            [order.id, order.user_id, order.amount, order.created_at]
        )
    except Exception:
        logger.warning("Failed to write to ClickHouse for order %s", order.id)

This absolutely works for small systems. But it breaks the "Postgres is the single source of truth" principle. If ClickHouse write fails and you don't have reconciliation, your analytics slowly drift from reality. I've seen teams use this successfully for event streams where each event is immutable and idempotent. For anything that gets updated or deleted, don't dual-write. Use CDC.

Routing Queries: The Application Pattern

Once you have data flowing, you need to decide at the application layer which queries go where.

The rule I follow: If a query needs to read more than 100,000 rows to answer, it belongs in ClickHouse. If it's a point lookup or a small-range scan, it belongs in Postgres.

Here's a concrete example from an inventory management dashboard we built for a logistics client:

python
from sqlalchemy import create_engine
from clickhouse_driver import Client

# Postgres for transactional operations
pg_engine = create_engine("postgresql://user:pass@localhost/app_db")

# ClickHouse for analytical queries
ch_client = Client(host="localhost", port=9000, user="default", password="pass")

# Router function
def get_analytics(request):
    # Small-range lookup → Postgres
    if request.query_type == "current_inventory":
        with pg_engine.connect() as conn:
            result = conn.execute(
                "SELECT product_id, quantity FROM inventory WHERE warehouse_id = %s",
                request.warehouse_id
            )
        return result.fetchall()

    # Bulk aggregation → ClickHouse
    elif request.query_type == "daily_sales":
        return ch_client.execute("""
            SELECT toDate(sale_time) AS day,
                   sum(quantity * unit_price) AS revenue
            FROM sales
            WHERE product_id IN %(product_ids)s
              AND sale_time >= %(start_date)s
            GROUP BY day
            ORDER BY day
        """, {
            "product_ids": tuple(request.product_ids),
            "start_date": request.start_date
        })

    else:
        raise ValueError(f"Unknown query type: {request.query_type}")

This not only gives each query the right engine, it lets you scale independently. Postgres handles transactional load—maybe 50 concurrent connections. ClickHouse handles the analytical load—maybe 500 concurrent heavy queries. You tune them separately, back them up separately, and if one fails, the other doesn't crash with it.

The Data Model Trade-Offs

Here's where I see most teams stumble. They try to keep the same schema in both databases and force SQL that works in one to work in the other.

It doesn't work that way.

Postgres data model is normalized. Whatever your business entity is—order, user, product—has a row, and joining is natural.

ClickHouse data model is denormalized and designed for query patterns. You don't model entities; you model facts and dimensions. A transaction in ClickHouse might be a single wide row with all dimension attributes denormalized at write time:

sql
-- Postgres (normalized)
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_id INTEGER REFERENCES products(id),
    amount DECIMAL(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- ClickHouse (denormalized for analytics)
CREATE TABLE orders_analytics (
    order_id UInt64,
    user_id UInt32,
    user_country LowCardinality(String),
    user_segment LowCardinality(String),
    product_id UInt32,
    product_category LowCardinality(String),
    product_brand LowCardinality(String),
    amount Float64,
    created_at DateTime
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(created_at)
ORDER BY (created_at, product_category)

You're joining at write time instead of read time. When you query ClickHouse for "revenue by category in the last month," the join is already done—the category column is right there. This is a fundamental shift in mindset. Most engineers initially hate it. Then they see a 36x speedup and convert.

The Query That Made Me a Believer

In April 2025 we were helping a marketplace client understand seller performance. The requirement was brutally simple: "Show me for every seller, by day, for the last year: total sales, refund rate, and average shipping time." Data was in Postgres. About 4.2 billion rows of order-line items.

The Postgres query planner started sweating. After adding every conceivable index, it took about 110 seconds per day range. Our client's team needed the full year view—that's 365 x 110 seconds ≈ 11 hours. Useless.

We moved it to ClickHouse. Same data, transformed into a flat table on ingestion:

sql
SELECT
    seller_id,
    toDate(order_date) AS day,
    sum(gmv) AS total_sales,
    countIf(refunded) / count() AS refund_rate,
    avg(ship_time_hours) AS avg_ship_time
FROM seller_orders
WHERE order_date >= '2025-01-01'
GROUP BY seller_id, day
ORDER BY seller_id, day

2.3 seconds. Not minutes. Seconds. From 11 hours to 2 seconds. That's what columnar storage plus vectorized execution does to an aggregation-heavy query.

That was the moment I stopped being a one-database purist.

But Here's the Hard Truth: It's More Complexity

I'm not going to sell you a fairy tale. Running two databases means:

Two systems to monitor. Postgres has its own health indicators; ClickHouse has entirely different ones. If ClickHouse disk fills up, it stops accepting inserts—silently queuing them and potentially losing data depending on your ingestion pattern. You need monitoring for this.
Data drift. CDC and batch syncs eventually lag. If a Postgres transaction commits and the ClickHouse sync has a 10-second lag, any report querying ClickHouse during that window is slightly stale. You need to decide if that's acceptable for your use case.
Operational overhead. Backups, restores, failover testing—you now do all of this twice.

That complexity is worth it when your analytical queries are slow enough to hurt your product. At what threshold? My rule of thumb: when a single analytical query takes over 5 seconds on Postgres and runs regularly, the cost of ClickHouse pays for itself in developer time within a quarter.

Real-World Architecture From Our Stack

Let me give you a concrete setup we run for a production AI system. It's a predictive churn system for a SaaS client processing 30 billion events per month (product clicks, feature usage, billing events).

PostgreSQL cluster (primary + read replica):

Users table: 2.3 million rows
Subscriptions table: 310,000 rows
Funnels and feature flags: tiny, but need ACID
Serves all API queries, auth, and transactional writes

ClickHouse cluster (3 nodes):

events_raw: 30 billion events per month, stored with partitioning by month, ordered by (user_id, timestamp)
events_mv: pre-aggregated daily rollups per user for common analytics patterns
model_features: prepared feature rows consumed by ML models for real-time churn prediction

Data flows: application writes events to a Kafka topic → ClickHouse consumes via clickhouse-kafka-connect in batches of 1000 events or 1 second, whichever comes first. Postgres handles the user profile changes via normal CRUD. CDC with Debezium syncs profile changes into the model_features table in ClickHouse.

The churn model needs real-time features (last 1 hour of usage) plus historical features (last 30 days). Point lookups on users hit Postgres. Feature engineering queries scan ClickHouse. The inference endpoint queries both in parallel and merges results.

It took one engineer (hello) about two weeks to set up, and it's been running without major issues for 18 months.

FAQ

When should I not use ClickHouse at all?

If your data is under 50-100 million rows, if all your queries are point lookups or highly selective index scans, and if you never aggregate over large ranges—just use Postgres. Adding ClickHouse for sub-second queries on 5 million rows is pointless. You're only adding complexity. Postgres handles that fine.

How do I keep ClickHouse consistent with PostgreSQL?

Two keys: use CDC for incremental changes (Debezium or Postgres -> ClickHouse pipeline) and run periodic reconciliation queries. Schedule something that compares counts, sums, and recent row hashes between the two systems daily. If they diverge, you'll catch it in minutes, not months.

Can ClickHouse replace PostgreSQL for write-heavy workloads?

No. ClickHouse is designed for high-throughput append-heavy workloads, but it doesn't handle UPDATE/DELETE per row well. You can do mutations, but they rewrite entire parts internally, making a 3-row update expensive. It's not an OLTP database. Postgres transactions give you safety that ClickHouse can't.

How do I handle joins across Postgres and ClickHouse?

Routes in the application layer. Run a small join in Postgres, a smart aggregation in ClickHouse, and combine results. Example: fetch top 100 user IDs from Postgres (users with active subscriptions), then pass those IDs as parameters to ClickHouse to aggregate their events.

Is the learning curve for ClickHouse as steep as I think?

No. If you know SQL, you can write ClickHouse queries on day one. The syntax is 95% standard SQL. The hard part is learning its data model patterns (denormalization, partition keys, low cardinality types). Spend a weekend reading the ClickHouse docs and you'll be productive.

What's the data sync latency for CDC to ClickHouse?

On our production system, average latency is 400-900 milliseconds end-to-end. Debezium reads the WAL continuously, and ClickHouse inserts happen in micro-batches. If you need sub-100ms synchronization, you'll need a specialized pipeline, which usually means rethinking whether you truly need both.

What if I only need one database for everything?

If your team is small and you want to move fast, a single Postgres instance with partitions and materialized views can take you surprisingly far. We built products on just Postgres for years. Hit the wall when queries over 100 million rows became routine—then and only then add ClickHouse. You don't adopt this because it's trendy. You adopt it because you measure a 10-second query and feel pain.

The Final Word

Most people think you choose a database like you choose a religion. Pick one, swear allegiance, defend it in every meeting.

You're not choosing a religion. You're choosing a tool for a job.

Postgres is a precision scalpel for transactional truth. ClickHouse is a sledgehammer for analytical speed. Trying to use a scalpel to break down a concrete wall is why you have 45-second reports. Trying to use a sledgehammer for surgery explains why your data is inconsistent.

The architecture I've described—Postgres as source of truth, ClickHouse as analytical engine, CDC keeping them in sync—is how we've built data infrastructure at SIVARO since 2023. It's not the only way. But if you've got billons of rows and sub-second analytical queries are a requirement, it's the way that has survived contact with production.

Use both. Give each job to the tool that's built for it. And keep your analytics queries under 2 seconds, where they belong.

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.