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.将事务型数据库中的数据提供给分析型数据库，是现代数据架构中不可或缺的一部分。然而，这往往伴随着与脆弱的工具、高昂的成本以及复杂的运维之间的持久战。当我们开始在 Snowflake 构建 Postgres 服务时，解决这一问题自然成为了我们的首要任务。
This post is a deep dive into the engineering behind data mirroring: how we reimagined Postgres replication from the ground up.本文将深入探讨数据镜像（Data Mirroring）背后的工程技术：我们如何从零开始重新构思 Postgres 复制机制。
Optimizing Postgres replication优化 Postgres 复制
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.Postgres 是一款出色的操作型数据库，但其变更数据捕获（CDC）方案仍有待改进。许多数据管道因复制工具难以处理持续数据、模式变更、快照和故障之间的复杂交互而变得脆弱。为了给 Snowflake Postgres 构建一种可靠且开箱即用的体验，我们必须从底层彻底重构 Postgres 复制机制。
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.数据镜像（Data Mirroring）是 Snowflake Postgres 的一项新功能（现已开启公开预览），旨在以低成本、低延迟和事务一致性的方式，实现向 Snowflake 的高弹性数据复制。其底层原理是将变更从 Postgres 直接以事务批次推送到 Apache Iceberg™ 表中。这些批次随后会自动以事务化且无服务器（serverless）的方式应用到 Snowflake 的表中。
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.“推送到数据湖即事务，在 Snowflake 应用即事务，无需额外基础设施”的简洁性，将复制过程从充满复杂故障条件的混乱状态，转变为一种可以永久运行的精密时钟。
You press a button, and you have your Postgres tables in Snowflake.只需按下一个按钮，你的 Postgres 表就会出现在 Snowflake 中。
From pull to push: moving change data capture into Postgres从“拉取”到“推送”：将变更数据捕获移入 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.变更数据捕获（CDC）是指从事务型数据库中以某种格式捕获变更，以便在另一个系统中重放这些变更的过程。
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.在 Postgres 中，主要设施被称为“逻辑解码”（logical decoding），即将预写日志（WAL）记录解码为逻辑行级的插入/更新/删除操作。这些操作通过网络以流的形式对外输出。此后，后续处理的负担便落在了客户端身上。
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.在实践中，复制涉及的步骤远不止于此。例如回填数据、模式变更、处理表创建/添加/移除/删除操作、新表快照、故障重启、高效合并变更、保持事务边界、调整规模等。即便是 Postgres 内置的逻辑复制也只能处理其中少数几个方面。
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.逻辑解码方法存在的问题之一是，消费变更的外部系统对 Postgres 的状态一无所知。例如，它不知道模式何时变更、表快照如何与变更对齐，甚至无法判断 Postgres 是否存活，还是仅仅出现了网络故障。
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.解决这一问题的方案非常简单：将变更从 Postgres 推送到数据湖——在我们的案例中，即推送到 Iceberg 表（使用压缩的 Parquet 格式）。像 Amazon S3 这样的对象存储具有极高的可扩展性和可靠性，且常用于 Postgres 备份。对于变更数据捕获而言，这也是最合适的归宿。
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.数据镜像使用了一个名为 snowflake_cdc 的新 Postgres 插件，它在后台（使用“基础工作进程”）持续将变更批次推送到各表的变更日志和一个“元日志”中。使用插件的好处在于，它能精确知晓 Postgres 内部正在发生什么。它可以仔细协调模式变更以及复杂的数据操作语言（DML）和数据定义语言（DDL）事务。它既能在推送变更的同时拍摄快照，又能将快照与变更对齐。
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:对 Postgres 的每次写入实际上都要经历四个阶段。它们代表了在同一时间线上运行的连续过程，但处于不同的时间点：
- Write: Write modifies the table and adds to the WAL (in the “now”)写入（Write）：修改表并添加到 WAL（在“当前”时刻）
- Decode: Past WAL is translated to a row-level change解码（Decode）：将过去的 WAL 转换为行级变更
- Capture: Past row-level changes are captured in batches捕获（Capture）：将过去的行级变更分批捕获
- Apply: Past change batches are merged into the destination tables应用（Apply）：将过去的变更批次合并到目标表中
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.解码过程依赖于 Postgres 的一项特殊功能，即读取写入时的目录表状态（“历史快照”）。这样，即使记录在解码时表已经被修改或删除，二进制 WAL 记录也能被理解为逻辑行变更。在数据镜像中，这些记录会进入临时文件。
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.解码器会定期收到信号以完成当前批次，并向捕获进程发送批次就绪的消息。捕获进程将完成的文件追加到 Iceberg 变更日志中，在元日志中写入一条记录，并跟踪已复制的 LSN。模式变更遵循相同的“写入 → 解码 → 捕获”路径，并可能产生新的变更日志。
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.新的元日志和变更日志记录会出现在 Iceberg 表中。Snowflake 中的应用进程随后充当有限状态机，执行元日志中的所有指令。当操作为变更批次（常见情况）时，每个表的所有相邻变更批次将一起被处理。
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.这种方法有助于确保模式变更正确地排序在变更流中，即使它们是作为包含额外写入的事务的一部分发生的。如果发生意外故障导致 WAL 被丢弃，Postgres 可以自动推送新快照并指示 Snowflake 进行消费——不过由于使用了故障转移槽（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.我们在处理 ETL 或 CDC 等流程时感到沮丧，是因为事务突然失效了，我们不得不亲自处理所有不同的故障模式。
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.针对这一问题，我们的首个方案是“Postgres for your data lake”，即我们开源 pg_lake 插件的托管版本——现已正式发布。它赋予了 Postgres 在 Postgres 表和 Iceberg 表之间执行跨库事务的独特能力。ETL 通常需要外部工具，且用户需要为幂等性进行设计并进行非常细致的账目核对。现在，你只需使用 SQL 删除 Postgres 表中的数据、插入 Iceberg 表并提交即可。此时，数据在 Snowflake 中即可查询。这种方法非常灵活，但 SQL 本身并不适合高频更新的端到端复制，而这正是数据镜像所补充的层面。
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.在底层，数据镜像充分利用了 pg_lake 和 Snowflake 的 Iceberg 实现。它会在一个 Postgres 端的事务中，将 Postgres 表的数据和模式变更批次推送到 Iceberg 的多个变更日志中。随后，Snowflake 在 Snowflake 端的事务中一次性合并多个批次。这意味着所有 Snowflake 表都在一个事务中同步推进，精确对齐到 Postgres 的事务边界，从而保持外键和连接的正确性。
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:一种常见的复制方法是将每个操作都转化为某种形式的“更新插入”（upsert）。这样做的原因是，解决表快照与变更之间的一致性问题，以及处理可能在故障后被多次重放的变更非常困难。然而，更新插入方法存在几个问题：
- 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.当我们使复制成为由 Postgres 控制的事务性过程时，就不会受到同样的限制。我们可以生成完美的删除和插入流，并确保它们被“精确一次”地应用，而无需担心插入操作是否已经出现在快照或其他变更批次中。
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）。
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.实时视图是数据镜像的一项功能，它将各表变更日志中未应用的数据与目标表中的数据结合起来。重要的是，查询中的任何过滤和投影都可以直接下推（push down）到存储层，并对变更日志中的 Parquet 文件和基础表进行扫描。换句话说：实时视图速度很快。
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.基于推送的 CDC、围绕时间线的精心设计、双方的事务边界以及实时视图的结合，使得数据镜像将复制过程从混乱的状态转变为瑞士钟表般精准。没有会滞后的外部连接器，没有会与变更冲突的快照，也没有随着表增长而变慢的更新插入。只有一个 Postgres 插件将批次推送到对象存储，由 Snowflake 进行应用——两者都是事务性的、独立的，且可以无限期运行。
You set it up once. It runs forever.设置一次，永久运行。
Data mirroring for Snowflake PostgresSnowflake 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:通过 Snowflake Postgres，你拥有了值得信赖的生产级 Postgres，现在还可以通过以下两种方式灵活地统一你的工作负载：
- 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 到 Snowflake 的始终在线、自动化的复制。只需设置一次，你的表（包括模式变更）就能持续保持同步。
- 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.Postgres for your data lake：此功能现已正式发布，通过使用 Iceberg 等开放格式，为你提供 Postgres 与 Snowflake 之间灵活的、由开发者控制的数据迁移。你只需编写 SQL，数据就会按你想要的时间和方式迁移。
Get started with Snowflake Postgres开始使用 Snowflake Postgres
If you’re ready to get started, you can check out these links:如果你准备好开始了，可以查看以下链接：
- Set up data mirroring设置数据镜像
- Learn more about data mirroring了解关于数据镜像的更多信息
