skip to content

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.