pg_clickhouse：PostgreSQL到ClickHouse的查询联邦扩展实现

随着数据量的指数级增长，越来越多的企业面临从 PostgreSQL 向 ClickHouse 迁移分析工作负载的需求。数据迁移相对简单，但查询和应用代码的迁移却成为主要瓶颈。ClickHouse 官方近期发布的 pg_clickhouse 扩展（v0.1.0）正是为了解决这一痛点，它允许 PostgreSQL 直接查询 ClickHouse 数据，实现透明的查询联邦。

架构设计与 FDW 原理

pg_clickhouse 基于 PostgreSQL 的 Foreign Data Wrapper（FDW）机制构建。FDW 是 PostgreSQL 9.3 引入的标准特性，遵循 SQL/MED 规范，允许数据库通过扩展访问外部数据源。pg_clickhouse 并非全新开发，而是在现有 clickhouse_fdw 项目基础上的现代化重构。

该扩展的核心设计目标包括：

1. 从 PostgreSQL 透明执行 ClickHouse 查询
2. 允许现有 PostgreSQL 查询无需修改即可运行
3. 尽可能将查询执行下推到 ClickHouse
4. 为持续查询优化和下推演进奠定基础

从技术实现看，pg_clickhouse 采用了标准的 PGXS 构建管道，支持 PostgreSQL 13-18 和 ClickHouse 22-25 版本。它集成了最新的 ClickHouse C++ 客户端库，并提供了 TLS 连接支持，这对于 ClickHouse Cloud 用户至关重要。

查询下推优化机制

查询下推是 pg_clickhouse 的核心价值所在。扩展需要智能地将 PostgreSQL 查询转换为 ClickHouse 能够高效执行的查询形式。这涉及到多个层面的优化：

1. 聚合函数转换

PostgreSQL 的聚合函数语法与 ClickHouse 存在差异。例如，PostgreSQL 的percentile_cont()有序集合聚合函数在 ClickHouse 中不存在对应函数。pg_clickhouse 能够智能地将：

percentile_cont(0.5) WITHIN GROUP (ORDER BY price)

转换为 ClickHouse 的：

quantile(0.5)(price)

这种转换不仅限于简单函数，还包括带FILTER子句的聚合。PostgreSQL 的：

min(price) FILTER (WHERE town='ILMINSTER')

会被转换为 ClickHouse 的：

minIf(price, ((town = 'ILMINSTER') > 0))

2. SEMI-JOIN 下推支持

对于包含EXISTS子查询的复杂查询，pg_clickhouse v0.1.0 引入了 SEMI-JOIN 下推支持。以 TPC-H Query 4 为例，原本需要两个独立的远程扫描，现在可以完全下推为单个 LEFT SEMI JOIN 查询：

-- 原始PostgreSQL查询
SELECT o_orderpriority, count(*) 
FROM orders
WHERE o_orderdate >= '1993-07-01' 
  AND o_orderdate < '1993-10-01'
  AND EXISTS (SELECT * FROM lineitem 
              WHERE l_orderkey = o_orderkey 
                AND l_commitdate < l_receiptdate)
GROUP BY o_orderpriority;

-- 下推后的ClickHouse查询
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;

3. 类型系统映射

pg_clickhouse 支持多种数据类型的透明映射，包括：

• 布尔类型：PostgreSQL boolean ↔ ClickHouse UInt8
• 十进制类型：PostgreSQL numeric ↔ ClickHouse Decimal
• JSON 类型：PostgreSQL jsonb ↔ ClickHouse String（需要应用层解析）

性能对比与工程实践

根据 ClickHouse 官方测试，使用 TPC-H 基准（scale factor 1）对比了三种场景的性能：

查询	PostgreSQL 原生	pg_clickhouse（无 SEMI-JOIN）	pg_clickhouse（有 SEMI-JOIN）
Q1	4478ms	✅ 82ms	✅ 73ms
Q3	1454ms	✅ 74ms	✅ 74ms
Q4	650ms	超时	✅ 67ms
Q6	740ms	✅ 33ms	✅ 42ms

从测试结果可以看出，对于能够完全下推的查询，性能提升可达数十倍。SEMI-JOIN 下推的引入显著改善了复杂查询的性能。

部署与配置参数

1. 快速部署

使用 Docker 快速体验：

docker run --name pg_clickhouse -e POSTGRES_PASSWORD=my_pass \
  -d ghcr.io/clickhouse/pg_clickhouse:18

2. 手动安装步骤

1. 安装扩展：

   CREATE EXTENSION pg_clickhouse;
   

2. 创建外部服务器：

   CREATE SERVER clickhouse_server 
   FOREIGN DATA WRAPPER pg_clickhouse
   OPTIONS (
     host 'clickhouse.example.com',
     port '9440',
     database 'analytics',
     use_tls 'true'
   );
   

3. 创建用户映射：

   CREATE USER MAPPING FOR current_user
   SERVER clickhouse_server
   OPTIONS (
     user 'clickhouse_user',
     password 'secret_password'
   );
   

4. 创建外部表：

   CREATE FOREIGN TABLE clickhouse_metrics (
     timestamp TIMESTAMP,
     metric_name TEXT,
     value FLOAT8
   ) SERVER clickhouse_server
   OPTIONS (
     table_name 'metrics_table',
     schema_name 'analytics'
   );
   

3. 关键配置参数

• use_tls: 是否使用 TLS 连接（ClickHouse Cloud 必需）
• clickhouse_settings: ClickHouse 特定设置，如max_threads、max_memory_usage
• fetch_size: 每次从 ClickHouse 获取的行数，影响内存使用和性能
• async_execution: 是否启用异步查询执行

限制与注意事项

尽管 pg_clickhouse 功能强大，但在生产环境中使用时需要注意以下限制：

1. 版本兼容性：目前仅支持 PostgreSQL 13-18 和 ClickHouse 22-25
2. DML 操作有限：主要支持 SELECT 查询，INSERT/UPDATE/DELETE 支持有限
3. 复杂查询下推：某些复杂嵌套查询可能无法完全下推
4. 事务支持：跨数据库事务处理需要谨慎设计
5. 数据类型映射：某些高级数据类型（如数组、范围类型）可能无法完全映射

监控与调试建议

1. 查询计划分析

使用EXPLAIN和EXPLAIN (VERBOSE)分析查询下推情况：

EXPLAIN (VERBOSE, COSTS)
SELECT * FROM clickhouse_metrics 
WHERE timestamp > NOW() - INTERVAL '1 day';

2. 性能监控指标

• 远程执行时间：通过EXPLAIN ANALYZE查看FDW Time
• 网络传输量：监控fetch_size与实际传输行数
• 内存使用：关注 PostgreSQL 和 ClickHouse 两端的内存使用情况

3. 常见问题排查

1. 连接失败：检查 TLS 配置和网络连通性
2. 查询超时：调整statement_timeout和 ClickHouse 的max_execution_time
3. 类型转换错误：验证数据类型映射，必要时使用显式类型转换

未来路线图

ClickHouse 团队已经规划了 pg_clickhouse 的未来发展方向：

1. 完善下推覆盖：支持所有 TPC-H 和 ClickBench 查询的完全下推
2. 函数映射扩展：支持更多 PostgreSQL 内置函数的透明转换
3. DML 操作增强：支持批量 INSERT、轻量级 UPDATE/DELETE
4. 设置管理：支持通过 CREATE SERVER 传递 ClickHouse 设置
5. UNION 查询支持：下推包含 UNION 的复杂查询

工程实践建议

对于考虑采用 pg_clickhouse 的团队，建议遵循以下实施路径：

1. 评估阶段：

   • 识别现有 PostgreSQL 分析查询的性能瓶颈
   • 测试关键查询在 pg_clickhouse 上的下推效果
   • 评估数据类型映射的完整性

2. 试点阶段：

   • 选择非关键业务的分析工作负载进行试点
   • 建立监控和告警机制
   • 收集性能基准数据

3. 扩展阶段：

   • 逐步迁移更多分析工作负载
   • 优化查询模式和索引设计
   • 建立跨数据库的数据治理流程

4. 生产阶段：

   • 实现高可用和故障转移机制
   • 建立容量规划和性能调优流程
   • 制定回滚和应急方案

总结

pg_clickhouse 代表了数据库联邦技术的重要进展，它巧妙地将 PostgreSQL 的灵活性与 ClickHouse 的分析性能相结合。通过智能的查询下推和类型映射，它显著降低了从 PostgreSQL 迁移到 ClickHouse 的技术门槛。

然而，作为 v0.1.0 版本，pg_clickhouse 仍在快速发展中。工程团队在采用时需要充分评估其限制，并建立相应的监控和应急机制。随着项目的成熟，pg_clickhouse 有望成为混合数据库架构中的关键组件，为现代数据平台提供更灵活的查询联邦能力。

资料来源：

1. ClickHouse 官方博客：Introducing pg_clickhouse: A Postgres extension for querying ClickHouse
2. GitHub 仓库：ClickHouse/pg_clickhouse
3. PostgreSQL 官方文档：Foreign Data Wrappers