Postgres with Drizzle · Chapter 31 of 40

The similarity engine

By the end of this chapter you can store embeddings in Postgres with pgvector, find the members closest to someone, index the search with HNSW, and summarise the vectors you have. 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 wants a "People you may know" box on every profile. A machine learning service already turns each member's profile into an embedding: 384 numbers, where members with similar interests get similar numbers. The box should show the five members whose numbers are closest.

You could load every vector into JavaScript, compute each distance, sort and keep five: with a million members, a million vectors cross the network for every profile view. pgvector adds a vector type and distance operators to Postgres, so the database does the comparing, and an index makes it fast.

What you build: 5 tasks

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

  1. Task 1

    insertUserWithEmbedding: a member with a vector

    Write insertUserWithEmbedding(username, email, embedding). username and email are strings; embedding is a JavaScript array of 384 numbers. It inserts a member with that username, email and embedding.

    Return the inserted row as an object with id, username and email (one row, not an array). Afterwards: the member's embedding column holds the vector.

  2. Task 2

    findSimilarUsers: the members closest to one member

    Write findSimilarUsers(userId, limit). userId is a member's id and limit a number. It finds the other members closest to that member by cosine distance (the <=> operator) between their embeddings. Leave out the member themself and every member with no embedding.

    Return result.rows: objects with id, username and distance (a number), closest first, at most limit rows.

  3. Task 3

    createHNSWIndex: an index for nearest-neighbour search

    Write createHNSWIndex(). It creates an HNSW index named idx_users_embedding_hnsw on the embedding column of users, with the operator class vector_cosine_ops and the settings m = 16 and ef_construction = 64, 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_embedding_hnsw. Afterwards: the index uses hnsw.

  4. Task 4

    findUsersInEmbeddingRange: members within a distance

    Write findUsersInEmbeddingRange(targetEmbedding, maxDistance, limit). targetEmbedding is a JavaScript array of 384 numbers, maxDistance and limit are numbers. It finds the members with an embedding whose cosine distance to targetEmbedding is less than maxDistance.

    Return result.rows: objects with id, username and distance (a number), closest first, at most limit rows.

  5. Task 5

    getEmbeddingStats: a summary of the stored vectors

    Write getEmbeddingStats(). It summarises the users table: how many members there are, how many have an embedding, and the average vector_norm of the embeddings.

    Return one object (not an array) with total_users and users_with_embeddings (both numbers, not strings) and avg_norm (a number above 0).

What you will learn

  • The vector type: how a vector is written, sent and read back.
  • <=>, cosine distance: 0 for the same direction, up to 2 for opposite.
  • HNSW indexes: USING hnsw with an operator class that matches the distance.
  • vector_norm and counts: a summary of the stored vectors.

More in Performance & Superpowers

Build The similarity 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.