# 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: ```sql 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): ```sql 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.