Three Ways to Set Up CDC from Postgres to ClickHouse

You cannot run analytical queries on the same Postgres primary that serves your application without paying for it in CPU and connections. A read replica does not help: Postgres is row-oriented and built for OLTP, not for scanning tens of millions of rows for a GROUP BY. If you want sub-second dashboards over a real dataset you need a column store on the side.

We picked ClickHouse. The interesting question is not how to query ClickHouse. The interesting question is how to keep it in sync with Postgres. That is what Change Data Capture (CDC) solves.

CDC is mechanical: every insert, update, and delete in Postgres gets captured and forwarded somewhere else. Postgres already writes everything to the Write-Ahead Log (WAL) for crash recovery. CDC reads that log via a logical replication slot and turns it into an ordered stream you can replay into another system. Polling with WHERE updated_at > $last is the wrong tool: it cannot see deletes, it adds load, and your updated_at column will lie eventually. Read the log.

There are three common ways to plug Postgres CDC into ClickHouse. We evaluated all three. We ran two of them in production. The trade-offs below come from running them.

Postgres prerequisites (all three options)

Logical replication needs postgresql.conf set up correctly. This applies to all three approaches.

# postgresql.conf

wal_level = logical

max_wal_senders = 10

max_replication_slots = 10

max_slot_wal_keep_size = 102400  # MB; cap WAL retained by stalled slots

Then a replication role and a publication on the source side:

CREATE USER cdc_user WITH REPLICATION PASSWORD 'redacted';

GRANT USAGE ON SCHEMA public TO cdc_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO cdc_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public

  GRANT SELECT ON TABLES TO cdc_user;



CREATE PUBLICATION cdc_pub

  FOR TABLE public.events, public.users, public.orders

  WITH (publish = 'insert,update,delete');



-- Tables in the publication need a primary key, or:

ALTER TABLE public.events REPLICA IDENTITY FULL;

REPLICA IDENTITY FULL writes the full old row to WAL on update/delete, which is required if a table does not have a primary key. It is more expensive on writes, so prefer a PK where you can.

A note on connection requirements: ClickPipes (option 3) needs a direct Postgres connection. Connection poolers like PgBouncer transaction mode, RDS Proxy, and Supabase Pooler are not supported.

Option 1: Kafka and Debezium

Debezium tails the Postgres WAL via a logical replication slot and publishes change events into Kafka. A separate sink consumes from Kafka and writes into ClickHouse. The same Kafka topic can fan out to your search index, your audit log, and your warehouse, which is the main reason teams accept the cost.

The cost is real: Kafka brokers (or KRaft setup), Kafka Connect, schema registry, monitoring, capacity planning, and an on-call rotation that knows what a partition reassignment looks like.

Pick this if you already operate Kafka, or if you need event streaming for reasons beyond CDC. Otherwise it is overkill, and we treated it as such.

Option 2: MaterializedPostgreSQL

ClickHouse has a built-in database engine called does the same job as MaterializedPostgreSQL (read Postgres logical replication, write into ClickHouse) but as a managed pipeline on ClickHouse Cloud. Under the hood it is PeerDB. The setup is a connection string, a publication name, a slot name, and a destination database. There is a UI, an OpenAPI, and a Terraform provider.

resource "clickhouse_clickpipe" "pg_to_ch" {

  name           = "pg-prod-events"

  source         = "postgres"

  connection_url = var.pg_dsn

  publication    = "cdc_pub"

  slot_name      = "cdc_slot"

  tables         = ["public.events", "public.users", "public.orders"]

  destination_db = "app_analytics"

}

Auto-created tables are still ReplacingMergeTree but with PeerDB's metadata columns instead of MaterializedPostgreSQL's:

CREATE TABLE events

(

    id                  Int64,

    user_id             Int64,

    event               String,

    created_at          DateTime64(6),

    payload             String,

    _peerdb_synced_at   DateTime64(9) DEFAULT now64(),

    _peerdb_is_deleted  Int8,

    _peerdb_version     Int64

)

ENGINE = ReplacingMergeTree(_peerdb_version)

PRIMARY KEY id

ORDER BY id;

_peerdb_synced_at gives you per-row freshness without building a heartbeat. _peerdb_is_deleted = 1 rows are deletes you should filter out in queries (or collapse with FINAL).

The differences that matter at this stage:

• Lag, throughput, error rate, and per-table replication status are first-class metrics in the UI.


• DDL on the source side does not break replication. Adding columns just propagates. Some type changes still require a resync.


• You can pick the destination engine, select columns, and apply basic transformations in the pipe.


• Replication slot lifecycle, ClickHouse upgrades, disk pressure, and backups are not your problem.

The line item is bigger than self-hosted. The honest comparison is what one engineer's day is worth, multiplied by the days that used to go into the pipeline.

A real query: Postgres vs ClickHouse

The whole reason you set up CDC is so this query stops killing your primary:

-- Postgres: 30-day daily active count by country with a join,

-- on a few tens of millions of events. Slow, eats CPU, blocks transactions.

SELECT date_trunc('day', e.created_at) AS day,

       u.country                       AS country,

       count(*)                        AS events,

       count(DISTINCT e.user_id)       AS dau

FROM   events e

JOIN   users  u ON u.id = e.user_id

WHERE  e.created_at >= now() - INTERVAL '30 days'

GROUP  BY 1, 2

ORDER  BY 1 DESC, events DESC;

-- ClickHouse equivalent against the CDC tables. FINAL deduplicates

-- ReplacingMergeTree rows; _peerdb_is_deleted filters tombstones.

SELECT toDate(e.created_at)  AS day,

       u.country             AS country,

       count()               AS events,

       uniqExact(e.user_id)  AS dau

FROM   events AS e FINAL

LEFT JOIN users AS u FINAL ON u.id = e.user_id

WHERE  e.created_at >= now() - INTERVAL 30 DAY

  AND  e._peerdb_is_deleted = 0

GROUP  BY day, country

ORDER  BY day DESC, events DESC;

ClickHouse's published Postgres-to-ClickHouse modeling guide reports the same kind of query going from minutes on Postgres to milliseconds on ClickHouse on similar shapes. Specific numbers depend on cardinality, partition layout, and whether you have a projection.

Tuning the destination beyond the auto-created defaults

Once you outgrow ReplacingMergeTree ordered by PK, the move is to define your own destination tables and have ClickPipes write into them with selected columns and a custom engine. A typical pattern:

-- Tuned for date-range scans; partitioned for cheap retention

CREATE TABLE events_tuned

(

    user_id     Int64,

    event       LowCardinality(String),

    created_at  DateTime CODEC(Delta, ZSTD(1)),

    payload     String,

    _version    Int64,

    _deleted    Int8

)

ENGINE = ReplacingMergeTree(_version)

PARTITION BY toYYYYMM(created_at)

ORDER BY (created_at, user_id);



-- Pre-aggregated rollup fed off events_tuned via a materialized view

CREATE TABLE daily_event_rollup

(

    day      Date,

    event    LowCardinality(String),

    events   AggregateFunction(count),

    dau      AggregateFunction(uniq, Int64)

)

ENGINE = AggregatingMergeTree

ORDER BY (day, event);

AggregatingMergeTree keeps the rollup tiny and dashboards fast. Materialized views consume from the CDC table and write the partial aggregates. None of this is reachable on the MaterializedPostgreSQL engine, where you do not get to choose.

Capability comparison

When to pick which

• 
  Small team, low budget, simple analytics needs. MaterializedPostgreSQL. One container, sub-second replication, ship the same day. Build a runbook for the DDL trap before you hit it. Note that this option is gone the moment you move to ClickHouse Cloud.


• 
  You already operate Kafka or need event streaming for reasons beyond CDC. Debezium and Kafka. Maximum flexibility, real cost, only worth it if the rest of your platform earns the Kafka tax.


• 
  Past the early stage, real analytics traffic, do not want to operate the pipeline. ClickHouse Cloud and ClickPipes.

The shape most teams converge on: MaterializedPostgreSQL first because you can ship it in an afternoon, then ClickPipes (or Kafka if the rest of your platform is going that way) when the trade-offs of the simple setup cost more than the operational tax of the managed one. Over-engineering at the start is the most expensive mistake, because you do not yet know which trade-offs will matter to you. Staying on the simple setup after you do know is the second most expensive.