PostgreSQL 到 ClickHouse：数据建模技巧

上个月，我们收购了 PeerDB，一家专门从事 PostgreSQL CDC 的公司。PeerDB 使从PostgreSQL 复制数据到ClickHouse变得快速而简单。PeerDB 用户的一个常见问题是在复制过程完成后如何在 ClickHouse 中建模其数据以最大化 ClickHouse 的优势。

出现这个问题是因为 ClickHouse 和 PostgreSQL 在数据建模方面存在差异，因为每个数据库都是为其特定的工作负载而设计的 **专用数据库** - PostgreSQL 是一个事务型 (OLTP) 数据库，而 ClickHouse 是一个分析型 (OLAP) 列式数据库。本指南将引导您了解来自 PostgreSQL 世界的用户在 ClickHouse 中的基本数据建模概念。请注意，这是博客系列的第一部分，未来还会有更多内容。

PeerDB 使用ReplacingMergeTree 引擎将 PostgreSQL 表映射到 ClickHouse。ClickHouse 最适合追加式工作负载，并且不建议频繁执行更新操作。这就是 ReplacingMergeTree 特别强大的地方。

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

在 PeerDB 中，来自 PostgreSQL 的插入和更新都作为具有不同版本（使用_peerdb_version）的新行捕获到 ClickHouse 中。ReplacingMergeTree 表引擎会定期使用排序键（ORDER BY 列）在后台处理去重，仅保留具有最新_peerdb_version的行。来自 PostgreSQL 的删除操作会传播为标记为已删除的新行（使用_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;

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

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

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

作为参考，以下是我们在将数据从 PostgreSQL 复制到 ClickHouse 时在 PeerDB 中使用的数据类型矩阵。

选择正确的排序键对于 ClickHouse 中的查询性能至关重要。排序键由创建表时的ORDER BY子句定义，其功能类似于 PostgreSQL 中的索引，但针对分析进行了优化。与 PostgreSQL 使用包含指向每行的条目的 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 和ORDER BY 列的一个示例是，当您的查询主要根据customer_id 而不是id 进行过滤时。在这种情况下，您可以仅在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. 开始将镜像指向新表：将镜像配置为指向实际表。PeerDB 在后台使用CREATE TABLE IF NOT EXISTS，并继续将数据摄取到新表中。

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

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 团队联系