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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.