Postgres to ClickHouse: Real-Time CDC Pipeline Guide

The fastest way to move data from PostgreSQL to ClickHouse in real time is change data capture (CDC): stream row-level changes out of Postgres as they happen, instead of running batch jobs that leave your analytics minutes or hours behind.

PostgreSQL is built for transactions, but analytical queries over large datasets slow it down. ClickHouse is a columnar OLAP database that processes billions of rows per second, so pairing them lets you run real-time dashboards and analytics on fresh transactional data without loading down Postgres. The catch is keeping ClickHouse continuously in sync, which is where a CDC pipeline comes in.

This guide walks through building that pipeline with Estuary, from capturing changes in the Postgres write-ahead log to materializing them into ClickHouse, including schema handling and deletes. By the end you'll have a working real-time pipeline.

Why Sync PostgreSQL with ClickHouse?

As data grows, PostgreSQL's analytical queries slow down: aggregations get expensive and query performance degrades. ClickHouse fills that gap with columnar storage, vectorized execution, and efficient compression, processing billions of rows per second with low latency.

Syncing the two lets you offload analytics from Postgres while keeping it as your source of truth. Common cases include tracking events in a SaaS app, analyzing ecommerce behavior, and monitoring financial transactions, all on data that is seconds old rather than hours.

Challenges with Traditional Postgres to ClickHouse ETL

Moving data from PostgreSQL to ClickHouse is not a new idea, but doing it well is a different story.

Most traditional ETL (Extract, Transform, Load) approaches are built around batch processing. You schedule periodic jobs to dump data from Postgres, transform it, and load it into ClickHouse. This works—for a while. But as data volumes grow and the need for real-time insights becomes critical, these methods quickly fall short.

Here are the main issues:

• Latency: Batch jobs introduce unavoidable lag. Your dashboards are always looking at data that’s minutes—or hours—old.
• Operational overhead: Managing extraction scripts, transformation logic, retries, and schema mismatches across two different systems adds complexity and failure points.
• Lack of change awareness: Most batch pipelines don’t track incremental changes effectively. You either reprocess everything or risk missing updates and deletes.
• Scalability bottlenecks: High-frequency batch loads can overwhelm both the source and the destination, leading to contention and degraded performance.

To build a truly real-time, reliable, and scalable sync from PostgreSQL to ClickHouse, you need a different architecture—one that’s stream-based, change-aware, and built to evolve with your data.

Ways to Move Data from Postgres to ClickHouse

There is no single way to get Postgres data into ClickHouse. The right choice depends on whether you need real-time freshness, how much operational work you want to own, and whether you run ClickHouse yourself or on ClickHouse Cloud.

How to choose:

• If you only need occasional reads or you are prototyping, the native Postgres table engine is the quickest start.
• If you are on ClickHouse Cloud and want ingestion handled inside that account, ClickPipes is a natural fit.
• If you already run Kafka and want to own every part of the pipeline, Debezium plus Kafka gives you that control at the cost of operating it.
• If you want real-time CDC without running streaming infrastructure, and the option to fan the same data out to more destinations later, a managed platform like Estuary handles the capture, backfill, and schema handling for you.

How Estuary Streams Data from PostgreSQL to ClickHouse

Estuary is a streaming-native platform that moves data in real time without the operational weight of traditional ETL. At its core, Estuary uses Change Data Capture (CDC) to detect row-level changes in PostgreSQL the moment they happen, then writes them into ClickHouse through a managed materialization.

The pipeline has three parts:

1. Capture from PostgreSQL. Estuary connects to your Postgres instance and reads inserts, updates, and deletes from the write-ahead log using logical replication. Because it reads the replication stream instead of polling tables, it captures every change in order without adding query load to your database.
2. Land in an Estuary collection. Change events are written to a collection, a schema-enforced data store backed by object storage that sits in the middle of the pipeline. The source is captured once, and the collection can feed any number of destinations, so you can add targets later without re-reading Postgres.
3. Materialize to ClickHouse. Estuary's ClickHouse connector writes the collection directly into ClickHouse tables over the database's native protocol. It runs the historical backfill first, then keeps the tables current as new changes arrive, with sub-second latency end to end.

Prerequisites

To build this pipeline you'll need:

• A PostgreSQL database (self-hosted or cloud-managed on RDS, Aurora, Cloud SQL, or Azure) with logical replication enabled (wal_level = logical).
• A PostgreSQL user with replication privileges.
• Network access from Estuary to your database, via a public IP or an SSH tunnel.
• A ClickHouse database, either self-hosted or ClickHouse Cloud.
• An Estuary account, using the web app or the CLI.

ClickPipes is only required if you follow the Dekaf alternative.

Step 1: Capture from PostgreSQL into an Estuary Collection

In the Estuary web app, go to Sources, click +New Capture, and select the PostgreSQL connector. Enter your connection details: address, user, password, and database. Estuary uses CDC to read changes from Postgres and writes them into a versioned collection.

yaml
captures:
  your-org/postgres-capture:
    endpoint:
      connector:
        image: ghcr.io/estuary/source-postgres:dev
        config:
          address: your-db-host:5432
          user: your-db-user
          password: your-db-password
          database: your-db-name
    bindings:
      - resource:
          table: public.orders
        target: your-org/orders

A few things worth knowing:

• You don't need to pre-create collections. Publishing the capture generates them from your tables.
• Estuary enforces a JSON schema on every collection and handles reserved words automatically.
• Logical replication must be enabled on the source. On a managed provider, see the docs for provider-specific steps such as Google Cloud SQL or Neon.

Step 2: Materialize the Collection into ClickHouse

Prefer to watch? Here's the full Postgres to ClickHouse build, start to finish.

Estuary's ClickHouse materialization connector writes directly to ClickHouse over its native protocol. This is the recommended path for most pipelines: there are no Kafka topics to name and no ClickPipes to configure, and it behaves the same against self-hosted ClickHouse and ClickHouse Cloud.

Open your Postgres capture and select Materialize, then choose the ClickHouse connector. Enter your ClickHouse host and port, the username and password, and the target database. Estuary pulls in the collections from your capture and proposes a table for each one. Review the mappings, then Save and Publish. The backfill runs first, and live changes begin landing in ClickHouse within seconds.

The same materialization as YAML:

yaml
materializations:
  your-org/clickhouse-mat:
    endpoint:
      connector:
        image: ghcr.io/estuary/materialize-clickhouse:v1
        config:
          address: your-clickhouse-host:9440
          credentials:
            auth_type: user_password
            username: your-user
            password: your-password
          database: your-database
    bindings:
      - resource:
          table: orders
        source: your-org/orders

The connector uses the ClickHouse native protocol on port 9440 with TLS (the default) or 9000 without it.

Grant the connector access. The ClickHouse user needs create, insert, and select on the target database, plus read access to a few system tables so the connector can discover schemas:

sql
GRANT SELECT ON system.columns TO <user>;
GRANT SELECT ON system.parts TO <user>;
GRANT SELECT ON system.tables TO <user>;

See the ClickHouse connector reference for the full permission set and optional row-level policies that scope system-table access to a single database.

How updates and deletes land in ClickHouse

The write model is worth understanding so you query the data correctly:

• Updates. In standard mode, each table is created with the ReplacingMergeTree engine, using flow_published_at as the version column. Updated records arrive as new rows, and ClickHouse collapses them in a background merge, keeping the highest flow_published_at per key. Add the FINAL directive to your queries to read the deduplicated state.
• Deletes. Deletions are soft by default: the operation is recorded in the _meta/op column (create, update, or delete) and the row stays in place, which is what you want when you're keeping history. Set hardDelete to true and the connector writes a tombstone row carrying _is_deleted = 1. ReplacingMergeTree excludes tombstoned rows from FINAL results and clears them during a background cleanup the connector configures. Hard delete is the right choice when ClickHouse should mirror Postgres exactly.
• High-churn tables. Turn on delta updates per binding to append changes instead of reconciling full row state, which cuts write amplification on frequently updated tables. When delta updates are enabled, the table uses the MergeTree engine instead of ReplacingMergeTree.

Alternative: Ingest via Dekaf and ClickPipes

The native connector covers most pipelines. Reach for the Dekaf-based integration when your organization requires ingestion through ClickPipes, or when you want ClickHouse Cloud to own the ingestion side. In this setup, Estuary emits your collection as Kafka-compatible topics that ClickPipes consumes.

Materialize via Dekaf

Select the ClickHouse Dekaf destination and link your Postgres collections. Estuary emits Kafka-compatible topics that ClickPipes reads.

yaml
materializations:
  your-org/clickhouse-mat:
    endpoint:
      dekaf:
        config:
          token: your-auth-token
          strict_topic_names: false
          deletions: kafka
        variant: clickhouse
    bindings:
      - resource:
          topic_name: orders
        source: your-org/orders

• Set a secure token; ClickHouse uses it to authenticate.
• Use the clickhouse variant to keep your Dekaf materializations organized.
• Bind each collection you want to sync to a corresponding topic.

Connect ClickHouse ClickPipes to Estuary

With your topics live via Dekaf, link them to ClickHouse. In the ClickHouse Cloud dashboard, go to Integrations and choose Apache Kafka:

• Broker address: dekaf.estuary-data.com:9092
• Schema registry URL: https://dekaf.estuary-data.com
• Security protocol: SASL_SSL
• SASL mechanism: PLAIN
• SASL username and schema registry username: the full name of your Estuary materialization (for example, your-org/clickhouse-mat)
• Password: the same token you set in the Dekaf materialization

Map the incoming fields to your target table, then save and activate the ClickPipe. Data begins streaming from PostgreSQL into ClickHouse within seconds.

Data Type Mapping and Schema Evolution

ClickHouse and PostgreSQL have overlapping but not identical type systems. Common mappings:

Estuary enforces a JSON schema on every collection, which means:

• Schema enforcement. Each record conforms to a validated schema before it reaches ClickHouse.
• Graceful evolution. New fields or type changes are handled through Estuary's schema evolution workflow, which reduces the risk of a broken pipeline.
• Compatibility checks. If an upstream change could break the destination, such as switching a NUMERIC field to TEXT, Estuary flags it early.

Key Features and Benefits

Estuary is a more resilient approach to real-time pipelines, not just a faster one. By connecting PostgreSQL and ClickHouse through CDC, it offers a set of features that solve the most common pain points in analytics infrastructure.

Real-Time Change Data Capture

Estuary captures inserts, updates, and deletes from PostgreSQL the moment they happen, with no polling or periodic syncs. This lets you power dashboards, anomaly detection, and alerts with always-fresh data.

ClickHouse-Native Materialization

Estuary's ClickHouse connector writes directly to ClickHouse over its native protocol, with no Kafka cluster or ClickPipes to run. For teams standardized on ClickPipes, a Dekaf-based path is also available. Either way, Estuary handles the hard parts.

Schema Enforcement and Evolution

Estuary collections are backed by JSON schemas, so you always know what your data looks like. When your upstream schema changes, Estuary helps you manage evolution without breaking downstream pipelines.

Delivery Semantics

Estuary provides at-least-once delivery by default. On ClickHouse, the connector deduplicates with the ReplacingMergeTreeengine keyed on flow_published_at, so FINAL queries return a single current row per key, without duplication in high-volume pipelines.

Delta Updates for Efficiency

The ClickHouse materialization supports delta updates on a per-binding basis, which cuts write amplification on high-churn tables.

Flexible Deployment Options

Run Estuary as a fully managed SaaS, deploy in your own cloud (BYOC), or use a private deployment model to meet compliance and control needs.

Production-Ready Monitoring

Estuary integrates with Prometheus via its OpenMetrics API, so you can track latency, throughput, error rates, and more, with no guesswork required.

Best Practices for Bulk Load and CDC Performance

A common challenge when moving data from PostgreSQL to ClickHouse is handling both the initial load of historical data and the continuous stream of new changes. Estuary addresses this by combining a one-time backfill with ongoing CDC, but there are best practices you can follow to maximize performance:

• Use snapshot + CDC together: Estuary automatically takes an initial snapshot of your Postgres tables before switching to streaming CDC. This ensures your ClickHouse tables start with a complete dataset and then stay continuously updated.
• Partition large tables: For very large or high-churn tables (like orders or events), partitioning in Postgres helps Estuary capture changes more efficiently and reduces lock contention.
• Enable delta updates where possible: Instead of re-writing entire rows, Estuary can propagate only the fields that changed. This reduces write amplification in ClickHouse and improves performance for high-frequency updates.
• Monitor pipeline health: Estuary integrates with Prometheus via its OpenMetrics API. Tracking metrics like end-to-end latency, throughput, and error rates helps you quickly spot bottlenecks and scale resources as needed.
• Tune resource allocation: For mission-critical workloads, dedicate sufficient Postgres replication slots and configure ClickHouse ingestion settings (like batch size) to match your data velocity.

Following these practices ensures you get both a fast initial load and a low-latency CDC pipeline that can handle production-scale workloads without surprises.

Real-World Use Case: E-commerce Order Analytics

Imagine you're running an e-commerce platform where every transaction is recorded in a PostgreSQL database. Your operations team wants a real-time dashboard that shows order volume, revenue trends, top-selling products, and customer activity across regions, updated every few seconds.

Here's how Estuary makes that possible:

Source: PostgreSQL

New orders, updates to shipping status, and cancellations are continuously logged in a public.orders table. Instead of relying on nightly ETL jobs, you capture this data in real time using Estuary's Postgres connector.

Stream: Estuary Collection

As changes occur, they're streamed into an Estuary collection with schema enforcement and versioning. You don't have to manage storage, transformation, or failover. Estuary handles it for you.

Destination: ClickHouse

The collection is materialized into ClickHouse through Estuary's ClickHouse connector, which writes directly over the native protocol into an analytics-optimized table. New orders and status changes appear in ClickHouse within seconds.

Outcome: Real-Time Visibility

Now your BI dashboard is powered by ClickHouse's fast queries, with data that is seconds old, not hours. You can monitor conversions, detect stockouts, or adjust promotions dynamically, all without putting load on your transactional database.

This setup gives your team the analytical agility of ClickHouse with the trusted source-of-truth integrity of PostgreSQL, and it is built entirely on streaming infrastructure.

Conclusion

Syncing PostgreSQL to ClickHouse no longer requires brittle batch pipelines, custom Kafka deployments, or hours of engineering work. With Estuary, you get a fully managed, streaming-first solution that brings transactional data into ClickHouse in real time, writing directly over the ClickHouse native protocol, with built-in schema management and CDC-aware delete handling. Exactly-once delivery is available depending on your destination configuration.

Whether you're building operational dashboards, powering real-time analytics, or simply offloading queries from Postgres, Estuary makes it easy to modernize your data stack.

Ready to stream from Postgres to ClickHouse in minutes? Try Estuary and see what real-time really looks like.