BrevFeed
Sign in

47. Full-text search: tsvector and tsquery

📖 Reading · 6 min

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.

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 →