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.
Push-based change data capture avoids a whole class of infrastructure and associated problems, and effectively decouples producer and consumer via object storage.
Untangling replication timelines
When building a replication system like data mirroring, an important aspect is the timeline of the database. Replication processes deal with the state of the database in the recent past.
Each write to Postgres effectively goes through four stages, which each represent a continuous process operating on the same timeline, but at a different point in time:
- Write: Write modifies the table and adds to the WAL (in the “now”)
- Decode: Past WAL is translated to a row-level change
- Capture: Past row-level changes are captured in batches
- Apply: Past change batches are merged into the destination tables
The decoder process relies on a special facility in Postgres to read the catalog tables as they were at the time of the write (a “historic snapshot”). That way, binary WAL records can be understood as logical row changes, even if a table was already changed or dropped at the moment the record is decoded. In the case of data mirroring, the records go into temporary files.
Periodically, the decoder gets a signal to finalize its current batch and send a message to the capture process that a batch is ready. The capture process appends the finalized files to Iceberg change logs, writes a record into the meta log and tracks the replicated LSN. Schema changes follow the same write → decode → capture path and can result in new change logs.
The new meta log and change log records appear in the Iceberg tables. The apply process in Snowflake then acts as a finite state machine that executes all the instructions in the meta log. When the operation is a change batch (the common case), all the adjacent change batches are processed together for each table.
This approach helps ensure that schema changes are correctly sequenced into the stream of changes, even if they happened as part of a transaction that did additional writes. If there was an unexpected failure that caused WAL to be dropped, Postgres can automatically push new snapshots and instruct Snowflake to consume them, which is very rare in practice due to using failover slots.
Transactions as a distributed systems building block
Database systems can hide an enormous amount of complexity related to system and hardware failure through one simple primitive: transactions.
If a transaction fails due to a lower-level system failure, nothing happens, and you try again. If a transaction succeeds, you can make sure that it will never do that same work again.
The frustration we feel when dealing with a process like extract, transform, load (ETL) or CDC happens because suddenly transactions go out the window, and we have to deal with all of the different failure modes ourselves.
Our first answer to this problem has been Postgres for your data lake, the managed version of our open source pg_lake extension — now generally available. It gives Postgres the unique ability to perform transactions across Postgres tables and Iceberg tables. ETL usually requires external tools, and users need to design for idempotency and do very careful bookkeeping. Now, you can just use SQL to delete from a Postgres table, insert into an Iceberg table and commit. At that point, the data is queryable in Snowflake. This approach is versatile, but SQL does not lend itself to end-to-end replication of high-frequency updates, so that’s the layer that data mirroring adds.
Under the covers, data mirroring takes full advantage of pg_lake and Snowflake’s Iceberg implementation. It will take batches of data and schema changes from Postgres tables and push them into multiple change logs in Iceberg in one Postgres-side transaction. Snowflake then merges multiple batches at a time in a Snowflake-side transaction. That means all Snowflake tables are moved forward in one transaction, exactly to a Postgres transaction boundary, preserving foreign keys and join correctness.
The transactional replication approach scales to very high throughput and avoids most of the usual failure and race conditions of conventional cross-system replication.
High performance apply and live views
There is another important thing that transactions enable: correctness at scale.
A common approach to replication is to turn every operation into a type of upsert. The reason for doing that is that it is very hard to solve the consistency problem between table snapshots and changes, as well as changes that might get replayed multiple times after a failure. However, the upsert approach has several problems:
- There can be inconsistent intermediate states on the destination, especially when a table is added
- Inserts actually become very expensive to replicate because they need to be matched against existing rows in the target table, which is very expensive with columnar storage
- It becomes very hard or impossible to efficiently combine recent changes with the target table
When we make replication a transactional process that’s controlled from Postgres, we do not have the same constraints. We can make a stream of perfect deletions and insertions that are applied exactly once, without the risk of an insert already having appeared in a snapshot or another change batch.
What that means is:
- Replication of insert-heavy workloads (usually the biggest tables) is extremely fast and cost-efficient, because inserts are appended, never upserted.
- Recent changes can be efficiently combined with the existing data.
That brings us to live views.
Live views are a feature of data mirroring that combines the unapplied changes in the per-table change logs with the data in the target tables. What’s important is that any filters and projections in the query can be directly pushed down into the storage layer and table scans on both the Parquet files in the change log and the base table. In other words: Live views are fast.
With live views, it is no longer necessary to apply changes very often to have low lag. Even if you apply infrequently, the live view lag will still be well below a minute, and you still get high performance queries, with only slightly higher overhead.
Replication as clockwork
The combination of push-based CDC, careful design around timelines, transactional boundaries on both sides and live views means mirroring turns replication from a chaotic process into a Swiss clock. There are no external connectors that can fall behind. No snapshots that can conflict with changes. No upserts that slow down as tables grow. There’s a Postgres extension pushing batches into object storage, and Snowflake applying them — both transactionally, both independently, both indefinitely.
You set it up once. It runs forever.
Data mirroring for Snowflake Postgres
Historically, your operational and analytical workloads have lived in different worlds. Those worlds used to be stitched together with brittle pipelines and additional systems adding to cost, with a world of pain and suffering sprinkled in. Our focus is to unify these worlds with solid foundations on both sides with simple yet well-engineered solutions.
With Snowflake Postgres you have production-grade Postgres you can trust, and now flexibility with two ways to unify your workloads:
- Data mirroring: The public preview gives you always-on, automatic replication from Postgres to Snowflake. You set it up once — your tables, including schema changes, stay in sync continuously.
- Postgres for your data lake: Generally available, this gives you flexible, developer-controlled data movement between Postgres and Snowflake using open formats like Iceberg. You write SQL, and data moves when and how you want.
Get started with Snowflake Postgres
If you’re ready to get started, you can check out these links:
Instagram Engineering
Shopify Engineering