Unifying Postgres and ClickHouse Natively

PostgreSQL and ClickHouse are two of the most popular open-source databases, powering transactional and analytical workloads across thousands of mission-critical applications. But what does it take to bring these two fundamentally different database systems together?

In this talk, we’ll explore what we’re seeing in the field as customers increasingly look to bring OLTP and OLAP closer together, the challenges they face, and how these real-world requirements are shaping our engineering efforts at ClickHouse. We will discuss two components:

WalShadow: A replication architecture that consumes PostgreSQL’s physical write-ahead log (WAL), bypassing traditional logical decoding to achieve replication latencies in the hundreds of milliseconds. We’ll explore its architecture and the challenges around consistency, schema evolution, and recovery.

pg_clickhouse: A PostgreSQL extension that brings ClickHouse’s analytical capabilities into PostgreSQL. We’ll explore how it enables transactional and analytical queries through a single interface, including the architecture and challenges of query pushdown across two different execution engines.

The talk will offer a look at the latest innovations in unifying OLTP and OLAP with PostgreSQL and ClickHouse, the technical challenges involved, and how real-world production workloads are shaping these architectures.

Transcript

1. Unifying Postgres and ClickHouse Natively Sai Srirampur Director, ClickHouse
2. About me • Lead the Postgres efforts in ClickHouse • Co-founder and CEO of PeerDB (acq. ClickHouse) • PeerDB  Postgres specialized replication product • Ex Microsoft Azure Postgres Team) and Citus Data (acq. Microsoft) Sai Srirampur
3. PostgreSQL Most popular open source Transactional Database Fastest Analytical Database in the world • • • • ACID compliant transactions • Low-latency read and writes • Rich SQL + extension framework Incremental Materialized Views • Open Source • • • • Open Source Vast ORM and app ecosystem Columnar Storage for fast aggregations Table Engines Skip indexes + Materialization Native JSON and full-text search Two great databases One unified Stack
4. Many companies use Postgres and ClickHouse to solve most of their data problem + OLTP OLAP
5. Why unifying Postgres and ClickHouse? We sampled 10 of 1000 AI customers using Postgres ClickPipes. Data grew 1,000% in six months. They hit the analytics wall faster than any other category.
6. A unified OLAP  OLTP stack = three engineering projects Stand up ClickHouse at scale. Bridge them. Every box in this diagram is a team's worth of operational work, and every team running the pattern owns all three. Ingestion pipeline Postgres Debezium Kafka Replication slot Topic A Backends, Ruby, Go Python Topic B Yoda Flink Topic C Android Event ingester Topic … iOS Public Instacart architecture · OLTP Postgres) + sync Debezium / Kafka / Flink) + OLAP ClickHouse) ClickHouse
7. ClickHouse Managed Postgres Fast, scalable, enterprise-grade Postgres, natively integrated with ClickHouse Native CDC WalShadow
8. Two pillars of unifying Postgres and ClickHouse 01 Native CDC with WalShadow Transform Physical WAL to ClickHouse native blocks, sub-second replicaiton 02 Unified querying using pg_clickhouse Postgres extension / foreign data wrapper to query ClickHouse from Postgres
9. Introducing WalShadowx Sub-second Postgres to ClickHoxuse replication, straight from physical WAL No logical replication and operational overhead around it
10. Why build WalShadow when Postgres has default CDC mechanism?
11. Why we built WalShadow? Logical decoding in Postgres has a bunch of caveats • Shares resources with primary Postgres ◦ • Adds CPU, memory and IO load to the database Logical replication slots retain WAL ◦ If consumption falls behind, retained WAL can fill the disk and disrupt Postgres. • Lacks support for schema changes. • Single threaded limiting throughput 1020K rps) • Build keeping Postgres as target in mind, not ClickHouse
12. Why we built WalShadow? WalShadow overcomes hassles of logical replication • No load on primary and no sharing storage with Primary ◦ • Orders of magnitude better performance ◦ • Parallel decoding and ingestion Support for advance schema changes ◦ • Disaggregating decoding from Postgres ALTER TABLE ADD / DROP COLUMN, CHANGE TYPE, RENAME COLUMN etc. Purpose-built for Postgres to ClickHouse replication
13. Technical Challenges while building WalShadow Decoding Postgres Physical WAL outside Postgres is HARD! • Maintaining a consistent snapshot of the Postgres catalog outside Postgres • Decoding WAL in parallel while correctly handling schema changes • Ingesting data natively into ClickHouse from outside ClickHouse • Durability and recovery • Supporting complex Postgres features, such as subtransactions • And many more.. WAL is a complex data structure with decades of engineering!
14. WAL contains bytes, not a self-describing schema How to get a consistent snapshot of the Postgres catalog at the time a WAL record was created? ILLUSTRATIVE HEAP UPDATE Record LSN Transaction Operation Present in the record 0/180 74291 Heap UPDATE PHYSICAL RELATION IDENTIFIER 1663 / 16384 / 24576 Physical relation identifier Page and tuple references 01 Encoded tuple data Requires schema metadata Tablespace OID / Database OID / Relation file number Block 42 • Table and schema names tuple offsets 7 → 9 T SELECTED TUPLE VALUE BYTES … 2a 00 00 00 64 00 00 00 … Column names and SQL types Column order and tuple layout
15. WalShadow reconstructs schema context Catalog replay supplies versioned metadata to the Rust decoder. Catalog records Shadow PostgreSQL Capture schema changes Catalog replica Physical replay Versioned descriptors LSN-indexed relation metadata Metadata lookup by relation + record LSN Source PostgreSQL Physical WAL WAL reader and filter Heap records Table / columns / types / layout Rust decoder Buffer + Batcher ClickHouse Resolve relation Decode tuple Convert / parallel inserts Native blocks Choose the schema valid at the record’s LSN 0/100 · v1 id int4, amount int4 0/180 · UPDATE Decode with v1 0/200 · v2 RENAME amount → total LSN Select greatest valid_from LSN ≤ record LSN
16. From physical WAL to a named, typed row Select the applicable descriptor, then use its schema to decode the bytes. 1 Read identifiers + LSN 2 Select descriptor version 5 Named, typed values Heap UPDATE · xid 74291 0/100 · v1 0/200 · v2 public.orders 1663 / 16384 / 24576 Latest valid_from ≤ 0/180 Column Type Value Tablespace / Database / Relation file number 3 Resolve public.orders id integer 42 amount integer 100 Record LSN 0/180 SELECTED TUPLE BYTES … 2a 00 00 00 64 00 00 00 … ← selected (after record) Position Column Type 1 id integer 2 amount integer Names + types + layout 4 Decode using schema Batch → ClickHouse 2a 00 00 00 → 42 64 00 00 00 → 100 Native INSERT blocks Names and types come from the descriptor. Values come from tuple bytes. Illustrative little-endian int32 values. Actual decoding handles headers, nulls, alignment and TOAST.
17. Parallel decoding and insertion Parallel within and across transactions, with commit and schema boundaries. Catalog record Physical WAL Shadow Postgres Catalog replay Heap record Sequential WAL filter Skip unneeded WAL (e.g. index records) Rust decoders Decoder 1 T1 part 1 Versioned descriptors Schema by LSN Commit or abort Transaction buffers Schema at the record’s LSN. Decoding waits until it is available. Batch by table Table A batch T1 + T2 changes Shared inserter pool Worker 1 Worker 2 Worker 3 Decoder 2 T1 part 2 Decoder 3 T2 / T3 Commit: release Abort: discard Table B batch T1 + T3 changes Any worker can take any ready table batch ClickHouse Eventual per-table convergence, no cross-table atomicity.
18. ReplacingMergeTree in ClickHouse The highest source LSN wins when row versions merge. An orders table in ClickHouse CREATE TABLE orders ( id UInt64, amount Decimal(10, 2), status String, _lsn UInt64 ) ENGINE = ReplacingMergeTree(_lsn) ORDER BY id; id = source primary key _lsn = row version Two versions can arrive out of order Arrival id amount status _lsn 1st 7 90.00 paid 200 2nd 7 70.00 pending 100 Retained row Background merge id amount status _lsn 7 90.00 paid 200 ORDER BY matches row versions. Insertion does not enforce uniqueness. Eventual convergence: replacement happens during background merges.
19. Schema changes during parallel decoding Schema boundaries coordinate catalog replay, decoding, and insertion. 1 Mark schema boundaries Catalog records Physical WAL Sequential WAL filter Heap records Shared boundaries in the LSN timeline Shadow Postgres Catalog replay Versioned descriptors Boundary at LSN B 2 Decode in parallel Wait for catalog replay to reach the record’s LSN. Use the schema at that LSN. Decoder 1 Transaction buffers Decoder 2 Commit: release Abort: discard Decoder 3 Each row uses the schema valid at its own LSN. Global counters and semaphores coordinate these steps. 3 Preserve insertion order Shared inserter pool By table Write old rows Apply schema change ClickHouse Write new rows
20. Other fun challenges and optimizations • • Recovery and durability ◦ LSN based ◦ Tightly coupled with backups in S3 We use WALG! Ingesting natively ◦ • Handling TOAST (super large) columns. ◦ • Keep historical state of TOAST columns in Shadow PostgreSQL or ClickHouse Sub-transactions ◦ • clickhouse-c and clickhouse-c-rs Unsupported And more…
21. REPLICATION LATENCY WalShadow Performance 50 Faster Physical Baseline Our Architecture Postgres streaming replica WalShadow Native physical streaming protocol 200 50 ms Theoretical latency floor Logical CDC Near the streaming replica floor ms Direct physical WAL replication to ClickHouse PeerDB / ClickPipes Standard logical decoding pipeline 10 s Traditional batching overhead
22. Demo
23. Q&A
24. pg_clickhouse A Postgres foreign data wrapper to query ClickHouse from Postgres Provide ClickHouse query execution from PostgreSQL Push down query execution to ClickHouse TPCH 18 of 22 push down). ClickBench (full push down). Minimize app and query migration while adopting ClickHouse for analytics Create foundation for continual query and pushdown evolution
25. Without pg_clickhouse SELECT AdvEngineID, COUNT(*) With pg_clickhouse SELECT AdvEngineID, COUNT(*) FROM hits FROM hits WHERE AdvEngineID <> 0 WHERE AdvEngineID <> 0 GROUP BY AdvEngineID GROUP BY AdvEngineID ORDER BY COUNT(*) DESC; ORDER BY COUNT(*) DESC; Time: 25838.272 ms Time: 470.377 ms
26. Callbacks connect PostgreSQL to the FDW A callback is a function that PostgreSQL calls at a defined stage. Inside the FDW handler Simplified C with example function names PostgreSQL planner PostgreSQL executor FdwRoutine *r = makeNode(FdwRoutine); r->GetForeignRelSize = estimate_rows; r->GetForeignPaths = propose_paths; r->GetForeignPlan = build_plan; r->BeginForeignScan = begin_scan; r->IterateForeignScan = next_row; r->ReScanForeignScan = restart_scan; r->EndForeignScan = end_scan; return r; PostgreSQL owns the query lifecycle. The FDW supplies the callbacks. 02 / 05 FdwRoutine Function pointers Your FDW functions
27. Thank you! Sai Srirampur @saisrirampur https://www.linkedin.com/in/sai-krishna-srirampur-1741b019/
28. ADD COLUMN changes the catalog Physical replay reconstructs the new schema in shadow PostgreSQL. ALTER TABLE public.orders ADD COLUMN priority integer; Source PostgreSQL Physical WAL Shadow PostgreSQL Catalog rows change (e.g. pg_attribute) Transaction commits Catalog page changes + transaction records Filter keeps catalog + required recovery WAL Replay updates catalog WAL records describe physical changes, not an ALTER TABLE SQL string. Example: nullable column, no DEFAULT; DDL commits in its own transaction. 04 / 07 Verified: catalog replay. Example SQL and event sequence are illustrative. ClickHouse
29. Schema changes create new descriptor versions The descriptor log preserves both old and new layouts. 1 Reach capture boundary Hold successor WAL while shadow reaches that boundary. 2 Read affected schema 3 Publish descriptor history Names, types, column order and tuple-layout metadata. Commit makes required history durable before dependent work. public.orders — illustrative descriptor history Version valid_from LSN Source columns v1 0/100 id, amount v2 0/200 id, amount, priority valid_from selects the layout; captured_at records when it was captured. Illustrative LSNs. The effective boundary is not universally the DDL commit LSN.
30. Heap records select the applicable schema Choose the latest valid_from ≤ record LSN, never the nearest version. v1: id, amount v2: id, amount, priority 0/100 0/1F0 0/200 0/240 v1 starts heap record A schema boundary heap record B A at 0/1F0 → v1 B at 0/240 → v2 id = 42 id = 43 amount = 120 priority = 2 amount = 100 v2 is closer, but it is still in the future. LSN The new descriptor explains the third column. Historical schema remains available even after the shadow catalog advances. 06 / 07 Illustrative records and LSNs; schematic spacing. Lookup requires covered, unambiguous history. ClickHouse
31. ClickHouse changes schema between row batches A barrier keeps earlier rows, destination DDL and later rows in source order. 1 Finish earlier rows 2 Add the mapped column 3 Continue later rows Flush and wait for preceding data to be durable. Apply destination schema change before later rows. Insert batches containing the new priority column. ClickHouse destination after the change id amount priority (mapped type) 43 120 2 Verified: ADD COLUMN automatically adds a mapped destination column. 07 / 07 Example assumes automatic column mapping, with no pinned projection or type override. ClickHouse
32. Parallel decoding and insertion Transactions span decoders. Inserters group committed changes by table. Shadow Postgres catalog replay Physical WAL Sequential WAL filter Per-table inserters Decoder pool Decoder 1 T1 part 1 Decoder 2 T1 part 2 Decoder 3 T2 / T3 Decoders wait until the required descriptor LSN is available. 08 / 10 Versioned descriptors Schema at each LSN Transaction buffers & table batchers Commit: release Abort: discard Table A inserter T1 + T2 changes Table B inserter T1 + T3 changes ClickHouse ClickHouse