# pg_fts — a fresh-eyes review **Date:** 2026-09-17, at v1.8.1. Written against the repository as a stranger would find it, with every claim checked against the tree or the measured data, not against my memory of having worked on it. Where I was the one who introduced a problem, it says so. --- ## The short version pg_fts is a **serious, correct, unusually well-measured BM25 index with a genuinely broad query language**, whose engineering discipline around correctness is better than most of its competitors' — and whose repository presentation actively undersells that, because the signal is buried under a benchmark journal that has grown larger than the source, 31 dead SQL files in the root, and a README paragraph that contradicts the project's own numbers. It should be a strong default choice for **self-hosted PostgreSQL users who need exact `count(*)`, phrase/NEAR/regex/fuzzy in one operator, MVCC-correct results, and an index that VACUUMs itself**. It should **not** be the default for **common-term ranked top-k at low latency**, where it trails ParadeDB by ~17× single-client and ~20× under load, and that gap is architectural rather than a tuning matter. --- ## Where it excels **Correctness discipline that competitors do not have.** This is the project's actual differentiator, and it is not marketing: - Ranked results are **exact** top-k, not approximate; the phrase operator returns `false` on a positionless document (matching PostgreSQL's `OP_PHRASE` semantics) rather than silently degrading to a conjunction — a bug we found in ourselves and fixed in 1.6.0. - Every benchmark row is backed by a **parity check against regex ground truth** (`bench/parity_check.sh`), and pg_search's headline speed was shown to be partly a smaller unit of work — Tantivy does not stem (495,580 vs the correct 734,896 for `year`). - `count(*)` is **index-native and MVCC-correct**, with a `df` fast path that now has ten gate-refusal tests each compared against a heap-only ground truth. pg_textsearch and VectorChord-bm25 cannot answer the query at all; pg_search does it 5.7× slower. - Crash recovery, replication, corruption tolerance and multi-encoding folding are TAP tested; corrupt pages degrade to "nothing to read" rather than to a wrong answer. - The **ctid-derived docid** design (see below) means merges never renumber — the same insight PlanetScale's closed TIN presents as its central architectural advantage. **Query-language breadth in one operator.** Boolean, phrase, NEAR with distance, prefix, fuzzy (Levenshtein DFA), regex, field zones, all through `@@@`. The comparison matrix marks this honestly: none of the three specialist BM25 extensions offer the full set. **Size and self-maintenance.** Smallest index of the five engines measured (1,421 MB vs 1,887–2,902 MB), and — after this month's work — an index that stays bounded and reclaims under unattended autovacuum at 1M docs, with no scheduled `fts_vacuum` required. **Measurement honesty.** `BENCHMARK_SUMMARY.md` has a section listing optimisations that were **rejected by measurement**, and a record of published numbers that were **wrong and corrected**. I know of no competitor that publishes its own retractions. This is the thing most worth protecting. ## Where it fails **Common-term ranked latency, and the architecture behind it.** `year` (df 734,896) top-10: pg_fts 36.16 ms, pg_textsearch 20.71, pg_search 2.12, vchord 3.49. That is the honest headline weakness, it is 17× single-client and **worse** (20.7×) under load, and the profile is unambiguous: 45% doclen path, 37% candidate iteration — per-posting scalar work. The posting format is FOR delta-packed + WAND with no vectorization; the engines that beat us use bitmaps and SIMD. Closing this is a posting-format redesign (ROADMAP item D), not a tuning pass, and the project has correctly declined to ship it without sign-off. **Bulk-ingest write amplification (open known issue).** At the field's shape (1,660 terms/doc), every document exceeds one pending page and mints a one-document segment; even after the 1.7.2 mitigation the index grows **~3.7 GB per 5,000 documents** until an `fts_vacuum` collapses it 210×. The root cause is measured (freed pages cannot be reused in the inserting transaction because the recyclability XID gate correctly rejects them) and the fix is a design change (merge outside the inserting transaction). Users doing bulk loads must know this; the docs now say so. **Build time.** 381 s vs pg_search's 127 s and vchord's 56 s on the same corpus. Not a blocker, but not a strength. **Release cadence as a signal.** 56 tags in 73 days. 23 commits mention a regression, revert, retraction or "wrong". 46 CHANGELOG lines mention a crash, SIGSEGV, P0, or an index that could never be vacuumed. Some of that is admirable transparency about a hard problem; some of it is a project that shipped too many plausible-but-unproven fixes and had to walk them back. **Both readings are true**, and a prospective user will see the second one first. ## Compared with the alternatives | | pg_fts | pg_search (ParadeDB) | pg_textsearch | vchord-bm25 | GIN/tsvector | TIN | |---|---|---|---|---|---|---| | ranked common-term | weak (36 ms) | **best (2 ms)** | mid (21 ms) | good (3.5 ms) | very slow | claims fast; unmeasurable | | exact `count(*)` | **best** | 6× slower | cannot | cannot | via heap | claims fast | | phrase / NEAR / regex / fuzzy | **all, one operator** | most | none | none | phrase only | broad (TINQL) | | index size | **smallest** | largest | mid | largest | smallest | ? | | correctness (exact top-k, stems, MVCC) | **strongest, tested** | does not stem | ok | ok | exact | claims | | self-maintaining under churn | yes (measured) | reads degrade under writes (per TIN) | writes stall (per TIN) | ? | yes | claims | | obtainable | source | source | source | source | built-in | **managed-only** | Two rows are borrowed from PlanetScale's article and are marked as such; we could not measure them. TIN has no column of real numbers because it cannot be installed anywhere but their platform, and this project's rule — competitor rows reflect what we ran — is the right one to keep. **Why pick it:** you self-host (or run on RDS/Aurora/anything not PlanetScale), you need exact counts and rich query syntax, you value verified correctness over headline latency, and your ranked queries are rare-to-mid-frequency or you can tolerate ~35 ms on common terms. **Why not:** your workload is dominated by low-latency common-term ranked top-k, or you bulk-load very long documents and cannot schedule an `fts_vacuum` afterwards. ## How well is it coded? **Mostly well, with three structural smells.** The good: consistent PostgreSQL style, `-Wdeclaration-after-statement` clean, every allocation that scales with corpus size routed through huge-safe macros and checked by a CI script (`ci/check-alloc.sh`), page reads validated through one shared helper after the 1.7.1 audit, standalone property tests (Hegel) and fuzzers for the codec and the page walkers — the fuzzer caught undefined behaviour in a fix of mine before it shipped. Comment density is 23–28% in the big files and, checked for it, **essentially none of it is agent narration**: the comments explain invariants and record the measurement that justified a decision. That is the right kind of comment. The smells: 1. **`pg_fts_am.c` is 6,718 lines and `#include`s two other `.c` files** (`pg_fts_am_scan.c` at 4,922 lines and `pg_fts_trgm_index.c`) into a single translation unit. There is a reason (shared statics) but it is the reason a 12,000-line TU exists, and it makes the syntax-check invocation in the build notes a trap for newcomers. 2. **Mutable file-scope state in the allocator** (`bm25_lowfree_*`, `bm25_alloc_extend_only`) owned by an implicit begin/end protocol. This is exactly what bit me in the 1.7.1 work: reading those globals without owning them handed out garbage block numbers, and only `t/007` caught it. State that a test has to protect from its own maintainers should be passed explicitly. 3. **`bm25_collect_matches` is 412 lines.** It is the function every scan goes through. One design constraint worth naming: **all WAL is `GenericXLog`** (16 sites, no custom rmgr). That is the pragmatic choice for an extension — no redo code to get wrong — but it means every page modification logs a full-page delta, which is a cost floor on write-heavy paths. It is the right trade for a project of this size and was made deliberately. ## Is the archive polluted? **Yes, in three specific ways, and one of them is mine.** 1. **31 dead base SQL scripts (512 KB) in the repository root.** Only `pg_fts--1.8.1.sql` is installed; the other 31 (`pg_fts--1.0.6.sql` … `pg_fts--1.8.0.sql`) exist because each release renamed the base script and left the old one tracked. They are never read by any install path. The 54 *upgrade* edges are legitimate; the 31 base snapshots are cruft that makes `ls` unreadable and the root 132 files deep. 2. **`bench/` is 1.2 MB tracked — larger than all C source (1.1 MB) — and contains 46 data directories and 78 markdown files.** 36 `RESULTS_*`, 24 `NOTE_*`, 7 `PLAN_*`, plus `P0_`, `P1_`, `DIAG_`, `REVIEW_`. This is a lab notebook, and much of it is genuinely valuable (the retractions, the rejected-optimisation record), but it is checked in beside the code with no index and no distinction between "current truth" and "a Tuesday". The eight `bench/` references *from inside the C source* couple the code to the journal. A stranger cannot tell `RESULTS_5WAY_159b` from `RESULTS_5WAY_159` without reading both. 3. **Agent tooling at the root** — `AGENTS.md`, `.agent/`, `.claude/`, `.kiro/`, `.mcp.json`, `.agent-steering-domains.md`. These are gitignored (good) but sit in the working tree and `AGENTS.md` is a two-line pointer to a gitignored file. Harmless to git; noisy to anyone who clones and looks. Also: `bench_vac/` is an untracked scratch directory in the root, and `CAPABILITIES.md`, `DEFERRED.md`, `HANDOFF.md`, `RELEASING.md`, `ROADMAP.md` are five overlapping project-state documents where one would do. ## Does it exude engineering excellence, or is it a rat's nest? **The engineering is excellent. The presentation is a rat's nest.** These are separable, and the second is much cheaper to fix than the first would have been. What inspires: the parity checks, the published retractions, the property tests, the fact that a P0 was isolated with `gdb` and fixed at the root in both places it existed, the honest "this test does NOT reproduce the P0" header in `t/010`, the refusal to benchmark against numbers that cannot be reproduced. A reviewer who reads `BENCHMARK_SUMMARY.md` will trust this project more than any competitor's. What does not: the reviewer has to *find* `BENCHMARK_SUMMARY.md` among 78 siblings first. The README's comparison section is **stale and self-contradicting**: it says pg_fts "leads on rare/mid-term ranked latency" against the specialists — the project's own 1.6.0 table shows pg_search at 2.13 ms vs our 5.89 on rare — and it describes the doclen sidecar as a future ROADMAP item when it shipped in 1.5.0. A stranger who checks that paragraph against the table two links away will conclude the docs cannot be trusted, which is the opposite of the truth about this project. ## Are the docs complete, accurate, and helpful? **Complete: yes, unusually.** SGML manual rendered to HTML on both Pages hosts, a migration guide from pg_textsearch, a comparison matrix that marks untested capabilities as untested, a testing guide, a release procedure. **Accurate: mostly, with the README defect above being the serious exception.** The SGML and the CHANGELOG are accurate to the code as far as I checked (the count fast-path gates, the separator semantics, the vacuum behaviour). The README paragraph at lines ~236–258 is not, and because it is the first thing read, it costs the most. **Helpful: the reference material is; the orientation is not.** Nothing tells a new reader which of the 78 bench documents are current. `HANDOFF.md`, `DEFERRED.md`, `ROADMAP.md`, `CAPABILITIES.md` overlap. The strongest single document in the repository — `BENCHMARK_SUMMARY.md` with its rejected-optimisations and corrections sections — is linked once from the README and otherwise invisible. ## Are the major design choices good? | choice | verdict | |---|---| | **ctid-derived docid** (`block × MaxHeapTuplesPerPage + offset`) | **Good.** No renumbering on merge; globally stable identifiers. Independently the same conclusion TIN reached. Its cost — a sparse docid space — is what made the 1.6.1 tombstone bug subtle, and the code now sizes by `sm_maximum` accordingly. | | **Segments + leveled merge, `BM25_MAX_SEGMENTS = 128` in the metapage** | **Sound but tight.** The 128 cap is a metapage-size artefact, and a field deployment hit it in an hour before the eager merge existed. The eager merge fixes that at the cost of the write amplification that is now the open known issue. This is the design tension the next major version has to resolve. | | **FOR delta-packed postings + WAND, scalar** | **Good for size and exactness; the ceiling on common-term latency.** Smallest index of the five, exact top-k. Cannot compete with bitmap+SIMD on high-df terms, and the project knows it. | | **Per-segment doclen sidecar (v4) with dual-read, no REINDEX** | **Very good.** 1.5.0 changed the on-disk format without forcing a rebuild, and that precedent is what makes future format evolution (bitmaps) tractable. | | **Tombstones as sparsemap, dense-decoded at merge** | **Good after the P0 fix.** Sparse in storage, dense where it is hot. The vendored library stays byte-identical to upstream, which has paid off three times. | | **`GenericXLog` for all WAL** | **Right for an extension.** Simplicity over write throughput. | | **Query lexer treating `-`/`/` as operators mid-word** | **Was flawed; fixed in 1.8.0.** The fix matched the doc analyzer and PostgreSQL's own parser. | | **Opportunistic merge inside the inserting transaction** | **Flawed at scale, and known.** Freed pages cannot pass the XID gate in their own transaction, so nothing is reused. The correct design merges out-of-transaction; that is the biggest open item. | **Net:** the storage and identifier decisions are strong and, in one case, vindicated by a well-funded competitor arriving at the same answer. The execution-engine decision (scalar postings) is the one that caps the product, and the merge-in-transaction decision is the one causing live pain. Both are understood and documented, neither is hidden. ## What I would do, in order 1. **Fix the README comparison paragraph today.** It is wrong about rare-term ranking and stale about the sidecar. It is the single highest-leverage defect in the repository. 2. **Delete the 31 dead base SQL files.** Zero risk (nothing reads them), immediate clarity. 3. **Move `bench/` under `docs/bench/` or a `history/` subtree with an `INDEX.md`** that names the ~6 documents that are current truth (`BENCHMARK_SUMMARY`, `COMPARISON_MATRIX`, the two known-issue notes, the TIN notes) and labels everything else as dated record. Stop referencing `bench/` files from C comments; reference the CHANGELOG entry instead. 4. **Collapse `HANDOFF`/`DEFERRED`/`CAPABILITIES`/`ROADMAP` into `ROADMAP.md`.** 5. **Pass the allocator state explicitly** instead of file-scope globals guarded by a test. 6. **Add `t/010` to the GitHub CI matrix** — it is the P1 regression test and currently runs only in the nix gate. 7. Then, and only with sign-off: the out-of-transaction merge (fixes the real known issue), and item D if common-term latency is the priority. None of items 1–6 touch the index. They are the difference between a project that *is* excellent and one that *looks* excellent to the person deciding whether to depend on it.