Making data from transactional databases accessible to analytical databases is a crucial component of contemporary data architecture. However, this endeavor often encounters challenges such as fragile tooling, elevated costs, and intricate operations. At Snowflake, the development of a Postgres service led us to prioritize the resolution of these issues, ultimately driving us to reimagine Postgres replication from the ground up.
Optimizing Postgres replication
Postgres is renowned as an exceptional operational database; however, its change data capture (CDC) capabilities have room for improvement. Many data pipelines suffer from fragility due to the complexities involved in managing the continuous flow of data alongside schema changes, snapshots, and potential failures. To create a dependable, user-friendly experience with Snowflake Postgres, we recognized the necessity of reinventing the replication process entirely.
Introducing data mirroring, a new feature in public preview that facilitates highly resilient data replication into Snowflake, characterized by low cost, minimal lag, and transactional consistency. This innovative approach operates by directly pushing changes from Postgres into Apache Iceberg™ tables in transactional batches. These batches are then automatically and transactionally applied to tables in Snowflake, all without the need for additional infrastructure.
The elegance of this system lies in its simplicity: “transactional push into the data lake, transactional apply in Snowflake.” This transformation turns replication from a chaotic process fraught with numerous failure conditions into a seamless mechanism that functions reliably over time. With just the press of a button, users can have their Postgres tables readily available in Snowflake.
From pull to push: moving change data capture into Postgres
Change data capture involves the process of recording changes from a transactional database in a manner that allows for their replay on another system. In Postgres, the primary method available is known as logical decoding. This technique decodes WAL records into logical row-level insert, update, and delete operations, which are then streamed over the network. However, from that point onward, the responsibility falls heavily on the client.
In reality, replication encompasses numerous additional steps, including backfilling, managing schema changes, handling various table operations, creating new table snapshots, recovering from failures, merging changes efficiently, and preserving transaction boundaries. Even the built-in logical replication in Postgres addresses only a fraction of these complexities.
A significant limitation of the logical decoding approach is that the external system receiving the changes lacks awareness of the state of Postgres. For instance, it cannot detect when schema changes occur, how table snapshots correlate with changes, or whether the issue lies with Postgres itself or the network.
The solution to this challenge is straightforward: push the changes from Postgres into a data lake, specifically into Iceberg tables utilizing compressed Parquet format. Object stores like Amazon S3 are not only highly scalable and reliable but are also frequently employed for Postgres backups, making them an ideal destination for change data capture.
The mirroring process employs a new Postgres extension called snowflake_cdc, which continuously pushes batches of changes into per-table change logs and a “meta log” in the background, utilizing base workers. The advantage of leveraging an extension is its inherent understanding of the Postgres environment. It can meticulously coordinate schema changes alongside complex data manipulation language (DML) and data definition language (DDL) transactions, taking snapshots while simultaneously pushing changes and ensuring alignment between the snapshots and the changes.
