clickhouse和pgSql跨库查询方案对比 - 实践
在企业内容架构中，将 PostgreSQL（简称 PG）外部数据“联接”到 ClickHouse 查询中 是一个特别常见的需求，比如做跨库分析、材料比对、报表或中台汇聚。
ClickHouse 原生就支持多种“外部数据源访问机制”，包括 PostgreSQL。下面我给你讲得 既清晰又实用
一、实现目标
让 ClickHouse 能直接访问 PostgreSQL 的表（或查询结果），
从而在 ClickHouse 的 SQL 查询中使用 PG 数据，就像本地表一样。
二、完成方式总览
方式 说明 场景 推荐度
✅ PostgreSQL 表引擎 ClickHouse 内置 PostgreSQL() 引擎直接访问 PG 表 最通用、官方支持 ⭐⭐⭐⭐⭐
PostgreSQL 外部字典（Dictionary） 从 PG 同步数据为 ClickHouse 字典 小数据量、维度表 ⭐⭐⭐
Materialized View + 定时同步 定期拉取 PG 数据入 ClickHouse 大数据量、离线同步 ⭐⭐⭐
通过 JDBC / ODBC 利用外部表或函数访问 PG 异构环境或特殊驱动 ⭐⭐
三、推荐方案：PostgreSQL 表引擎
✅ ClickHouse 自 21.3 版本起支持原生 PostgreSQL 引擎
1️⃣ 启用 PostgreSQL 引擎
先确认你的 ClickHouse 支持该引擎：
SELECT * FROM system.table_engines WHERE name = ‘PostgreSQL’;
若无结果，请在 clickhouse-server 启动参数或 config.xml 中开启：
1并确保安装了 PostgreSQL 驱动（默认已包含）。
2️⃣ 语法示例：直接访问 PG 表
CREATE TABLE pg_orders
ENGINE = PostgreSQL
(
‘host=192.168.10.12’,
‘database=mydb’,
‘user=myuser’,
‘password=mypass’,
‘orders’
);
含义：
PostgreSQL：引擎类型；
参数依次为：host, database, user, password, table_name。
现在你就可以直接查询 PG 的表啦
SELECT * FROM pg_orders LIMIT 10;
它会在查询时实时读取 PostgreSQL 数据（不做持久化缓存）。
3️⃣ 跨库 JOIN 示例
你可以直接与 ClickHouse 本地表进行联表查询：
SELECT c.customer_name, o.amount, o.create_time
FROM clickhouse.customers AS c
JOIN pg_orders AS o
ON c.customer_id = o.customer_id
WHERE o.create_time >= today() - 7;
ClickHouse 会在查询时从 PostgreSQL 拉取匹配信息（push-down 优化有限，但十分方便）。
4️⃣ 可选：限制数据量或分区读取
行在查询中指定条件：
SELECT * FROM pg_orders WHERE order_date >= ‘2025-11-01’;
或在创建时指定 schema：
CREATE TABLE pg_table
ENGINE = PostgreSQL(‘pg_host:5432’, ‘db’, ‘user’, ‘pass’, ‘public.orders’);
四、外部表（postgresql() 函数）
假如不想长期建表，行用函数式访问（临时查询）：
SELECT *
FROM postgresql(‘192.168.10.12:5432’, ‘mydb’, ‘orders’, ‘myuser’, ‘mypass’)
LIMIT 10;
同样可以直接 JOIN：
SELECT c.name, p.amount
FROM customers AS c
JOIN postgresql(‘192.168.10.12:5432’, ‘mydb’, ‘orders’, ‘myuser’, ‘mypass’) AS p
ON c.id = p.customer_id;
✅ 优点：
无需创建 ClickHouse 表；
即查即用；
非常适合临时数据分析或验证。
五、运用 PostgreSQL Dictionary （维表方式）
想同步 PG 的维度数据（如地区、用户标签），就是如果你只
可以采用 ClickHouse 的 外部字典（Dictionary）：
然后在 SQL 中：
SELECT dictGet(‘user_dict’, ‘user_name’, toUInt64(1001));
✅ 适合：
小表（几十万行以内）；
缓存型维表；
低延迟场景。
六、性能与注意事项
困难 说明 优化建议
行存储，不适合大范围扫描 仅联查小表或加过滤条件就是查询慢 PostgreSQL
连接超时 默认超时较短 在 config.xml 调高 connect_timeout、receive_timeout
认证失败 检查 PG 的 pg_hba.conf 是否允许远程访问
JOIN 性能低 可先同步 PG 内容到本地临时表再 JOIN 用 INSERT INTO local SELECT * FROM pg_table
网络消耗高 每次查询都会从 PG 拉取材料 可定期物化（用 MATERIALIZED VIEW 缓存）
七、实战建议
场景 推荐方案
临时分析 / 跨库 JOIN ✅ postgresql() 函数式查询
稳定跨库数据表 ✅ ENGINE = PostgreSQL 创建表
小维度表、标签表 ✅ 外部字典（Dictionary）
大数据同步分析 ✅ 定期 INSERT SELECT 同步至 ClickHouse
✅ 八、总结一句话
底层原理：ClickHouse 依据 PostgreSQL 引擎或 postgresql() 函数，在查询时建立 TCP 连接，实时从 PG 读取数据并参与查询计算。
“实时拉取 + 按需处理”。就是它不是复制信息，而
方案 是否实时 是否持久化 适用场景
PostgreSQL ENGINE ✅ ❌ 实时跨库联查
postgresql() 函数 ✅ ❌ 临时查询
Dictionary 定期刷新 ✅ 小表维度缓存
定期同步 ❌（准实时） ✅ 大表分析场景