How we pushed CDC into Postgres — and turned replication into clockwork

Making data from transactional databases available to analytical databases is an essential part of any modern data architecture. It is also a perpetual battle against fragile tooling, high costs and complex operations. When we started building a Postgres service at Snowflake, solving this problem naturally became our number one priority.

This post is a deep dive into the engineering behind data mirroring: how we reimagined Postgres replication from the ground up.

Optimizing Postgres replication

Postgres is an amazing operational database, but its change data capture (CDC) story still leaves much to be desired. Many pipelines end up being fragile because replication tools are burdened with handling the complex interplay between continuous data and schema changes, snapshots and failures. To build a reliable, out-of-the-box experience for Snowflake Postgres, we were going to have to reinvent Postgres replication from the ground up.

Data mirroring is a new Snowflake Postgres feature in public preview to perform highly resilient data replication into Snowflake with low cost, low lag and transactional consistency. Under the covers, it works by pushing changes directly from Postgres into Apache Iceberg™ tables, in transactional batches. The batches are automatically applied to tables in Snowflake — transactionally and serverlessly.

The simplicity of “transactional push into the data lake, transactional apply in Snowflake, no extra infrastructure” changes replication from a chaotic process with many complex failure conditions to a simple clockwork that will run forever.

You press a button, and you have your Postgres tables in Snowflake.

From pull to push: moving change data capture into Postgres

Change data capture is the process of capturing changes from a transactional database in a form that allows them to be replayed on another system.

In Postgres, the primary facility available is called “logical decoding,” which refers to the decoding of WAL records into logical row-level insert/update/delete operations. The operations are exposed as a stream over the network. From that point onward, the burden is on the client.

In practice, replication involves a lot more steps. Backfilling, schema changes, handling create/add/remove/drop table operations, new table snapshots, restarting on failure, merging changes efficiently, preserving transaction boundaries, right-sizing and more. Even the built-in logical replication in Postgres only handles a few of these aspects.

One of the problems with the logical decoding approach is that the external system consuming the changes knows nothing about the state of Postgres. For instance, it doesn’t know when schema changes happen, how table snapshots align with changes — or whether Postgres is even alive or it’s the network that’s down.

The solution to this problem is quite simple: Push the changes from Postgres into a data lake, and in our case into Iceberg tables (with compressed Parquet). Object stores like Amazon S3 are highly scalable and reliable and already used for Postgres backups all the time. It is the correct destination for change data capture, too.

Mirroring uses a new Postgres extension called snowflake_cdc that continuously pushes batches of changes into per-table change logs and a “meta log” in the background (using “base workers”). The benefit of using an extension is that it knows exactly what is happening in Postgres. It can carefully coordinate schema changes and complex data manipulation language (DML) and data definition language (DDL) transactions. It can take snapshots while also pushing changes and aligning the snapshots with the changes.