Introducing pg_re2, fast, RE2-powered regular expressions in Postgres

Summary

pg_re2 is a Postgres extension that brings RE2-powered regular expression functions and operators to Postgres, designed to address both execution speed and syntax compatibility with ClickHouse. It uses finite-automata-based RE2 rather than Postgres's backtracking POSIX engine, and version 0.4.0 adds the @~ operator plus btree and GIN index support. Benchmarks on an AWS m6g.2xlarge with PostgreSQL 18 report RE2's functions running at 1.8x to 8.6x the speed of native equivalents, while indexed operations are 1.1x to 1.8x faster. With pg_clickhouse, using RE2-backed functions such as re2match() keeps regex behavior consistent between Postgres execution and ClickHouse pushdown, including patterns such as Quake\b. The extension duplicates ClickHouse's suite for string search, replacement, extraction, and splitting.

Takeaways

1. 1

   RE2-backed functions benchmarked at 1.8x to 8.6x the performance of PostgreSQL's native regular expression functions on an AWS m6g.2xlarge running PostgreSQL 18.

2. 2

   pg_re2 v0.4.0 supports btree and GIN indexes; its gin_re2_ops opclass can index byte-equivalent trigrams across punctuation, including patterns such as error_code=42[0-9].

3. 3

   The re2match() function uses the same RE2 library as ClickHouse, so a pattern such as Quake\b returns consistent results in Postgres and after ClickHouse pushdown.