pg_fts roadmap

The single plan file. Open work first, in priority order, each with its status and the evidence behind it. Closed items are one line each; the full record of how they were closed (and the wrong turns on the way) is in bench/ROADMAP_HISTORY_2026-09-17.md and the files bench/INDEX.md lists.

Rules for this file: an item is either open, blocked, or closed. When it closes, collapse it to one line here and leave the detail in the CHANGELOG. Do not create a second plan file.

Open – repository and presentation (from REVIEW_2026-09-17.md)

These do not touch the index and are the cheapest, highest-leverage work in the project.

# item status
R1 README comparison paragraph contradicted the project’s own table (claimed a rare-term lead; called the doclen sidecar future work). Rewritten as a wins / loses / caveat structure; every figure grep-verified against BENCHMARK_SUMMARY.md and RESULTS_C1X. The paragraph now states it is derived from the summary and that the summary wins on disagreement. Rule 27 in AGENTS.md and RELEASING.md step 1 keep it in sync. done 2026-09-17
R2 Delete the 31 dead base SQL scripts in the root. Only pg_fts--<current>.sql is installed; the rest were snapshots left by each rename. Verified none referenced by Makefile/meson/flake before git rm. RELEASING.md step 1 now says git mv, not copy. done 2026-09-17
R3 bench/INDEX.md naming current-truth vs dated-record files. done 2026-09-17
R4 One plan file. HANDOFF/DEFERRED/CAPABILITIES folded: DEFERRED (all resolved) and the old ROADMAP moved to bench/ as history; CAPABILITIES moved to doc/ (it is user-facing Q&A, not a plan). done 2026-09-17
R5 C comments no longer cite bench/ files (9 sites -> 0). They cite the CHANGELOG release that closed the issue; the journal can be reorganised, the CHANGELOG cannot. done 2026-09-17
R6 t/010 in all CI matrices – and what verifying it uncovered. GitHub: added, and the run log confirms t/010_vacuum_delete_heavy.pl .. ok. Forgejo: added too, but the Codeberg CI has never executed a single run – every ci.yml run back to v0.1.0 is cancelled with started_at = None (queued, never picked up, superseded by the next push). Cause is account-side: Codeberg Actions needs a runner enabled per repository, which is not visible or fixable from the tree. The README’s first badge had therefore shown “waiting”/“cancelled” to every visitor since the repo existed; it now points at the GitHub CI, which is the pipeline that actually runs. Open sub-item R8 below. done (GitHub verified); Forgejo cannot run
R8 Codeberg Actions had never run – and PGXN was frozen at 0.2.0 because of it. Corrected diagnosis: Actions was enabled (has_actions: true); the cause is that Codeberg provides no shared runners, so all 115 queued runs (ci / release / docs) from v0.1.0 to v1.8.2 sat unstarted and were cancelled by the next push. The consequence was not cosmetic: PGXN publishing and the postgresql.org announcement lived only in the Codeberg release workflow (the GitHub one explicitly deferred to it), so PGXN served 0.2.0 for 45 releases – pre-dating every field fix – and the README’s second badge pointed at it. Also the Codeberg Pages docs link was a 404. Resolved by deletion, not by standing up a runner: a 24/7 self-hosted runner to duplicate a matrix GitHub already runs green is real cost for zero signal. .forgejo/workflows/ removed; PGXN publish + announce moved into the GitHub release workflow (409 = already published, idempotent); README badges now point at GitHub CI and the real latest tag; dead Pages link removed; RELEASING.md describes the one pipeline that exists. Both follow-ups closed the same day: PGXN_USER/PGXN_PASSWORD were already set as GitHub secrets (since 2026-07-08, unused until now); a workflow_dispatch re-publish of the existing v1.8.2 tag ran the new definition – PGXN upload HTTP 303, accepted – and api.pgxn.org now reports version: 1.8.2, with 0.2.0 retained in history. The GitHub release was re-targeted, not duplicated (1 asset). PGORG_* is unset so the announcement step skipped, as designed; set it if pgsql-announce posts are wanted. done – PGXN current
R7 Agent tooling out of the working tree’s face. .agent/, .claude/, .kiro/, .mcp.json, .agent-steering-domains.md are gitignored but visible. AGENTS.md is now tracked and is the one entry point; the rest stay local. Also found and fixed while doing this: result-1, a nix build-output symlink, was tracked (now git rm --cached, result* ignored). done 2026-09-17

Code quality (from REVIEW_2026-09-17.md) – all three resolved 2026-09-17

# item outcome
C1 Allocator state passed explicitly. The four file-scope globals (bm25_lowfree, _n, _i, bm25_alloc_extend_only) became one BM25AllocCtx struct reachable only through bm25_alloc_scope_enter(index, mode) / bm25_alloc_scope_exit(prev), which nest by returning the previous context – the hand-rolled save/restore at two sites became the mechanism. bm25_new_buffer() now elog(ERROR)s if it finds compaction state with no scope active: exactly the dangling-pointer failure from the 1.7.1 work that only t/007 caught. Deliberately an elog, not an Assert: the release gate is not a cassert build, and a check that only fires in a build nobody ships is documentation. Threading a struct through all 16 bm25_new_buffer callers was considered and rejected – compaction is single-writer, so the context is backend-scoped in effect either way, and the scoped-lifetime design makes the failure mode a hard error at the same cost as a comment. done
C2 bm25_collect_matches split: 412 -> 226 lines. The 176-line per-segment loop body became bm25_collect_segment() (153 lines, returns SEG_RESTART for the positional-phrase fallback the loop used to express as s = -1; continue), and the 71-line pending-list walk became bm25_collect_pending(). State shared with the extracted evaluator travels in a BM25CollectCtx. Behaviour-preserving; full gate green. While doing it, the 14 page + pd_lower reads in _scan.c and the one in pg_fts_trgm_index.c’s blob reader (a 9th instance of the 1.7.0 defect class, feeding a memcpy length) were routed through bm25_page_data_end(). done
C3 The 12,000-line translation unit: KEEP, and document why. Measured the cost of splitting: 15 am.c statics would go extern (13 for _scan, 5 for _trgm), 1 the other way, ~10 shared struct types would move into pg_fts_am.h (the on-disk-format header), and the hot-path static inline helpers (bm25_tid_to_docid, bm25_docid_to_tid, bm25_page_data_end, bm25_doclen_cursor_lookup – inside the 45%/37% profile) would stop inlining without LTO, which PGXS does not use. In return: three .o files and no behaviour change. Decision recorded at the #include site and in both included files' headers, with the two prerequisites (an internal header; a before/after latency measurement) if separate compilation is ever needed. decided: keep

Open – index behaviour

# item status evidence
I1 Bulk-ingest write amplification – FIXED for row-per-transaction ingest (1.8.3). Root cause was not the XID horizon (that binds only inside one multi-row statement) but the merge’s EXTEND_ONLY allocation never consulting the free list. BM25_ALLOC_SNAPSHOT (free list gathered once at entry, never re-read) keeps the recycle-race guard by construction and reuses earlier frees. Field shape, 30k then 100k docs of row-per-txn churn, autovacuum on, nothing manual: 1,823 -> 1,823 MB and 1,823 -> 1,875 MB with fts_vacuum finding nothing to reclaim; v1.8.2 on the same harness grew 3.2 GB per 5k rows and then deadlocked (a pre-existing concurrent-merge deadlock, also fixed, plus a live-page handout deadlock the fix exposed – both in the CHANGELOG). Residual: one very large INSERT ... SELECT of oversized rows still needs an fts_vacuum after; modelled the fewer-larger-merges alternative at ~2x, not worth its complexity. done; residual narrowed bench/RESULTS_I1_2026-09-18.md
I2 Common-term ranked latency – done in 1.11.0 (Approach A). Best-first block order with an exact effective-length block bound: year top-10 8.48 -> 0.81 ms (pg_search 2.63), 1,911 -> 22,369 tps at 16 clients. Conjunctive best-first for AND and phrase: united & states 23.9 -> 2.7 ms, "united states" 25.0 -> 3.6 ms. No format change. done 1.11.0 bench/RESULTS_A_2026-10-07.md
A1 Build time vs pg_search (257 s vs 71 s as expression indexes, 2.19M articles). The merge is now byte-identical and 5x faster than 1.10.0’s, and a plain build needs no fts_vacuum; what remains (measured on the stored-column build, 105 s: DEBUG1 merge timings in a3_merge_notes.txt) is the parallel heap scan into 7 worker segments, the single-backend collapse merge of those segments (42.2 s) and the in-build pack (23.7 s); an expression index adds to_ftsdoc analysis inside the scan (257 s total). Next measurable step: have workers emit sorted runs to a shared tuplesort and the leader write ONE segment (no collapse, no pack). Unmeasured. open bench/data_A_2026-10-07/a3_merge_notes.txt
A2 Many-block conjunctions and wide ORs (world & war 5.87 vs pg_search 5.75; 4-term OR 13.97 vs 13.40, 1.11.0 release vs pg_search 0.26.1, bench/RESULTS_111_2026-10-09.md): both terms frequent, the k-th best spread over ~600 driver blocks, so the best-first walk visits most of them. Candidates: a tighter per-block conjunctive bound (pairwise maxima, not the sum of per-term maxima) and best-first for the 4+ term OR. Offline simulation first, as for A. open bench/data_A_2026-10-07/and_sim.py
D Two-level page bitmaps + SIMD (TIN-style). Standby: Approach A closed the common-term gap it was meant to close without it (I2). Format side is tractable via the 1.5.0 optional-per-segment-pointer + dual-read precedent (no REINDEX). Real cost: no SIMD infrastructure exists (no intrinsics, no runtime dispatch, no -mavx2 plumbing) and a scalar fallback must be kept for non-AVX and ARM – two implementations forever. Largest change the project has attempted, against a competitor that cannot be benchmarked. Needs explicit sign-off. Do not vectorize the vendored sparsemap. blocked on sign-off bench/NOTE_TIN_FEASIBILITY_2026-09-14.md, NOTE_SIMD_VENUE_2026-09-14.md
I5 Lazy phrase gate – done in 1.9.1. A ranked phrase/NEAR on a positions=on index checks adjacency per WAND candidate from the cursor’s own block instead of materializing every match first. "united states" k10: 138 (1.9.0) -> 34.8 ms, vs pg_textsearch 43.0 (same day, separate hosts). Identical results to the collect path on 28 phrase/k cases at 2.2M rows; phrase_gate test with planted mutants. done 1.9.1 bench/RESULTS_191_2026-10-06.md
I6 Rare-term throughput fell with backend count – fixed in 1.10.0. Each backend held a private ~4.8 MB copy of the C1 slot doclen array, and the copies competed for L3. 1.10.0 keeps one copy per segment per server in DSM (pg_fts.shared_doclen), refcounted per scan, keyed by segment identity so backends with different snapshots stay exact. Same-host A/B on Graviton3: rare-term tps at 64 clients 16,654 vs 13,283 (+25%), now flat 16 -> 64; per-transaction cost of bm25_doclen_cursor_lookup 21.2 -> 6.9 units. done 1.10.0 bench/RESULTS_110_2026-10-07.md, bench/PLAN_I6_SHARED_DOCLEN_IMPL_2026-10-07.md
I7 Per-query shared cache-line traffic – done in 1.12.0. Ranked lookups re-walked the dictionary-index chain (up to 85 pages, pinned and share-locked, read by every concurrent query) three times per term; each backend now binary-searches an in-memory copy of each segment’s dictionary index, kept in the existing relcache chunk. Rare top-10 278 -> 80 shared buffers; single-client rare 0.54 -> 0.27 ms; throughput rare +67-97%, common +74-92% at 16-64 clients on six hosts, results identical. Host-state variance itself is not addressable in pg_fts (bench/RESULTS_TPS_VARIANCE_2026-10-09.md); pg_fts.shared_doclen stays a documented trade. Remaining per-query costs: the doclen lookup and scoring (profile in bench/data_i7_2026-10-09/h1/). done 1.12.0 bench/RESULTS_112_2026-10-09.md
I8 Relcache chunk leaked on an ERROR during its rebuild (pre-existing since 1.5.8; known issue in 1.12.0). bm25_doclendir_cache frees the stale chunk, builds the new one in CacheMemoryContext through three builders (directory, slot arrays, 1.12.0 dictionary directory) that each can raise (CHECK_FOR_INTERRUPTS, palloc), and attaches it to rd_amcache only at the end; an ERROR in between leaks it (bounded by the 1.4 MB CacheMemoryContext of a backend after one ranked query on 2.19M docs; the chunk alone is unmeasured). Fix: attach each intermediate chunk to rd_amcache before the next builder runs (every intermediate is a valid, smaller cache; missing parts fall back), plus a test that cancels a rebuild and checks CacheMemoryContext. open CHANGELOG 1.12.0 Known issues
I3 Managed-service validation on a compute/storage-separated backend (Aurora-style). GenericXLog-only WAL should be safe; unverified externally. open, external doc/CAPABILITIES.md
I4 Independent human review of WAL/crash/recovery paths. Checklist exists in RELEASING.md; the review itself is a release-integrator step. open, external
I9 Configured analyzer loses positions after 16,383 (reported in PR #12). `to_ftsdoc(‘simple’, repeat(‘filler ’,17000) ‘alpha beta’) @@@ ‘“alpha beta”’` is false, while the unconfigured analyzer returns true. PostgreSQL’s configured parsing pipeline caps these positions before pg_fts receives them. Existing saved vectors cannot recover lost positions. Verify long-document analysis and stored input positions before adopting this path. | open, inherited limitation | README / CHANGELOG Unreleased
I13 VACUUM bulkdelete memory was O(postings) – FIXED 1.14.0. bm25_segment_docids decoded every posting of a segment into one array and bulk-added it: ~24 bytes per posting, independent of maintenance_work_mem or dead-tuple count. A production index (977M postings/segment) OOM-killed an autovacuum at 13.9 GB. Now a v4 segment reads the doclen sidecar (O(documents), zero posting decode) and an inline-doclen segment flushes in maintenance_work_mem/24 batches; both loops are cancellable + cost-delayed. 400k docs/120M postings: one-row INDEX_CLEANUP ON 1,429 -> 151 MB. vacuum_mem test. Present since 1.1.5. done 1.14.0 pg.ddx.io report 2026-10-15
I14 Manual VACUUM errored under parallel index vacuuming on PG18 – FIXED 1.14.0. bm25_truncate_lock took AccessExclusiveLock in the parallel-vacuum leader’s cleanup; under wal_level >= replica that assigns an xid (hot-standby lock logging), an ERROR in parallel mode, rolling back the whole VACUUM so relfrozenxid stalled. Now declines truncation when IsInParallelMode() (tail stays in FSM, reclaimed by a later non-parallel VACUUM/fts_vacuum). Reproduced + fixed on PG 18.6; autovacuum and VACUUM (PARALLEL 0) never affected. Introduced 1.11.0 (ada32aa). vacuum_parallel test (fails on the mutant on PG18). done 1.14.0 pg.ddx.io report 2026-10-15
I10 The CI sanitizers job’s regression step has never run the tests. The job is continue-on-error and so is the step (“best-effort”, since 2026-07-15). In every run checked (1.12.1, 1.13.0) CREATE EXTENSION pg_fts fails to load the clang+ASan .so into the pg_virtualenv server, so all 15 regression tests fail and the job still reports success. The gating memory-safety signal is the fuzz job; ASan over the SQL paths is therefore not covered. Fix: run the regression under an ASan-built PostgreSQL (like the cassert build used for test/a1_recycle), or drop the step and say so in doc/testing.md. in progress 1.14.0 – ASan-built PostgreSQL approach proven on a Debian EC2 host (needs raised ulimit -s + max_stack_depth); CI change pending CI run 38659999070
I11 Ranked LIMIT >= 250 on an expression index took the bitmap + Sort plan – FIXED 1.14.0. fts_matchsel estimates @@@ from the index dictionary df (a term’s df, AND/phrase the min, OR the sum) instead of tsmatchsel’s fixed default, so the planner prices a common term apart from a rare one; it falls back to tsmatchsel for a non-constant query / no bm25 index / join clause. year LIMIT 5000 at 50k rows: 21.6 s -> 44 ms, staying on the ordering scan. SQL change (new estimator, both @@@ operators rewired); pg_fts--1.13.0--1.14.0.sql. planner_cost test. done 1.14.0 metal smoke test 2026-10-15
I12 Ranked reads under sustained inserts – FIXED 1.14.0. Every ranked query read the whole unflushed pending list (since 1.13.0, so pending documents rank), so latency grew with inserts since the last merge (0.22 ms none pending, 205 ms at 1M). pg_fts.pending_flush_threshold (default 20000, 0 disables) makes the inserting backend fold the pending list into a segment at the threshold, taking the maintenance lock only if free (inserters never wait); and a single term across many segments now runs one global best-first pass instead of MaxScore, so the extra flushed segments do not slow single-term reads (7 segments: rare top-10 3.80 -> 0.26 ms, identical results). Mixed load 8+8 at 2.19M rows: ranked reads 97 -> 3,223 tps at 10% fewer inserts. pending_flush test. Metal write-band A/B still to re-run for the release. done 1.14.0 (metal A/B pending) metal benchmark 2026-10-15

Open – measurement debt

# item
M1 C2 cross-engine ingest. Rivals' ingest paths differ fundamentally; needs per-engine forms chosen as carefully as C1X’s.
M2 C3 NDCG vs rivals. Matters because pg_search (Tantivy) does not stem – its speed is partly a smaller unit of work. bench/ndcg.py exists.
M3 Longer ingest run done 2026-09-18: 20 rounds / 100k row-per-txn docs. No decay – throughput oscillates 33-56 rows/s with round durations at exactly 1x / 1.33x / 1.67x a 90 s floor, i.e. autovacuum cycles landing in the round or not. The absolute rate is the harness (psql building 1,660-term strings), not publishable. Appended to RESULTS_I1_2026-09-18.md.
M4 The published competitor set on TIN’s exact rig (i7i.8xlarge, 8 vCPU / 32 GB container, Stack Exchange corpus) plus pg_fts – places us on their axis without asserting anything about TIN. Several EC2 hours.

Declined (with the measurement that declined them)

  • Impact-ordered postings – breaks the docid ordering that count(*)/AND/phrase/prefix need.
  • Early termination – breaks exact top-k.
  • Heap-side positions=off – saves ~16% of a STORAGE=extended column; no-go.
  • Parallel ranked scan – built, measured, reverted (bench/NOTE_PARALLEL_RANKED.md).
  • Parallel merge – 1.45x slower and 19% larger at scale; mpmw=8 silently serial.
  • df-threshold bulk load – cost ~linear in df, no fixed floor.
  • Verbatim posting copy on merge (TIN item C) – the merge re-encodes through the build hash table; no splice point.
  • Vectorizing sparsemap – never a query hotspot; the one time it was the bottleneck (P0, 99.75%) the fix was algorithmic; its compressed layout is SIMD-hostile; it is vendored byte-identical to upstream on purpose.

Closed (one line each; detail in CHANGELOG)

  • 1.11.0 (Approach A): best-first single-term and conjunctive top-k with exact block bounds; MaxScore wrong top-k for 4+ terms (every release) and ranked term:LABEL fixed; merge 5x faster with identical output and no fts_vacuum after a plain build; maintenance buffer ring; prefetch where chains jump and for the ranked heap fetches; VACUUM pending-delete fix and four concurrency fixes (lost inserts during flush, truncation without AccessExclusiveLock, freed-page link overwritten by the recycle XID, double free on scan retry), all in every earlier release, regression test t/012. Champion lists (Approach C) not needed. bench/RESULTS_A_2026-10-07.md.

  • 1.12.0: ranked dictionary lookups from a per-backend in-memory copy of each segment’s dictionary index (I7); pg_fts.doclen_cache_mb changes now take effect in the session. bench/RESULTS_112_2026-10-09.md.

  • 1.9.0 ranked-retrieval release (LIMIT pushdown, resident doclen, dense high-df scoring, score reuse) and two exactness fixes present since 1.x (negative idf after deletes; WAND last-block skip). Leads pg_textsearch 1.4.0 on every latency and throughput band except phrase (3.2x behind).

  • 1.8.6 sparsemap 5.7.0 -> 5.8.0: bulk sm_add_many_grow merges instead of inserting per bit (1.4-4x faster standalone; unmeasured in pg_fts at scale), plus a coalesce over-read fix. Byte-identical wire output.
  • 1.8.5 sparsemap 5.6.0 -> 5.7.0: small-set tombstone encoding, verified live through pageinspect and by a new sanitizer fuzz target over the vendored library; the 1.8.4 open guard holds in both encodings.
  • 1.8.4 sparsemap 5.5.1 -> 5.6.0 (upstream security hardening); every sm_open now goes through bm25_sm_open_checked, so a corrupt tombstone/trigram bitmap raises ERRCODE_DATA_CORRUPTED instead of silently opening as EMPTY – which for a tombstone map would have resurrected every deleted row. t/003 proves it (red unguarded, green guarded).
  • 1.8.3 two deadlocks that shipped in every prior version (insert-path full-directory merge ran without the maintenance mutex; a live page could be handed out as merge output), and the bulk-ingest bloat root cause (merge was extend-only, never reused pages) – zero net growth over 100k row-per-txn docs.
  • 1.8.2 allocator state is a scoped struct with misuse an elog(ERROR); bm25_collect_matches 412 -> 226 lines; the single translation unit kept as a costed, documented decision; a 9th pd_lower read site guarded.
  • 1.8.1 count-path: df fast-count gate tests (10, non-vacuous); block-run VM checking measured ~1%, kept as cleanup.
  • 1.8.0 intra-word - . / are terms, not operators (pkg-config no longer parses as pkg & !config).
  • 1.7.2 insert-time merge gated on segment pressure: bulk-ingest growth -31%.
  • 1.7.1 pd_lower guard generalised to all 8 page-read sites; “one WAL record per page” known issue retracted (0.005 ms/page measured).
  • 1.7.0 P0: unvalidated pd_lower in the merge dict walk made an index permanently unvacuumable at field shape; fixed. Huge-alloc gaps in doclen/tombstone arrays fixed. Cleanup no longer grows the index (18/18/18 MB vs 35/52/69).
  • 1.6.1 P0: VACUUM never completed on a delete-heavy index (4h39m -> 393 s; dense tombstone bitmap sized by sm_maximum). sparsemap 5.5.1.
  • 1.6.0 phrase over positionless docs returns false, not a silent conjunction (matches OP_PHRASE).
  • 1.5.9 / 1.5.10 non-UTF-8 case folding; common-term 1.56x via ascending-resume + word-load bm25_for_get.
  • 1.5.0 doclen sidecar (format v3 -> v4) with dual-read, no REINDEX – the precedent for all future format changes.
  • COUNT pushdown (CustomScan), fts_search under-fetch, reserved keywords as literals, sparsemap error-path leaks, recovery guard on fts_merge/fts_vacuum, privilege lockdown, recently-dead exclusion from corpus stats, parallel-build memory ceiling – all shipped; see history file.