Postgres 到 ClickHouse：数据建模技巧

上个月，我们收购了 PeerDB，这是一家专注于 Postgres CDC 的公司。PeerDB 使从 Postgres 复制数据到 ClickHouse 变得快速而简单。PeerDB 用户经常问的一个问题是，在复制过程之后，如何在 ClickHouse 中建模数据，以最大限度地利用 ClickHouse 的优势。

这个问题出现的原因是 ClickHouse 和 Postgres 在数据建模方面存在差异，因为它们都是专门构建的数据库，针对其特定的工作负载进行了高度优化——Postgres 是事务型 (OLTP) 数据库，而 ClickHouse 是分析型 (OLAP) 列式数据库。本指南将引导您了解 ClickHouse 中面向 Postgres 用户的基本数据建模概念。请注意，这是博客系列的第一部分，未来还会有更多内容。

PeerDB 使用 ReplacingMergeTree 引擎将 PostgreSQL 表映射到 ClickHouse。ClickHouse 在仅追加工作负载下表现最佳，并且不建议频繁的 UPDATE 操作。这正是 ReplacingMergeTree 特别强大的地方。

ReplacingMergeTree 支持涉及数据摄取和修改的工作负载。每个表都是仅追加的，用户更新作为版本化的 INSERT 摄取。ReplacingMergeTree 引擎在后台管理行的去重（合并）。这是 ClickHouse 能够提供卓越的实时摄取性能的关键因素之一。

在 PeerDB 中，来自 Postgres 的 INSERT 和 UPDATE 操作都作为新行捕获，在 ClickHouse 中具有不同的版本（使用 _peerdb_version）。ReplacingMergeTree 表引擎使用排序键（ORDER BY 列）定期在后台处理去重，仅保留最新 _peerdb_version 的行。来自 PostgreSQL 的 DELETE 操作会作为标记为已删除的新行传播（使用 _peerdb_is_deleted 列）。下面的代码片段显示了 ClickHouse 中 public_goals 表的目标表定义。

clickhouse-cloud :) SHOW CREATE TABLE public_goals;
CREATE TABLE peerdb.public_goals
(
    `id` Int64,
    `owned_user_id` String,
    `goal_title` String,
    `goal_data` String,
    `enabled` Bool,
    `ts` DateTime64(6),
    `_peerdb_synced_at` DateTime64(9) DEFAULT now(),
    `_peerdb_is_deleted` Int8,
    `_peerdb_version` Int64
)
ENGINE = SharedReplacingMergeTree
('/clickhouse/tables/{uuid}/{shard}', '{replica}', _peerdb_version)
PRIMARY KEY id
ORDER BY id
SETTINGS index_granularity = 8192

ReplacingMergeTree 在后台异步清除重复项，但不保证没有重复项。因此，当您查询数据时，您可能仍然会看到同一行或主键的重复项，但版本不同。这是预期的。要删除重复项，您有几种方法：

ClickHouse 有一个名为 FINAL 的独特修饰符，它在查询时执行去重（合并行）。此去重发生在过滤（WHERE 子句）之后，但在聚合（GROUP BY）之前。

过去人们一直担心 FINAL 可能会降低查询性能。虽然它确实在一定程度上影响了查询性能，但最新版本的 ClickHouse 引入了 重大改进，以提高 FINAL 查询性能。因此，不要犹豫使用 FINAL 子句并评估您的查询性能。下面是如何使用 FINAL 子句的示例：

SELECT owner_user_id, COUNT(*) FROM goals FINAL 
WHERE enabled = true GROUP BY owner_user_id;

在 ClickHouse 中，argMax 是一个强大的函数，用于在查询执行期间动态去重行。当您需要根据版本或时间戳列保留最新或最相关的记录时，这尤其有用。

例如，如果您正在使用像 peerdb.public_goals 这样的表，其中 id 是主键，_peerdb_version 跟踪版本，则可以使用 argMax 为每个 id 选择具有最高 _peerdb_version 的行。这种方法允许您有效地删除重复项，而无需更改底层数据。然后，您可以针对此去重后的结果集运行聚合，以进行进一步分析。以下查询是使用 argMax 的示例：

SELECT
    owned_user_id,
    COUNT(*) AS active_goals_count,
    MAX(ts) AS latest_goal_time
FROM
(
    SELECT
        id,
        argMax(owned_user_id, _peerdb_version) AS owned_user_id,
        argMax(goal_title, _peerdb_version) AS goal_title,
        argMax(goal_data, _peerdb_version) AS goal_data,
        argMax(enabled, _peerdb_version) AS enabled,
        argMax(ts, _peerdb_version) AS ts,
        argMax(_peerdb_synced_at, _peerdb_version) AS _peerdb_synced_at,
        argMax(_peerdb_is_deleted, _peerdb_version) AS _peerdb_is_deleted,
        max(_peerdb_version) AS _peerdb_version
    FROM peerdb.public_goals
    WHERE enabled = true
    GROUP BY id
) AS deduplicated_goals
GROUP BY owned_user_id;

您可以使用 ClickHouse 的 窗口函数 来实现类似的去重，方法是选择每个 id 分区中具有最高 _peerdb_version 的行。这是一个例子：

SELECT
    owned_user_id,
    COUNT(*) AS active_goals_count,
    MAX(ts) AS latest_goal_time
FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn
    FROM peerdb.public_goals
    WHERE enabled = true
) AS ranked_goals
WHERE rn = 1
GROUP BY owned_user_id;

将去重封装在 视图 中，使 BI 工具可以轻松查询最新的数据。例如，在视图中使用窗口函数来仅保留每个行的最新版本：

CREATE VIEW goals AS
SELECT * FROM
(
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY _peerdb_version DESC) AS rn
    FROM peerdb.public_goals
    WHERE enabled = true
) WHERE rn = 1;

SELECT
    owned_user_id,
    COUNT(*) AS active_goals_count,
    MAX(ts) AS latest_goal_time
FROM goals
GROUP BY owned_user_id;

如果您来自 Postgres 世界，那么 ClickHouse 令人惊讶的一个方面是，除非您显式地将列类型包装在 Nullable 中，否则它不会存储列的 NULL 值。例如，ClickHouse 不会为日期存储 NULL，而是存储 1970-01-01 作为默认值，这可能出乎意料。这种行为是由于存储 NULL 可能会影响 ClickHouse 中的查询性能，因为它是一个列式数据库。因此，ClickHouse 要求用户显式定义 Nullable 类型。

在 PeerDB 中，我们引入了一个名为 PEERDB_NULLABLE 的设置，当设置为 true 时，它会自动检测 Postgres 中的可空列，并在复制过程中将其标记为 ClickHouse 中的 Nullable。这意味着您无需在复制期间手动定义 Nullable 类型。您可以在以下 PR 中阅读有关此功能的更多信息。

ClickHouse 提供了各种各样的数据类型，从数字、文本、时间戳、日期和数组到最近引入的 JSON 类型。Postgres 中的许多数据类型都可以原生存储在 ClickHouse 中，而无需进行太多修改。

作为参考，这是我们在 PeerDB 中用于从 Postgres 复制数据到 ClickHouse 的 数据类型矩阵。

选择正确的排序键对于 ClickHouse 中的查询性能至关重要。排序键由创建表时的 ORDER BY 子句定义，其功能类似于 Postgres 中的索引，但针对分析进行了优化。与 Postgres 使用 B 树索引且条目指向每一行不同，ClickHouse 使用稀疏索引。

1. 数据根据排序键排序： 排序键确保磁盘上的数据根据指定的列排序。这允许更好的压缩，因为相关值存储在一起。

2. 排序键还创建稀疏索引： 排序键还创建稀疏索引，仅存储列的范围，每个条目指向一组排序后的行。这使索引保持较小，使 ClickHouse 可以使用二进制搜索快速识别相关的行组并高效地执行查询。您可以在此处阅读更多相关信息。

您可以将排序键视为类似于 Postgres 中的 BRIN 索引，但在 ClickHouse 中，数据会根据排序键通过异步合并部分自动排序，因此您无需在数据摄取期间处理排序。

选择排序键时，请根据查询过滤器中最常用的列来选择。优先考虑 WHERE 子句中常用的列，并按基数升序对它们进行排序——从具有最少不同值的列开始。这种方法优化了数据压缩和查询性能。要更深入地了解此主题，请参阅 此处 的详细指南。

如果您观察 public_goals 的表定义，它具有 PRIMARY KEY。您可能想知道 PRIMARY KEY 与排序键有何不同。让我们了解一下它们的区别：

1. PRIMARY KEY（如果指定）定义稀疏索引中的列，而 ORDER BY 子句中的列确定数据在磁盘上的排序方式。它们也用于 ReplacingMergeTree 对数据进行去重。

2. 如果未指定 PRIMARY KEY，则排序键自动成为 PRIMARY KEY 并定义稀疏索引中的列。

注意： PRIMARY KEY 中的列应始终作为排序键的前缀。这确保索引与物理数据顺序对齐，通过最大限度地减少不必要的数据扫描来最大化查询性能。

PRIMARY KEY 可能与排序键不同的示例

当您的查询主要按 customer_id 而不是 id 过滤时，您可能会有不同的 PRIMARY KEY 和 ORDER BY 列。在这种情况下，您可以仅在 customer_id 上定义 PRIMARY KEY，并在 customer_id, id 上定义 ORDER BY。此方法确保了更小、更高效的稀疏索引用于查询，而数据去重发生在 id 上，从而确保不会丢失数据。

注意： 与 Postgres 中 PRIMARY KEY 是保证唯一性的 B 树索引不同，在 ClickHouse 中，它不保证唯一性。相反，它定义了应成为稀疏索引一部分的列。

选择正确的排序键对于 ClickHouse 中的查询性能至关重要，因为它在查询数据时充当索引。默认情况下，PeerDB 使用 PostgreSQL PRIMARY KEY 来定义 ClickHouse 表中的排序键，但您可以使用以下方法更改它：

您可以使用物化视图创建一个具有不同排序键的新表，以适应您的工作负载。在排序键的末尾包含主键列，以确保正确去重，因为 ReplacingMergeTree 使用 ORDER BY 子句进行去重，并且包含主键可确保不会丢失数据。

CREATE MATERIALIZED VIEW goals_mv
ENGINE = ReplacingMergeTree(_peerdb_version)
ORDER BY (enabled, ts, id)  POPULATE AS
SELECT * FROM peerdb.public_goals;

注意： 创建物化视图后，请务必按照上一节中描述的关于处理重复项的步骤操作，以确保在查询时正确去重。

要更改排序键，您可以预定义具有所需排序键的新表，然后将它们与现有表交换。以下是操作方法：

1. 创建虚拟镜像： 在 PeerDB 中创建一个虚拟镜像，以生成具有正确元数据列和数据类型的默认表。

2. 创建具有所需排序键的新表： 使用 PeerDB 创建的表来定义具有所需排序键的新表。在排序键的末尾包含主键列，以确保正确去重。这是一个例子：

CREATE TABLE public_events_new AS public_events
ENGINE = ReplacingMergeTree(_peerdb_version)
ORDER BY (user_id,id);

3. 删除旧表

DROP TABLE public_events;

4. 重命名新表： 将新表重命名为实际表

RENAME TABLE public_events_new TO public_events;

5. 启动 MIRROR 以指向新表： 配置镜像以指向实际表。PeerDB 在后台使用 CREATE TABLE IF NOT EXISTS 并继续将数据摄取到新表中。

如前所述，来自 PostgreSQL 的 DELETE 操作会作为标记为已删除的新行传播（使用 _peerdb_is_deleted 列）。要从查询中排除已删除的行，您可以在 ClickHouse 中基于 _peerdb_is_deleted 列创建行级策略。这是一个例子：

CREATE ROW POLICY policy_name ON table_name
FOR SELECT USING _peerdb_is_deleted = 0;

此策略确保在查询表时仅显示 _peerdb_is_deleted 为 0 的行。

希望您喜欢阅读这篇博客。我旨在涵盖您从 PostgreSQL 迁移到 ClickHouse 时可能遇到的大多数常见数据建模挑战。在下一篇博客中，我计划深入探讨更高级的主题，例如连接、编写高效的 SQL 查询等等。如果您想尝试 PeerDB 和 ClickHouse 以开始从 Postgres 复制数据到 ClickHouse，请查看下面的链接或直接与我们联系！

1. 免费试用 ClickHouse Cloud

2. 免费试用 PeerDB Cloud

3. Postgres 到 ClickHouse 复制文档

4. 直接与 PeerDB 团队交谈