Postgres in depth · Chapter 58 of 65
Search the lessons
By the end of this chapter you can search text by meaning-carrying words instead of exact characters, rank the results, and index the search so it stays fast. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Extensions and graduation
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
LCO has 269 lessons, and most have a transcript (lessons.transcript). Kabir wants a search box: a student types fixing errors and finds the lessons that explain how to fix errors. Arjun wants the best matches first, not in whatever order the rows come back.
ILIKE cannot do it. The transcripts say "the most common Docker errors and how to fix them", so transcript ILIKE '%fixing errors%' finds zero lessons: the words are there, but not in that form or that order. ILIKE also has no idea which match is better, and with a % at the start it reads every row. Postgres has a whole search engine built in for this: full-text search. (It is part of core Postgres; no extension needed.)
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
matches.sql: matching lessons per course
A student types fixing errors in the lesson search. Count, for each course, the lessons whose transcript matches that search, read the way a search engine reads it, with the english configuration.
Return two columns: course (the course title) and lessons (how many of its lessons match). Only courses with at least one match. Order by lessons from most to fewest, then course.
- Task 2
ranked.sql: the best five matches, weighted
Rank the lessons for the search debugging or postgres. Each lesson's document is its title with weight A followed by its transcript with weight B, both with the english configuration; the query comes from the search-box function, also with english.
Return the five best matches: course (the course title), lesson (the lesson title) and rank (ts_rank of the document against the query, rounded to 4 decimal places). Order by rank from highest, then course, then lesson.
Careful: some lessons have no transcript, and they still have a title that can match.
- Task 3
search-column.sql: a stored, indexed search document
Add a column search to lessons that Postgres keeps up to date by itself: a stored generated column of type tsvector, holding the title with weight A followed by the transcript (an empty text when there is none) with weight B, with the english configuration. Then add a GIN index on it.
Afterwards: every lesson's search equals that document, and a search on the column can use your index.
- Task 4 · interview question
Interview: common-words.sql, what Postgres really stores
An interviewer asks which lexemes appear in the most lesson transcripts, after Postgres has processed them with the english configuration.
Return the top five: word (the lexeme) and lessons (the number of transcripts that contain it). Order by lessons from most to fewest, then word.
Careful: count a lesson once even when the word appears in it twice.
What you will learn
- tsvector: a document turned into its searchable words.
- tsquery and @@: a search, and whether a document matches it.
- Ranking and weights: ts_rank, setweight, and the NULL trap.
- A GIN index: a generated tsvector column, and why it must be immutable.
More in Extensions and graduation
Build "Search the lessons" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.