DATABASES Signal 154
pg_tre and pg_re2 bring new regex index options to PostgreSQL but pg_trgm still wins for simple patterns
↗
Illustration only Photo by Brecht Corbeel on Unsplash
A PostgreSQL engineer benchmarked two newer regex extensions, pg_tre and pg_re2, against the established pg_trgm, finding that pg_tre's index is dramatically larger and slower to build while offering no advantage for simple substring searches.
If you rely on regex searches over large text columns in PostgreSQL, pg_trgm remains the practical default for simple patterns. pg_tre may offer value for more complex regex matching, but it comes with a 21 GB index on a 33 GB table and a 7-hour build time, so adoption requires careful evaluation of whether your queries actually need what it provides.
Written by elseif from the cluster below · every claim links back to a sourceThe three things worth knowing
pg_tre built a 21 GB index on 1.6 million rows (~33 GB of text) in over 7 hours, compared to pg_trgm's 1667 MB index built in roughly 17 minutes.
For a simple substring search, pg_trgm returned results in 1.6 seconds while pg_tre took 2.3 seconds, and pg_tre's own documentation recommends pg_trgm for exact substring and LIKE queries.
The article was cut off before testing more complex regex patterns where pg_tre might justify its cost, and pg_re2 was not benchmarked in the available material.
THE CLUSTER