# 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. All
`IndexAmRoutine` flags cited are from the `bm25_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--1.2--1.3.sql:19`; `@@@` operators `pg_fts--1.0.sql:108,117`; bitmap scan `bm25_getbitmap` `pg_fts_am_scan.c:1486` |
| `<=>` relevance ordering scan (`ORDER BY d <=> q LIMIT k`, no Sort) | Yes | `amcanorderbyop=true` `pg_fts_am.c:2112`; `<=>` operators + `FOR ORDER BY` `pg_fts--1.15--1.16.sql:18,25,35`; `bm25_gettuple` block-max WAND/MaxScore `pg_fts_am_scan.c:1147` |
| BM25 (Okapi) scoring, index-maintained corpus stats (N, avgdl, df) | Yes | `fts_bm25` `pg_fts--1.1--1.2.sql` (stage 1.2); `fts_index_stats`/`fts_index_df` `pg_fts--1.6--1.7.sql:7,15`; metapage `meta->ndocs/sumdoclen` `pg_fts_am.c:1571-1583` |
| BM25 variants (lucene, robertson, atire, bm25+) | Yes | `fts_bm25_opts` (stage 1.4), README lines 24 |
| BM25F multi-field weighting | Yes | `fts_bm25f(ftsdoc[], ftsquery, weights, ...)` `pg_fts--1.11--1.12.sql:9` |
| Phrase queries (`"a b c"`) via per-term positions | Yes | ftsdoc format v2 (stage 1.9), README line 28; `sql/pg_fts.sql:206-215` |
| Prefix (`term*`), fuzzy (`term~k`), regex (`/re/`) | Yes | README lines 31-34; sequential + index paths, `sql/pg_fts.sql:147-271`; trigram pre-filter `pg_fts_trgm_index.c` |
| Highlight / snippet | Yes | `fts_highlight`, `fts_snippet` `pg_fts--1.4--1.5.sql:6,13` |
| Fast bulk count (`fts_count(regclass, ftsquery)`) | Yes | `pg_fts--1.18--1.19.sql:9`; visibility-map-aware, heap probed only for not-all-visible pages |
| MVCC-correct deletes (tombstones) | Yes | `bm25_bulkdelete` per-segment livedocs tombstone bitmap `pg_fts_am.c:1843-1853`; 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:1521,1552-1560` |
| WAL-logged / crash-safe / physical-replication safe | Yes | every page write via GenericXLog (14 `GenericXLogStart` cycles: 12 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` |
| tsquery -> ftsquery migration | Yes (partial: helper, not transparent) | `tsquery_to_ftsquery` `pg_fts_migrate.c:130` + ASSIGNMENT cast `pg_fts--1.5--1.6.sql:8-15` |
| CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY | Yes (verified empirically) | `aminsert` routes all concurrent writes to the pending list (immediately searchable) `pg_fts_am.c:1515-1520,1521`; see Q1 |
| Index-only / covering scan (IOS) | No | `amcanreturn = bm25_canreturn` returns `false` `pg_fts_am_scan.c:1101-1114`; `amcaninclude=false` `pg_fts_am.c:2131`; non-covering (stores postings, not the ftsdoc) |
| Parallel index build (PARALLEL workers) | Yes | amcanbuildparallel=true; parallel heap scan, per-worker segment flush, leader merge |
| Parallel scan | No | `amcanparallel=false` `pg_fts_am.c:2127`; `amestimateparallelscan/aminitparallelscan/amparallelrescan = NULL` `pg_fts_am.c:2161-2163` |
| Parallel VACUUM | No | `amparallelvacuumoptions = VACUUM_OPTION_NO_PARALLEL` `pg_fts_am.c:2133` |
| Unique / multicolumn / ordered-btree / clusterable | No | `amcanunique=false` `:2119`, `amcanmulticol=false` `:2120`, `amcanorder=false` `:2111`, `amclusterable=false` `:2125` |
| NULL / optional-key indexing | No | `amsearchnulls=false` `:2123`, `amoptionalkey=false` `:2121` (a NULL ftsdoc is not indexed: `bm25_insert` returns early on `isnull[0]` `pg_fts_am.c:1531`) |
| Predicate locks (SSI) | No | `ampredlocks=false` `pg_fts_am.c:2126` |
| 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 `pg_fts_am_scan.c:2519-2525` |
| 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 |
| pg_repack 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:1521`) always routes a new document to the
**pending write buffer** — "stored verbatim ... and is searched directly at
scan time, so newly inserted rows are immediately visible to `@@@` without a
REINDEX" (`pg_fts_am.c:1515-1520`). 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:1552-1560`). 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`, `pg_fts_am.c:1405`) 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:1101-1114`):
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:2131`), 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` comment, `pg_fts_am_scan.c:1106-1113`).
The **fast count** is therefore the explicit
`fts_count(regclass, ftsquery)` (`pg_fts--1.18--1.19.sql:9`), 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

**pg_repack does not apply to an index AM** — it rewrites the *table*, which is
orthogonal to `fts`. What pg_fts offers for compaction:

- **VACUUM + `fts_merge()`** — the size-tiered segment merge. VACUUM's
  `amvacuumcleanup` folds pending docs into a segment and merges;
  `fts_merge(regclass)` (`pg_fts--1.12--1.13.sql:9`) forces it on demand.
  `bm25_merge_segments` (`pg_fts_am.c:1337`) coalesces similarly-sized segments
  and **physically drops tombstoned docs** (`pg_fts_am.c:25`,
  `bm25_bulkdelete` comment `pg_fts_am.c:1848-1851`).
- **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--1.12--1.13.sql:6-8`). 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--1.11--1.12.sql:9`).
- 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:1147`, `pg_fts--1.15--1.16.sql:35`).
- Fast MVCC-correct bulk count `fts_count` (`pg_fts--1.18--1.19.sql:9`).
- Highlight / snippet (`fts_highlight`, `fts_snippet`, `pg_fts--1.4--1.5.sql`).
- MVCC-correct tombstone deletes (`bm25_bulkdelete`, `pg_fts_am.c:1843`).
- Oversized-document handling (one-doc segments, `pg_fts_am.c:1552-1560`).
- 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:2127`; parallel-scan
  hooks all `NULL`, `:2161-2163`) → **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 (`pg_fts_am_scan.c:2519-2525`).

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--1.0.sql:108`) and `<=>` (`pg_fts--1.15--1.16.sql:18`), 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--1.5--1.6.sql:14-15`) 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.

**(c) Physical replication + crash recovery ARE safe.** Every page write goes
through GenericXLog (14 `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 on a premise correction:* the actual WAL write-site count is **14
`GenericXLogStart` cycles** (12 in `pg_fts_am.c`, 2 in `pg_fts_trgm_index.c`),
not 27 — but the underlying claim holds and is stronger: **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.