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 #includes 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.