Full-Text Search

Search prose columns with stemming, ranking, and highlighting — using tsvector, tsquery, and GIN indexes

Both the tv_db and beer_db have pre-computed tsvector columns: episodes and shows have description_tsv; beers have description_tsv; beer_ratings have review_tsv. These are GENERATED ALWAYS AS columns — PostgreSQL keeps them in sync automatically when the source text changes.\n\nFull-text search differs from LIKE in two important ways. First, it stems words: searching for "brewed" also matches "brew", "brewing", "brews". Second, it ranks results by relevance rather than just returning a binary match/no-match.\n\nIn this lab you will search episode descriptions for thriller-adjacent keywords, search beer reviews for positive sentiment words, and rank and highlight the results — all using the GIN-indexed tsvector columns that already exist in both databases.

tsvector: The Indexed Document

A tsvector is a sorted list of lexemes (normalised, stemmed word roots) with their positions. It is stored pre-computed so queries do not need to parse the text on every search.

SELECT to_tsvector('english', 'An intensely hoppy IPA bursting with tropical fruit aromas.');
-- 'aroma':7 'burst':5 'fruit':6 'hopp':3 'intensely':2 'ipa':4 'tropical':6

tsquery: The Search Expression

-- plainto_tsquery: splits on spaces, ANDs the terms, handles stemming
SELECT plainto_tsquery('english', 'tropical fruit');  -- 'tropical' & 'fruit'

-- to_tsquery: explicit operators (& AND, | OR, ! NOT, <-> phrase)
SELECT to_tsquery('english', 'hop & bitter & !sweet');
SELECT to_tsquery('english', 'chocolate <-> stout');  -- phrase: chocolate followed by stout

@@ Match Operator

-- Search beer descriptions for tropical fruit
SELECT name, description
FROM beers
WHERE description_tsv @@ plainto_tsquery('english', 'tropical fruit')
LIMIT 5;

Ranking with ts_rank

SELECT name,
       ts_rank(description_tsv, plainto_tsquery('english', 'tropical fruit')) AS rank
FROM beers
WHERE description_tsv @@ plainto_tsquery('english', 'tropical fruit')
ORDER BY rank DESC
LIMIT 5;

Highlighting with ts_headline

SELECT name,
       ts_headline('english', description,
                   plainto_tsquery('english', 'tropical fruit'),
                   'MaxFragments=1,MaxWords=15,MinWords=5') AS snippet
FROM beers
WHERE description_tsv @@ plainto_tsquery('english', 'tropical fruit')
LIMIT 5;

ts_headline wraps matched terms in ... tags by default.

tsvector

A sorted list of stemmed lexemes with position information, stored pre-computed in a column. to_tsvector() parses raw text into a tsvector, applying the language's stop-word list and stemming rules. When stored in a GIN-indexed column, FTS queries against it are index scans — not sequential text scans.

tsquery

A parsed search expression that can include AND (&), OR (|), NOT (!), and phrase proximity (<->). plainto_tsquery() converts a plain phrase into a tsquery by splitting on spaces and ANDing the terms. to_tsquery() lets you write explicit operators for more control.

ts_rank and ts_headline

ts_rank() scores a tsvector against a tsquery based on how many terms match, their frequency, and their positions. Higher scores mean more relevant matches. ts_headline() returns a short snippet of the original text with matching terms highlighted — useful for displaying search result previews.

🔌 Connect to tv_db

Connect to tv_db as the tv_db user. Full-text search exercises start with the shows and episodes tables, which have pre-computed description_tsv columns.

psql -U tv_db -d tv_db

psql (18.4) Type "help" for help. tv_db=>

🔎 Search Show Descriptions with plainto_tsquery

Find shows whose description contains words related to "corporate conspiracy" using plainto_tsquery. plainto_tsquery splits the phrase and ANDs the terms with automatic stemming — no need to worry about "conspired" vs "conspiracy".

SELECT title, status, description FROM shows WHERE description_tsv @@ plainto_tsquery('english', 'corporate conspiracy') ORDER BY title;

title | status | description ---------------+--------+--------------------------------------------------- Neon District | ended | In 2077, a hacker exposes a corporate conspiracy. (1 row)

🎯 Boolean Search with to_tsquery (OR)

Find shows related to either "investigation" or "detective" using to_tsquery with the | (OR) operator. This returns shows matching either term — more results than AND.

SELECT title, status, ts_rank(description_tsv, to_tsquery('english', 'investigation | detective')) AS rank FROM shows WHERE description_tsv @@ to_tsquery('english', 'investigation | detective') ORDER BY rank DESC LIMIT 8;

✂️ Highlight Matches with ts_headline

For shows matching "detective" or "investigation", add ts_headline to show a short snippet of the description with the matching words highlighted.

SELECT title, ts_headline('english', description, to_tsquery('english', 'investigation | detective'), 'MaxFragments=1, MaxWords=12, MinWords=5') AS snippet FROM shows WHERE description_tsv @@ to_tsquery('english', 'investigation | detective') ORDER BY title LIMIT 5;

title | snippet -----------------+----------------------------------------------------------------------------------- Apex Predator | investigative journalist hunts a serial killer who hunts journalists City of Signals | Near-future detective uses augmented reality overlays to solve murders Clockwork City | steam-powered metropolis, a detective investigates automaton crimes Crimson Shore | Coastal town murders investigated by a disgraced detective Dark Harbour | Gritty detective series set in a fictional port city (5 rows)

🔀 Switch to beer_db and Search Reviews

Disconnect from tv_db and connect to beer_db as the beer_db user. Then search beer_ratings.review_tsv for reviews mentioning "aroma" to warm up for the next step.

\q

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

⭐ Rank Beer Reviews by Keyword Relevance

Search beer_ratings for reviews mentioning "tropical" or "aroma". Join to beers to show the beer name. Rank by ts_rank and show the top 8 results. Use a subquery to resolve the beer name — no manual ID lookup needed.

SELECT b.name AS beer_name, b.style, br.reviewer_name, ROUND(br.score, 1) AS score, ts_rank(br.review_tsv, to_tsquery('english', 'tropical | aroma')) AS rank, br.review_text FROM beer_ratings br JOIN beers b ON b.id = br.beer_id WHERE br.review_tsv @@ to_tsquery('english', 'tropical | aroma') ORDER BY rank DESC LIMIT 8;

Lab complete! You can now build full-text search queries over tsvector columns:\n\n\n col @@ plainto_tsquery('english', phrase) : ✅ natural language match\n col @@ to_tsquery('english', 'a | b & !c'): ✅ boolean keyword logic\n ts_rank(col, query) : ✅ relevance score\n ts_headline(lang, text, query, options) : ✅ highlighted snippet\n GIN index on tsvector column : ✅ makes @@ an index scan\n

Enable JavaScript to run the live terminal and track your progress.