pg_fts capability / production-readiness matrix

BM25 full-text search index access method (fts) for PostgreSQL.

Every claim below is grounded in the source under contrib/pg_fts/. File:line citations are to the tree this document was generated against; SQL objects cite the squashed install script pg_fts--0.3.2.sql. All IndexAmRoutine flags cited are from the fts_handler function in pg_fts_am.c.

Capability matrix

Capability Supported? Evidence
@@@ boolean / phrase / NEAR / prefix / fuzzy / regex match Yes opclass strategy 1, pg_fts--0.3.2.sql:237; @@@ operators pg_fts--0.3.2.sql:130,139; bitmap scan bm25_getbitmap pg_fts_am_scan.c:1970
<=> relevance ordering scan (ORDER BY d <=> q LIMIT k, no Sort) Yes amcanorderbyop=true pg_fts_am.c:3313; <=> operators + FOR ORDER BY pg_fts--0.3.2.sql:319,326,336; bm25_gettuple block-max WAND/MaxScore pg_fts_am_scan.c:1197
BM25 (Okapi) scoring, index-maintained corpus stats (N, avgdl, df) Yes fts_bm25 pg_fts--0.3.2.sql:156; fts_index_stats/fts_index_df pg_fts--0.3.2.sql:243,251; metapage meta->ndocs pg_fts_am.c:820,2694
BM25 variants (lucene, robertson, atire, bm25+, bm25l) Yes fts_bm25_opts pg_fts--0.3.2.sql:164
BM25F multi-field weighting Yes fts_bm25f(ftsdoc[], ftsquery, weights, ...) pg_fts--0.3.2.sql:177
Phrase queries ("a b c") / NEAR via per-term positions Yes evaluated from stored positions; adjacency enforced exactly on all paths (@@@, bitmap, ranked, fts_count) – verified 2026-09-08 by cross-checking seq scan, index positions=off and index positions=on against regex ground truth (all 1000/1000). Where adjacency cannot be verified (a doc built without positions, or a boolean sub-expression under a phrase) the result is false, matching PostgreSQL’s OP_PHRASE without TS_EXEC_PHRASE_NO_POS – 1.6.0 fixed a silent degradation to a conjunction here. One deliberate exception: a prefix operand inside a phrase ("quick bro*") is presence-only, since prefix positions are not tracked, so it is over-permissive by design
Index-native phrase/NEAR (no heap recheck) with WITH (positions=on) Yes token positions stored in the postings (BM25 format v3, 4th lazily-decoded FOR column); phrase count/match answered from postings, no per-candidate heap fetch. Default positions=off keeps the smaller index + correct-but-slower heap recheck. Non-phrase queries never decode positions (bm25_decode_term skip); size cost ~1.03x (prose) to ~2.8x (high term-repetition)
Prefix (term*), fuzzy (term~k), regex (/re/) Yes README lines 31-34; sequential + index paths, sql/pg_fts.sql:147-271; optional trigram pre-filter pg_fts_trgm_index.c, built only WITH (trigrams = on) (default off; regex/long-fuzzy fall back to a dictionary scan without it)
Ranked (<=>) over fuzzy/prefix/regex returns a correct subset Partial the ranked WAND path builds cursors from the literal term, so docs matching only via an expansion aren’t ranked; results are always correct (never a non-match) but may be incomplete. Use @@@ for exhaustive fuzzy/prefix/regex retrieval. PHRASE/NEAR/boolean ranking is exact (bm25_recheck_exact, pg_fts_am_scan.c:1988)
Highlight / snippet Yes fts_highlight, fts_snippet pg_fts--0.3.2.sql:188,195
Fast bulk count (fts_count(regclass, ftsquery)) Yes pg_fts--0.3.2.sql:299; visibility-map-aware, heap probed only for not-all-visible pages. 1.3.0: a single plain term over a tombstone-free / pending-free / all-visible index is answered from the dictionary df alone (no posting decode, ~hundreds of times faster); the transparent count(*) WHERE @@@ Custom Scan pushdown now also fires for a plain-column index, not only an expression index
Lexical anomaly detection (fts_anomalous_docs(index, k, max_df)) Yes top-k docs containing globally rare terms, scored by max idf on global df; walks only the low-df dictionary tail (skips high-df terms before decode -> sub-ms, not a full scan) pg_fts_am_scan.c; pg_fts--0.3.2.sql:288; tombstones honored
MVCC-correct deletes (tombstones) Yes bm25_bulkdelete per-segment livedocs tombstone bitmap pg_fts_am.c:2971; scans/counts subtract, merge drops
Oversized-document handling Yes bm25_insert_oversized_as_segment one-doc segment (no per-doc size cap) pg_fts_am.c:2566,2660
WAL-logged / crash-safe / physical-replication safe Yes every page write via GenericXLog (15 GenericXLogStart cycles: 13 in pg_fts_am.c, 2 in pg_fts_trgm_index.c); no raw XLogInsert/log_newpage/MarkBufferDirty/PageSetLSN/smgrwrite anywhere (grep: 0 matches); header pg_fts_am.c:26-28; failover verified (t/002 promotes the standby and re-queries)
Managed-service safety (replica guard, privileges, live-only stats) Yes (1.3.0) fts_merge/fts_vacuum error during recovery (RecoveryInProgress) and require index ownership (object_ownercheck), shared bm25_maintenance_guard pg_fts_am.c; fts_search/fts_anomalous_docs REVOKEd from PUBLIC (install + pg_fts--1.2.2--1.3.0.sql); corpus stats exclude tupleIsAlive=false recently-dead tuples pg_fts_am.c
tsquery -> ftsquery migration Yes (partial: helper, not transparent) tsquery_to_ftsquery pg_fts_migrate.c:130 + ASSIGNMENT cast pg_fts--0.3.2.sql:214
CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY Yes (verified empirically) aminsert (bm25_insert) routes all concurrent writes to the pending list (immediately searchable) pg_fts_am.c:2632; see Q1
Index-only / covering scan (IOS) No amcanreturn = bm25_canreturn returns false pg_fts_am_scan.c:1144; amcaninclude=false pg_fts_am.c:3332; non-covering (stores postings, not the ftsdoc)
Parallel index build (PARALLEL workers) Yes, with a size trade-off amcanbuildparallel=true pg_fts_am.c:3330; parallel heap scan, per-worker segment flush, leader merge. Faster but larger: workers pack output pages independently, measured at 2.19M docs (bench/RESULTS_GATING_2026-09-09.md) – at maintenance_work_mem=1GB, 464 s / 5,386 MB with 4 workers vs 523 s / 4,605 MB serial; the gap nearly vanishes at 2GB. The same fragmentation makes a parallel fts_merge 1.45x SLOWER and 19% larger than serial (bench/RESULTS_PARALLEL_MERGE_2026-09-08.md), so do not raise max_parallel_maintenance_workers for merges
Parallel scan No amcanparallel=false pg_fts_am.c:3328; amestimateparallelscan/aminitparallelscan/amparallelrescan = NULL pg_fts_am.c:3362-3364
Parallel VACUUM No amparallelvacuumoptions = VACUUM_OPTION_NO_PARALLEL pg_fts_am.c:3334
Unique / multicolumn / ordered-btree / clusterable No amcanunique=false :3320, amcanmulticol=false :3321, amcanorder=false :3312, amclusterable=false :3326
NULL / optional-key indexing No amsearchnulls=false :3324, amoptionalkey=false :3322 (a NULL ftsdoc is not indexed: bm25_insert returns early on isnull[0] pg_fts_am.c:2649)
Predicate locks (SSI) No ampredlocks=false pg_fts_am.c:3327
Ranked scan over unflushed pending docs No (partial) <=>/fts_search cover merged segments only; pending docs matched by @@@/counted by fts_count but ranked only after a flush
Faceting / aggregation Custom Scan pushdown No none in tree; only fts_count count-pushdown exists
Impact-ordered postings No postings are docid-ordered (block-max WAND); listed as future work, README lines 62-64
Storage AIO / read_stream prefetch No (build heap scan gets core AIO free) 0 read_stream/StartReadBuffers sites; nextblk pointer-chains defeat prefetch; see Q6
REPACK <table> command (PG19) — plain + (CONCURRENTLY) Yes REPACK (renamed CLUSTER + new concurrent mode) rewrites the heap and rebuilds the bm25 index via the standard REINDEX path (repack.c:515); pg_fts’s ambuild/aminsert handle it like VACUUM FULL/CIC. CONCURRENTLY replays concurrent writes via logical decoding + core WAL (pg_fts is fully GenericXLog-logged). see Q3
REPACK <table> USING INDEX <bm25> (PG19) No (correctly rejected) orders the heap by an index; check_index_is_clusterable (repack.c:800) errors on amclusterable=false. BM25 order is query-dependent, so this is semantically inapplicable — clean error, not a gap.
pg_repack extension of the table N/A (table-level tool) see Q3 — pg_fts offers VACUUM+fts_merge() and REINDEX for in-place compaction

Answers

1. Concurrent index builds (CIC / REINDEX CONCURRENTLY)

Yes — verified empirically. amcanbuildparallel=true enables parallel CREATE INDEX (a separate capability from) the two-phase concurrent build. What makes CIC correct here is that aminsert (bm25_insert, pg_fts_am.c:2632) always routes a new document to the pending write buffer so newly inserted rows are immediately visible to @@@ without a REINDEX. Oversized documents that will not fit a pending page take the equivalent path as a one-document segment (bm25_insert_oversized_as_segment, pg_fts_am.c:2566). So writes that arrive during the build’s validate phase are captured by the index and found by the subsequent scan; the build (bm25_build) does a standard table_index_build_scan and never disables inserts. Both CREATE INDEX CONCURRENTLY and REINDEX CONCURRENTLY have been verified to work.

2. Index-only scans and the count tradeoff

No index-only scan — this is by design and drives the count strategy. amcanreturn = bm25_canreturn returns false (pg_fts_am_scan.c:1144): the index is non-covering because it stores analyzed postings (terms, term frequencies, positions, doc lengths), not the original ftsdoc, so it cannot reproduce a column value. amcaninclude=false (pg_fts_am.c:3332), so there is no covering INCLUDE either.

Consequence for counting: count(*)/EXISTS need no attribute but the planner still includes the @@@ restriction column in the IOS coverage check, so with amcanreturn=false they fall back to a bitmap (or plain index) scan — every matching TID is visited (bm25_canreturn, pg_fts_am_scan.c:1144). The fast count is therefore the explicit fts_count(regclass, ftsquery) (pg_fts--0.3.2.sql:299), which counts matches in bulk from the index using the visibility map, probing the heap only for not-all-visible pages — no per-tuple executor round-trips.

Tradeoff: you trade IOS convenience (transparent count(*) over the index) for a smaller, ranking-ready index (no stored source doc) plus an explicit, MVCC-correct bulk-count primitive. Callers wanting a fast count must call fts_count rather than relying on the planner picking an index-only count(*).

3. REPACK / pg_repack / in-place compaction

Two different things share the name. (a) The PG19 in-core REPACK command (renamed CLUSTER + a new CONCURRENTLY mode) rewrites the table and then rebuilds every index via the standard REINDEX path (repack.c:515) — pg_fts is rebuilt correctly by it, exactly as by VACUUM FULL (plain and CONCURRENT both work; the USING INDEX <bm25> form is rejected because amclusterable=false, which is correct — BM25 order is query-dependent). Note: REPACK ... CONCURRENTLY’s logical-decoding replay is a newer path than CIC and is not yet empirically exercised in the isolation suite (PG19 isn’t in the local test matrix) — the code properties are all satisfied, but an isolation test is a good future add. (b) The pg_repack extension rewrites the table too and is orthogonal to fts. Either way, for compacting the index itself pg_fts offers:

  • VACUUM + fts_merge() — the size-tiered segment merge. VACUUM’s amvacuumcleanup folds pending docs into a segment and merges; fts_merge(regclass) (pg_fts--0.3.2.sql:344) forces it on demand. bm25_merge_segments (pg_fts_am.c:2120) coalesces similarly-sized segments and physically drops tombstoned docs (pg_fts_am.c:25, bm25_bulkdelete comment pg_fts_am.c:1368,1414).
  • REINDEX / REINDEX CONCURRENTLY — full rebuild.

Honest gap: the merge leaves superseded blocks unreferenced; they are reclaimed only by REINDEX (“Old blocks are left unreferenced and reclaimed by REINDEX (a page recycler is future work)”, pg_fts--0.3.2.sql). So fts_merge() compacts logical content (fewer segments, tombstones gone) but does not shrink the physical file — REINDEX is the only way to reclaim that space. There is no online index REPACK beyond VACUUM+fts_merge() and REINDEX.

4. Feature parity vs. pg_search/Tantivy, ZomboDB/Elasticsearch, tsvector/GIN

HAS: - BM25 (Okapi) + variants (lucene/robertson/atire/bm25+) and BM25F multi-field weighting (fts_bm25f, pg_fts--0.3.2.sql:177). - Rich query language: boolean, phrase "a b c", NEAR(...), prefix term*, fuzzy term~k, regex /re/ (README 28-34; sql/pg_fts.sql:147-271,416-426). - <=> relevance ordering index scan (no Sort) via block-max WAND / MaxScore (pg_fts_am_scan.c:1197, pg_fts--0.3.2.sql:336). - Fast MVCC-correct bulk count fts_count (pg_fts--0.3.2.sql:299). - Highlight / snippet (fts_highlight, fts_snippet, pg_fts--0.3.2.sql:188,195). - MVCC-correct tombstone deletes (bm25_bulkdelete, pg_fts_am.c:2971). - Oversized-document handling (one-doc segments, pg_fts_am.c:2566). - Full WAL logging via GenericXLog → crash recovery + physical replication safety, no custom resource manager (pg_fts_am.c:26-28; 0 raw-write sites).

HONEST GAPS: - No parallel scan (amcanparallel=false, pg_fts_am.c:3328; parallel-scan hooks all NULL, :3362-3364) → single-threaded query execution. - No index-only / covering scan (amcanreturn→false, amcaninclude=false). - No faceting / aggregation Custom Scan pushdown (only count-pushdown exists). - No impact-ordered postings — docid-ordered only (README 62-64). - <=> / fts_search ranking does not cover unflushed pending docs until a flush.

Versus Elasticsearch/Tantivy this is a single-node, single-threaded-per-query engine with no distributed aggregation; versus tsvector/GIN it adds real BM25 ranking, index-maintained corpus stats, and a <=> ordering scan that GIN cannot provide, at the cost of GIN’s parallel-scan and mature-tooling maturity.

5. Drop-in replacement for a tsvector/pg_textsearch system under logical replication?

No — it is a re-platform, not a drop-in. Three reasons, all code-backed:

(a) Different API, no transparent shim. pg_fts uses ftsdoc/ftsquery with @@@ (pg_fts--0.3.2.sql:130) and <=> (pg_fts--0.3.2.sql:319), not tsvector/tsquery/@@. Ranking is fts_bm25/<=>, not ts_rank. There is a migration helper tsquery_to_ftsquery() (pg_fts_migrate.c:130, faithful &→AND, |→OR, !→NOT, <N>→FTS_OP_PHRASE preserving the gap) and an ASSIGNMENT cast (pg_fts--0.3.2.sql:214) so existing tsquery values flow into @@@, but there is no transparent operator/type shim — queries, index DDL (USING fts (to_ftsdoc(...))) and ranking calls must be rewritten.

(b) Logical replication does not replicate indexes. Under logical replication the subscriber maintains its own indexes; a subscriber must have pg_fts installed and its own fts index provisioned. This is no worse than GIN (indexes are never logically replicated), but it is a per-subscriber provisioning step, not automatic.

© Physical replication + crash recovery ARE safe. Every page write goes through GenericXLog (15 GenericXLogStart cycles; zero raw-WAL/buffer-dirty sites), so the index is fully WAL-logged and replicated on a physical standby with no custom resource manager (pg_fts_am.c:26-28).

Bottom line: physical replicas and crash recovery are covered transparently; moving a tsvector/GIN workload to pg_fts is a deliberate migration (rewrite queries/DDL, provision the index per subscriber for logical replication), not a transparent swap.

Note: the WAL write-site count is 15 GenericXLogStart cycles (13 in pg_fts_am.c, 2 in pg_fts_trgm_index.c). The underlying claim is what matters: 100% of page mutations go through GenericXLog, with zero raw XLogInsert, log_newpage, MarkBufferDirty, PageSetLSN, or smgrwrite/smgrextend sites (grep: 0 matches).

6. Asynchronous I/O (AIO / read_stream)

pg_fts issues no storage AIO of its own; the one place it matters already gets it from core. Every index-side read is a plain synchronous ReadBuffer + LockBuffer (grep for read_stream/StartReadBuffers/ PrefetchBuffer across contrib/pg_fts/: 0 matches; the only prefetch hits are __builtin_prefetch CPU cache-line hints in vendored sparsemap, unrelated to storage AIO). The build’s heap scan runs through table_index_build_scan → heap_getnextslot → read_stream_next_buffer, so CREATE INDEX’s heap side is already streamed/prefetched by core with no pg_fts code.

Could it? Only in one path with real payoff. Every pg_fts on-disk structure is a nextblk linked list (BM25PageOpaqueData.nextblk), so the next block is known only after the current page is read — the classic pointer-chase that read_stream cannot prefetch. The hot query path (block-max WAND) is anti-prefetch by design: it reads block headers precisely to skip blocks without reading their payload, so prefetching would fetch pages it means to skip. The only cheap win is the cold merge full-scan (bm25_read_segment_into), which reads every posting page end-to-end: those pages are written as one contiguous run per segment, so recording a [firstblk,lastblk] range in BM25SegMeta would let a trivial blk++ read_stream callback prefetch the merge.

Should it? Not for a warm-cache OLTP search workload (a handful of resident pages per selective query; AIO adds setup cost with no I/O to hide). A bounded, low-effort win exists for cold TB-scale merges if a cold-merge I/O bottleneck is actually measured — deferred until then.

AIO for the parallel-merge WRITES? Considered and rejected on two grounds. (1) No API: pg_fts writes every page through shared buffers + GenericXLog (a WAL/MVCC/crash-safety requirement), and this tree’s buffer manager exposes AIO for reads only (aio_shared_buffer_readv_cb; there is no buffer-manager AIO write path – FlushBuffer is synchronous). Using the low-level pgaio_io_start_writev would mean bypassing shared buffers with raw smgr writes, breaking the GenericXLog invariant the design rests on. (2) It would not help: the merge tail measured at 2M is CPU-bound (one backend decoding + re-encoding postings; workers=0 with the index resident in a 32 GB shared_buffers, so the writes are absorbed by shared buffers and flushed lazily by the checkpointer – no write I/O wait to hide). AIO accelerates I/O wait, not CPU-bound re-encode. The real lever for the merge tail is the same as the ranked-query gap: a cheaper posting codec (format v3), not asynchronous writes.