Postgres to ClickHouse CDC in Real Time (No Kafka)
Build a real-time Postgres to ClickHouse CDC pipeline with Estuary, no Kafka, no Debezium, no streaming infrastructure to manage. Stream live changes from Postgres into ClickHouse Cloud in about a second using log-based change data capture, then fan the same data out to Google Sheets from a single capture.
How do you stream Postgres to ClickHouse in real time without running your own Kafka Connect cluster? Estuary captures changes straight from the Postgres write-ahead log (WAL), lands them in a managed collection, and materializes that collection into ClickHouse with exactly-once delivery. Because the data sits in a reusable collection, you can add a second destination like Google Sheets later without re-reading the source database. This build walks through local Postgres in logical replication mode, the replication user and watermarks table, the flow YAML, merge vs delta updates, hard vs soft deletes, and automatic schema evolution when a new column appears at the source.
Chapters
00:00 Intro: real-time Postgres to ClickHouse CDC
01:04 How the pipeline works
03:19 Prepare Postgres for CDC
07:02 Simulating live traffic
08:17 The flow YAML explained
09:53 ClickHouse and merge vs delta
11:31 Connect and capture in Estuary
13:39 Materialize to ClickHouse
15:51 Fan out to Google Sheets
17:02 Handling deletes
18:13 Live schema evolution
20:36 flowctl CLI and wrap-up
What you'll learn:
Why log-based Change data capture beats query polling for real-time replication
How to configure Postgres for CDC: logical wal_level, replication slot, publication, replica identity full
Why the flow_watermarks table must be in your publication (the backfill-hang gotcha)
The difference between merge and delta updates in a ClickHouse materialization
When to use hard delete vs soft delete (production mirror vs audit history)
How schema drift is handled automatically when a column is added upstream
How one capture feeds multiple destinations without extra load on the source
LINKS
Try Estuary free: https://dashboard.estuary.dev/register
Docs: https://docs.estuary.dev
Tools used: Postgres, ClickHouse Cloud, Google Sheets, Docker, ngrok, flowctl, Estuary, Python
#Postgres #ClickHouse #CDC #ChangeDataCapture #DataEngineering #Estuary #RealTimeData #DataPipeline #ETL #Debezium #StreamingData #analytics
This is a Sponsored Video
🎬 Watch My Daily Data & AI Shorts: / @dataandaiguyshortform