Дата публикации: 22-05-2026 00:00:00

I am pleased to announce the first public release of [pg_tre] (https://codeberg.org/gregburd/pg_tre), a native PostgreSQL 18+ index access method for approximate-regex matching.
pg_tre indexes text columns through a three-tier filter funnel (BRIN-style range bloom -> sparsemap trigram postings -> per-tuple bloom) backed by Ville Laurikari's TRE library for the heap recheck. The result is genuine Levenshtein-distance regex matching ("find text within k edits of this pattern") driven through a real IndexAmRoutine, with WAL coverage, VACUUM awareness, and REINDEX CONCURRENTLY support.
Highlights
Custom IndexAmRoutine registered as USING tre, with custom
rmgr (id 140) and full crash-recovery / streaming-replication
coverage validated by TAP tests.
Edit-distance regex with per-subexpression budgets, e.g.
body %~~ tre_pattern('(error){~1}.*(42[0-9]){~0}', 1)
runs as a single indexed bitmap heap scan.
UTF-8 codepoint trigrams: CJK, accented characters, and
emoji are indexed correctly; ASCII pays zero overhead.
DoS protection via configurable caps on NFA states, compile
time, and per-match runtime.
Three-tier funnel cuts heap I/O dramatically: queries that
return a handful of rows out of millions typically finish in
sub-millisecond time.
Backward-compatible tre_amatch* UDFs from 0.1.0 are
preserved.
How pg_tre fits alongside what you already have
PostgreSQL already ships strong text-search primitives. pg_tre is meant to complement them, not replace them.
pg_trgm (GIN/GiST): exact regex, LIKE, and trigram-set
similarity. Battle-tested. pg_trgm % is Jaccard similarity
over trigram sets, which is not the same as edit distance:
two strings with overlapping trigrams can score "similar"
even when their Levenshtein distance is huge. Use pg_trgm
when you need exact-substring or LIKE acceleration.
tsvector / tsquery (built-in FTS): word-level linguistic
search with stemming, stopwords, ranking, and language
configuration. Use FTS for natural-language prose. pg_tre
is language-agnostic, which is a feature for identifiers,
SKUs, error codes, and log lines, and a non-feature for
"running" matching "run".
pgvector / pgvectorscale: semantic similarity over float
embeddings. Orthogonal to pg_tre -- meaning vs. lexical
structure. The two compose naturally as a hybrid filter
(lexical pre-filter with pg_tre, semantic rank with
pgvector).
pg_tre: approximate regex with explicit edit budgets and
full regex semantics (character classes, alternation,
anchors, {m,n} repetition) composable with the {~k} edit
operator. No other in-tree or out-of-tree PostgreSQL
extension answers "is this text within N edits of this
regex?" through an index.
A few things only pg_tre does well
Typo-tolerant log and trace search:
body %~~ tre_pattern('(timeout){~1}.*(connection){~1}', 1)
finds "timeoutt" and "conection" in the same query.
Catalog and SKU lookup with edit tolerance:
sku %~~ tre_pattern('AB-9?[0-9]{4}', 1)
catches dropped digits or transposed characters without
expensive post-filtering.
Per-phrase edit budgets in a single index query:
body %~~ tre_pattern('(postgres){~2}.*(system){~0}', 0)
expresses "postgres-ish" with strict "system" -- one round
trip, no application-level reranking.
Hybrid retrieval for agent / RAG pipelines: pg_tre is the
fuzzy-lexical leg that pg_trgm, FTS, and pgvector cannot
cover on their own. An LLM-generated identifier with a
typo, an OCR error in a scanned document, a near-duplicate
error code -- pg_tre catches them all without sacrificing
regex expressivity.
Installation
-- Requires shared_preload_libraries = 'pg_tre'
CREATE EXTENSION pg_tre;
CREATE INDEX docs_body_tre ON docs USING tre (body);
SELECT id FROM docs
WHERE body %~~ tre_pattern('database', 1);
A note on stability and feedback
pg_tre is new software. The on-disk format is stable from 1.0.0 forward (1.1.x is byte-compatible with 1.0.0), the SQL surface is fixed, the WAL records are versioned, and the test suite covers the storage, query, recovery, and replication paths -- but the project is young and almost certainly has bugs that the existing tests do not yet provoke. It is not a beta and not a research toy; it is real software released early, and it should be used with appropriate caution.
Bug reports, feature requests, and pull requests are very welcome. If you can attach a reproduction case (a minimal schema and a query that misbehaves), that is the most useful form, but anything is better than nothing.
Links
Repository: https://codeberg.org/gregburd/pg_tre
Issues: https://codeberg.org/gregburd/pg_tre/issues
TRE library: https://github.com/laurikari/tre

Основное содержимое страницы с новостью.

-- Requires shared_preload_libraries = 'pg_tre'
CREATE EXTENSION pg_tre;

CREATE INDEX docs_body_tre ON docs USING tre (body);

SELECT id FROM docs
 WHERE body %~~ tre_pattern('database', 1);

Схожие новости