Full-text search in PostgreSQL

From words to lexemes

A lexeme is the term left after preparing a word for search. With PostgreSQL’s built-in english configuration, mugs becomes mug, capitals become lower case, and common words such as the disappear as stop words. This reduction of word endings is called stemming. It helps related forms match; it does not discover that cup means something similar to mug.

to_tsvector prepares a document and returns a tsvector: its lexemes with the positions where they occurred. For The blue mugs, English preparation produces blue at position 2 and mug at position 3. The simple configuration instead retains the, blue and mugs, changing which requests match.

Removing The did not move blue into position 1. Those positions later let a phrase test the distance between words. The vector prints lexemes in sorted order, with all occurrences of the same lexeme collected together; it is not a sentence to read from left to right.

SELECT to_tsvector('english', 'The blue mugs');
-- 'blue':2 'mug':3

SELECT to_tsvector('english', 'The blue mugs')
       @@ plainto_tsquery('english', 'mug');
-- true

The right-hand value is a tsquery, a query over lexemes. Here it contains just mug, which the vector contains. Neither value is the original text. Keep that text for display and excerpts. Casting 'The blue mugs'::tsvector instead gives the literal lexemes 'The' 'blue' 'mugs', without stemming or positions. A cast reads an already prepared representation; it does not perform the work of to_tsvector.

Choosing a text search configuration

Use a configuration suited to the document language and vocabulary, and prepare requests compatibly. An English request for mug will not find the mugs lexeme kept by the built-in simple configuration. Simple can suit vocabulary whose written forms matter, but giving up stemming changes which requests match. Neither configuration is universally better.

Specify the configuration in stored expressions and queries. Omitting it makes preparation depend on default_text_search_config, a session setting. A different default can then change a request’s meaning. For a multilingual catalogue, retain the document’s language/configuration and decide which languages the interface searches; changing the query’s language alone does not translate a document.

When an expected word disappears, inspect preparation before rebuilding an index:

SELECT token, dictionary, lexemes
FROM ts_debug('english', 'The blue mugs')
WHERE alias <> 'blank';
-- The   | english_stem | {}
-- blue  | english_stem | {blue}
-- mugs  | english_stem | {mug}

The parser separates text into tokens and assigns token types. The configuration maps those types to dictionaries, which produce lexemes or discard a token. In this output, {} means The was recognised as a stop word. ts_debug exposes the decision that a missing-match investigation needs. An index cannot recover a lexeme preparation never produced.

Turning a search request into a query

The query constructor defines the search box’s language. For an ordinary box where all entered words are required, use plainto_tsquery. For quoted phrases, alternatives and exclusions, websearch_to_tsquery accepts a small web-style language. Choose that behaviour deliberately: a customer who types blue OR tea should know whether OR is an instruction or another word.

& means AND, | OR, and ! NOT. Parentheses group the conditions. :* in an explicit query matches a lexeme prefix, not arbitrary characters anywhere in the original text. Weight A will identify the product-name occurrences below.

to_tsquery expects query syntax and can reject malformed input. It suits a controlled advanced interface or a query assembled from validated choices. Plain and phrase constructors treat punctuation as text separators rather than recognising that syntax; web-style input recognises quotes, OR and a minus for exclusion, but not explicit weight or prefix labels. Its tolerance of malformed search syntax does not make SQL string concatenation safe. Bind the search input as SQL data, for example websearch_to_tsquery('english', $1), as in SQL at the application boundary.

Phrases and positions

'blue' & 'mug' matches Blue ceramic mugs and Mugs in blue. The phrase 'blue' <-> 'mug' matches neither: the first has an intervening word, and the second has the wrong order. Stemming still applies, so blue mug and blue mugs can satisfy the same phrase.

SELECT phraseto_tsquery('english', 'mugs for tea');
-- 'mug' <2> 'tea'

SELECT to_tsvector('english', 'Mugs with tea')
       @@ phraseto_tsquery('english', 'mugs for tea');
-- true

<2> requires the second lexeme exactly two positions after the first. For was removed, but its position counted. With is also a stop word here, so Mugs with tea passes the same test. A normalised phrase therefore proves an ordered lexeme sequence with specified gaps, not an exact character quotation or the identity of a removed word. Use a separate test on original text when the interface promises that stronger meaning.

Also decide what to do when preparation leaves no useful positive request. Empty text and English the produce an empty query, which matches nothing. numnode(query) = 0 detects that case.

A request such as -mug can match almost the whole catalogue, and it gives no positive key to narrow the search. querytree(query) = 'T' identifies requests with no useful index restriction. That includes broader combinations such as blue OR -mug. This shop asks the customer to add a positive search term, and browsing the catalogue is a separate interface. That is a product rule, not a syntax error.

Building the searchable document

Choose what one result represents before choosing its index. Here it is one product, with its name and description forming one searchable document. Orders and individual word occurrences do not become result rows.

A name hit should count more than a description hit. setweight labels each occurrence with A, B, C or D; the ranking function assigns a numerical weight to each label. Give names A and descriptions D, then combine their vectors with ||. The combined vector must retain where each occurrence belongs in the resulting positional sequence.

The right-hand vector’s positions shift by the largest position in the left-hand vector. This is a positional continuation, so a phrase can cross from name to description. It also differs from preparing concatenated source text when the left field ends in removed stop words: their trailing positions are no longer in that left vector. If phrases must remain inside one field, match the separate field vectors, or require a consistent field weight on each phrase term. Do not infer a boundary from a visual line in the application.

CREATE TABLE catalogue_search (
  product_id text PRIMARY KEY,
  name text NOT NULL,
  description text,
  stock integer NOT NULL,
  search tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('pg_catalog.english'::regconfig, name), 'A') ||
    setweight(to_tsvector('pg_catalog.english'::regconfig,
                         coalesce(description, '')), 'D')
  ) STORED
);

CREATE INDEX catalogue_search_gin
ON catalogue_search USING gin (search);

coalesce(description, '') makes an absent description contribute an empty vector. Without it, to_tsvector(NULL) returns NULL and the concatenation loses even a perfectly good name. STORED makes the generated vector occupy space in the row and be computed on writes, ready for matching and ranking.

Expression index or stored search vector

An expression index can instead index to_tsvector('english', ...) directly, without retaining a separate vector column. It saves that column’s storage. The query must use the indexed document expression with the same explicit configuration; relying on the session default is not equivalent. Any ranking or recheck which needs the vector must derive it again from the source.

A stored vector costs space but makes the prepared document available repeatedly. This article uses one because phrases, weights and ranking all need more than lexeme membership.

A generated expression can depend only on the current row, using immutable operations. It cannot join supplier names or tags from other tables. If those become part of a product’s document, choose a design that handles every contributing change, such as an explicitly maintained search table. A product-name trigger alone would miss a supplier rename. An asynchronously maintained copy also needs an explicit freshness contract.

Finding matches with a GIN index

The index-method article introduced an inverted index: a component key points to the rows containing it. For text, those keys are lexemes. The posting list for blue leads to documents containing blue; intersecting it with the list for mug finds documents containing both.

GIN’s built-in text-search class keeps lexeme keys, without their positions or weight labels. A document containing both blue and mug therefore remains a candidate even when another word separates them or they occur in reverse order.

The keys suffice for ordinary lexeme membership. The phrase must inspect the vector to check positions, and blue:A must check that a blue occurrence carries A. GIN still helps by excluding documents missing necessary lexemes. PostgreSQL performs the required rechecks; the application does not accept every candidate as a match.

On the native three-product fixture, an index-assisted blue:A query collected two candidates: P7 and P9. P7 has blue in its name, P9 only in its description. The heap recheck rejected P9. Selected fields from that observed plan are:

Bitmap Heap Scan on catalogue_search (actual rows=1.00 loops=1)
  Recheck Cond: (search @@ '''blue'':A'::tsquery)
  Rows Removed by Index Recheck: 1
  Heap Blocks: exact=1
  -> Bitmap Index Scan on catalogue_search_gin
       (actual rows=2.00 loops=1)
       Index Cond: (search @@ '''blue'':A'::tsquery)

Sequential scans were discouraged to expose GIN on this tiny fixture. With normal settings, the checked phrase lookup used a sequential scan. Neither observation recommends forcing a plan in an application. Heap Blocks: exact means exact tuple locations survived in the bitmap; it does not mean those tuples already passed the weighted predicate. A Recheck Cond line alone also does not count rejected rows. The parent article separates operator rechecks from lossy bitmaps and snapshot visibility.

GIN supplies neither the original document nor a relevance order. A query can find few lexeme candidates and still spend time fetching their vectors, testing phrases, checking stock and sorting scores.

Ranking the eligible results

A text match answers whether a product satisfies the query. A rank supplies an ordering signal among matches. Neither establishes whether the product may be returned. For this shop’s in-stock search, P7’s stock 0 makes it ineligible however good its text score is.

The request blue OR tea matches all three products. With the weighted document above, ts_rank_cd gives P7 1.2, P3 1.1 and P9 0.2. P7’s high score cannot make it eligible; the stock test must still apply before choosing the final two products.

WITH request AS (
  SELECT websearch_to_tsquery('english', $1) AS query
)
SELECT p.product_id, p.name,
       ts_rank_cd(p.search, r.query) AS rank
FROM catalogue_search AS p
CROSS JOIN request AS r
WHERE p.search @@ r.query
  AND p.stock > 0
  AND numnode(r.query) > 0
  AND querytree(r.query) <> 'T'
ORDER BY rank DESC, p.product_id
LIMIT 2;

For bound input blue OR tea, this returns P3, then P9. Put every required eligibility condition into this result’s selection: stock, visibility, tenant and access restrictions where applicable. The secondary product-ID order makes equal scores deterministic for the same data and query. It does not freeze results across later catalogue edits.

A subquery which first takes the top two text matches would retain P7 and P3. Filtering stock outside it then returns just P3; it cannot recover P9. An arbitrary limit on index candidates is worse: those candidates have no promised score order. A bounded final answer does not generally bound how many matching vectors PostgreSQL must score. The native ranking plan filtered P7 and sorted the two surviving rows before Limit.

What the relevance score measures

ts_rank ranks from matching-lexeme frequency; ts_rank_cd uses cover density, rewarding nearby query evidence. Both accept field weights. The default array is ordered D, C, B, A with values {0.1, 0.2, 0.4, 1.0}. A name occurrence therefore carries ten times a description occurrence’s weight in this setup, although the final ranking formula is more than a raw word count.

Separating blue and mugs reduced cover density. Repeating them raised both scores. Adding unrelated words left the unnormalised scores unchanged in the last row, while normalisation 2 divided by the represented document length and lowered the score. Choose that length penalty only if it improves the wanted ordering; a short product name is not automatically a better answer than a useful description.

Keep positions when using cover density: it ignores stripped lexemes and gives zero if none retain positions. Scores use local document/query information, rather than a corpus-wide measure of how rare a term is. A value 0.8 does not mean an 80% chance of relevance. Normalisation 32 transforms rank into rank/(rank+1); it changes the scale without changing that ordering. Treat scoring choices as hypotheses to check against useful results.

Showing why a result matched

After selecting the winners, ts_headline can choose an excerpt from original text and mark query terms. Do that work for the returned rows, rather than every candidate. It parses source text separately; a stored vector cannot reconstruct the excerpt.

SELECT ts_headline(
  'english', 'Blue ceramic mugs for tea.',
  phraseto_tsquery('english', 'blue mugs'),
  'StartSel=[[, StopSel=]], HighlightAll=true'
);
-- [[Blue]] ceramic [[mugs]] for tea.

This diagnostic text fails the adjacent phrase, yet both terms are highlighted. Highlighting shows term evidence, not proof that every phrase or weight restriction passed. Use @@ for selection. Headline output is also not guaranteed safe as HTML: escaping or sanitising untrusted source remains necessary. Plain-text markers displayed as text are one option; structured highlighting must keep source text from becoming executable markup.

Keeping search data current

A stored generated vector follows source-column writes. In a separate edit to P7’s description, removing its first Blue changes the vector and its GIN entries as part of that write. Blue remains in the name at weight A, but no longer occurs at weight D. After commit, a new reader sees the committed representation under its normal snapshot rules.

Changing the preparation rules is different. PostgreSQL does not revisit every stored document because a dictionary or configuration changed. For example, changing preparation so that new requests retain mugs rather than stem it to mug can make them disagree with old stored vectors derived from unchanged source text.

Rebuilding GIN over a stored vector reads that vector. If it still contains the old lexemes, the rebuilt index faithfully preserves the old meaning. Regenerate the vectors from source under the intended configuration; ordinary writes to that stored column’s source also maintain its index entries. For an expression index, rebuilding instead evaluates its document expression from the base row. The distinction is which representation the index is reading.

Changing the search configuration

Treat a language, dictionary, stop-word or field-composition change as a change of representation. First prepare representative documents and requests under the proposed rules. Check the resulting lexemes and expected matches. Then derive a new, identifiable vector/index version from source, account for writes while it is being populated, and switch matching queries with their compatible preparation. Keep the earlier version available until the switch and its checks are complete.

Changing the meaning of a named configuration in place can mix old stored documents with newly prepared requests. A separate configuration name helps identify the intended version, provided its dependencies are also kept under control. Merely copying a configuration does not isolate it from later changes to dictionaries that both versions reference.

Dictionary files add another step: sessions can cache their contents. PostgreSQL documents using ALTER TEXT SEARCH DICTIONARY, even with unchanged options, to make existing sessions reload a changed file. Reloading rules still does not recompute stored vectors. Check both fresh derivation and the materialised search data. Lock management, concurrent index builds, backfill races and switching live writers need the procedures in PostgreSQL schema changes under load.

Checking search quality and query work

Keep two comparisons separate. Search quality asks whether the right products match and appear near the top. Execution cost asks how much database work produces those results. A faster index cannot repair a stop word which removed an essential term, or a ranking rule which puts irrelevant products first.

Retain representative requests with expected matches and useful ordering judgements. For this projection the expected matches are:

• mugs finds P7.
• green tea finds P3 and P9.
• blue mugs as a phrase finds P7.
• blue OR tea, with stock eligibility, returns P3 and P9.

Include zero-result requests, phrases, exclusions, product codes and misspellings. A judgement such as “P3 should lead for green tea” is a search requirement to test, not something an index proves.

For result correctness, compare the full matching set from a fresh source derivation with the indexed stored representation, using the same configuration, document composition and query. Check both missing and extra IDs. Then compare the complete eligible score order before applying the same final limit. A complete scan with those same rules is a reference for implementation agreement; it cannot establish that the rules themselves are useful.

EXPLAIN (ANALYZE, BUFFERS)
SELECT product_id, ts_rank_cd(search, query) AS rank
FROM catalogue_search
CROSS JOIN websearch_to_tsquery('english', 'blue OR tea') AS query
WHERE search @@ query AND stock > 0
ORDER BY rank DESC, product_id
LIMIT 2;

On a representative corpus, compare a rare term with a common one, narrow and broad filters, and a phrase with simple membership. Count candidates, actual returned rows, index-recheck rejects, separate filter rejects and buffer accesses. Check how many rows reach the ranking and sort, and whether the sort spills. Reading and repairing PostgreSQL plans develops that diagnosis. The tiny catalogue’s sequential scan is sensible evidence about three rows, not a reason to discard GIN for a large search.

Measure writes too. Longer documents and more distinct lexemes add index memberships; stored preparation adds write work and space. GIN can defer updates through its pending list, so include later cleanup and search work rather than timing only an initial insert. The parent’s write-cost explanation follows that trade.

The catalogue SQL experiment creates temporary example rows and runs the matching and ranking cases in one transaction, then rolls them back. The drawings are models built from those checked meanings. The native GIN plan above used explicitly discouraged sequential scans to show a mechanism; it is not a benchmark or a guaranteed plan.

When the request needs another kind of search

Use the request’s meaning to choose the tool. For exact product code P7, compare the code column directly, with the required case rules. For arbitrary character fragments, use a substring predicate such as LIKE '%mug%'; pg_trgm offers GIN/GiST support for suitable pattern and similarity queries. Very short or non-discriminating patterns can still provide little index restriction.

A misspelling such as mgu does not become mug through stemming. Trigram similarity can supply spelling candidates, but its threshold and suggestions need evaluation. Domain synonyms require explicit dictionary/query rules or another retrieval design; built-in English preparation does not know every catalogue relationship.

A request for something to keep tea warm might be useful even when no document shares those words. Embeddings provide a different representation for that question. Vector search in PostgreSQL separates the embedding’s usefulness from exact or approximate neighbour retrieval. Combining lexical and vector candidates can be useful, but their scores have different meanings. Judge the combined final order against the search task instead of treating either score as a common probability.