. -->
Introducing pg_re2, fast, RE2-powered regular expressions in Postgres | ClickHouse<br>Skip to content
Open searchOpen region selectorEnglish<br>Japanese
48.5kSign inGet Started
->Scroll to top<br>BackBlog<br>Engineering<br>Copy pageCopied!More actionsView as Markdown Open this page in Markdown<br>Open in ChatGPT Ask questions about this page<br>Open in Claude Ask questions about this page<br>Open in v0 Ask questions about this page
Introducing pg_re2, fast, RE2-powered regular expressions in Postgres
David Wheeler and Philip Dubé<br>Jul 8, 2026 · 10 minutes read
Speed is a feature that motivates much of ClickHouse product design and<br>engineering. One can plainly see this attention to performance in our<br>development of benchmarks, such as ClickBench and PostgresBench, some of<br>which have become industry standards. Unsurprisingly this ethos reaches the<br>deepest crevices of our products, down to SQL functions and operators.
In that spirit, we've introduced pg_re2, a Postgres extension providing fast<br>RE2-powered regular expressions in Postgres. Install it from<br>PGXN or GitHub; ClickHouse Managed Postgres already has it.
You can install it like so:
1CREATE EXTENSION re2;Copy command
Then you're ready to go!
1SELECT 'HouseQuake' @~ 'Quake\b';Copy command
1?column?<br>2----------<br>3 t<br>4(1 row)Copy command
Hit the docs for the full list of features.
Of course, Postgres itself provides a wealth of regular expression operators<br>and functions. Why provide an alternate? Two reasons: speed, of course,<br>but also compatibility.
The need for speed #
Ultimately, the Postgres and RE2 regular expression engines rest on<br>fundamentally different search algorithms. Postgres POSIX regular<br>expressions depend on backtracking, returning the longest match, while RE2<br>relies on finite automata. In his article announcing RE2 back in 2010,<br>Russ Cox summarized:
RE2 demonstrates that it is possible to use automata theory to implement<br>almost all the features of a modern backtracking regular expression library<br>like PCRE. Because it is rooted in the theoretical foundation of automata,<br>RE2 provides stronger guarantees on execution time than and enables<br>high-level analyses that would be difficult or impossible with ad hoc<br>implementations. Finally, RE2 is open source. Have fun!
Such findings underpin the choice to power ClickHouse string search, string<br>replacement, string extraction, and string splitting with RE2. We wanted<br>to provide similar benefit to our Postgres users.
Benchmark #
Naturally, the pg_re2 project includes a benchmark that compares<br>the raw performance of its RE2-backed regex functions to the Postgres native<br>functions. This graph summarizes the horizontal speedup factors on an AWS<br>m6g.2xlarge instance with PostgreSQL 18:
Findings:
RE2 wins every test , with performance 1.8x to 8.6x that of Postgres.
The largest gains come from extract_all (7.2-8.6x) which returns<br>arrays directly, in contrast to the admittedly unfair comparison to the<br>Postgres regexp_matches(..., 'g') function's set-returning overhead.
Indexing #
pg_re2 v0.4.0 adds a matching operator, @~, to complement the Postgres ~<br>operator, along with btree and GIN index support to reduce the need for<br>table scans when evaluating RE2 regular expressions. A second benchmark<br>compares RE2 to Postgres regular expression index performance on the same<br>server:
Findings:
RE2 again wins every test , with 1.1x to 1.8x acceleration over the<br>equivalent Postgres operations.
Prefix matching against a btree index provides similar overall speedup<br>to a table scan because each applies raw execution speed to index entries.
The gin_re2_ops GIN opclass benefits from collecting the byte-equivalent<br>trigrams that RE2 requires, including across punctuation, such that<br>patterns like error_code=42[0-9] use the index where Postgres regular<br>expressions against the pg_trgm opclass fall back to a full table scan.
On plain-word patterns the RE2 gin_re2_ops and Postgres pg_trgm<br>opclasses extract similar keys and pg_trgm's cheaper consistent check<br>can win (see error_code=123 above).
Overall, the performance improvements identified by Russ Cox hold true for<br>regular expressions in Postgres, too.
We are so compatible #
The second driver for creating pg_re2 was compatibility with ClickHouse<br>regular expression functions. Turns out that RE2 differs from Postgres<br>POSIX expressions not only in speed but also syntax. These variations<br>matter because pg_clickhouse v0.2.0 added pushdown for several PostgreSQL<br>operators and functions. It works well for simple regexen:
1SELECT id, by FROM log.entries WHERE by ~ 'Click' ORDER BY id;Copy command
1id | by<br>2----+------------<br>3 1 | ClickHouse<br>4 5 | MouseClick<br>5 (2 rows)Copy command
If you'd like to follow along, the following examples use this ClickHouse<br>table:
1CREATE TABLE entries (<br>2 id UInt32 NOT NULL,<br>3 by String NOT NULL<br>4) ENGINE = MergeTree ORDER BY (id);<br>6INSERT INTO entries VALUES<br>7 ( 1, 'ClickHouse' ),<br>8 ( 2, 'HouseQuake' ),<br>9 ( 3, 'HouseQuake' ),<br>10 ( 4,...