Postgres with Drizzle · Chapter 30 of 40
The search engine
By the end of this chapter you can search text the way people expect: by words in any form, ranked by relevance, forgiving typos, and fast enough for a search box. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Performance & Superpowers
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Meera's search box finds members by what they wrote about themselves. A member types "engineer" and expects everyone whose bio says engineer, engineers or engineering. Another types "alce" and still expects alice_dev.
So far you could write ILIKE '%engineer%'. It knows nothing about words: searching "running chai" finds no post that says "Running a chai stall", because that exact text is not in it. It cannot rank the best match first, a typo finds nothing, and with a % at the front no index can help, so every search reads the whole table. Postgres has two tools for this built in: full-text search and the pg_trgm extension.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
fullTextSearch: members by the words in their profile
Write fullTextSearch(searchTerm). searchTerm is a string, like engineer. It searches each member's bio and display_name together as one English document: the bio, a space, then the display name, with a NULL in either treated as empty text. A member matches when that document contains the words of the search (plainto_tsquery, english).
Return result.rows: objects with id, username, display_name and rank (the ts_rank of the document against the search, a number above 0), only the members who match, best rank first.
- Task 2
createFullTextIndex: a GIN index for the profile search
Write createFullTextIndex(). It creates a GIN index named idx_users_fts on users, on the same document as task 1 (the english to_tsvector of the bio and the display name joined by a space, each NULL treated as empty text), if it does not exist yet. Then it checks that pg_indexes lists it.
Return an object with created: true when pg_indexes has a row for idx_users_fts. Afterwards: the index uses GIN.
- Task 3
trigramSearch: usernames that look like the search
Write trigramSearch(searchTerm). searchTerm is a string, maybe misspelled, like alice. It finds members whose username is similar to it: the trigram operator % says so, or similarity(username, searchTerm) is above 0.1.
Return result.rows: objects with id, username, display_name and sim_score (the similarity, a number), most similar first. With the check's members, searching alice gives alice_dev first.
- Task 4
createTrigramIndex: a GIN index for fuzzy usernames
Write createTrigramIndex(). It creates a GIN index named idx_users_trgm_username on the username column of users with the trigram operator class gin_trgm_ops, if it does not exist yet. Then it checks that pg_indexes lists it.
Return an object with created: true when pg_indexes has a row for idx_users_trgm_username. Afterwards: the index definition names gin_trgm_ops.
- Task 5
combinedSearch: words in the bio or a username that looks alike
Write combinedSearch(searchTerm). searchTerm is a string. It keeps a member when either their bio matches the search as English words (to_tsvector of the bio, NULL treated as empty text, against plainto_tsquery) or similarity(username, searchTerm) is above 0.1.
Return result.rows: objects with id, username, display_name, bio, fts_rank (the ts_rank of the bio against the search) and trgm_score (the similarity of the username to the search), ordered by fts_rank plus trgm_score, highest first.
What you will learn
- to_tsvector, plainto_tsquery and @@: match words, not characters.
- ts_rank: score each match, to show the best first.
- similarity and % (pg_trgm): how alike two strings are, for typos.
- GIN indexes: one for full-text search, one with gin_trgm_ops for trigrams.
More in Performance & Superpowers
Build The search engine yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. 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 with Drizzle are free. This one comes with the all labs plan.