Postgres in depth · Chapter 62 of 65

"Students who took this also liked"

By the end of this chapter you can store vectors in Postgres, find the nearest ones by meaning, index them for millions of rows, and combine meaning with keywords in one search. 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

Kabir wants a row under every course: students who took this also liked. Arjun wants the search box to understand that someone who types databases might want SQL Fundamentals even though its title never says the word. Both are questions about meaning, and nothing so far measures meaning. Full-text search (chapter 58) matches words; React from Zero and HTML and CSS from Zero share almost no words, yet they belong together.

The modern answer is embeddings, and the Postgres extension for them is pgvector (vector): a vector type, distance operators, and two index types built for nearest-neighbour search.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    embeddings.sql: a vector for six courses

    Add a column embedding of type vector(4) to courses, with the dimensions frontend, backend, data and devops in that order, and set it for these six courses, found by slug:

    html-css-from-zero [0.9, 0.1, 0.0, 0.1], complete-javascript [0.7, 0.5, 0.1, 0.1], react-from-zero [0.95, 0.2, 0.05, 0.0], sql-fundamentals [0.1, 0.6, 0.8, 0.1], postgres-for-developers [0.1, 0.8, 0.6, 0.3] and docker-basics [0.1, 0.4, 0.1, 0.9]. Every other course keeps no embedding.

    Install vector in the file if it is missing: the Check runs it in a fresh copy of LCO's database.

  2. Task 2

    also-liked.sql: what React from Zero students also like

    Under React from Zero, show the three other courses whose embeddings are nearest to its embedding by cosine distance. The Check's copy has the embeddings from task 1.

    Return two columns: title and distance (the cosine distance, rounded to 4 decimal places). Order by distance, then title.

  3. Task 3

    vector-index.sql: an index for nearest by cosine

    Add an HNSW index on courses.embedding that the nearest-by-cosine search from task 2 can use. The Check's copy has the embeddings from task 1.

    Afterwards: courses has an HNSW index on embedding, and a query ordered by cosine distance to a vector with a limit can use it.

  4. Task 4 · interview question

    Interview: hybrid.sql, keywords and meaning in one ranking

    An interviewer asks for a hybrid search with reciprocal rank fusion over the six courses that have embeddings. The Check's copy has the embeddings from task 1.

    Keyword ranking: the courses whose document (the title, a space, then the tags joined by spaces, with the english configuration) matches the web search databases, ranked 1, 2 and so on by ts_rank from highest, ties by title. Meaning ranking: all six courses, ranked 1 to 6 by cosine distance to [0.2, 0.9, 0.4, 0.3] (what a model might make of the same search), ties by title.

    A course's score is 1 / (60 + its keyword rank) plus 1 / (60 + its meaning rank), where a ranking it is not in adds nothing. Return the best three: title and score (rounded to 5 decimal places). Order by score from highest, then title.

What you will learn

  • Embeddings: what they are, and what this lab uses instead.
  • The vector type and its distances: <->, <=> and <#>.
  • HNSW and IVFFlat: how the two indexes find neighbours without reading every row, and what they trade for it.
  • Hybrid search: full-text search and vectors fused into one ranking.

More in Extensions and graduation

Build ""Students who took this also liked"" 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.