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). 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 so they match the way documents were analyzed. 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; the heap is probed only for pages not marked all-visible. 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_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. 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. 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')); 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. Greg Burd.