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 WHERE query and the ORDER BY query need not be the same. WHERE filters and ORDER BY only ranks: the scan returns every row the WHERE clause admits (every @@@ condition on the index applies), ordered by the ORDER BY query's BM25 score; a row holding none of the ranking terms scores 0 (distance 1) and follows every scored row:

SELECT id FROM docs
  WHERE to_ftsdoc('english', body) @@@ to_ftsquery('english', '"query planner"')
  ORDER BY to_ftsdoc('english', body) <=> to_ftsquery('english', 'index | scan')
  LIMIT 10;

The ranking query's boolean operators do not filter here: its terms are scored, as the <=> operator scores them.

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.

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. Any combination of plain terms, phrases, OR groups, nested proximity, AND and NOT is then answered directly from those positions, without heap rechecks:

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. Modifier and weighted terms use exact heap rechecks, as does a document whose positions the index could not store (a posting block too large for one page) or that exceed the evaluator's bound -- that document alone. Results agree with either position setting. The earlier introduction of stored positions changed the on-disk format (BM25 v2 → v3) and required REINDEX. The proximity syntax described here does not change that format.

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:

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.

API.  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 positional index queries. Weight labels apply to plain terms and prefixes (vacuum*:A or vacuum:*A). Fuzzy+weight combinations and regex modifiers raise 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:

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. Since 1.11.0 a plain CREATE INDEX or REINDEX, which holds the index exclusively, runs that compaction itself at the end of the build, so the new index is already at its packed size. After CREATE INDEX CONCURRENTLY, run fts_vacuum once, and do not judge the index's size before you do: a concurrent build cannot reuse pages it freed while other sessions may be reading.

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. VACUUM's own compaction only packs live data into older free pages and does not run the extending vacate rewrite (the pack can still extend the file a little when its room estimate falls short, as segment merges can). Truncating the tail it frees needs a moment when no other session holds a lock on the index, and a parallel VACUUM does not truncate at all. Until then the space stays reusable, and the next VACUUM that gets the lock returns it. (From 1.11.0 to 1.14.0, a VACUUM that could not truncate extended the file instead; fixed in 1.14.1.) 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.

Automatic maintenance.  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.

fts_merge() vs fts_vacuum().  Call fts_merge(index) to optimize now: it folds the pending buffer and merges live segments into one, e.g. after heavy churn, or after a build too large for pg_fts.build_collapse_max_mb (which leaves the workers' segments unmerged). 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 CREATE INDEX CONCURRENTLY, a bulk load, or heavy update churn) without a REINDEX. A plain CREATE INDEX or REINDEX already compacts at the end of the build (1.11.0). A single fts_vacuum() call reclaims most of the space; a second converges to the floor.

Transient space during compaction.  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.

Upgrading an index written by 1.10.0 or earlier.  ALTER EXTENSION pg_fts UPDATE does not repair an index that took writes under 1.10.0 or earlier. If, before the upgrade, an insert was rolled back or failed part-way, or a row was deleted while its entry was still pending, that index can return rows that do not match, as exact matches. If a pending flush ran while rows were being inserted, it can also miss rows. VACUUM, fts_merge() and fts_vacuum() repair neither. Rebuild the index with REINDEX INDEX (or REINDEX INDEX CONCURRENTLY to keep it online) whatever a check says: the check below finds false rows only for the terms it probes. It must run as a plain index scan, with sequential and bitmap scans off: a bitmap of a common term goes lossy and its recheck hides the false rows. Use plain terms (a prefix, fuzzy, regex or weighted term is rechecked by design and reads 0), and EXPLAIN it once to confirm an Index Scan on the fts index. Any non-zero count means it needs the rebuild (CHANGELOG 1.14.2 has the check for missing rows too):

SET enable_seqscan = off;
SET enable_bitmapscan = off;
SELECT count(*) FROM (
  SELECT 1 FROM t
   WHERE to_ftsdoc('english', body) @@@ to_ftsquery('english', 'vacuum')
     AND NOT fts_match(to_ftsdoc('english', body), to_ftsquery('english', 'vacuum'))
  OFFSET 0) s;

Behavior on a read replica.  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.

Privileges.  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).

Behavior under continuous ingestion.  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.

These settings change only how fast a ranked query runs, never which rows it returns or in what order.

Gregory Burd.