skip to content

Full-Text & Trigram Search

PostgreSQL's built-in search: tsvector matched by tsquery and ranked by ts_rank, plus pg_trgm similarity for fuzzy and LIKE '%x%' matching. Interviewers probe when to move to a search engine.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL full-text search, what are tsvector and tsquery, and what does the @@ operator check between them?

level: juniorimportance: must knowfreq 62%

answer

  1. document versus question
  2. sorted lexemes with positions
  3. lexemes joined by & | ! <->
  4. a cast skips normalisation

basics

~20 s

A tsvector is a document reduced to sorted, normalised lexemes with their positions; a tsquery is normalised lexemes joined by &, |, ! and <->. The @@ operator returns true when the tsvector satisfies the tsquery's expression.

solid answer

~40 s

`to_tsvector('english', text)` parses the text, drops stop words, stems what is left and stores each distinct **lexeme** once, sorted, with its positions: `to_tsvector('english', 'Reset your passwords')` gives `'password':3 'reset':1`. A `tsquery`, built by `to_tsquery`, `plainto_tsquery` or `websearch_to_tsquery`, normalises the search terms the same way and combines them with `&` (AND), `|` (OR), `!` (NOT) and `<->` (FOLLOWED BY). `tsvector @@ tsquery` is true when the document's lexemes satisfy that expression, so `resetting passwords` finds `Reset your passwords`. Both sides must be normalised with the same configuration: a literal cast such as `'reset passwords'::tsvector` stores `passwords` unstemmed and will not match the query lexeme `password`. Matching is on whole lexemes, not substrings.

code

sql · 11 lines
sql
SELECT to_tsvector('english', 'Reset your passwords');
-- 'password':3 'reset':1

SELECT to_tsquery('english', 'resetting & passwords');
-- 'reset' & 'password'

SELECT to_tsvector('english', 'Reset your passwords')
       @@ to_tsquery('english', 'resetting & passwords');   -- true

SELECT 'reset passwords'::tsvector
       @@ to_tsquery('english', 'password');                -- false

go deeper

for a junior

Recall the pair: tsvector is the processed document, tsquery the processed question, and @@ asks whether one satisfies the other. Be ready to show to_tsvector output with lexemes and positions.

for a middle

Explain the pipeline from parser to dictionaries to sorted lexemes with positions, and why both sides need the same configuration. The cast-versus-to_tsvector trap is the classic probe.

for a senior

Connect the basics to production choices: always pass the configuration explicitly, store or index the tsvector, and know that positions are what phrase queries and cover-density ranking depend on.

for a principal

Frame built-in search as a trade: it keeps search transactional and inside the database, at the price of PostgreSQL's simpler language analysis and ranking. Know where that line sits before committing a product to it.

## Why PostgreSQL has dedicated search types A software vendor's help centre holds articles such as *How to reset your password* or *Exporting invoices to CSV*. A user types `resetting passwords` and expects the first one. `title LIKE '%resetting passwords%'` finds nothing, because no title contains that exact string. PostgreSQL's built-in **full-text search** compares *normalised words* instead of characters, using two data types and one operator: - **`tsvector`**: the searchable form of a document; - **`tsquery`**: the searchable form of a question; - **`@@`**: the match operator, answering "does this document satisfy this query?" ## tsvector: the document as sorted lexemes with positions `to_tsvector(config, text)` runs the text through a **text search configuration** such as `english`. The configuration's parser splits the text into typed tokens, and its dictionaries turn each token into a **lexeme** (a normalised word) or discard it: 1. The parser classifies each token: ASCII word, word with non-ASCII letters, number, URL, e-mail address and so on. 2. **Stop words** such as `your` and `the` are discarded. 3. The remaining words are lower-cased and **stemmed**, so `passwords` becomes `password` and `resetting` becomes `reset`. 4. Each distinct lexeme is stored once, in sorted order, with the **positions** where it occurred. `to_tsvector('english', 'Reset your passwords')` returns `'password':3 'reset':1`. Position 2 is absent because `your` was a stop word, yet it still counted: positions follow the original word order, which is what makes phrase search and proximity ranking possible later. A `tsvector` does **not** keep the original text, so a result snippet has to come from the source column. ## tsquery: lexemes joined by operators A `tsquery` holds lexemes combined with operators: - `&` (AND): both sides must be present; - `|` (OR): at least one side must be present; - `!` (NOT): the lexeme must be absent; - `<->` (FOLLOWED BY): the right lexeme must sit immediately after the left one; the general form `<N>` requires the positions to differ by exactly N. A lexeme can also carry a weight restriction (`password:A`) or a prefix marker (`reset:*`). Four functions build a `tsquery` from text, and each normalises its terms with the same configuration machinery as `to_tsvector`: `to_tsquery` (expects operator syntax), `plainto_tsquery` (ANDs plain words), `phraseto_tsquery` (chains words with `<->`) and `websearch_to_tsquery` (accepts search-box syntax). The same caution as for documents applies: a cast such as `'reset & passwords'::tsquery` takes the words literally and never stems them. ## The @@ operator and its shortcuts | Expression | Equivalent to | Typical use | |---|---|---| | `tsvector @@ tsquery` | itself | a stored or indexed `tsvector` column | | `tsquery @@ tsvector` | the same, operands swapped | style preference | | `text @@ tsquery` | `to_tsvector(text) @@ tsquery` | quick ad-hoc checks | | `text @@ text` | `to_tsvector(text) @@ plainto_tsquery(text)` | quick ad-hoc checks | The two `text` forms call the one-argument `to_tsvector`, which uses whatever **`default_text_search_config`** holds in the session. Its built-in default is `pg_catalog.simple`, although `initdb` usually writes a language configuration matching the cluster's `lc_ctype` into `postgresql.conf`. Production queries therefore pass the configuration explicitly. ## The normalisation trap The most common beginner bug is building one side without normalisation: - **A cast is not a parse.** `'reset passwords'::tsvector` takes the words at face value: the lexemes are `passwords` and `reset`, unstemmed. `'reset passwords'::tsvector @@ to_tsquery('english', 'password')` is **false**, because the query lexeme is `password`. - **Mismatched configurations.** A document indexed with `english` (stemmed) and a query built with `simple` (only lower-cased) disagree on every inflected word. - **Substring expectations.** `@@` matches whole lexemes. A query for `pass` does not match `password` unless you write the prefix form `pass:*`; for character-level fuzziness PostgreSQL offers `pg_trgm` instead. ## Where the neighbours take over Making `@@` fast on a large table needs a GIN or GiST index on the `tsvector`; that belongs to the PostgreSQL indexing material on GIN and GiST. Why stemming and stop words exist at all, and how an inverted index stores lexemes, is general search theory (text analysis and inverted indexes). The PostgreSQL-specific point to carry into an interview: **both sides of `@@` must go through the same configuration, and positions are what make phrases and proximity work.**

  • Why does WHERE body @@ 'password' work without calling to_tsvector at all?
    The `text @@ text` form is shorthand for `to_tsvector(body) @@ plainto_tsquery('password')`, using the session's `default_text_search_config`. It is convenient for ad-hoc checks but re-parses every row, depends on a session setting, and its one-argument `to_tsvector` does not match an index built on `to_tsvector('english', body)`, so production queries spell out the configuration.
  • How does @@ differ from body ILIKE '%reset%' for the same help-centre table?
    `ILIKE` matches characters: `'%reset%'` also hits `preset`, a search for `'%resetting%'` misses an article that only says `reset`, and it supports no AND, OR or phrase logic and gives no relevance score. `@@` matches stemmed lexemes, skips stop words, supports AND, OR, NOT and phrase operators, and its `tsvector` positions feed ranking.

  • Casting a string to tsvector stems it and removes stop words automatically.
  • The @@ operator matches substrings, just like LIKE '%term%'.
  • A tsvector keeps the original text, so results can be displayed from it.
  • Stop words disappear without trace, so word positions shift down to close the gap.
  • Word order is lost in a tsvector, so phrase search needs a different feature.
open as a page

With PostgreSQL's pg_trgm extension, how do similarity() and the % operator match misspelled search terms, and what sets the cut-off?

level: middleimportance: must knowfreq 52%

basics

~10 s

pg_trgm splits strings into lower-cased three-character trigrams and similarity() returns shared trigrams divided by all distinct trigrams, from 0 to 1. The % operator is true when that score reaches pg_trgm.similarity_threshold, default 0.3.

open as a page

In PostgreSQL full-text search, what does a text search configuration control, and how do you add unaccent so 'parametres' matches 'paramètres'?

level: middleimportance: should knowfreq 33%

basics

~20 s

A text search configuration binds a parser to a dictionary list per token type, which turns tokens into lexemes. Copy one such as french and ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem, so accents are stripped before stemming.

open as a page

In PostgreSQL, why should a help-centre search box pass user input to websearch_to_tsquery rather than to_tsquery, and what syntax does it accept?

level: middleimportance: should knowfreq 46%

basics

~20 s

to_tsquery expects tsquery operator syntax and raises a syntax error on ordinary input such as two bare words. websearch_to_tsquery never raises: plain words are ANDed, quoted text becomes a phrase, 'or' becomes OR and a dash becomes NOT.

open as a page

For a PostgreSQL 18 help-centre search, how do you store a weighted tsvector, then rank with ts_rank_cd and highlight with ts_headline without slowing every query?

level: seniorimportance: should knowfreq 41%

basics

~10 s

Concatenate setweight(to_tsvector('english', title), 'A') with lower-weighted summary and body in a generated column marked STORED, since 18's default VIRTUAL cannot be indexed. Rank matches with ts_rank_cd, then call ts_headline only on the final page.

open as a page

When does PostgreSQL's built-in full-text and trigram search stop being enough, so that you would move a help-centre search to a dedicated search engine?

level: seniorimportance: should knowfreq 38%

basics

~20 s

Stay in PostgreSQL while keyword matching, field weights and modest corpus size are enough. Move when you need corpus-aware relevance (ts_rank has no IDF or BM25), facets, typo tolerance and language analysis at scale, or search load isolated from the database.

open as a page