Hotdry.
归档
database-systems

Pg_ClickHouse查询计划重写与下推优化机制深度解析

深入分析Pg_ClickHouse扩展的查询计划重写机制，实现Postgres查询到ClickHouse原生查询的谓词下推、聚合下推等优化，减少跨系统数据传输。

在混合数据架构日益普及的今天，如何让事务型数据库 PostgreSQL 与列式分析数据库 ClickHouse 高效协同工作，成为许多企业面临的技术挑战。2025 年 12 月发布的 pg_clickhouse v0.1.0 扩展，通过创新的查询计划重写机制，实现了 PostgreSQL 查询到 ClickHouse 原生查询的透明转换，将复杂的跨系统查询优化问题转化为可工程化的下推策略。

问题场景：从数据迁移到查询迁移的鸿沟

许多组织在业务初期使用 PostgreSQL 处理所有数据，包括事务数据和日志指标。随着数据量增长，分析查询性能逐渐成为瓶颈。ClickPipes 等工具解决了数据从 PostgreSQL 到 ClickHouse 的迁移问题，但查询迁移却成为更大的挑战 —— 数个月甚至数年的 SQL 查询散布在仪表板、ORM 和定时任务中，手动重写成本极高。

pg_clickhouse 的核心价值在于：让 ClickHouse 表看起来就像普通的 PostgreSQL 表，只需修改search_path，现有查询就能继续工作 —— 只不过现在它们在 ClickHouse 上执行。这背后的关键技术就是查询计划重写与下推优化。

PostgreSQL FDW 架构与下推机制基础

要理解 pg_clickhouse 的工作原理，首先需要了解 PostgreSQL Foreign Data Wrapper（FDW）的查询规划机制。根据 PostgreSQL 官方文档，FDW 通过一系列回调函数与查询规划器交互：

  • GetForeignRelSize: 估算远程表的大小和成本
  • GetForeignPaths: 生成访问远程表的路径
  • GetForeignPlan: 创建实际的执行计划节点
  • GetForeignJoinPaths: 处理连接操作的路径生成
  • GetForeignUpperPaths: 处理上层操作（如聚合、分组）

关键点在于baserel->baserestrictinfo，它包含了 WHERE 子句中的限制条件。FDW 可以选择将这些条件 "下推" 到远程数据库执行，从而减少数据传输量。pg_clickhouse 正是在这个基础上，实现了从 PostgreSQL 查询语法到 ClickHouse 原生查询的智能转换。

SEMI JOIN 下推：解决 EXISTS 子查询的性能瓶颈

TPC-H Query 4 是一个典型的性能挑战案例，它包含 EXISTS 子查询：

SELECT o_orderpriority, count(*) as order_count
FROM orders
WHERE o_orderdate >= date '1993-07-01' 
  AND o_orderdate < date '1993-10-01'
  AND EXISTS (
    SELECT * FROM lineitem 
    WHERE l_orderkey = o_orderkey 
      AND l_commitdate < l_receiptdate
  )
GROUP BY o_orderpriority
ORDER BY o_orderpriority;

在没有 SEMI JOIN 下推支持时，PostgreSQL 会生成两个独立的远程扫描：一个用于 lineitem 表（过滤条件），另一个用于 orders 表。这意味着大量数据需要在 PostgreSQL 端进行连接操作，性能极差。

pg_clickhouse 通过 SEMI JOIN 下推，将整个查询重写为 ClickHouse 的 LEFT SEMI JOIN：

SELECT r1.o_orderpriority, count(*) 
FROM tpch.orders r1 
LEFT SEMI JOIN tpch.lineitem r3 
  ON (((r3.l_commitdate < r3.l_receiptdate)) 
    AND ((r1.o_orderkey = r3.l_orderkey))) 
WHERE ((r1.o_orderdate >= '1993-07-01')) 
  AND ((r1.o_orderdate < '1993-10-01')) 
GROUP BY r1.o_orderpriority 
ORDER BY r1.o_orderpriority ASC

这一优化使得 Query 4 的执行时间从 650ms 降至 67ms，性能提升近 10 倍。SEMI JOIN 下推的关键在于识别 PostgreSQL 的 EXISTS/NOT EXISTS 子查询模式，并将其转换为 ClickHouse 支持的半连接语义。

聚合函数重写：从 PostgreSQL 语法到 ClickHouse 函数

分析查询中经常使用复杂的聚合函数，pg_clickhouse 需要处理 PostgreSQL 特有的语法结构。最典型的例子是 ordered-set aggregates：

percentile_cont(0.5) WITHIN GROUP (ORDER BY price)

ClickHouse 没有完全相同的函数，但提供了quantile参数化聚合函数。pg_clickhouse 的查询重写引擎执行以下转换：

percentile_cont(0.5) WITHIN GROUP (ORDER BY price) 
=> quantile(0.5)(price)

转换规则包括：

  1. 将percentile_cont()的直接参数（如 0.5）转换为quantile()的参数化常量
  2. 将ORDER BY子句中的列转换为函数参数
  3. 保持函数嵌套结构的一致性

FILTER (WHERE) 表达式到 - If 组合器的转换

PostgreSQL 9.4 引入的聚合 FILTER 子句是另一个语法挑战：

min(price) FILTER (WHERE town='ILMINSTER' 
  AND district='SOUTH SOMERSET' 
  AND postcode1='TA19')

ClickHouse 通过-If组合器支持条件聚合。pg_clickhouse 的转换规则为：

minIf(price, ((((town = 'ILMINSTER') 
  AND (district = 'SOUTH SOMERSET') 
  AND (postcode1 = 'TA19'))) > 0))

转换要点：

  1. 将 FILTER 条件转换为布尔表达式
  2. 确保表达式在 ClickHouse 中可求值（使用 > 0 确保布尔转换）
  3. 保持原始条件的逻辑等价性

查询计划重写的工程实现细节

1. 成本模型调整

pg_clickhouse 通过调整 FDW 成本估算，鼓励 PostgreSQL 规划器选择下推计划。关键参数包括：

  • fdw_startup_cost: 设置为较低值，鼓励远程执行
  • fdw_tuple_cost: 考虑网络传输成本
  • fdw_scan_selectivity: 准确估计远程过滤的选择性

2. 表达式树遍历与重写

查询重写引擎需要遍历 PostgreSQL 的表达式树，识别可下推的模式：

  • 识别函数调用及其参数
  • 检查函数是否在 ClickHouse 中有对应实现
  • 转换语法结构（WITHIN GROUP -> 参数化函数）
  • 验证转换后的表达式在 ClickHouse 中的有效性

3. 类型系统映射

PostgreSQL 和 ClickHouse 的类型系统不完全一致，需要处理：

  • Decimal 类型的精度和范围映射
  • JSON 类型的存储格式差异
  • 时间戳时区处理
  • 布尔值的表示方式（PostgreSQL 的 boolean vs ClickHouse 的 UInt8）

性能调优参数与监控要点

关键配置参数

  1. 连接参数优化：

    CREATE SERVER clickhouse_srv FOREIGN DATA WRAPPER clickhouse_fdw
OPTIONS (
  host 'clickhouse.example.com',
  port '9440',  -- TLS端口
  database 'analytics',
  compression 'lz4',  -- 启用压缩
  connect_timeout '10',
  send_receive_timeout '300'
);

  2. 查询下推控制：

    -- 强制下推尝试（开发环境）
SET clickhouse_fdw.enable_pushdown = 'force';

-- 禁用特定类型的下推
SET clickhouse_fdw.enable_join_pushdown = 'off';
SET clickhouse_fdw.enable_agg_pushdown = 'off';

监控与诊断

  1. EXPLAIN 分析：

    EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS)
SELECT ... FROM clickhouse_table;

-- 关键输出：
-- "Relations: Aggregate on (...)" 表示聚合下推成功
-- "Remote SQL: ..." 显示发送到ClickHouse的实际查询
-- "FDW Time: ..." 显示FDW处理时间

  2. 性能基准测试：

    • 使用 TPC-H SF1 作为标准工作负载
    • 监控 21/22 查询的下推成功率
    • 重点关注 Query 2、13、15、16、20、21、22 的性能表现

  3. 网络与资源监控：

    • ClickHouse 端查询执行时间
    • 网络往返延迟
    • 数据传输量（压缩前后对比）

工程落地建议与限制

适用场景

  1. 渐进式迁移：将分析工作负载逐步从 PostgreSQL 迁移到 ClickHouse
  2. 混合查询：需要同时查询本地 PostgreSQL 表和远程 ClickHouse 表
  3. 遗留系统兼容：保持现有应用代码不变，仅修改数据源配置

当前限制与规避策略

  1. 未完全下推的查询模式：

    • 复杂相关子查询
    • 某些窗口函数
    • 递归 CTE 查询

    规避策略：考虑将复杂逻辑拆分为多个步骤，或使用物化视图预处理

  2. 数据类型兼容性：

    • PostgreSQL 的 range 类型
    • 某些自定义类型
    • 复杂的数组操作

    规避策略：在 ETL 过程中进行类型转换

  3. 事务语义差异：

    • ClickHouse 的最终一致性模型
    • 缺乏完整的 ACID 支持

    规避策略：分析工作负载通常可接受最终一致性

部署最佳实践

  1. 版本兼容性矩阵：

    • PostgreSQL 13-18
    • ClickHouse 22-25
    • 定期测试新版本兼容性

  2. 连接池配置：

    -- 避免连接风暴
ALTER SERVER clickhouse_srv OPTIONS (
  ADD max_connections '50',
  ADD keepalive '60'
);

  3. 错误处理与重试：

    • 实现查询级别的重试逻辑
    • 监控连接失败率
    • 设置合理的超时时间

未来演进方向

根据 pg_clickhouse 的路线图，未来重点包括：

  1. 完全的下推覆盖：解决剩余 10 个 TPC-H 查询的下推问题
  2. ClickBench 支持：针对 ClickHouse 基准测试优化
  3. 全函数下推：支持所有 PostgreSQL 内置函数
  4. 子查询下推：完善相关子查询的支持
  5. DML 操作：支持轻量级 DELETE/UPDATE 和 COPY 批量插入

结语

pg_clickhouse 代表了数据库联邦技术的重要进展，它通过精妙的查询计划重写机制，在保持 PostgreSQL 查询语法兼容性的同时，充分利用 ClickHouse 的分析性能优势。对于面临数据分析性能瓶颈的组织，这种 "透明迁移" 方案提供了平滑的演进路径。

然而，技术决策者需要清醒认识到当前限制：并非所有查询都能完美下推，数据类型和事务语义的差异需要仔细评估。在实际部署中，建议从小规模试点开始，通过详细的性能测试和监控，逐步扩大使用范围。

随着 pg_clickhouse 的持续演进，我们有理由相信，PostgreSQL 与 ClickHouse 的协同将变得更加无缝，为混合数据架构提供更强大的技术支撑。

资料来源：

  1. ClickHouse 官方博客：Introducing pg_clickhouse: A Postgres extension for querying ClickHouse (2025-12-10)
  2. PostgreSQL 官方文档：Foreign Data Wrapper Query Planning
查看归档