BrevFeed
Sign in

47. Full-text search: tsvector and tsquery

📖 Reading · 8 min
💡 Most code boxes below are live — edit one and hit Run. Boxes without a Run button are reference-only (they can't run in your browser).

Why LIKE isn't "search"

Module 10 established that LIKE '%word%' can't use a normal index and forces a full scan. But there's a deeper problem beyond performance: LIKE has no concept of language — it can't match "running" when you search "run," can't ignore word order, can't rank results by relevance, can't treat "the"/"a"/"and" as noise words. Full-text search is Postgres's built-in solution to all of this — a real, if basic, search engine built into the database.

tsvector: text, preprocessed for searching

A tsvector is text broken into lexemes (normalized word stems, with stop words like "the"/"a" removed) plus their positions:

SELECT to_tsvector('english', 'A dinosaur runs through the jungle');
-- 'dinosaur':2 'jungl':6 'run':3

Notice: "the"/"A" are gone (stop words), "runs" became run and "jungle" became jungl (stemming — matches "running"/"run"/"runner" alike, and "jungles"/"jungle" alike). This preprocessing is why full-text search can match linguistic variants that LIKE never could.

pagila's film table already has this built in — a fulltext tsvector column kept in sync by a trigger, visible in psql's \d film output:

SELECT title, fulltext FROM film WHERE film_id = 1;

That trigger (film_fulltext_trigger, using title and description) automatically keeps fulltext in sync on every INSERT/UPDATE — a real, practical use of Module 9's trigger lesson, not just a demonstration.

tsquery: the search terms, similarly preprocessed

SELECT to_tsquery('english', 'dinosaur & jungle');   -- AND
SELECT to_tsquery('english', 'dinosaur | jungle');   -- OR
SELECT to_tsquery('english', 'dinosaur & !jungle');  -- AND NOT

&/|/! are the boolean operators inside a tsquery — AND/OR/NOT, applied to the same stemmed-lexeme matching tsvector uses. plainto_tsquery is the friendlier version for plain user input (no operators, just AND's every word together):

SELECT plainto_tsquery('english', 'dinosaur jungle');   -- same as dinosaur & jungle

Matching: @@

SELECT title FROM film WHERE fulltext @@ to_tsquery('english', 'epic & drama');
SELECT title FROM film WHERE fulltext @@ plainto_tsquery('english', 'shark');

@@ is the match operator — does this tsvector satisfy this tsquery. This is the core of a full-text search query, and (like JSONB containment) can be accelerated with a GIN index — pagila's film table already has one (film_fulltext_idx, using GiST in pagila's specific case — both GIN and GiST support full-text search, GIN generally reads faster, GiST updates faster; pagila's designers chose GiST, a legitimate tradeoff for a table this size and update pattern).

Ranking results by relevance

Unlike a plain WHERE filter, search results usually need to be ordered by relevance, not an arbitrary column:

SELECT title, ts_rank(fulltext, to_tsquery('english', 'epic & drama')) AS rank
FROM film
WHERE fulltext @@ to_tsquery('english', 'epic & drama')
ORDER BY rank DESC
LIMIT 10;

ts_rank scores how well a document matches a query (roughly: how many matching terms, how close together, how rare the terms are overall) — this is what turns "which rows match" into "which rows match best," the difference between a filter and an actual search feature.

Try it

Build a tiny hand-rolled version of ts_rank to appreciate what FTS automates. Search film descriptions for the terms 'Epic' and 'Drama': return films matching either term, with a matched_terms column counting how many of the two terms each description contains (a CASE per term, summed) — relevance-ordered: matched_terms descending, then title, first 10 rows. Notice what your version can't do that to_tsvector could: no stemming, no stop words, no term-rarity weighting.

SELECT title,
    (...) AS matched_terms
FROM film
WHERE ...
ORDER BY matched_terms DESC, title
LIMIT 10;

SELECT title,
    (CASE WHEN description LIKE '%Epic%' THEN 1 ELSE 0 END
   + CASE WHEN description LIKE '%Drama%' THEN 1 ELSE 0 END) AS matched_terms
FROM film
WHERE description LIKE '%Epic%' OR description LIKE '%Drama%'
ORDER BY matched_terms DESC, title
LIMIT 10;

Check yourself

  1. What does to_tsvector do to a piece of text that makes it fundamentally different from just storing the raw string?
  2. Why can LIKE '%running%' never match a row containing the word "run," while full-text search can?
  3. What does ts_rank add on top of a plain @@ match?
← Previous Next →