从 Postgres 迁移到 ClickHouse：数据建模技巧更新版 V2

原创

于 2025-04-06 20:30:00 发布 · 1.2k 阅读

27 ·

CC 4.0 BY-SA版权

文章标签：

#clickhouse #mfc #c++

本文字数：16012；估计阅读时间：41 分钟

作者：Lionel Palacin & Sai Srirampur

本文在公众号【ClickHouseInc】首发

从 Postgres 到 ClickHouse：数据建模指南

越来越多的客户开始同时使用 Postgres 和 ClickHouse，其中 Postgres 主要用于事务处理，而 ClickHouse 负责分析型工作负载。两者各自针对不同的工作负载进行了优化。Postgres 与 ClickHouse 的常见集成方式之一是变更数据捕获（Change Data Capture，CDC）。CDC 可持续跟踪 Postgres 中的插入、更新和删除操作，并将这些数据复制到 ClickHouse，从而支持实时分析。

要实现 Postgres CDC 到 ClickHouse，可以使用 PeerDB（一个开源复制工具），或在 ClickHouse Cloud 中通过 ClickPipes 享受更完整的集成体验。由于 Postgres 和 ClickHouse 在架构上存在差异，除了复制数据外，在 ClickHouse 中合理设计表结构和查询方式同样至关重要，以提升性能。

本文将深入解析 Postgres CDC 到 ClickHouse 的内部工作原理，并介绍数据建模和查询优化的最佳实践。主要内容包括数据去重策略、自定义排序键的处理、JOIN 优化、物化视图（MVs，包括可刷新 MVs）、反规范化等。此外，这些方法不仅适用于 CDC 方案，还可以用于一次性迁移，因此，希望本文能帮助所有希望在 ClickHouse 进行数据分析的 Postgres 用户。去年年底，我们发布了该博客的第一版，本次更新将是进阶版本。

数据集

在本文中，我们将通过一个真实的数据集来演示这些策略。具体来说，我们选用了 StackOverflow 数据集的一个子集，并将其加载到 PostgreSQL 中。该数据集广泛用于 ClickHouse 官方文档，您可以在此处找到更多相关信息。此外，我们还开发了一个 Python 脚本来模拟 StackOverflow 用户的行为，并在 GitHub 上提供了实验复现的详细说明。

数据是如何同步的？

PostgreSQL 逻辑解码

ClickPipes 和 PeerDB 通过 Postgres 逻辑解码（Logical Decoding）实时获取 Postgres 中的数据变更。逻辑解码的机制允许 ClickPipes 这样的客户端以易读的方式接收变更数据，例如一系列 INSERT、UPDATE 和 DELETE 语句。如果想深入了解逻辑解码的工作原理，可以参考我们的一篇详细介绍该过程的博客。

在数据同步过程中，ClickPipes 会自动在 ClickHouse 中创建对应的表，并采用最本地化的数据类型映射。此外，它还能高效执行初始快照（snapshot）和回填（backfill）操作，以确保数据同步的完整性。

ReplacingMergeTree

ClickPipes 在 ClickHouse 中使用 ReplacingMergeTree 引擎来映射 Postgres 表。ClickHouse 在仅追加（append-only）模式下性能最佳，因此不建议频繁执行 UPDATE 操作，而 ReplacingMergeTree 在这种场景下尤为适用。

在 ReplacingMergeTree 模型中，UPDATE 操作会转换为带有更新版本号（_peerdb_version）的新插入（INSERT），而 DELETE 操作则转换为新插入，并将 _peerdb_is_deleted 标记为 true。ReplacingMergeTree 引擎会在后台自动去重并合并数据，确保同一主键（id）只保留最新版本，从而高效地处理 UPDATE 和 DELETE 操作，使其表现得像是版本化的插入。

下面是 ClickPipes 在 ClickHouse 中执行的 CREATE TABLE 语句示例：

CREATE TABLE users
(
    `id` Int32,
    `reputation` String,
    `creationdate` DateTime64(6),
    `displayname` String,
    `lastaccessdate` DateTime64(6),
    `aboutme` String,
    `views` Int32,
    `upvotes` Int32,
    `downvotes` Int32,
    `websiteurl` String,
    `location` String,
    `accountid` Int32,
    `_peerdb_synced_at` DateTime64(9) DEFAULT now64(),
    `_peerdb_is_deleted` Int8,
    `_peerdb_version` Int64
)
ENGINE = ReplacingMergeTree(_peerdb_version)
PRIMARY KEY id
ORDER BY id;

示例说明

下图展示了如何使用 ClickPipes 在 PostgreSQL 和 ClickHouse 之间同步 users 表的基本流程。

步骤 1：展示 PostgreSQL 中 users 表的初始快照，其中包含两行数据。同时，ClickPipes 执行初始加载，将这两行数据完整复制到 ClickHouse。

步骤 2：展示 users 表的三种数据变更操作——插入（INSERT）一行新数据、更新（UPDATE）一行已有数据，以及删除（DELETE）另一行数据。

步骤 3：展示 ClickPipes 如何将这些变更操作作为版本化的插入同步到 ClickHouse。UPDATE 操作会在 ClickHouse 中生成 ID 为 2 的新版本数据，而 DELETE 操作则会生成 ID 为 1 的新版本数据，并将 _deleted 字段标记为 true。因此，与 PostgreSQL 相比，ClickHouse 中的 users 表会额外存储三行数据。

因此，在 ClickHouse 中执行 SELECT count(*) FROM users; 这样的查询，可能会返回与 PostgreSQL 不同的结果。根据 ClickHouse 的合并（merge）机制，旧版本的数据最终会在合并过程中被清理。但由于合并发生的时间不可预测，这意味着在合并完成之前，ClickHouse 可能会返回与 PostgreSQL 不一致的查询结果。

那么，我们如何确保 ClickHouse 和 PostgreSQL 返回相同的查询结果呢？