How to Build Reliable CDC from PostgreSQL to ClickHouse

Draft outline. This page exercises the publishing layout while the full article is being written and reviewed.

Define the delivery contract

Start with the guarantees the system actually needs: which changes must arrive, how duplicates are handled, and how operators can prove that the destination has caught up.

What correctness means

Document ordering, delivery, deletion, and replay semantics before selecting infrastructure. This section will turn those promises into concrete invariants.

Map the data path

Trace every boundary between PostgreSQL and ClickHouse, including the replication slot, change event transport, consumers, and destination tables.

Keep ownership explicit

Each stage needs one owner, an observable progress signal, and a recovery procedure that does not depend on memory.

Design recovery before throughput

The complete article will cover snapshots, replay windows, schema changes, poison events, and reconciliation. Throughput tuning comes after the recovery path is known to work.

SELECT
  slot_name,
  active,
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Observe the guarantees

The final section will define the lag, freshness, error, and reconciliation signals that make silent data loss visible.