Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 90 ยท Querying with PostgreSQL Features

Full-Text Search

Learn PostgreSQL full-text search: tsvector and tsquery, websearch_to_tsquery, ts_rank, ts_headline highlighting, GIN indexes, weights and language dictionaries.

Search boxes are everywhere, and LIKE '%word%' is the first thing most people try. It works for tiny tables, but it has three problems. It is slow, because a leading wildcard cannot use a normal index. It is literal, so searching for "query" misses "queries". And it has no notion of relevance: every match is equal, so the best result is not listed first.

PostgreSQL has a search engine built in. Full-text search breaks documents into normalized words (lowercased, stemmed, with filler words removed), stores them in a special type, and matches queries against that type, ranking results by relevance. With a GIN index it stays fast on millions of rows, and there is nothing to install or keep in sync.

Our in-browser SQLite does not include its own full-text module (FTS5), so every PostgreSQL search example below is read-only. We use a LIKE-based stand-in to run something live, and we are honest about where it falls short, which is exactly why full-text search exists.

From text to searchable words

Document Raw text such as a title and a body.
Normalize Lowercase, drop stop words, reduce to stems.
tsvector Sorted list of words with positions.
1 / 2

tsvector: the document

to_tsvector(config, text) converts text into a tsvector, a sorted set of lexemes (normalized words) with their positions. Stop words such as "the" and "a" are dropped, and words are reduced to their stems by the configuration you name, here english:

SQL
SELECT to_tsvector('english', 'The quick brown foxes jumped');
--  'brown':3 'fox':4 'jump':5 'quick':2

"The" disappeared (but position 1 is still counted), "foxes" became fox and "jumped" became jump. A query for "jumping" will reduce to the same jump lexeme, which is how one search term finds many word forms.

tsquery and the @@ operator

A tsquery holds the normalized search terms combined with & (and), | (or), ! (not) and <-> (followed by). The operator @@ asks whether a tsvector matches a tsquery:

SQL
SELECT to_tsvector('english', 'Running queries safely')
       @@ to_tsquery('english', 'run & query');      -- true

SELECT to_tsquery('english', 'running & queries');   -- 'run' & 'queri'

You can see that queries stems to queri, an odd-looking but consistent form, since the document side goes through the same stemmer. Prefix matching uses :*, for example to_tsquery('english', 'inde:*').

to_tsquery expects strict syntax and raises an error on user text like foo bar!. PostgreSQL offers friendlier parsers for what people actually type:

Function Input style Meaning
plainto_tsquery fast index all words, joined by AND
phraseto_tsquery fast index the words next to each other, in order
websearch_to_tsquery "full text" -mysql or postgres quoted phrases, - for NOT, or for OR

websearch_to_tsquery is the one to put behind a search box. It never raises a syntax error, whatever the user types, and it understands the conventions people know from web search engines.

The match, in practice

Put the pieces together. This is the PostgreSQL version of a search over an articles table:

SQL
SELECT id, title
FROM articles
WHERE to_tsvector('english', title || ' ' || body)
      @@ websearch_to_tsquery('english', 'index "b-tree" -vacuum');

Because the document is converted on every row, this works but scans the whole table. To make it fast, store the vector and index it. A generated column keeps it in step with the text automatically:

SQL
ALTER TABLE articles ADD COLUMN search tsvector
  GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(body,  '')), 'B')
  ) STORED;

CREATE INDEX articles_search_idx ON articles USING GIN (search);

SELECT id, title
FROM articles
WHERE search @@ websearch_to_tsquery('english', 'index tree');

Two details matter here. First, the configuration is a literal ('english'). The one-argument form to_tsvector(text) depends on a setting, so it is only STABLE and cannot be used in an index or generated column. Second, setweight labels the vector's words A, B, C or D, so matches in the title can count more than matches in the body when you rank. Indexes get their own chapter, including GIN and GiST indexes.

If you would rather not store the column, an expression index works too, but the query must repeat the same expression and configuration exactly:

SQL
CREATE INDEX articles_fts_idx ON articles
  USING GIN (to_tsvector('english', title || ' ' || body));

Ranking with ts_rank

ts_rank(vector, query) returns a relevance number: higher means better. It considers how often the terms occur and, with weights, where. You order by it, descending:

SQL
SELECT id, title,
       ts_rank(search, q) AS rank
FROM articles,
     websearch_to_tsquery('english', 'index tree') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 10;

The query appears in the FROM clause under a name so the expression is parsed once and reused in WHERE and SELECT. Weights default to {0.1, 0.2, 0.4, 1.0} for D, C, B and A, so a hit in a weight-A title outranks the same hit in a weight-B body. A sibling function, ts_rank_cd, also rewards words that appear close together.

Ranking has limits worth knowing. ts_rank is computed from the matching row only, with no knowledge of how rare a word is across your whole collection, and it must be computed for every matching row before sorting. For very large result sets or sophisticated relevance (typo tolerance, synonyms, facets) a dedicated engine such as Meilisearch or Elasticsearch is the better tool. For most applications PostgreSQL search is more than enough and much simpler to run.

Highlighting with ts_headline

ts_headline(config, text, query, options) returns a snippet of the original text with the matched words wrapped in tags:

SQL
SELECT ts_headline(
  'english',
  body,
  websearch_to_tsquery('english', 'index'),
  'StartSel=<mark>, StopSel=</mark>, MaxFragments=2, MaxWords=20, MinWords=8'
) AS snippet
FROM articles
WHERE search @@ websearch_to_tsquery('english', 'index');
-- A B-tree <mark>index</mark> lets the database find rows without scanning ...

Only call it for the rows you will show (after LIMIT, for example), because it re-parses the full text. If you display the result as HTML, remember that the surrounding text is not escaped by PostgreSQL, so escape it in your application before inserting the marks, or use a safe approach for untrusted content.

Languages and dictionaries

The configuration name chooses the stop words and the stemmer. \dF in psql lists them, and PostgreSQL ships configurations for many languages:

SQL
SELECT to_tsvector('german',  'Die Katzen schlafen');   -- 'katz':2 'schlaf':3
SELECT to_tsvector('simple',  'Die Katzen schlafen');   -- 'die':1 'katzen':2 'schlafen':3

The simple configuration only lowercases: no stemming, no stop words. It is the right choice for names, codes or mixed-language text. If you store several languages, keep the language of each row in a column and use it when building the vector. For accent-insensitive search, install the unaccent extension and add it to a custom configuration (extensions are covered in the Extensions chapter). The function ts_debug('english', 'some text') shows how each token was processed, which is the best tool when a search does not behave as expected.

A live stand-in with LIKE

You can feel the problem full-text search solves with a plain LIKE search. Here is a small articles table, searched for "query":

SQL Substring search: what LIKE misses and what it over-matches

Press Run (or Ctrl + Enter) to see the result here.

The first query finds only article 2: "queries" does not contain the letters "query". The second, with the cut-down stem quer, finds articles 1 and 2, which is what a stemmer does automatically and correctly. The third returns articles 4 and 6, and article 6 ("beginners") is a false hit, because substring matching has no idea where words begin and end. Full-text search matches whole lexemes, so it avoids both mistakes.

Now add a crude version of ranking and highlighting. Score each article by how many of three search terms it contains, then wrap one term in tags:

SQL A hand-made relevance score and highlight

Press Run (or Ctrl + Enter) to see the result here.

Article 4 scores 3 (it has "index", "b-tree" and "column") and article 1 scores 2, so article 4 comes first. The highlight turns Indexes into <b>Index</b>es, which is also a reminder that literal replacement cuts words in half and cannot cope with stemming or case. PostgreSQL's ts_rank and ts_headline do the same jobs by word, by stem and by weight, for every row at once.

Do not build a production search on LIKE '%term%' or on replace() tricks. They scan the whole table, miss word forms, over-match short terms and have no real ranking. Use tsvector with a GIN index instead.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does to_tsvector('english', 'The foxes jumped') produce?

    It lowercases, removes stop words such as 'the', reduces words to stems (fox, jump) and records positions.
  2. 2. Which function should sit behind a search box that accepts arbitrary user input?

    to_tsquery errors on input like 'foo bar!'. websearch_to_tsquery accepts anything and supports the familiar search-engine conventions.
  3. 3. Why does an index on to_tsvector(title) need the configuration written as a literal, like 'english'?

    The two-argument form with a constant configuration is immutable and can be indexed. The one-argument form uses default_text_search_config, so it is only stable.
  4. 4. What is the main purpose of setweight(..., 'A')?

    Weights A to D label words by origin, for example title versus body. ts_rank multiplies by the weight, so a title hit outranks a body hit.
  5. 5. In the LIKE stand-in, why does LIKE '%gin%' wrongly return the article about beginners?

    'gin' occurs inside 'beginners'. Full-text search compares whole normalized lexemes, so it does not make this mistake.

Summary

  • Full-text search turns text into a tsvector (stemmed lexemes with positions) and matches it with a tsquery using @@.
  • Use websearch_to_tsquery for user input; plainto_tsquery and phraseto_tsquery are simpler cousins, and to_tsquery is strict.
  • Store the vector in a generated column (with setweight for title versus body) and index it with GIN for fast searches; index expressions need a literal configuration.
  • Rank with ts_rank (or ts_rank_cd) and highlight with ts_headline, which you call only for the rows you display.
  • The configuration (english, german, simple, ...) chooses stop words and stemming; ts_debug shows how text is processed.
  • Built-in search covers most apps; heavier needs (typo tolerance, facets) call for a dedicated engine.
  • SQLite's LIKE stand-in misses word forms, over-matches and has no true ranking, which is why this lesson's PostgreSQL blocks are shown rather than run.

Next: Pattern Matching and Regular Expressions, for when you need to match shapes of text rather than words.