跳到正文
Hacker News· saisrirampur·· 6 天前AI 评分22

Postgres FDW 的下推为何是一场协商：pg_clickhouse 实践解析

Postgres FDW: Pushdown is a negotiation

AI 导读

pg_clickhouse 团队用 6 个月开发经验说明，FDW 的谓词下推不是"全部发送"的开关，而是 Postgres 与远端 SQL 语法之间逐条子句的协商。

正文 · AI 翻译

Postgres 扩展添加了 Postgres 本身不包含的功能。例如用于地理空间数据的 PostGIS、用于嵌入的 pgvector，以及用于时间序列的 TimescaleDB。通过扩展，Postgres 生态系统分发新功能；只需 CREATE EXTENSION 即可接入 Postgres 内部，此后用户大多会忘记它的存在。

外部数据包装器扩展，也称为“FDW”，教会 Postgres 如何读取（有时也写入）Postgres 之外的数据。只需声明一个外部表：

CREATE FOREIGN TABLE events (...)
SERVER my_clickhouse OPTIONS (table 'events');

然后 SELECT * FROM events 就像对任何其他表的查询一样。在内部，Postgres 要求 FDW 从其他地方获取数据。

pg_clickhouse 是我们维护的 FDW，它从 ClickHouse 获取数据。虽然人们常常依赖统一数据栈——用 Postgres 处理事务数据、用 ClickHouse 处理分析数据——但 pg_clickhouse 会在两个系统上执行 SQL 查询。在过去 6 个月的工作中，我们发现一个单一的问题驱动了大部分工程工作：一个查询中有多少作为 SQL 通过网络发送，又有多少行作为数据被拉回来？

这个问题涵盖了下推的含义：我们能把多少工作“下推”到远程服务？答案似乎很简单：“全部发送！”——WHERE 子句、GROUP BY、LIMIT。但仔细审视后会发现其复杂性：有些子句我们可以发送。有些我们几乎可以发送——如果我们重写它们。有些我们曾经发送但后来停止了，因为它们返回了错误的结果。还有些子句是任何 FDW 都永远无法发送的，无论投入多少工程努力。

我们发现，做出这些判断的过程是高度迭代的。

为了演示，让我们考察这种迭代对单个查询的影响：哪些被下推，哪些没有，以及我们如何针对这个问题不断修改代码。

目标是向那些听说过 FDW 但不了解其详细工作原理的人阐明 FDW 的内部运作。无论你是对 ClickHouse 好奇的 Postgres 用户、对 Postgres 好奇的 ClickHouse 用户，还是正在考虑自己编写 FDW 的人，我们希望这个练习对你有启发。

这个查询按国家和事件名称对过去一周美国、英国和德国最繁忙的 Web 事件进行排名。它报告量、独立用户数、付费占比、p95 延迟，以及每个事件在各国的排名，并返回总体前 100 行，以提供一个小快照并避免流式传输原始事件。

SELECT
  u.country,
  e.event_name,
  count(*) AS n,
  count(DISTINCT e.user_id) AS unique_users,
  count(*) FILTER (WHERE e.properties->>'tier' = 'premium') AS premium_count,
  percentile_cont(0.95) WITHIN GROUP (ORDER BY e.duration_ms) AS p95_ms,
  ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY count(*) DESC) AS rank_in_country
FROM events_ch e JOIN users_ch u USING (user_id)
WHERE e.ts >= now() - interval '7 days'
  AND u.country IN ('US', 'UK', 'DE')
  AND e.properties->>'platform' = 'web'
GROUP BY u.country, e.event_name
ORDER BY n DESC
LIMIT 100;

events_ch 和 users_ch 是由 ClickHouse 支持的外部表。当此查询中的每个子句都被下推时，它大约在 80 毫秒内返回 100 行。

如果有一个子句未能下推，查询就会耗时数分钟。我们构建了对窗口函数、百分位数、JSON 访问和 FILTER 聚合的下推支持；但每一个都曾在某个更早的时间点尚未被下推。当一个操作无法下推时，查询的其余部分也无法下推；本应在远程聚合的行必须流回 Postgres，以便它在本地聚合它们。最终，网络上传输的是数千万到数亿行，而不是 100 行。

下推看起来像是一个功能，但它实际上是两种 SQL 语法之间的约定，并且会随每个 Postgres 版本而修订。这解释了为什么 pg_clickhouse 的发布说明常常看起来像是在做修正：撤销不正确的数组函数下推，添加更安全的函数如 levenshtein 和 soundex，并且即使远程端可以更快地执行，也要遵守像 EvalPlanQual 这样的计划器不变量。

FDW 不会将原始的 Postgres 查询发送到远程数据库；这样做会在 SQL 方言不同的地方导致错误。但理想情况下，它也不会拉回每一行，除非不得不这样做（就像 file_fdw 这样的简单 FDW 那样），因为尽管结果会是正确的，但执行会很慢。

Diagram showing how a Foreign Data Wrapper sits between an application and a remote database, forwarding SQL queries and returning data rows

Postgres 不会直接决定扫描、连接、聚合、排序或限制可以在 ClickHouse 中执行。相反，它通过 FDW 规划回调要求 FDW 向计划器提供外部路径。pg_clickhouse 注册了这些规划回调：

routine->GetForeignRelSize    = clickhouseGetForeignRelSize;
routine->GetForeignPaths      = clickhouseGetForeignPaths;
routine->GetForeignPlan       = clickhouseGetForeignPlan;
routine->GetForeignJoinPaths  = clickhouseGetForeignJoinPaths;
routine->GetForeignUpperPaths = clickhouseGetForeignUpperPaths;

GetForeignPaths、GetForeignJoinPaths 和 GetForeignUpperPaths 告诉计划器哪些内容可以下推。如果计划器选择了其中一条路径，GetForeignPlan 会构建计划并生成 ClickHouse SQL。

这些回调只是入口点；pg_clickhouse 的回调函数会验证每个子句或表达式是否可以在不改变结果的情况下进行转换。当然，它一开始相当简单：只有从 postgres_fdw 原始分支继承来的少量子句和表达式。因此，pg_clickhouse 最初下推了 count(*) 但没有下推 count(*) FILTER (...)，无法下推 percentile_cont 或像 properties->>'platform' 这样的 JSON 谓词：因为它不知道如何下推。

因此，下推不是对整个查询的是/否决定，而是一系列更小的决定：Postgres 是否暴露了钩子，pg_clickhouse 能否转换该表达式，以及 ClickHouse 能否以相同的含义执行它？

为了回答“为什么 X 不下推？”这个问题，让我们看一个真实的查询，并让它经历 pg_clickhouse 已发布的所有功能。同一个查询，七个步骤。每一步都向 ClickHouse 下推更多内容；Postgres 必须评估在拉回所需行之后剩下的部分。

每一步都检查同一个查询。标记为 ✓ 的行在该步骤下推；标记为 ✗ 的行保留在本地。每一步还标记了我们历史中的一个真实日期，即相关下推落地到代码库中的时间，或者我们预计发布它的时间。每一步都展示了截至该日期查询计划的样子。

通过初始移植 e5035bc（2025 年 10 月 2 日）从原始 clickhouse_fdw 继承而来。自 v0.1.0（2025 年 12 月 9 日）起在 pg_clickhouse 中可用。

起点故意很小：pg_clickhouse 只下推它能够有把握转换的谓词。像 >= 这样的比较、像 IN 这样的成员检查以及简单的时间戳算术可以安全地下推，因为 Postgres 和 ClickHouse 对它们有明确的等价物。换句话说，这些过滤在 ClickHouse 中运行，而不是收集所有行在 Postgres 中评估。

SELECT
  u.country,                                                                -- ✗
  e.event_name,                                                             -- ✗
  count(*) AS n,                                                            -- ✗
  count(DISTINCT e.user_id) AS unique_users,                                -- ✗
  count(*) FILTER (WHERE e.properties->>'tier' = 'premium') AS …,           -- ✗
  percentile_cont(0.95) WITHIN GROUP (ORDER BY e.duration_ms) AS …,         -- ✗
  ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY count(*)) AS …         -- ✗
FROM events_ch e                                                            -- ✓ scan only
  JOIN users_ch u USING (user_id)                                           -- ✗
WHERE e.ts >= now() - interval '7 days'                                     -- ✓
  AND u.country IN ('US','UK','DE')                                         -- ✓
  AND e.properties->>'platform' = 'web'                                     -- ✗
GROUP BY u.country, e.event_name                                            -- ✗
ORDER BY n DESC                                                             -- ✗
LIMIT 100                                                                   -- ✗

此时，只有基表过滤条件下推。SQL 转换器（内部称为“deparser”）为带有时间戳谓词的 events 生成一个远程扫描，为带有国家谓词的 users 生成一个远程扫描。它将这两个独立的查询发送到 ClickHouse 并收集结果。所有合并行或改变其形状的操作，包括连接、分组、聚合、窗口函数、排序和限制，都在 Postgres 内针对检索到的行运行。因此，在 Postgres 能将结果缩减到 100 行之前，网络必须传输过去七天中所有匹配的 events 行，以及所有匹配的 users 行。

关键在于，即使是最简单的下推也已经是一个翻译问题：now() 可以下推为 now()，而 interval '7 days' 则被翻译为 7 * 86400。这些映射位于 src/custom_types.c 中，pg_clickhouse 在此记录了它知道可以安全地为 ClickHouse 翻译的表达式。下面概述的步骤将该词汇表从基础过滤扩展到连接、聚合、窗口和限制。

内连接反解析来自最初的 clickhouse_fdw，但继承的成本估算只是占位符，Postgres 规划器经常将行拉回本地连接，而不是下推。提交 b345682（2025 年 12 月 1 日）用基于行数的估算和高于连接路径的基于成本的扫描替换了它们，因此规划器可靠地选择下推计划。此外，6a297ec（2025 年 11 月 13 日）用于 join_use_nulls 外连接语义。所有这些都在 v0.1.0（2025 年 12 月 9 日）中发布。

在这一步，pg_clickhouse 不再将 events_ch 和 users_ch 视为两个独立的远程扫描。由于两个表都位于同一个 ClickHouse 服务器上，并且连接条件有 ClickHouse 等价物，FDW 可以要求 ClickHouse 在任何连接行跨网络传输之前进行连接。

SELECT ...                                                         -- ✗ (still)
FROM events_ch e                                                   -- ✓
  JOIN users_ch u USING (user_id)                                  -- ✓ joins push
WHERE e.ts >= now() - interval '7 days'                            -- ✓
  AND u.country IN ('US','UK','DE')                                -- ✓
  AND e.properties->>'platform' = 'web'                            -- ✗ stays local
GROUP BY u.country, e.event_name                                   -- ✗
ORDER BY n DESC                                                    -- ✗
LIMIT 100                                                          -- ✗

远程 SQL 现在大致为 SELECT * FROM events ALL INNER JOIN users USING (user_id) WHERE …。ALL 关键字是故意的：ClickHouse 的默认连接只能返回一行匹配，而 Postgres 内连接返回所有匹配。发出 ALL INNER JOIN 保留了 Postgres 语义。此时 JSON 谓词仍然无法翻译，因此它被排除在远程 WHERE 之外，并在本地应用于返回的行。

Postgres 在 9.6 中启用了这种连接下推（提交 e4106b25287，2016 年，Etsuro Fujita）。此前，FDW 必须更深入地挂钩到规划器中才能实现连接下推。

对于我们的查询，这一步将查询时间从大约 30 分钟减少到 30 秒，因为连接在行离开远程之前就过滤了行。

同样通过 e5035bc 继承。GROUP BY、基本聚合（count、sum、min、max、avg）、ORDER BY 和 LIMIT 自 v0.1.0（2025 年 12 月 9 日）起全部下推。

这一步强调结果整形工作：分组行、计算基本聚合、对分组结果排序以及应用最终限制。Postgres 将这些操作暴露为“上层”规划阶段，当整个阶段可以在 ClickHouse 中表示时，FDW 可以为每个阶段提供远程版本。聚合下推在 Postgres 10 中实现（提交 7012b132d07）；ORDER BY 和 LIMIT 下推随后在 Postgres 12 中实现。

不过，此时 pg_clickhouse 仍然无法下推所有操作：分组阶段必须涵盖整个 SELECT 列表，并且它尚未连接以下三个表达式的下推：

  • count(*) FILTER (WHERE e.properties->>'tier' = 'premium')：FILTER 主体包含一个没有下推映射的 JSON 操作
  • percentile_cont(...) WITHIN GROUP (ORDER BY ...)：ClickHouse 有等价函数，但 pg_clickhouse 尚未映射它
  • ROW_NUMBER() OVER (...)：窗口函数根本没有映射到 ClickHouse 等价物

注意，对于给定的分组结果，分组下推是全有或全无的。因此，即使 count(*)、count(DISTINCT)、GROUP BY、ORDER BY 和 LIMIT 各自单独都没问题，这个特定查询的分组部分仍然保留在本地。

SELECT
  u.country, e.event_name,                                          -- ✗ grouped result blocked
  count(*) AS n,                                                    -- ✗ blocked
  count(DISTINCT e.user_id) AS unique_users,                        -- ✗ blocked
  count(*) FILTER (WHERE e.properties->>'tier' = 'premium') AS …,   -- ✗ FILTER body has JSON
  percentile_cont(...) AS p95_ms,                                   -- ✗ no mapping yet
  ROW_NUMBER() OVER (...) AS rank_in_country                        -- ✗ window not remote yet
FROM events_ch e                                                    -- ✓
  JOIN users_ch u USING (user_id)                                   -- ✓
WHERE e.ts >= ... ✓ AND u.country IN (...)                          -- ✓
  AND e.properties->>'platform' = 'web'                             -- ✗
GROUP BY u.country, e.event_name                                    -- ✗ blocked
ORDER BY n DESC                                                     -- ✗ blocked
LIMIT 100                                                           -- ✗ blocked

这个查询的一个更简单的子集（去掉 FILTER、percentile 和 ROW_NUMBER）此时可以完全下推：

-- The subset that DOES push down at Step 3:
SELECT u.country, e.event_name, count(*) AS n, count(DISTINCT e.user_id) AS unique_users
FROM events_ch e JOIN users_ch u USING (user_id)
WHERE e.ts >= now() - interval '7 days' AND u.country IN ('US','UK','DE')
GROUP BY u.country, e.event_name
ORDER BY n DESC LIMIT 100;

该子集反解析为单个 ClickHouse SELECT 语句，其中 count(DISTINCT e.user_id) 作为 ClickHouse count(DISTINCT user_id) 发出。完整、更丰富的查询还需要三步才能完全落地。

提交 087cfdc（2025 年 11 月 10 日），在 v0.1.0 中。此前，percentile_cont 会阻止任何使用它的查询的上层关系。

这一步教会 pg_clickhouse 多一种聚合翻译：Postgres percentile_cont(p) WITHIN GROUP (ORDER BY x) 可以表示为 ClickHouse quantile(p)(x)。此更改将 percentile 从阻止列表中移除。允许列表仍然很窄：例如，pg_clickhouse 仍然拒绝 string_agg(... ORDER BY ...)，因为 ClickHouse 最接近的等价物不保留相同的组内排序语义。

SELECT
  ...                                             -- ✗ still blocked
  percentile_cont(...) AS p95_ms,                 -- ✓ now shippable
  ROW_NUMBER() OVER (...) AS rank_in_country      -- ✗ window not remote yet
FROM ...                                          -- (everything else unchanged from Step 2-3)

percentile_cont 函数变得可以单独下发是必要但不充分的。分组结果仍然无法下推，因为还有两个阻止因素：带 JSON 的 FILTER 和 ROW_NUMBER 窗口函数。

但请注意这些步骤中反复出现的模式：一次翻译每次解除一个下推阻止因素，但只有当所有此类阻止因素都解决后，整个查询的下推才会发生。这解释了为什么对 pg_clickhouse 的一系列单次翻译更改，虽然单个看起来不起眼，但随着时间的推移会累积起来，突然下推一个以前完全在本地运行的完整查询。

提交 0b4c03e（2026 年 4 月 2 日）和 669924a（4 月 3 日），在 v0.1.6 / v0.2.0 中。此前，每个 ->/->>/jsonb_extract_path 都是本地过滤器；甚至 e.properties->>'platform' = 'web' 也无法下推。

这一步将 JSON 字段访问添加到共享词汇表中。像 e.properties->>'platform' 这样的 Postgres JSON 访问器表达式现在翻译为 ClickHouse 子列表达式 e.properties.platform，因此 JSON 谓词不再需要等待行被取回 Postgres。

SELECT
  u.country, e.event_name,                                          -- ✗ still blocked (window)
  count(*) AS n, count(DISTINCT e.user_id) AS unique_users,         -- ✗ blocked (window)
  count(*) FILTER (WHERE e.properties->>'tier' = 'premium') AS …,   -- ✓ FILTER body now lifts
  percentile_cont(...) AS p95_ms,                                   -- ✓
  ROW_NUMBER() OVER (...) AS rank_in_country                        -- ✗ window still blocks
FROM events_ch e                                                    -- ✓
JOIN users_ch u USING (user_id)                                     -- ✓
WHERE ...
  AND e.properties->>'platform' = 'web'                             -- ✓ JSON qual lifts
GROUP BY u.country, e.event_name                                    -- ✗ still blocked
ORDER BY n DESC                                                     -- ✗ blocked
LIMIT 100                                                           -- ✗ blocked

同时发生两件事：

  • properties->>'platform' = 'web' 谓词作为 properties.platform = 'web' 下发，因此 ClickHouse 可以在将这些行发送回来之前对其进行过滤。
  • 过滤后的聚合 count(*) FILTER (WHERE properties->>'tier' = 'premium') 作为 countIf(properties.tier = 'premium') 下推，使用 ClickHouse 的条件聚合形式。

分组结果仍然不会下推，但现在只剩下一个阻止因素：ROW_NUMBER。

提交 0caf913（2026 年 4 月 2 日，与 JSON 子列访问同一天），在 v0.1.6 / v0.2.0 中。此前，任何 OVER (...) 子句都会阻止上层关系。

这一步让 pg_clickhouse 为窗口函数提供远程计划。当分区键和排序键也可以翻译时，ROW_NUMBER、RANK、DENSE_RANK、LEAD、LAG、FIRST_VALUE、LAST_VALUE、NTH_VALUE 和 MIN/MAX OVER 都在 ClickHouse 中运行。

在这里，pg_clickhouse 超越了其前身 postgres_fdw 的下推能力，后者不下推窗口函数。但 ClickHouse 执行它们很快且靠近数据，这一好处大大超过了翻译这些函数的开销。

SELECT
  u.country,                                                        -- ✓
  e.event_name,                                                     -- ✓
  count(*) AS n,                                                    -- ✓
  count(DISTINCT e.user_id) AS unique_users,                        -- ✓
  count(*) FILTER (WHERE e.properties->>'tier' = 'premium') AS …,   -- ✓
  percentile_cont(0.95) WITHIN GROUP (ORDER BY e.duration_ms) AS …, -- ✓
  ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY count(*)) AS …,-- ✓
FROM events_ch e                                                    -- ✓
  JOIN users_ch u USING (user_id)                                   -- ✓
WHERE e.ts >= now() - interval '7 days'                             -- ✓
  AND u.country IN ('US','UK','DE')                                 -- ✓
  AND e.properties->>'platform' = 'web'                             -- ✓
GROUP BY u.country, e.event_name                                    -- ✓
ORDER BY n DESC                                                     -- ✓
LIMIT 100                                                           -- ✓

下推不再有任何障碍。连接、分组、聚合、窗口函数、排序和限制都变成一个 ClickHouse 查询。以前是一个带有独立 Limit / Sort / WindowAgg / Group / Join / Scan / Scan 节点的 Postgres 计划，现在折叠成一个单一的外部扫描，其远程 SQL 大致如下：

-- What lands on the ClickHouse wire:
SELECT
  u.country,
  e.event_name,
  count(*) AS n,
  count(DISTINCT e.user_id) AS unique_users,
  countIf(e.properties.tier = 'premium') AS premium_count,
  quantile(0.95)(e.duration_ms) AS p95_ms,
  ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY count(*) DESC) AS rank_in_country
FROM events e
  ALL INNER JOIN users u USING (user_id)
WHERE e.ts >= now64(9, 'UTC') - INTERVAL 7 DAY
  AND u.country IN ('US','UK','DE')
  AND e.properties.platform = 'web'
GROUP BY u.country, e.event_name
ORDER BY n DESC
LIMIT 100;

（近似；确切的 count(DISTINCT) 和 countIf 反解析存在此处未显示的边缘情况。）

网络上传输 100 行。Postgres 接收它们并返回它们。与第 1 步相比，当时网络上传输了数千万行，差异是几个数量级。

我们有时会看到这样的反应：“我很惊讶这个表达式居然没有从第一天起就被下推”，这是你在没有历史背景的情况下查看 v0.2 变更列表时才会有的想法。把查询放到那段历史中走一遍，会浮现出一些仅凭一份简单的变更列表无法看到的事实，即：

  • 下推是细粒度的。 每个子句都必须独立协商。一个看起来“显而易见”的子句可能因为某个子表达式而无法下推：count(*) FILTER (WHERE json_op) 要等待 JSON 操作，尽管 count 和 FILTER 各自都没问题。
  • 在上层关系级别，下推是全有或全无的。一个分组查询只有在整个分组关系能够被安全地表示为 ClickHouse SQL 时才能被下推。一个不受支持的聚合或子表达式就可能阻断整个分组计划。换句话说，为一个小缺失的翻译添加支持，就能解锁整个查询的下推。
  • 大多数“改进”是翻译，而不是新功能。 percentile_cont、ROW_NUMBER、->>。Postgres 有它们，ClickHouse 也有它们。缺失的是连接这两种语法的反解析器代码。这类改进看起来像新能力，但在内部它们只是 custom_types.c 中的新映射以及翻译它们的代码。
  • 有些下推会被撤销。 pg_clickhouse 和 postgres_fdw 都有因被发现会泄漏而被移除的下推列表。在我们的 v0.2.0 中，我们撤销了对 array_dims、array_lower 及其同类项的下推。在 postgres_fdw 这边，8cfbac1492b （PG 17）拒绝了下推 FETCH FIRST n WITH TIES；5c571a34d0e （PG 18）拒绝了需要向后游标扫描的 LIMIT 下推。这正是系统在正常运作：当我们无法保证语义正确时，宁可撤回，也不要快速返回错误的行。

如果反解析契约被违反，我们宁可报错也不猜测

从外部看，下推似乎是二元的：要么子句在远端执行，要么不执行。但在 FDW 内部，它是一份契约。Postgres 必须暴露正确的计划器钩子，pg_clickhouse 必须在不改变含义的情况下翻译表达式，ClickHouse 必须支持相同的语义。如果这条链的任何一环缺失，安全的做法就是把工作留在本地，或者大声报错。

因此，从外部看，FDW 的工作往往显得是渐进式的。JSON 子列、percentile_cont、带过滤的聚合、窗口函数、连接语义、DML 支持：每一项都代表着两个 SQL 系统之间的一小份共识。但一旦下推的最后一个障碍被清除，效果可就不小了。一个过去要把数百万行流式传入 Postgres 的查询，可以坍缩成一条只返回 100 行的 ClickHouse SELECT。

下推不是一场把每个子句都送到远端的竞赛。有些翻译被添加。有些被撤销。有些在等待 Postgres 计划器的支持。有些在等待 ClickHouse 引擎的行为。还有些则根本不应该被下推，因为远端系统无法诚实地承诺给出与 Postgres 相同的答案。

这项工作不是让 ClickHouse 假装成 Postgres。这项工作是一子句一子句地决定，这两个系统能在哪里达成一致。

每一个不经过网络传输的字节，都是三方一致同意省略的字节。

来源：Hacker News · clickhouse.com

开源生态部署工程教程实践#教程/实践#部署/工程#开源生态