Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 19 · 6 min read

PostgreSQL Full-Text Search Tutorial

On this page
  1. Why ILIKE search doesn't scale
  2. How full-text search works: tsvector and tsquery
  3. A GIN-indexed search column with ranking
  4. websearch_to_tsquery and phrase search
  5. Debugging searches that don't match
  6. Check yourself

Why ILIKE search doesn't scale

Every search feature starts the same way:

SQL
SELECT title FROM articles WHERE body ILIKE '%running%';

It's fine for a demo. In production it has three problems. It can't use a normal index, because the leading % means a match could start anywhere, so every search reads every row. It only matches the exact letters, so "running" misses "runs" and "ran". And it can't rank results, so the best match might be on page five.

PostgreSQL has a proper search engine built in. For many apps it's enough, without a separate service like Elasticsearch.

How full-text search works: tsvector and tsquery

Full-text search turns the document and the query into the same normalized form, then compares those.

A document becomes a tsvector, a sorted list of word roots called lexemes, with their positions:

Code
SELECT to_tsvector('english', 'The runners were running quickly through the rain');
-- 'quick':5 'rain':8 'run':4 'runner':2

Look at what happened. Common words like "the" and "were" (stop words) are gone. "running" became run and "quickly" became quick. That's stemming: reducing words to a shared root using the rules of the language you named, here 'english'. Stems aren't always real words. "databases" becomes databas, and that's fine, because the query side is stemmed the same way.

A search becomes a tsquery, and the @@ operator tests whether it matches:

Code
SELECT to_tsvector('english', 'The runners were running quickly through the rain')
       @@ to_tsquery('english', 'run & rain');
-- true

A GIN-indexed search column with ranking

Store the tsvector in a generated column so it's computed once per write, not on every search. Then index it with GIN, the same inverted index type you met for jsonb on Day 18. For each lexeme it keeps the list of rows containing it.

SQL
CREATE TABLE articles (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  body        text NOT NULL,
  search_doc  tsvector GENERATED ALWAYS AS (
                setweight(to_tsvector('english', title), 'A') ||
                setweight(to_tsvector('english', body),  'B')
              ) STORED
);
CREATE INDEX articles_search_idx ON articles USING gin (search_doc);

INSERT INTO articles (title, body) VALUES
  ('Tuning PostgreSQL performance', 'This guide covers indexes, vacuum and query planning.'),
  ('How indexes speed up search', 'Learn how tsvector and tsquery work, and how to index them.'),
  ('Running a database in containers', 'Volumes, backups and indexes for a containerized database.');

setweight labels title words A and body words B, so title matches can rank higher. Now search and rank:

SQL
SELECT title, round(ts_rank(search_doc, q)::numeric, 3) AS rank
FROM articles, websearch_to_tsquery('english', 'indexes') AS q
WHERE search_doc @@ q
ORDER BY rank DESC
LIMIT 10;

All three articles mention indexes, so all three match. "How indexes speed up search" comes first, because its match is in the title, which carries weight A. Always pair full-text queries with ORDER BY rank and LIMIT, because a popular word can match thousands of rows. There's also ts_rank_cd, which rewards documents where the matching words appear close together.

to_tsquery expects its own syntax with &, | and !. Feed it raw input from a search box and it breaks:

Code
SELECT to_tsquery('english', 'C++ & rust!');
-- ERROR: syntax error in tsquery

For user input, use websearch_to_tsquery. It understands the syntax people already know from web search engines, and it never raises a syntax error:

Code
SELECT websearch_to_tsquery('english', '"quick fox" OR rain -dog');
-- 'quick' <-> 'fox' | 'rain' & !'dog'

Quoted text becomes a phrase, OR means or, a leading - excludes a word, and everything else must all match. plainto_tsquery is the simpler option: it just ANDs every word together.

The <-> in that output means "followed by". It's how phrases work. Plain quick & fox matches if both words appear anywhere. A phrase requires them next to each other, and <N> allows a set distance:

Code
SELECT to_tsvector('english', 'the quick brown fox') @@ phraseto_tsquery('english', 'quick fox');  -- false
SELECT to_tsvector('english', 'the quick brown fox') @@ to_tsquery('english', 'quick <2> fox');    -- true

Debugging searches that don't match

Sooner or later someone reports "I searched for X and it's right there, but nothing came up". Here's a real one:

Code
SELECT to_tsvector('english', 'PostgreSQL') @@ websearch_to_tsquery('english', 'postgres');
-- false

Stemming doesn't know that "postgres" and "postgresql" mean the same thing. ts_debug shows exactly what happens to each word, and it's the first tool to reach for:

SQL
SELECT alias, token, lexemes FROM ts_debug('english', 'The PostgreSQL databases are running');
-- asciiword | The        | {}            (stop word, dropped)
-- asciiword | PostgreSQL | {postgresql}
-- asciiword | databases  | {databas}

Common causes and fixes:

  • Different configurations on each side. If you index with 'english' and query with 'simple', the stems won't line up. Use the same configuration everywhere.
  • Product names and codes. For SKUs or tags, the 'simple' configuration lowercases words but doesn't stem or drop anything.
  • Synonyms. A custom configuration with a synonym dictionary can map "postgres" to "postgresql".
  • Typos and partial words. Full-text search doesn't do fuzzy matching. The pg_trgm extension adds trigram similarity, which handles misspellings and also speeds up ILIKE '%...%' (Day 23).

Check yourself

Switch to full-text search: a stored tsvector with a GIN index, matched against websearch_to_tsquery('english', :q). Stemming turns "running" into run and "shoes" into shoe, so both match.

The code passes raw input to to_tsquery, which raises a syntax error on characters it treats as operators. Use websearch_to_tsquery or plainto_tsquery for user input.

There's no index matching that expression. Store the tsvector in a generated column with a GIN index, or create a GIN expression index on exactly to_tsvector('english', body), and query against it.

to_tsvector('english', ...) reduces words to their stems, so "running", "runs" and "run" all become the lexeme run, and the query is stemmed the same way. That's the main advantage over ILIKE, which only matches exact substrings.

Order by ts_rank(search_vector, query) DESC, or ts_rank_cd for cover density. Give important fields more weight with setweight, for example the title as A and the body as B, so a match in the title ranks higher.