Full-text search and trigram similarity

conceptThe concept

to_tsvector('english', text) splits text into lexemes, removes stop words and stems words (batteries becomes batteri). websearch_to_tsquery('english', 'battery -refund "sound quality"') parses search-box syntax into a tsquery. vector @@ query tests a match, ts_rank scores it and ts_headline highlights matches.

Store the vector in a generated column (GENERATED ALWAYS AS (to_tsvector(...)) STORED) and index it with GIN; setweight lets title matches outrank body matches.

pg_trgm breaks strings into three-character chunks. similarity(a, b) returns 0 to 1, a % b is true above a threshold, and <-> is distance for nearest-neighbour ordering. A GIN or GiST index with gin_trgm_ops also accelerates LIKE and ILIKE with leading wildcards, which a B-tree cannot.

whyWhy it matters

An external search cluster is another system to run, secure and keep in sync. For many applications, built-in search plus trigram matching is good enough and transactionally consistent with the data.

Use caseReal-world scenario

Customers search reviews for "battery" but not "refund", and support agents look up customers by misspelled names. Full-text search handles the first, trigrams the second.

Try itExamples

Run and edit these in the interactive lesson.

SELECT to_tsvector('english', 'The batteries died within days, asked for a refund.') AS vector,
       websearch_to_tsquery('english', 'battery -refund "sound quality"') AS query;

SELECT r.id, r.rating, r.title,
       round(ts_rank(to_tsvector('english', r.title || ' ' || r.body), q)::numeric, 3) AS rank,
       ts_headline('english', r.body, q, 'StartSel=[, StopSel=]') AS snippet
FROM reviews r,
     websearch_to_tsquery('english', 'battery -refund') q
WHERE to_tsvector('english', r.title || ' ' || r.body) @@ q
ORDER BY rank DESC, r.id
LIMIT 5;

BEGIN;
ALTER TABLE reviews ADD COLUMN search tsvector
  GENERATED ALWAYS AS (setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')) STORED;
CREATE INDEX reviews_search_gin ON reviews USING gin (search);
ANALYZE reviews;
EXPLAIN SELECT id, title FROM reviews WHERE search @@ websearch_to_tsquery('english', 'refund');
ROLLBACK;

SELECT name, round(similarity(name, 'Prya Sharmaa')::numeric, 2) AS sim
FROM customers
WHERE name % 'Prya Sharmaa'
ORDER BY sim DESC, name
LIMIT 5;

BEGIN;
CREATE INDEX customers_email_trgm ON customers USING gin (email gin_trgm_ops);
EXPLAIN SELECT id, email FROM customers WHERE email ILIKE '%kenji.fis%';
SET LOCAL enable_seqscan = off;
EXPLAIN SELECT id, email FROM customers WHERE email ILIKE '%kenji.fis%';
ROLLBACK;

internalsUnder the hood

A GIN index stores, for each lexeme or trigram, a compressed list of row locations. New entries go to a pending list first (fastupdate) and are merged later, which speeds up writes but can slow searches until VACUUM or gin_pending_list_limit flushes them.

Avoid / preferDo this, not that

SELECT id FROM reviews WHERE to_tsvector(body) @@ to_tsquery('battery');

SELECT id FROM reviews WHERE to_tsvector('english', body) @@ websearch_to_tsquery('english', 'battery');

to_tsquery('english', $user_input)

websearch_to_tsquery('english', $user_input)

PitfallsCommon mistakes

• Calling to_tsvector(body) without a configuration in queries but with one in the index. The expressions must match exactly for the index to be used.
• Using LIKE '%term%' on large tables with only a B-tree index. Add a trigram index or use full-text search.
• Expecting full-text search to handle typos. It matches stems, not misspellings; combine it with pg_trgm.

LimitsWhen not to use it

For substring and typo-tolerant matching on short fields such as names and SKUs, pg_trgm is a better fit. For complex relevance tuning, facets and analytics over very large corpora, a dedicated search engine may be justified.

SecuritySecurity notes

ts_headline output contains the original document text plus highlight tags. Escape it before rendering in HTML, or user-written review text becomes a cross-site scripting vector.

ChallengeTry it yourself

SELECT id, rating, ts_headline('english', body, q) AS snippet
FROM reviews, websearch_to_tsquery('english', 'battery -charger') q
WHERE to_tsvector('english', body) @@ q
ORDER BY ts_rank(to_tsvector('english', body), q) DESC, id
LIMIT 5;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Full text search
• pg_trgm