GoKawiilMaking 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.
... continue reading