ReaderMX.
ClickHouseInc

pg_clickhouse 强力更新：JSONB 支持，复杂数据查询更高效！

CDavid Wheeler约 10 分钟
图片

本文字数：5920；估计阅读时间：15 分钟

作者：David Wheeler

Image

我们对社区对pg\_clickhouse的欢迎感到欣慰，它是一个用于从 Postgres 查询 ClickHouse 数据库的扩展。近期大量的使用产生了许多反馈，我们一直在最近的几个版本中积极地解决这些问题。这些改动遵循了我们对 pg\_clickhouse 一贯的宗旨：下推，下推，再下推！让我们快速了解一下。

Image
设置

如果您想跟着操作，以下示例使用了这个 ClickHouse 表：

CREATE TABLE events (    id    UInt32,    event String,    tags  Array(String),    at    DateTime64,    props JSON) ENGINE = MergeTree ORDER BY (event, id);
INSERT INTO events VALUES    (        1, 'order', ['a', 'b', 'c'], '2025-12-28 10:42:35.342',        '{"cid": "C100", "address": {"city": "Paris, France", "code": "75001"}}'    ),    (        2, 'order', ['d', 'e', 'f'], '2026-02-22 05:26:26.982',        '{"cid": "C200", "address": {"city": "London, UK", "code": "SW1A"}}'    ),    (        3, 'return', ['😀', '⚽️'], now64() - 86400 * 2,        '{"cid": "C200", "address": {"city": "Manchester, UK", "code": "M2 1AB"}}'    ),    (        4, 'order', ['x', 'y', 'z'], now64() - 86400,        '{"cid": "C300", "address": {"city": "New York, USA", "code": "10030"}}'    ),    (        5, 'deliver', [], now64(),        '{"cid": "C500", "address": {"city": "Portland, USA", "code": "97212"}}'    );

以及 Postgres 中以下 pg\_clickhouse 外部表配置：

CREATE SERVER ch FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(driver 'http');CREATE USER MAPPING FOR CURRENT_USER SERVER ch;CREATE EXTENSION pg_clickhouse;CREATE SCHEMA customer;IMPORT FOREIGN SCHEMA "default" FROM SERVER ch INTO customer;
Image
JSONB 访问器

对于映射到 ClickHouse JSON 类型的 Postgres JSONB 列，pg\_clickhouse v0.1.10 增加了 JSONB 访问器运算符和函数在 SELECT 子句之外（通常在 WHERE、ORDER BY 和 HAVING 子句中）的下推功能。它通过将 JSON 属性访问器转换为 ClickHouse 子列语法来实现此功能。

例如，在使用 ->> 比较 JSON 对象值与字符串时，来自这个详细 EXPLAIN 的 Remote SQL 输出如下：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, event, props FROM customer.events WHERE props ->> 'cid' = 'C200';
                                        QUERY PLAN------------------------------------------------------------------------------------------ Foreign Scan on customer.events   Output: id, event, props   Remote SQL: SELECT id, event, props FROM "default".events WHERE ((props.cid = 'C200'))(3 rows)

这使得 ClickHouse 能够直接过滤“C100”。输出结果正如您所预期：

SELECT id, event, propsFROM customer.events WHERE props ->> 'cid' = 'C200';
 id | event  |                                  props----+--------+--------------------------------------------------------------------------  2 | order  | {"cid": "C200", "address": {"city": "London, UK", "code": "SW1A"}}  3 | return | {"cid": "C200", "address": {"city": "Manchester, UK", "code": "M2 1AB"}}(2 rows)

对于返回 JSONB 值的 -> 运算符，pg\_clickhouse 让 ClickHouse 将 子列语法返回的值转换为 JSON，以便像 Postgres 那样进行值比较：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, event, props FROM customer.events WHERE props -> 'cid' = '"C300"'::jsonb;
    QUERY PLAN---------------------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id, event, props   Remote SQL: SELECT id, event, props FROM "default".events WHERE ((toJSONString(props.cid) = '"C300"'))(3 rows)

执行查询返回预期结果：

SELECT id, event, propsFROM customer.events WHERE props -> 'cid' = '"C300"'::jsonb;
 id | event |                                 props----+-------+------------------------------------------------------------------------  4 | order | {"cid": "C300", "address": {"city": "New York, USA", "code": "10030"}}(1 row)

同样，JSONB 函数 jsonb_extract_path() 和 jsonb_extract_path_text() 也遵循相同的模式，它们同样支持通过多条路径访问嵌套值。这一点可以在此计划的 Remote SQL 中清晰可见：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, event, props FROM customer.eventsWHERE jsonb_extract_path_text(props, 'address', 'city') = 'Paris, France';
  QUERY PLAN------------------------------------------------------------------------------------------------------------ Foreign Scan on customer.events   Output: id, event, props   Remote SQL: SELECT id, event, props FROM "default".events WHERE ((props.address.city = 'Paris, France'))(3 rows)

当然，执行结果符合预期：

SELECT id, event, props FROM customer.eventsWHERE jsonb_extract_path_text(props, 'address', 'city') = 'Paris, France';
 id | event |                                 props----+-------+------------------------------------------------------------------------  1 | order | {"cid": "C100", "address": {"city": "Paris, France", "code": "75001"}}(1 row)

同样，在使用 jsonb_extract_path() 下推 JSON 值比较时也适用：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, event, props FROM customer.eventsWHERE jsonb_extract_path(props, 'address', 'city') = '"New York, USA"';
 QUERY PLAN---------------------------------------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id, event, props   Remote SQL: SELECT id, event, props FROM "default".events WHERE ((toJSONString(props.address.city) = '"New York, USA"'))(3 rows)
SELECT id, event, props FROM customer.eventsWHERE jsonb_extract_path(props, 'address', 'city') = '"New York, USA"';
 id | event |                                 props----+-------+------------------------------------------------------------------------  4 | order | {"cid": "C300", "address": {"city": "New York, USA", "code": "10030"}}(1 row)
Image
SQL 值函数

我们的一位客户在将查询从 Postgres 迁移到 pg\_clickhouse 表后，发现在使用某些 日期和时间函数（如 CURRENT_DATE 和 CURRENT_TIMESTAMP）时遇到了一些故障。由于 pg\_clickhouse 未能下推这些函数，当它们与 date_part() 和 date_trunc() 等（这些函数能够下推）配合使用时便会产生问题。

pg\_clickhouse v0.2.0改进了所有“当前”类型日期和时间函数的下推能力，现在这些函数都能被下推，并且能比以前更准确地根据本地 Postgres 配置生成相应的值。

例如，要查询 `CURRENT_DATE` 之前的记录时，pg\_clickhouse 会生成以下计划：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id FROM customer.events WHERE AT < CURRENT_DATE;

它利用 Postgres 会话中当前设置的时区，以确保日期相对于预期的时区。对于 CURRENT_TIMESTAMP 也是如此，它同时指定了精度 6，这是 Postgres 时间戳的默认精度：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id FROM customer.events WHERE AT < CURRENT_TIMESTAMP;
  QUERY PLAN------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id   Remote SQL: SELECT id FROM "default".events WHERE ((at < now64(6, 'America/New_York')))(3 rows)

自然地，它会传递一个显式精度：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id FROM customer.events WHERE AT < CURRENT_TIMESTAMP(3);
                                        QUERY PLAN------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id   Remote SQL: SELECT id FROM "default".events WHERE ((at < now64(3, 'America/New_York')))(3 rows)

除了这些 SQL 标准的当前日期和时间关键字外，我们还为 Postgres 特有的时间戳函数 clock_timestamp()、statement_timestamp() 和 transaction_timestamp() 增加了下推能力，它们都能被下推到最接近的 ClickHouse 等效函数 nowInBlock64：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id FROM customer.events WHERE AT < clock_timestamp();
QUERY PLAN-------------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id   Remote SQL: SELECT id FROM "default".events WHERE ((at < nowInBlock64(6, 'America/New_York')))(3 rows)

这些函数能与 date_part 等其他下推函数正常配合使用：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, at FROM customer.eventsWHERE date_part('year', at) < date_part('year', CURRENT_DATE);
QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id, at   Remote SQL: SELECT id, at FROM "default".events WHERE ((toYear(at) < toYear(cast(toDate(now('America/New_York')), 'Nullable(DateTime)'))))(3 rows)
SELECT id, at FROM customer.eventsWHERE date_part('year', at) < date_part('year', CURRENT_DATE);
 id |             at----+----------------------------  1 | 2025-12-28 05:42:35.342-05(1 row)

同样，它们也能与 date_trunc 正常配合，即便加入了某些间隔日期运算：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, atFROM customer.eventsWHERE date_trunc('day', at) >= date_trunc('day', CURRENT_DATE) - INTERVAL '1 day';
  QUERY PLAN----------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id, at   Remote SQL: SELECT id, at FROM "default".events WHERE ((toStartOfDay(at) >= (toStartOfDay(toDate(now('America/New_York'))) - 86400)))(3 rows)
SELECT id, atFROM customer.eventsWHERE date_trunc('day', at) >= date_trunc('day', CURRENT_DATE) - INTERVAL '1 day';
 id |             at----+----------------------------  5 | 2026-04-17 17:29:47.046-04  4 | 2026-04-16 17:29:47.046-04(2 rows)
Image
数组函数

继 v0.1.4中 HTTP 驱动数组解析得到改进之后，pg_clickhouse v0.2.0 增加了对一系列 数组函数的下推 (pushdown) 支持。例如，array_cat 映射到 arrayConcat：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, tags FROM customer.events WHERE array_cat(tags, ARRAY['🥏']) = ARRAY['😀','⚽️','🥏'];
 QUERY PLAN------------------------------------------------------------------------------------------------------------ Foreign Scan on customer.events   Output: id, tags   Remote SQL: SELECT id, tags FROM "default".events WHERE ((arrayConcat(tags, ['🥏']) = ['😀','⚽️','🥏']))(3 rows)
SELECT id, tagsFROM customer.eventsWHERE array_cat(tags, ARRAY['🥏']) = ARRAY['😀','⚽️','🥏'];
 id |  tags----+---------  3 | {😀,⚽️}(1 row)

array_to_string 映射到 arrayStringConcat：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, tagsFROM customer.eventsWHERE array_to_string(tags, '|') = 'a|b|c';
QUERY PLAN------------------------------------------------------------------------------------------------------ Foreign Scan on customer.events   Output: id, tags   Remote SQL: SELECT id, tags FROM "default".events WHERE ((arrayStringConcat(tags, '|') = 'a|b|c'))(3 rows)
SELECT id, tagsFROM customer.eventsWHERE array_to_string(tags, '|') = 'a|b|c';
 id |  tags----+---------  1 | {a,b,c}(1 row)

此外，string_to_array 映射到 splitByString，此处与 前述 JSONB 访问器结合使用：

EXPLAIN (VERBOSE, COSTS OFF)SELECT id, event, jsonb_extract_path_text(props, 'address', 'code')FROM customer.eventsWHERE string_to_array(jsonb_extract_path_text(props, 'address', 'city'), ', ') = ARRAY['Portland', 'USA'];
 QUERY PLAN---------------------------------------------------------------------------------------------------------------------------------------------- Foreign Scan on customer.events   Output: id, event, tags, at, props   Remote SQL: SELECT id, event, tags, at, props FROM "default".events WHERE ((splitByString(', ', props.address.city) = ['Portland','USA']))(3 rows)
SELECT id, event, jsonb_extract_path_text(props, 'address', 'code')FROM customer.eventsWHERE string_to_array(jsonb_extract_path_text(props, 'address', 'city'), ', ') = ARRAY['Portland', 'USA'];
 id |  event  | jsonb_extract_path_text----+---------+-------------------------  5 | deliver | 97212(1 row)

我们还支持了更多映射！有关更多功能，请查阅 完整列表(https://pgxn.org/dist/pg_clickhouse/doc/pg_clickhouse.html#Pushdown.Functions "pg_clickhouse Docs: Pushdown Functions")，探索其广泛的可能性。

Image
HTTP 结果集流式传输

当然，我们并非只关注下推 (pushdown)；有时，我们也需要处理所谓的“回推 (pushback)”。

默认情况下，当 Postgres 外部数据包装器 (foreign data wrapper) 执行外部查询时，它会在将所有结果返回给调用方之前，将它们全部收集到内存中。这对于 ClickHouse 典型聚合查询返回的小型结果集非常有效。但有时应用程序需要自行处理大量数据，这可能导致内存压力问题，因为 Postgres 会将整个数据集加载到内存中。警惕 OOM Killer 的出现！

在 pg\_clickhouse v0.1.10中，我们为 HTTP 驱动程序添加了查询结果流式传输 (query result streaming) 功能。该功能默认在内存中缓冲约 50MB 的结果批次，并在内存被下一批次数据重用前将其返回。为了实际演示其效果，我们首先将 NYC taxi data set加载到 ClickHouse 中，然后启动了一个未启用流式传输功能的 pg\_clickhouse v0.6.1 OCI 镜像 (image)，并使用 http 驱动程序将 trips_small 表导入到 nyc_taxi 模式：

docker run --name pg_clickhouse -p 6432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust -d ghcr.io/clickhouse/pg_clickhouse:18
docker exec -it pg_clickhouse bash -c 'apt-get update && apt-get install ca-certificates'
psql -U postgres -h localhost -p 6432 <<EOFCREATE EXTENSION pg_clickhouse;CREATE SERVER my_ch FOREIGN DATA WRAPPER clickhouse_fdw OPTIONS(    driver 'http', host 'abcdefghij.us-east-1.aws.clickhouse.cloud', port '8443');CREATE USER MAPPING FOR CURRENT_USER SERVER my_ch OPTIONS(user 'default', password 'xxxxxxxxxxxxx');CREATE SCHEMA nyc_taxi;IMPORT FOREIGN SCHEMA nyc_taxi FROM SERVER my_ch INTO nyc_taxi;EOF

我们启动了一个进程，持续测量该 OCI 容器的内存消耗：

while true; do    docker stats --no-stream --format "{{.MemUsage}}" pg_clickhouse | \        cut -d '/' -f 1 | xargs printf "%s%s\n" ",[object Object]," | sed -e 's/MiB//g'done

最后，我们运行了以下查询：

psql -U postgres -h localhost -p 6432 -c 'SELECT * FROM nyc_taxi.trips_small' > /dev/null

随后，我们使用已启用流式传输功能的 pg\_clickhouse v0.2.0 OCI 镜像 (image)重复了上述步骤，并对结果进行了比较。下图清晰地展示了两者差异：

Image

以下是经过时间对齐处理后的数据，同样印证了这一结果：

Image

可以看到，v0.6.0 版本的内存消耗飙升至 600MiB 以上，而启用流式传输功能的 v0.2.0 版本从未超过 86 MiB。同时，v0.2.0 的性能也略有提升！显然，结果集越大，内存节省越显著。我们还计划在未来的版本中，在二进制驱动程序中引入流式传输功能。

Image
后续计划

我们还有更多内容正在开发中。敬请关注此空间，获取有关 window function pushdown 和正则表达式兼容性的更多进展。在此期间，欢迎加入我们的 [PGConf.dev](https://2026.pgconf.dev/ "PGConf.dev 2026") 大会，了解我们在 构建 Foreign Data Wrapper过程中所学到的经验。

/END/

试用阿里云 ClickHouse企业版

轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G

图片
图片

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：[email protected]

图片图片

KEEP THE THREAD GOING

更多来源文章