pg_fts pg_fts provides full-text search with Okapi BM25 relevance ranking. It adds two data types — ftsdoc (an analyzed document) and ftsquery (a parsed query) — the @@@ match operator and the <=> relevance-distance operator, and a dedicated fts index access method that answers both. Unlike the built-in tsvector/tsquery stack with a GIN index, pg_fts maintains the corpus statistics that BM25 ranking requires (document count, average document length, and per-term document frequency) inside the index, and its posting lists carry the term frequency and document length needed to score a match — so relevance ranking is computed from the index without re-reading the heap. This makes ranked top-k retrieval (ORDER BY doc <=> query LIMIT k) an index scan that stops early, rather than a scan-and-sort of every match. This module is under active development. Its on-disk format and SQL interface may change between versions; an ALTER EXTENSION pg_fts UPDATE that changes the on-disk format requires a REINDEX of existing fts indexes. ftsdoc An analyzed document: a sorted list of terms, each with its term frequency and token positions, plus the document length. Produced from text with to_ftsdoc. A fts index stores the analyzed postings derived from an ftsdoc, not the original text. ftsquery A parsed query. Supports boolean & (AND), | (OR), ! (NOT); quoted phrases "a b c"; NEAR(a b, k) proximity; prefix term*; fuzzy term~k (edit distance k, default 2); and regular expressions /re/. Produced with to_ftsquery or the input syntax ('a & b'::ftsquery). A leading - is also accepted as NOT (a -b excludes b), but only in prefix position. A -, . or / that sits between two word characters is part of the term, so pkg-config, foo/bar and python3.14 each search for the word they look like rather than being split into an expression. This matches how the document side tokenizes them, and PostgreSQL's own parser, which classifies those as asciihword, file and file respectively. A trailing separator is dropped, as it is by to_tsvector: c++ searches for c. OperatorDescription ftsdoc @@@ ftsquery → boolean Does the document match the query? ftsdoc <=> ftsquery → float8 Relevance distance 1/(1+score) (a smaller distance is a higher BM25 score). Used in ORDER BY to rank; the fts index answers this as an ordering scan. Create an index over an ftsdoc expression: CREATE INDEX docs_bm25 ON docs USING fts (to_ftsdoc('english', body)); The expression form is the recommended external content model: the text lives in the table, and the index derives the analyzed ftsdoc from it, so no document copy is stored in the index. The index answers a boolean match as a bitmap scan: SELECT count(*) FROM docs WHERE to_ftsdoc('english', body) @@@ to_ftsquery('english', 'postgres & index'); and a ranked top-k as an ordering index scan with no Sort node: SELECT id FROM docs WHERE to_ftsdoc('english', body) @@@ to_ftsquery('english', 'postgres') ORDER BY to_ftsdoc('english', body) <=> to_ftsquery('english', 'postgres') LIMIT 10; The index is a set of immutable segments plus a small pending write buffer. An INSERT appends to the pending buffer and is immediately searchable without a REINDEX. A flush — performed automatically by VACUUM, or on demand by fts_merge — folds pending documents into a segment; a size-tiered merge coalesces segments and physically drops tombstoned (deleted) documents. All page writes go through GenericXLog, so the index is crash-safe and replicated on a physical standby. Deletes are handled MVCC-correctly: VACUUM records a per-segment tombstone, and scans and counts exclude tombstoned documents. The fts index is not covering (it stores postings, not the source document), so it does not support index-only scans. A fast, visibility-map-aware count is available as fts_count. to_ftsdoc(config regconfig, text text) → ftsdoc Analyze text into an ftsdoc. With a config, the text is parsed and normalized through that text search configuration (stemming, stop words); without one, a simple whitespace/fold analysis is used. to_ftsquery(config regconfig, text text) → ftsquery Parse a query string. With a config, query terms are normalized through that configuration (case-folding, stemming, and stopword removal) so they match the way documents were analyzed by to_ftsdoc. A term that the configuration treats as a stopword is dropped from the query — for example to_ftsquery('english', 'the & postgres') reduces to just postgres, and a query of only stopwords becomes empty (matching nothing) — exactly as to_tsquery behaves, so a stopword can never silently zero out a boolean query. Prefix (term*), fuzzy (term~k), and regex (/re/) terms are matched literally and are never stopword-normalized. Note that and, or, not, and near are reserved query operators and cannot currently be searched for as literal words (even quoted); this is a rare limitation for natural-language corpora, where they are stopwords anyway. fts_count(index regclass, query ftsquery) → bigint Count the documents matching query using the given fts index, in bulk, without per-row executor overhead. Visible rows are counted via the visibility map, one lookup per run of matches on the same heap page; the heap is probed only for pages not marked all-visible. A single plain term is answered from the dictionary's document frequency alone — no posting decode and no heap access at all — when the index carries no unmerged pending documents, no segment has tombstones, and the whole heap is marked all-visible (typically: after a VACUUM, on a corpus that is not being written). The fast path refuses and falls back to the exact count if any of those conditions fails, or if the query is a prefix, fuzzy, regex, weighted, or multi-term expression. Both paths return the same number; the regression suite asserts that for every gate individually against a heap-only ground truth. fts_search(index regclass, query ftsquery, k int DEFAULT 10) → setof record Return the top k visible documents by BM25 score as (ctid, score) rows, computed from the index. fts_anomalous_docs(index regclass, k int DEFAULT 100, max_df int DEFAULT NULL) → setof record Return the top k most lexically anomalous documents in the index — those containing globally rare terms — as (ctid, score, rarest_term, min_df) rows. A document's score is the maximum IDF over its terms (driven by its single rarest term). The scan walks only the low-document-frequency tail of the dictionary, skipping any term whose global document frequency exceeds max_df before decoding a posting; max_df defaults to a small fraction of the corpus (max(N/1000, 1)) when omitted. This is a lexical (not semantic) heuristic; the returned ctids are index-resident heap pointers (join back and filter for visibility if needed), and per-segment tombstones are honored. fts_merge(index regclass) → boolean Flush the pending write buffer into a segment and merge every live segment into one now, instead of waiting for VACUUM. Returns whether any work was done. This compacts the segment directory but does not shrink the physical file; use fts_vacuum to reclaim disk space. Requires ownership of the index and errors on a read replica (it writes WAL, so it cannot run during recovery). fts_vacuum(index regclass) → boolean Flush pending documents, compact to a single segment, and reclaim the physical space of superseded blocks by relocating live pages to the front of the index file and truncating the free tail back to the operating system — shrinking an index that has grown larger than its live contents (for example after a bulk build or heavy update churn), without a REINDEX. Returns whether any work was done. A single call reclaims most of the space; a second call converges to the fully compacted size. Runs automatically during VACUUM when the index is substantially bloated. Takes AccessExclusiveLock on the index (like REINDEX), requires ownership of the index, and errors on a read replica (it writes WAL, so it cannot run during recovery). fts_bm25(doc ftsdoc, query ftsquery, n_docs float8, avgdl float8, dfs float8[] DEFAULT NULL) → float8 The BM25 score of doc for query given the corpus size n_docs, average document length avgdl, and per-term document frequencies dfs. fts_bm25_opts exposes the tuning knobs and selectable variants (lucene, robertson, atire, bm25+, bm25l) matching the rank_bm25 reference implementations. fts_bm25f(docs ftsdoc[], query ftsquery, ...) → float8 The BM25F score across multiple fields (for example title and body) with per-field weights. fts_index_stats(index regclass), fts_index_df(index regclass, query ftsquery), fts_index_nsegments(index regclass) Introspect the index: corpus statistics (document count, average length, distinct terms); the per-term document frequencies used for IDF; and the current live segment count. fts_highlight(doc text, query ftsquery, ...), fts_snippet(doc text, query ftsquery, ...) Result presentation: wrap matched query terms in the source text, and return the best-matching window of the text. tsquery_to_ftsquery(query tsquery) → ftsquery Convert a tsquery to an equivalent ftsquery (boolean operators and the <-> phrase operator are carried over). There is also an assignment cast, so an existing tsquery value can be used with @@@. This is a migration aid; queries, index DDL, and ranking calls must still be rewritten to the pg_fts API — it is not a transparent replacement for the tsvector stack. CREATE EXTENSION pg_fts; CREATE TABLE docs (id serial PRIMARY KEY, body text); INSERT INTO docs (body) VALUES ('the quick brown fox'), ('a quick red fox jumps'), ('lazy brown dogs sleep'); CREATE INDEX docs_bm25 ON docs USING fts (to_ftsdoc('english', body)); -- boolean match SELECT id FROM docs WHERE to_ftsdoc('english', body) @@@ to_ftsquery('english', 'quick & fox'); -- ranked top-2 by relevance SELECT id FROM docs WHERE to_ftsdoc('english', body) @@@ to_ftsquery('english', 'fox') ORDER BY to_ftsdoc('english', body) <=> to_ftsquery('english', 'fox') LIMIT 2; -- fast count SELECT fts_count('docs_bm25', to_ftsquery('english', 'brown')); By default the bm25 index stores no token positions, so a phrase ("a b") or NEAR query generates the term-conjunction candidate set and then rechecks adjacency against the heap document, re-deriving the ftsdoc for each candidate. For a common two-word phrase whose conjunction set is large, that recheck dominates. Building the index WITH (positions = on) stores per-token positions in the posting lists, so phrase and NEAR are answered directly from the index -- with no heap access and no recheck -- bringing phrase count and match to posting-scan speed: CREATE INDEX docs_bm25 ON docs USING fts (to_ftsdoc('english', body)) WITH (positions = on); The trade-off is index size: positions roughly double the posting bytes on high-term-frequency corpora (little effect when terms occur once per document). Positions are decoded lazily, so plain ranked / boolean / count queries are unaffected whether positions are on or off. Phrase and NEAR are always correct either way; positions = on only makes them fast (index-only). This is an on-disk format change (BM25 v2 → v3); an index built by an older release must be REINDEXed. The size of that difference is easy to underestimate. Measured on 2,188,038 Wikipedia articles (16 vCPU, 128 GB, warm cache), with the phrase "united states" matching 361,465 documents: querydefault (positions = off)positions = on ranked top-108,385 ms229 ms (36×) exact count(*)7,170 ms132 ms (54×) index size1,421 MB2,626 MB (1.85×) In other words the default is measured in seconds at this scale, because the adjacency recheck performs a heap probe per candidate. If an application issues phrase or NEAR queries against a large table, enable positions = on when the index is created. See bench/NOTE_PHRASE_PROFILE_2026-09-06.md. Note that phrase syntax uses double quotes: to_ftsquery('english', '"united states"') parses to ('unit' <-> 'state'). Single quotes yield a plain conjunction ('unit' & 'state'), which is a different (much larger) match set. Regex (/re/) and long fuzzy (term~k) queries match many dictionary terms. The index can build a per-segment trigram tier that narrows the candidate terms for these queries; by default it is off, and such queries fall back to a full dictionary scan (always correct, just slower on regex / long fuzzy). Build WITH (trigrams = on) for a regex- or long-fuzzy-heavy workload: CREATE INDEX docs_bm25 ON docs USING fts (to_ftsdoc('english', body)) WITH (trigrams = on); The trade-off is index size (the trigram tier was ~18 percent of the index in one 2.19M-document measurement). Results are identical whether trigrams are on or off; the option only affects the speed of regex and long fuzzy queries. This is not an on-disk format change — both settings read on any release — so it needs no REINDEX to change (rebuild the index to actually add or drop the tier). Like tsvector's A/B/C/D weight labels, an ftsdoc can carry per-field provenance so a query term restricts itself to a field (zone). Tag a sub-document with a weight and concatenate the labelled parts: CREATE INDEX msg_fts ON messages USING fts (( to_ftsdoc('english', subject, 'A') || to_ftsdoc('english', from_addr, 'B') || to_ftsdoc('english', body, 'C') )) WITH (positions = on); SELECT * FROM messages WHERE (to_ftsdoc('english', subject, 'A') || to_ftsdoc('english', from_addr, 'B') || to_ftsdoc('english', body, 'C')) @@@ to_ftsquery('english', 'vacuum:A & tgl:B'); -- vacuum in subject, tgl in from A query term followed by : and one or more of the labels A, B, C, D (e.g. vacuum:A or x:AB) matches only occurrences carrying one of those labels — exactly as to_tsquery('english', 'vacuum:A') does. A term with no label matches any zone (unchanged behavior). BM25 scoring stays document- level: a zone filter changes which documents match, not how a matching one scores. to_ftsdoc(config, text, weight "char") tags every token with A/B/C/D; setftsweight(doc ftsdoc, weight "char") relabels an existing document (like setweight); ftsdoc || ftsdoc concatenates labelled sub-documents, re-basing token positions and preserving each side's labels. to_ftsdoc(tsvector) also carries the tsvector's own weights. Field restriction needs the document to carry token positions (the labels ride on positions), so build the index WITH (positions = on) for field-restricted ranked or count queries. Weight labels apply to plain terms; combining a label with a prefix, fuzzy, or regex term (vacuum:A*) is a syntax error. This is not an on-disk index format change: labels live only in the stored ftsdoc value, and a field-restricted query is answered via the heap recheck. An index built before weight support (or from an unlabelled to_ftsdoc) keeps working with no REINDEX — every position reads as label D, so term:D matches it and term:A does not, and unlabelled queries are unchanged. To add field provenance, rebuild that table's ftsdoc from labelled to_ftsdoc(...,weight) || ... documents — opt-in per table, never a forced global reindex. Building an index over a large corpus of long, high-vocabulary documents (for example full email bodies or source code — many distinct, low-frequency terms per document) has three cost drivers: build memory, build time (dominated by per-document text analysis), and merge time. Build time and throughput. The largest cost on a long-document corpus is usually the per-document text analysis itself (tokenizing and, for a language configuration such as english, stemming every token). This work is inherent to the text-search configuration, is proportional to the total token count, and for very long documents dominates everything the index does. It is also embarrassingly parallel: set max_parallel_maintenance_workers (and enough max_parallel_workers / max_worker_processes) so the analysis runs across cores — on a many-core host this is the single biggest reduction in wall-clock build time. Keep the memory formula above in mind when choosing the worker count (each participant holds its own budget). A serial build (max_parallel_maintenance_workers = 0) analyzes documents one at a time and, on a multi-gigabyte corpus, can legitimately run for a long time before the first segment is flushed — the buffer fills only after a whole budget's worth of (large) documents. During that window the segment count does not change and nothing is written yet; the build emits a LOG-level progress line as documents are analyzed and at each segment flush (set log_min_messages to log or lower to see them), so a long build can be distinguished from a stuck one. Memory. Peak build memory is bounded by roughly shared_buffers + (max_parallel_maintenance_workers + 1) x 2 x maintenance_work_mem. Size maintenance_work_mem and max_parallel_maintenance_workers so that figure fits your host (and any cgroup MemoryMax). A larger maintenance_work_mem also flushes fewer, larger segments during the scan, which reduces the amount of merging afterwards — a good trade when you have RAM to spare. How much it matters is easy to underestimate, so here it is measured on 2,188,038 Wikipedia articles (16 vCPU, 128 GB; bench/RESULTS_GATING_2026-09-09.md). Merge is the fts_merge that consolidates whatever the build left: maintenance_work_mem build segments after build follow-up merge final index 64 MB (the PostgreSQL default)475 s8216 s8,613 MB 256 MB367 s6223 s5,793 MB 1 GB523 s1none needed4,605 MB 2 GB527 s1none needed4,323 MB At 1 GB and above this corpus builds straight to a single segment, so the post-build merge disappears entirely — saving both the ~220 s merge and about 4 GB of transient index size. At the 64 MB default the same corpus needs 8 segments plus a 216 s merge and lands twice as large. If a build is followed by a long merge, raising maintenance_work_mem is the first thing to try. Parallel builds: faster, same final size. max_parallel_maintenance_workers speeds a build up (464 s versus 523 s serial at maintenance_work_mem = 1GB on 2.19M articles) and, once fts_vacuum has run, produces an identically sized index — 1,420 MB either way, with identical match counts. What a parallel build leaves behind is more reclaimable residue, not a larger index: immediately after the build the file measures 5,365 MB versus 4,605 MB serial, and fts_vacuum collapses both to the same floor. (An earlier revision of this documentation reported a durable ~17% size penalty; that figure was measured before vacuuming and is withdrawn.) The residue is intentional. Merge output is allocated by extending the file so that a committed merge's freed input pages can never be handed out as the next merge's output while in-flight read chains still point through them; the alternative is a wrong read or a crash. Truncation then reclaims only a contiguous free tail, so pages freed underneath the final output survive until fts_vacuum's compaction pass relocates live data toward the front of the file. Live pages are ~98.8% full, so this is not a packing inefficiency. Run fts_vacuum once after a large build, and do not judge the index's size before you do. Unattended autovacuum holds the index bounded and reclaims after deletes; no scheduled maintenance is required. Measured at one million documents with autovacuum enabled and no manual maintenance whatsoever: five consecutive cleanups with nothing changed stayed flat at 511 MB, six rounds of insert-plus-delete churn stayed flat at 875 MB, and after deleting half the table cleanup brought the index from 875 MB to 106 MB (8.3×) with queries served throughout and results exact. Truncation is safe while the index is online under ShareUpdateExclusiveLock, because scans treat an out-of-range block as end-of-chain. Earlier releases grew on every vacuum pass. Two fixes on 2026-09-12 (a merge truncates the free tail it creates; cleanup truncates unconditionally before deciding whether a fuller repack is worthwhile) cut that by roughly 6×, and a further fix on 2026-09-13 removed the remainder at small scale: a compaction pass is now skipped when its free space is not yet reusable. Without that check, a pass running straight after a merge relocates the live data upward and reclaims nothing, because the pages the merge just freed are still visible to the pass's own transaction and so fail the recyclability test. See bench/RESULTS_P1_SCALE_AB_2026-09-13.md and bench/RESULTS_SELF_LIMITING_2026-09-12.md. fts_vacuum remains useful for a one-off tighter reclaim: it always performs the full vacate-and-pack rather than waiting for the bloat threshold, reaching roughly 3× smaller than the automatic steady state. Run it once after a bulk load or a mass delete if you want the index at its floor. It is not needed to keep the index from growing. Incremental inserts of LARGE documents can grow the index file far beyond its settled size. A pending document is stored verbatim, and one that does not fit in an 8 kB page is indexed immediately as its own one-document segment. Measured on a Wikipedia corpus, where 33% of documents exceed that threshold: inserting 200,000 rows into a settled 792 MB / 1,000,000-document index grew the file to 32 GB before any merge ran. fts_merge then added only about 7% more, and fts_vacuum returned the file to 971 MB. Reproduced at a second scale (250,000 docs + 50,000 inserts: 272 MB → 7.7 GB → 319 MB). This is corpus-dependent, not a general property: the effect scales with the fraction of documents larger than a page, so a corpus of short documents will not show it. If you bulk-insert large documents, run fts_merge and fts_vacuum periodically rather than accumulating pending data, and provision disk headroom accordingly. See bench/RESULTS_C2_INGEST_2026-09-11.md. Do not raise max_parallel_maintenance_workers to speed up a merge. fts_merge can take a parallel path, and it is slower than the serial one. Measured on 2,188,038 Wikipedia articles, merging an 8-segment 7,185 MB index (bench/RESULTS_PARALLEL_MERGE_2026-09-08.md): serial 230.6 s producing 8,606 MB, versus parallel 333.5 s producing 10,229 MB — 1.45x slower and 19% larger. One worker costs the same as three, so this is a fixed penalty for taking the parallel path rather than a scaling curve; a merge is sequential-I/O bound, and per-worker output streams pack pages independently. Results are unaffected (every run converged to one segment with identical match counts). There is also a trap worth knowing: at max_parallel_maintenance_workers = 8 on the test host the workers registered, started, and exited within about 2 ms, so the merge silently ran serially — which is why that setting looked fast. Do not infer from a fast merge that parallelism helped; check for parallel workers in pg_stat_activity if it matters. The safe configuration for merges is the default (leave the setting alone, or 0). Monitoring a build. The merge phase is not covered by pg_stat_progress_create_index (its blocks_done freezes once the scan finishes). Poll fts_index_nsegments(index) and fts_index_stats(index) (both work on an in-progress indisvalid = f index) to watch the segment count fall and the doc/term counts grow as merges complete, and set client_min_messages = debug1 (or log_min_messages = debug1) to see per-merge progress lines (merging N of M segments ... wrote merged segment (T terms, D docs) in S s). Partitioning. For a very large corpus you can partition the table and build a per-partition fts index; each partition's build and merge are correspondingly smaller. A query with a partitionwise plan fans out across the per-partition indexes and each is scored against its own partition's corpus statistics. BM25 scores are therefore comparable within a partition; if you need a single global ranking across partitions, prefer one whole-corpus index and the tiered-build behavior above. pg_fts is designed to run with little hands-on maintenance and to be safe on a managed PostgreSQL service (all page changes go through GenericXLog, so the index is crash-safe and replicates on a physical standby with no extra configuration). This section is the operator summary. Two things happen without operator action. Auto-merge: incremental inserts land in a small pending buffer and are folded into the main segments as they accumulate; a leveled, bounded-fan-in merge runs on the write path so the segment count stays bounded under continuous ingestion (it does not grow without limit). Auto-vacuum: ordinary VACUUM (autovacuum included) removes deleted documents from the index via the access method's bulk-delete + cleanup callbacks, and when the index is substantially bloated its cleanup gently compacts and reclaims space across passes under a lock that does not block reads. For most workloads you never need to call anything by hand. Call fts_merge(index) to optimize now: it folds the pending buffer and merges live segments into one, e.g. right after a parallel build (which leaves the workers' segments unmerged for speed) or after heavy churn. It compacts the segment directory but does not shrink the physical file. Call fts_vacuum(index) to reclaim disk: it additionally relocates live pages to the front of the index file and truncates the free tail back to the operating system, shrinking an index that has grown larger than its live contents (after a bulk build or heavy update churn) without a REINDEX. A single fts_vacuum() call reclaims most of the space; a second converges to the floor. fts_vacuum() and REINDEX rewrite the live data into fresh pages before freeing the old copy, like a table rewrite: provision enough free disk for both the old and the new copy transiently. fts_vacuum() takes AccessExclusiveLock on the index (like REINDEX); use REINDEX INDEX CONCURRENTLY if you need the rebuild to stay online. Reads work normally on a hot standby (the index is fully replicated). The maintenance functions fts_merge() and fts_vacuum() write WAL, so they cannot run during recovery: called on a replica they raise an error (cannot run during recovery) rather than doing damage. Run them on the primary; the effect replicates. Both also require the caller to own the target index. The value-level API (to_ftsdoc, to_ftsquery, fts_bm25, the operators and I/O functions) is available to PUBLIC. Two functions that emit indexed content by index OID — fts_search and fts_anomalous_docs — are revoked from PUBLIC; the index owner and superusers keep access, and an owner can grant them explicitly (for example GRANT EXECUTE ON FUNCTION fts_search(regclass, ftsquery, int) TO role). Inserts are cheap (pending buffer) and periodically merged; the write amplification is that of a tiered/leveled merge (each document's postings are rewritten a bounded number of times as small segments merge into larger ones), and the live segment count stays bounded. Deletes are marked and reclaimed by VACUUM; an old snapshot held open (for example continuous replica feedback) defers that reclaim and defers physical shrink, which is expected — results and corpus statistics stay correct, the index simply holds the deferred space until the horizon advances. Corpus statistics used for scoring count only live documents, so ranking is not biased by not-yet-reclaimed deleted rows. No index-only scan (the index is not covering); use fts_count for a fast count. Query execution (scan) is single-threaded (no parallel scan). The index build is parallel (amcanbuildparallel). Ranked results cover flushed segments; documents still in the pending write buffer are found by @@@ and counted by fts_count, but are ranked by <=> only after the next flush (VACUUM or fts_merge). fts_merge and the automatic merge compact the index logically (fewer segments, tombstones dropped) but do not shrink the physical file; use REINDEX to reclaim space. Gregory Burd.