How We Pushed CDC into Postgres

The quest to integrate transactional databases with analytical databases has long been a daunting challenge. The need for reliable, cost-effective, and low-lag data replication compounds this difficulty. At Snowflake, the commitment to addressing this issue became a top priority, leading to the development of a revolutionary Postgres service.

The core of the solution lies in data mirroring, a feature set to be released in public preview. This innovative approach involves pushing changes directly from Postgres into Apache Iceberg tables, enabling transactional consistency and low-cost replication. By leveraging Apache Iceberg tables and object storage like Amazon S3, Snowflake Postgres achieves highly resilient data replication with minimal infrastructure.

The process begins with logical decoding in Postgres, which exposes changes as a stream of row-level operations over a network. However, this method leaves much to be desired, as it fails to account for complex interplays between continuous data, schema changes, snapshots, and failures. To overcome these challenges, Snowflake devised a push-based change data capture system.

This system uses a new Postgres extension called snowflake_cdc to push batches of changes into per-table change logs and a meta log. By decoupling the producer and consumer via object storage, the system effectively eliminates a multitude of infrastructure-related issues, allowing for a simple, reliable, and out-of-the-box experience.

The timeline of the database plays a crucial role in this replication process. Each write to Postgres goes through four stages - decoder, capture, apply, and failure handling. The decoder relies on a historic snapshot of the catalog tables to interpret binary WAL records as logical row changes, even in the face of table changes or drops.

The capture process then appends finalized batches to Iceberg change logs and writes records into the meta log, which in turn is consumed by the apply process in Snowflake. This state machine ensures that schema changes are correctly sequenced into the stream of changes, providing a seamless and reliable replication experience.