Pg_re2扩展为Postgres引入高速RE2正则表达式

背景

ClickHouse 团队一直以性能为核心设计理念，这一点从其开发的 ClickBench 和 PostgresBench 等基准测试中可见一斑，部分测试已成为行业标准。这种对速度的追求深入到产品最细微的角落，包括 SQL 函数和运算符。在正则表达式领域，PostgreSQL 原生使用基于回溯的 POSIX 正则引擎，而 ClickHouse 则采用了基于有限自动机的 RE2 引擎，后者在性能上具有理论优势。为了将这一优势带给 Postgres 用户，同时解决跨数据库正则表达式语法不兼容的问题，ClickHouse 团队推出了 pg_re2 扩展。

核心内容

pg_re2 是一个 PostgreSQL 扩展，它使用 Google 的 RE2 库为 Postgres 提供快速的正则表达式功能。安装方式简单，只需执行 CREATE EXTENSION re2; 即可，随后便可以使用 @~ 运算符（如 'HouseQuake' @~ 'Quake\b' 返回 t）。该扩展可从 PGXN 或 GitHub 获取，ClickHouse Managed Postgres 服务已内置支持。

速度优势

Postgres 的 POSIX 正则引擎基于回溯算法，返回最长匹配；而 RE2 基于有限自动机理论，能够提供更稳定的执行时间保证。早在 2010 年，RE2 的作者 Russ Cox 就指出：RE2 证明了利用自动机理论可以实现现代回溯正则库（如 PCRE）的几乎所有特性，同时由于理论根基扎实，RE2 在执行时间上具有更强的保证，并支持高级分析。正是基于这样的发现，ClickHouse 将 RE2 用于字符串搜索、替换、提取和分割。pg_re2 为 Postgres 用户带来了同样的收益。

基准测试（AWS m6g.2xlarge, PostgreSQL 18）显示：

• RE2 在所有测试中胜出，性能是 Postgres 原生函数的 1.8 倍到 8.6 倍。
• 最大提升来自 extract_all 操作（7.2-8.6 倍），它直接返回数组，而与之对比的 Postgres 的 regexp_matches(..., 'g') 函数是集合返回，存在额外开销。

索引支持

pg_re2 v0.4.0 新增了匹配运算符 @~，并提供了 btree 和 GIN 索引支持，以减少对 RE2 正则表达式进行表扫描的需求。第二次基准测试比较了 RE2 与 Postgres 原生正则表达式的索引性能：

• RE2 再次在所有测试中胜出，速度提升 1.1 倍到 1.8 倍。
• 前缀匹配在 btree 索引上的总体加速与表扫描类似，因为两者都将原生执行速度应用于索引条目。
• gin_re2_ops GIN 操作类受益于收集 RE2 需要的字节等价三元组，包括跨标点符号，使得像 error_code=42[0-9] 这样的模式能够使用索引，而 Postgres 的 pg_trgm 操作类在此类模式上会退化为全表扫描。
• 对于纯单词模式，RE2 的 gin_re2_ops 和 Postgres 的 pg_trgm 提取的键相似，但 pg_trgm 的代价更低的检查函数可能获胜（如 error_code=123 的情况）。

兼容性优势

创建 pg_re2 的第二个驱动力是与 ClickHouse 正则表达式函数的兼容性。RE2 与 Postgres POSIX 表达式不仅在速度上不同，在语法上也有差异。这些差异很重要，因为 pg_clickhouse v0.2.0 已经为多个 PostgreSQL 运算符和函数添加了下推功能。对于简单的正则表达式，下推工作良好，如 SELECT id, by FROM log.entries WHERE by ~ 'Click' ORDER BY id; 能正确返回结果。但有时会出现意外结果，例如：

SELECT id, by ~ 'Quake\b' FROM log.entries WHERE by ~ 'Quake\b';

在上面的查询中，WHERE 子句中的 by ~ 'Quake\b' 被下推到 ClickHouse，返回两行；但 SELECT 列表中的相同表达式在 Postgres 中执行，意外地返回 false。原因在于：ClickHouse 中 \b 表示单词边界，而 Postgres 中 \b 表示退格字符。因此下推后的结果与本地执行结果不一致。

Postgres 和 ClickHouse 之间还存在大量其他差异，例如：

• 反斜杠转义：许多特性工作方式不同，或只在一方存在。
• 标志（flags）：两者通过 (?xyz) 或额外参数支持的标志偏离很大，尽管部分术语含义可能等价，但文档差异使验证行为变得困难。
• 前瞻/后顾断言：ClickHouse 不支持 (?=re)、(?!re)、(?<=re)、(?<!re)。
• 反向引用：ClickHouse 不支持 \1、\2 等，但两者都支持替换表达式中的反向引用。
• 字符类：Postgres 的字符类包含非 ASCII 字符（受排序规则和区域设置影响），而 ClickHouse 的 RE2 字符类仅包含 ASCII 字符。

解决方案：pg_re2 函数下推

为了简化问题（或引入两个问题），pg_clickhouse v0.3.0 增加了 pg_re2 函数的下推支持。使用 pg_re2 函数处理 pg_clickhouse 表中的列，具有多重好处：

• 无需判断是否发生下推，因为行为在两端完全一致。
• RE2 提供渐近更优的性能。
• 对于 ClickHouse 用例，只需掌握一种正则语法即可。

回到之前的意外结果，如果使用 re2match() 函数替代 ~ 运算符，则 WHERE 和 SELECT 中的行为一致，因为两者都使用 RE2 引擎，\b 均被解释为单词边界。

关键要点

• pg_re2 是一个 PostgreSQL 扩展，通过集成 Google RE2 正则引擎，为 Postgres 用户提供更快的正则表达式处理能力。
• 性能提升显著：基准测试显示，RE2 在大部分场景下比 Postgres 原生 POSIX 正则快 1.8 倍到 8.6 倍，索引操作快 1.1 倍到 1.8 倍。
• 索引支持全面：支持 btree 和 GIN 索引，GIN 操作类 gin_re2_ops 能更好地利用三元组索引，减少表扫描。
• 核心优势源于算法差异：RE2 基于有限自动机，避免回溯导致的指数级性能退化，执行时间更稳定。
• 兼容性问题是重要驱动力：Postgres 和 ClickHouse 的正则语法存在大量差异（如 \b 含义、标志、字符类、前瞻/后顾、反向引用等），使用 pg_re2 可以统一语法，避免跨数据库查询时的意外结果。
• **pg_clickhouse 用户受益最大