Data Mirroring: How Snowflake Postgres Modernizes CDC

Replicating data from Postgres into Snowflake is one of the most common data challenges that our customers face. Since launching Snowflake Postgres earlier this year, one of our main goals has been to make it simple to bring your Postgres data into Snowflake. I’m happy to announce that Postgres mirroring, which automatically connects data from Postgres to Snowflake, is now generally available.

Data mirroring takes a new approach to change data capture (CDC) that optimizes ease of use and reliability while reducing cost and lag, and preserving transactional consistency across tables. This blog describes how we approached each of our design goals for mirroring.

Postgres data mirroring at a glance

Data mirroring replicates a set of tables and/or schemas from a Snowflake Postgres instance into a Snowflake database. You can set it up easily via the Snowflake Postgres UI or SQL, or just by asking Snowflake CoCo.

Unlike conventional replication solutions, data mirroring is built directly into Postgres and Snowflake without additional components, using only object storage as the intermediary. This allows secure, reliable, performant data transfer with a queryable change history from both sides.

Postgres pushes snapshots and small batches of change records for each table from Postgres directly into Apache Iceberg™ change logs in object storage using a snowflake_cdc extension. Snowflake runs a periodic serverless task that applies new changes to regular Snowflake or Snowflake-managed Iceberg tables at a configurable interval. Each table additionally has a $live view that gives low lag regardless of how often the apply task runs, by combining the mirrored tables with the pending changes.

Mirroring is designed to always keep running. It does this with fully automatic recovery from failures, automatic schema change replication, transactions to avoid partial failures, and by managing the full end-to-end pipeline from Postgres to Snowflake. It adds only the cost of storing changes and applying changes using serverless compute infrastructure.

Now let’s look at how we approached the different design aspects of mirroring.

How data mirroring creates highly resilient pipelines

One of the main challenges of building reliable systems is that it is a function of the amount of complexity in the system and the number of failures, and gracefully handling failures requires additional complexity.

CDC pipelines fail because they try to map all of the operations that can happen to a Postgres database into another database system. They do this while handling a large variety of failures and system configuration changes that are possible on the source, on the destination and in between. This problem is inherently extremely complex, and a failure in any link of the chain will cause the whole chain to fail. Moreover, each part of the pipeline is typically managed by a different vendor. As a user, you inevitably become the call leader for every outage.

Data mirroring is different. When you run your operational database on Snowflake Postgres and want to get your data into Snowflake, the entire pipeline is under our control and we can design and manage each subsystem to provide a reliable experience.

Mirroring uses several techniques to achieve high reliability:

No additional infrastructure: Push change records directly into S3
The most reliable dependency is no dependency at all. Instead of running an intermediate replication service that needs to be available and right-sized, data mirroring pushes changes directly from Postgres into an internal object storage stage, and Snowflake consumes the changes directly from there.

Using object storage as the intermediary avoids problems where Postgres starts filling up its disk in case of downstream issues in the replication service. Its replication slot can be consumed as long as object storage is available. Using object storage also enables various fast parallel data transfer techniques, and avoids network configuration and security issues.

No intermediate states on failure: Commit all or nothing
CDC involves many different tables, schema changes, truncation, configuration changes, and varying transaction sizes and volumes. When you combine that with aspects like network and storage failures, the system can end up in many different states that are hard to recover from.

Data mirroring addresses this by making CDC transactional. A batch of changes across tables is written to a set of Iceberg change logs in one Postgres-side transaction, and batches are applied in one Snowflake-side transaction. If something fails, everything rolls back and the batch is retried. Avoiding intermediate states keeps the system relatively simple and predictable, though of course both Postgres and Snowflake have a lot of sophisticated failure handling logic underneath their transaction management.

The meta log:
Change replication involves capturing row-level insert, update, and delete operations in a log, and then replaying that log on the other side. Reliable change replication also involves handling table add/create/drop/remove/rename, column add/rename/drop/type change, schema renames/moves, restart, truncate and other operations.

Data mirroring handles this via a meta log, a “log of log changes”, which specifies all the high-level operations that happen to the mirror. The meta log is written to Iceberg in the same transaction as the change logs it references, and can be used to coordinate schema changes, type mappings, resnapshotting and other operations. The apply task in Snowflake is effectively a state machine that takes its inputs from the meta log, with each transition being a transaction.

Automatic recovery:
Snowflake Postgres ultimately prioritizes the availability of the application (read: writes) over replication availability. If the machine is overloaded, or there are unforeseen problems like an S3 outage, there comes a point where Postgres might “invalidate” the replication slot that controls how much of the write-ahead log to retain for CDC. This is normally a point where every other replication tool would give up.

In data mirroring, when we observe an invalidated replication slot or even a dropped slot, the system will later automatically resnapshot tables and coordinate a restart via the meta log. In the meantime, the data on the Snowflake side remains queryable and tables (and grants and views) are preserved. That way, a catastrophic replication failure ultimately becomes a momentary increase in lag without needing operator intervention.

End-to-end pipeline management and development:
An important aspect of data mirroring is that all parts of the pipeline are managed for you. The Snowflake team is monitoring all mirrors for failures and delays and can act on issues in any part of the pipeline via runtime mitigations and code improvements.

Importantly, we did not build mirroring from scratch. We built data mirroring on top of our robust pg_lake investments that span over 2.5 years, and other mature features in Snowflake like Iceberg and serverless tasks. Of course, there is a substantial amount of testing, including fault injection at every part of the pipeline.

Move your data without the cost of moving your data

Over the years we have seen cases where a replication pipeline often costs more than the system it’s replicating from, and causes a lot more headache. One of the big advantages of data mirroring is that it only charges for the cost of Snowflake compute applying changes (also applicable to other replication solutions) and temporary storage.

To further reduce costs, we use several techniques:

Exactly-once delivery:
The biggest Postgres tables are typically append-only and continuously taking new inserts. By carefully using transactions and hooking into Postgres internals, data mirroring captures and processes all rows and row changes exactly once. For instance, a row is either in the snapshot, or emitted as an insert, but never both. A significant benefit is that inserts can be directly appended to the target tables without the cost of scanning the existing data for duplicates, which makes the apply a lot cheaper.

Serverless compute in Snowflake:
Any CDC solution that does end-to-end replication into Snowflake will incur compute cost to apply changes to target tables, especially for update/delete operations to columnar storage. Data mirroring optimizes this cost by using serverless tasks, which are billed by the second. Applying changes typically only adds a few seconds per table.

Deferred apply with live views:
Applying changes infrequently in bigger batches is computationally cheaper than applying frequently in small batches, but has the downside of increasing lag. Data mirroring allows you to still get low lag even when you defer the apply by using $live views. You can even share the $live views with other Snowflake accounts to defer the compute cost of the views.

Compressed storage:
Snapshots and change batches are kept in object storage for a few days in Parquet format. In practice, the temporary storage is many times smaller than the size of the source tables (not even counting indexes), and it’s billed at regular Snowflake storage cost.

Reducing lag

Lag refers to the time between a transaction being committed and it becoming visible in Snowflake. This is never a single value, since it’s different for every transaction, and varies based on timing.

A dominant input into the lag is how frequently changes are applied to Snowflake tables, though data mirroring distinguishes between the lag of the target table and the $live view, with the latter providing relatively low lag regardless of the apply interval.

Low-lag live views:
There is an inherent trade-off between lag and cost in CDC pipelines. Data mirroring addresses this by providing a $live view that combines unapplied changes with the target tables at query time, allowing you to defer table application without waiting for all changes to be applied.

Data mirroring has several important design aspects that implement efficient live views:

1. Change logs are in Iceberg and therefore efficiently queryable from Snowflake
2. Updates are split into delete and insert
3. Inserts contain values for all the columns, even large columns that are not updated

This combination means that the $live view can union the target table minus the unapplied deletions with the unapplied inserts minus the unapplied deletions. The use of a union is important, because it enables filters on queries on the $live view to “push down” into both sides, meaning that we can use Iceberg statistics to prune out any change batches that are not needed for the query. Moreover, Snowflake is heavily optimized for this type of query.

Live views give you a combination of low lag and low apply cost, with slightly increased query cost, but with a lot of deliberate optimization for the latter.

Lag cycle times:
Two values still bound the lag. Change batches are pushed from Postgres periodically, every 15 seconds, and Iceberg change logs are auto-refreshed in Snowflake every 30 seconds. Lag is an area we’re continuously improving, and we’ll have some really exciting announcements in this area soon.

High-throughput snapshots and change feed:
In an idle system, lag is a function of end-to-end response and cycle times, but under heavy load, lag becomes a function of throughput. On bigger instances, a mirror can snapshot over 1M rows/sec, and sustain throughputs of well over 100k row changes/sec, which satisfies the requirements of most workloads.

Transactional consistency across systems

As database engineers, part of our goal in life is to make transactions atomic, consistent, isolated and durable (ACID). When doing database replication, those properties should be preserved as much as possible. For instance, if the source database enforces consistency between tables via foreign keys, then so should the replica, otherwise the data is effectively corrupted at read time.

The change batches that data mirroring pushes into change logs represent an ordered sequence of Postgres transactions, and the end of a batch represents a transaction boundary (a commit). The batches for different tables are then pushed into Iceberg and made available to Snowflake in one Postgres-side transaction, and Snowflake applies batches from different tables in one Snowflake-side transaction.

These simple mechanics mean that the target tables of the mirror follow the Postgres write transaction boundaries, and the target tables are both internally consistent (for example, no duplicate keys) and consistent with each other (foreign keys hold). So, when joining mirrored tables with each other, you always get correct results. No orders from missing customers.

Coming soon: Snowflake to Postgres mirroring

One more neat thing about mirroring from Postgres to Snowflake is that everything we discussed actually works in the opposite direction. Snowflake could push change logs into an internal stage, and Postgres could consume those via pg_lake.

Data mirroring from Snowflake to Postgres is coming soon, allowing full bi-directional data exchange in Snowflake Postgres.

Get started with mirroring

• Data mirroring overview
• Create your first mirror
• Watch the webinar: "Replicate Data from Postgres to Snowflake Analytics in Real Time with Data Mirroring"
• Postgres mirroring documentation

Forward-looking statements
This article contains forward-looking statements, including about our future product offerings, and are not commitments to deliver any product offerings. Actual results and offerings may differ and are subject to known and unknown risk and uncertainties. See our latest 10-Q for more information.