pg_tre — Known Limitations and Production Sizing

This file captures honest, evidence-based limits of pg_tre as deployed. It exists because the previous documentation set implied production-readiness at scales where the build path is not viable, and an operator hit it the hard way (see “Field report” below).

TL;DR

As of v1.8.0 the build is no longer in-memory — it sorts trigram tuples with PostgreSQL’s tuplesort, so peak build memory is bounded by maintenance_work_mem (disk-spilled) instead of by corpus size. The OOM behavior described in older revisions of this file is fixed. The table below now reflects 1.8.0; for 1.6.x/1.7.x behavior see the git history.

dataset shape safe? notes
≤ 100k rows of long text (e.g., email bodies, ~1 KB) ✅ builds memory-safely; but check temp disk first — ~64 B per trigram occurrence (run tre_estimate_index_build)
≤ 500k rows of short text (~50 chars) ✅ modest temp footprint; fine on a normal host
100k–500k rows of long text ⚠️ builds memory-safely, but build temp disk can reach tens of GB on long text — size it with tre_estimate_index_build before starting
≥ 500k rows of long text (multi-GB column) ❌ (today) memory-safe and cancellable, but build-time temp-disk is the wall: a production user measured ~21 GB temp at 2.1 % of a body corpus — hundreds of GB extrapolated. Pair with pg_trgm until 2.0’s de-dup lands
≥ 1M rows of any natural text ❌ (today) same temp-disk wall; also query-time selectivity / index size questions

The gating factor at large scale is build-time temp disk, not memory (memory has been bounded by maintenance_work_mem since 1.8.0). A production user reported the index AM unusable on a multi-GB text column at every shipped version for the temp-disk reason; see the sizing section and field report below. Pairing pg_tre with pg_trgm (GIN) for substring/LIKE and tsvector BM25 for ranked full-text, reserving pg_tre for true edit-distance/regex fuzzy matching, remains the recommended pattern for the largest text-search workloads (see README.md#deployment-recommendations).

Sizing the build (memory model)

As of v1.8.0 ambuild sorts trigram tuples via tuplesort, so peak build memory is bounded by maintenance_work_mem plus a fixed per-backend overhead — it does not grow with the number of trigram emissions. Measured on a body corpus under maintenance_work_mem = 32MB:

corpus emissions peak build-backend RSS
100k rows, ~395-char bodies ~40 M 219 MB
200k rows, ~395-char bodies ~80 M 231 MB

Peak RSS is essentially flat across a 2x corpus (the ~12 MB delta is the tid-bloom hash, which is O(distinct TIDs)); the former in-memory build would have grown from ~960 MB to ~1.9 GB of resident entries[] alone. Raise maintenance_work_mem to trade RAM for fewer merge passes and faster builds.

What does still scale with corpus size:

temp_disk    ≈ emitted_trigrams × ~32 B    (tuplesort spill, measured 4.3.1)
index_size   ≈ emitted_trigrams × ~9-16 B  (sparsemap TID lists; see "size at scale")
tid_bloom    ≈ N_rows × ~56 B              (resident; O(distinct TIDs))

The temp-disk figure is the one that bites. Each distinct trigram of each row becomes one tuplesort entry (since 2.0, repeats within a row are emitted once): a 24-byte key in a by-reference datum, about 32 bytes per entry on tape (4.3.1, measured on 165 M entries). Natural text has roughly one distinct trigram per character for short rows and fewer for long ones, so:

temp_disk ≈ (bytes_of_text) × up to ~32 B/char

tre_estimate_index_build and pg_tre.build_max_entries_mb still charge ~64 B per entry, so both are about 2x conservative.

Field data point (production, v1.8.2): a user indexing message bodies measured ~21 GB of build temp at 2.1 % of their body corpus — consistent with ~64 B per emission over hundreds of MB of text. Extrapolated, a multi-GB text column needs hundreds of GB of build-time temp disk. If you do not have that, the build will not complete; size it up front.

Size it before you start. As of 2.0 call:

SELECT * FROM tre_estimate_index_build('your_table'::regclass, attno);
--  sample_rows | est_rows | est_trigrams | est_temp_mb | est_index_mb

It samples up to 2000 rows of the column and extrapolates the emission count, build temp-disk, and final index size. Set pg_tre.build_max_entries_mb from est_temp_mb (the GUC uses the same ~64 B/tuple cost), or decide the column is too large and pair with pg_trgm instead.

The pg_tre.build_max_entries_mb GUC (default 0 = unlimited since 1.8.0) caps build temp-disk: when emitted-tuple count × ~64 B would exceed it, the build fails cleanly with ERRCODE_PROGRAM_LIMIT_EXCEEDED (cancellable, never OOM) rather than filling the temp tablespace. As of 2.0 the ceiling is measured against the real ~64 B/tuple cost, not the bare 24 B key, so it tracks actual temp consumption.

Roadmap (2.0): per-row de-duplication of repeated trigrams collapses the dominant cost — repetitive text (a body that mentions the same words many times) emits one tuple per distinct trigram per row instead of one per occurrence, cutting temp-disk several-fold on natural text. Tracked for the 2.0 line.

Cancellability

As of v1.7.0 the build is cancellable throughout, and v1.8.0’s tuplesort_performsort is itself interruptible; the per-TID sm_add_grow loops and multi-leaf write paths carry CHECK_FOR_INTERRUPTS. A pg_cancel_backend() / SIGINT during a build is honored within a second or so on any phase. (Before 1.7.0 the pg_qsort over the in-memory entries array — 5–60s on a large corpus — and the posting-build loops were uncancellable.)

Avoid the heavy-lock build entirely: plain REINDEX takes AccessExclusiveLock on the index and blocks the table’s writes for the whole build (this is what stalled ingest for 6 minutes in the field report below). Use the CONCURRENTLY variants instead — they are supported and verified (CIC/RIC are generic in PostgreSQL; pg_tre’s build works correctly under the two-phase MVCC-snapshot protocol, confirmed by a CIC-result == seq-scan-ground-truth cross-check):

CREATE INDEX CONCURRENTLY my_tre ON t USING tre (body);
REINDEX INDEX CONCURRENTLY my_tre;

CONCURRENTLY builds are slower in wall-clock but do not block concurrent reads/writes on the table, and (since 1.8.0) are memory-bounded by maintenance_work_mem like any other build.

Format-version upgrades

A pg_tre format-version bump (e.g., 1.5.x → 1.6.0’s v5 → v6) requires REINDEX, but the REINDEX may itself be unviable per the “Sizing the build” section above. In that case the safe operator path is:

  1. DROP INDEX … RESTRICT; the pg_tre index
  2. ALTER EXTENSION pg_tre UPDATE TO 'X.Y.Z';
  3. Decide whether the column actually warrants pg_tre at all (see the table at the top), or whether pg_trgm GIN + tsvector BM25 covers the workload.
  4. If still wanted, recreate the index during a maintenance window with maintenance_work_mem set generously and the build memory budget calculated from the table above.

The reader (since v1.6.0) rejects pre-format-v6 pages with ERRCODE_FEATURE_NOT_SUPPORTED and a REINDEX hint, so an un-rebuilt index fails loudly on first scan rather than returning wrong rows — there is no silent-corruption window.

Field report — 2026-06 (Agora deployment)

A production user (501 k rows, email-archive schema) hit two distinct failures while attempting the 1.6.0 REINDEX:

  1. idx_ag_msg_from_tre (501 k rows): the reindex hung 6 min holding a lock that stalled mail ingestion. I cancelled it. It was invalid + idx_scan=0 (unused) + from_addr is pg_trgm by design — so I dropped it.
  2. Building the body tre index OOM-killed the entire postgres cgroup (auto-restarted, replication survived). v1.6.0 fixes the data-loss overflow (32→64-bit offset) — but that only removes the fast error, so the build now grinds until it exhausts memory and crashes the server.

Their conclusion (“subject/from_addr use pg_trgm GIN, body uses BM25; pg_tre indexes are not viable at this scale”) is correct for v1.6.x. Their schema design note had already documented this pattern internally; the gap was that pg_tre’s own README/ CHANGELOG implied the indexes were production-ready. This file exists to close that gap.

The two failure modes are addressed as follows: the lock stall is avoidable today with REINDEX INDEX CONCURRENTLY / CREATE INDEX CONCURRENTLY (supported and verified), and v1.7.0 adds full build cancellability plus the pg_tre.build_max_entries_mb ceiling so the OOM becomes a clean error. v1.8.0’s tuplesort-based build is the structural fix that lets large body corpora build within maintenance_work_mem. Until 1.8.0 ships and qualifies on a real >500k-row body corpus, plan as if pg_tre is small/medium-corpus territory for long-text columns, per the table at the top.

At-scale stress qualification (3.2.0, 2026-08)

A full adverse-conditions suite (bench/stress/, see bench/stress/RESULTS-stress-3.2.0.md) was run on an AWS i4i.8xlarge (32 vCPU, 256 GB, NVMe RAID-0) against 250k–10M-row corpora. Every correctness oracle (index result set == sequential-scan result set) passed with zero mismatches, crash recovery after SIGKILL-mid-build was clean (heap intact, no corruption, no orphaned valid index), the DoS guards bound pathological patterns, and parallel builds showed no deadlocks. Two non-correctness findings that affect sizing and maintenance:

Build throughput and size at scale

Measured in 4.3.1 on PostgreSQL 18.6, i4i.8xlarge (32 vCPU, NVMe):

corpus settings build index pg_trgm GIN
2 GB prose, 702 k rows 1 GB maintenance_work_mem, 5 participants 154 s 7.3 GB 97 s, 1.0 GB
2.7 GB synthetic EXPLAIN plans, 160 k rows 1 GB, 4 participants 67 s 1.35 GB 66 s, 0.36 GB
30 GB prose, 10.5 M rows 64 MB, 2 participants 1 h 33 min 109 GB 52 min, 14 GB
30 GB prose, 10.5 M rows 4 GB, 8 participants 30 min 109 GB

The index sizes above are 4.3.1’s (legacy TID packing). 4.4.0 builds 1.5-1.8x smaller indexes in the same time; see the size item below. The 30 GB corpus has not been re-measured.

  • Memory is bounded (a few hundred MB, with maintenance_work_mem on top) since 4.3.1. 4.3.0 and earlier leaked every compressed row’s text for the whole scan.
  • Temp disk is the big cost: 32 bytes per emitted trigram (measured), and a row emits one trigram per distinct trigram it contains. The 30 GB corpus needed 366 GB of pgsql_tmp. Check with tre_estimate_index_build first: its trigram count is close (818 M estimated vs 826 M on the 2 GB corpus), and its est_temp_mb assumes 64 bytes per trigram, so it is about 2x high.
  • Give the build memory and workers. Every phase (scan, sort and, since 4.3.1, the posting trees) runs on all participants. The planner assigns at most maintenance_work_mem / 32 MB - 1 workers, so 64 MB allows a single worker. 1–4 GB with max_parallel_maintenance_workers 4–8 is a good start.
  • The index is several times larger than pg_trgm’s on long-document columns: each heap block a posting touches costs at least part of a sparsemap chunk, and long documents leave few rows per block. 4.4.0’s dense TID packing (format v11) shrinks a new or REINDEXed index 1.5-1.8x (14.2 GB -> 8.0 GB on 4.1 GB of prose, where pg_trgm’s GIN is 2.0 GB; doc/design/dense-tid-packing.md). An index built before 4.4.0 keeps the old packing until REINDEX: check with pg_tre_index_tid_packing().

Posting-leaf bloat under sustained churn — REINDEX to reclaim

Under a delete+reinsert churn workload with the row count held flat, the index grew ~3.9× (276 MB → 1070 MB over 6 rounds) and VACUUM did not shrink it; a REINDEX returned it to baseline (278 MB). The growth is in posting_leaf pages held at ~88 % fill: VACUUM removes dead TIDs and recycles fully emptied leaves (nbtree-style deferred reclaim) but does not merge/compact partially-empty leaves, so a churn pattern that never fully empties a leaf accumulates allocated pages. This mirrors nbtree (which also needs REINDEX to reclaim half-empty pages) and never affects correctness. Churn-heavy deployments should schedule periodic REINDEX INDEX CONCURRENTLY.

Old snapshots pin every merge’s old copy

Each VACUUM merge rewrites the posting tier; the old copy is freed only once no snapshot could still be reading it (nbtree’s rule for deleted pages). A long transaction, an idle-in-transaction session or a forgotten prepared transaction therefore pins one full copy per merge, and the index grows without bound until it ends. Measured (4.2.1): six ingest+VACUUM rounds of source files under one open REPEATABLE READ snapshot took the index from 140 MB to 1.6 GB against 75 MB of heap; every page came back once the snapshot ended. Since 4.2.1 VACUUM raises a WARNING when pages held this way pass half the index (and 128 MB). End the session, VACUUM again; to return the space to the OS, REINDEX INDEX CONCURRENTLY.

Upgrading does not repair an index damaged by an earlier version

“No REINDEX required” means the on-disk format is unchanged — not that an index already damaged by a defect is repaired by installing the fix. Up to 4.2.0: a stale FSM after a crash could wedge INSERT on LWLock:BufferContent (repeatedly, across restarts), and dead TIDs from the pending list were never removed (scans could fail with could not read blocks N..N ... read only 0 of 8192 bytes, and the index never shrank). 4.2.1 fixes all three going forward, and an existing index stops wedging once 4.2.1 is loaded. Dead TIDs already merged into an index by an earlier version stay there: REINDEX any tre index that has shown a read error or grew far larger than its heap.

Upgrading to 4.3.0 on a non-UTF-8 database: REINDEX

Before 4.3.0 the trigram tokenizer decoded every database as UTF-8. On a non-UTF-8 database (SQL_ASCII, LATIN1, WIN1252, EUC_*, …) a row whose bytes did not form valid UTF-8 failed to insert. A row whose non-ASCII bytes happened to form valid UTF-8 (for example é in LATIN1, or raw UTF-8 stored in SQL_ASCII) was indexed as Unicode code points. 4.3.0 decodes text in the database encoding, so those characters produce different trigrams, and exact (k = 0) searches through an index built by an earlier version miss such rows. After upgrading, REINDEX every tre index on a non-UTF-8 database that holds non-ASCII text. UTF-8 databases and pure-ASCII indexes need nothing.

The pending list is merged by VACUUM, or by INSERT past a limit

Every INSERT appends to the index’s pending list, and every scan reads the whole list, so on an insert-heavy table that autovacuum rarely visits, searches get slower as the list grows. By default only VACUUM (or a REINDEX) merges it into the main tree. At 1.67 M rows the default autovacuum_vacuum_insert_scale_factor (0.2) waited for about 334 k inserts.

Since 4.5.0, pending_list_limit (storage parameter, KiB; or the pg_tre.pending_list_limit GUC for indexes that do not set it) caps the list: the INSERT that pushes it past the limit merges it, unless a merge is already running. That INSERT pays for the merge, which rewrites the upper tree and every posting the list touches, like VACUUM’s, so a merge on a large table with common trigrams takes seconds. The limit is a trigger, not a hard cap: INSERTs that arrive while a merge runs, or while VACUUM runs on the table (it holds the same lock), append without merging, so a busy table can pass the limit until then. The default is 0, no limit, which keeps 4.4’s behaviour. The other options: set a low per-table autovacuum_vacuum_insert_threshold, or schedule VACUUM.

There is no way to bypass the list: fastupdate = off is refused (4.4 and earlier accepted it and did nothing).

In 4.2.0–4.2.2 a long pending list also widened the window for a backend crash. Any ERROR raised during a scan that had built the pending-list overlay could abort with free(): invalid pointer in the clang-built (nix build) extension; statement_timeout and query cancel are common triggers. 4.2.3 fixes the crash, but the scan cost remains.

Character classes follow the collation, as for core ~

Since 4.3.0 pg_tre decodes text in the database encoding (any server encoding, not just UTF-8), and regex character classes ([[:alpha:]], \w) and case folding ((?i)) follow the collation of the call exactly as PostgreSQL’s own ~ / ~* do: the input collation of tre_amatch*, tre_amatch_detail, tre_similarity, tre_distance, %~~ and <@> (the column’s, or an explicit COLLATE), and for an index scan the index column’s collation. COLLATE "C" classifies ASCII only; an ICU, builtin or libc collation classifies non-ASCII. Character boundaries and edit counting depend only on the encoding. As for ~, an indeterminate collation (two differently collated arguments) and a nondeterministic one are errors.

Nondeterministic collations are refused at CREATE INDEX, REINDEX and any ALTER that rebuilds the index: equal strings under them need not share trigrams.

A known difference from core ~ / ~* in character classes (PostgreSQL 17 and 18)

TRE’s classes ([[:alpha:]], \w, \s, \d, …) and case folding ((?i)) mirror PostgreSQL’s regex engine (regc_pg_locale.c): test/sql/tre_ctype_strategies.sql checks every class and every case pair against ~ / ~*. On PostgreSQL 19 they call the same per-collation ctype functions core’s regex calls (pg_iswalpha() and friends, new in 19), so they agree with core for every provider, ICU included. On PostgreSQL 17 and 18 they mirror the C, builtin and libc strategies, and one difference remains:

  • ICU class membership follows PostgreSQL’s Unicode tables, not libicu (17 and 18 only). Under an ICU collation core calls ICU’s u_isalpha() and friends; pg_tre uses PostgreSQL’s own Unicode tables (pg_u_isalpha() and friends, with the POSIX-compatible definitions that builtin C.UTF-8 uses), because pg_tre.so deliberately does not link ICU (it stays loadable into any PostgreSQL build of the same major) and PostgreSQL 17 and 18 expose no ctype entry point an extension could call instead. The two agree on ordinary letters, digits, spaces and punctuation; they differ on, for example, Arabic-Indic digits (\d), letter-like numbers and symbols such as Ⅻ and Ⓐ ([[:alpha:]]), the soft hyphen ([[:graph:]]), and symbols such as $ + ~ © € which ICU does not count as [[:punct:]]. Case folding is the same in both (the Unicode simple case mappings).

(?i) takes a character’s other cases from toupper()/tolower(), as core does, so titlecase letters (Dž), letter numbers (Ⅻ) and circled letters (Ⓐ) fold the same way under ~* and pg_tre on every provider (4.3.x differed on titlecase letters, and on FreeBSD’s libc also on Ⅻ and Ⓐ). What a character folds to is the ctype’s: FreeBSD’s libc maps Dž to itself where glibc maps it to dž, and pg_tre follows whichever the database uses.