HomeEducationSimilarity search with pgvector and Supabase | Swizec Teller Receive US

Similarity search with pgvector and Supabase | Swizec Teller Receive US

Some time again I shared how one can construct semantic search in a day with fashionable tech. Fairly candy, proper?

That article used a big CSV file as its “database”, which is in regards to the slowest most inefficient software you should use. Studying an enormous CSV file into reminiscence, sorting all rows, then choosing 5 and throwing away all the pieces else You possibly can see the slowness of that approach in my SwizBot constructed on articles. Each response takes without end.

Final week I attempted one thing sooner – pgvector on Supabase!

PS: you may learn and share this on-line

That gif exhibits a similarity search between convention attendees. We’re constructing a software for AI Engineer Summit to make the convention extra fascinating.

Everybody coming to the convention solutions a number of questions. What are you searching for, what will get you fired up about AI, why are you right here?

We discover individuals with matching pursuits and, if you happen to each say sure, make a heat intro. Go speak to Swizec about constructing prototypes with LLMs. It is gonna be nice.

Here is a mockup of what the UI will appear like. Simon‘s constructing that single-handedly as a result of he is superior

AI Engineer matching UI mockup

AI Engineer matching UI mockup

pgvector is a Postgres plugin that provides vector assist to Postgres, the most effective basic database on the market. It provides a pair new question operators and allows you to effectively retailer and question vectors as a part of a daily database desk.

Supabase is a hosted Postgres supplier fashionable with the serverless crowd. Supabase manages the internet hosting, affords a pleasant administration UI, and allows you to entry information via their SDK or a daily Postgres consumer utilizing SQL queries.

I constructed all of the code you are about to see utilizing markdown programming with ChatGPT. You might optimize components of the code by hand, nevertheless it’s high quality for the quantity of information we have got.

The approach works in 3 steps:

  1. Prep the Supabase DB for pgvector
  2. Compute and retailer embeddings for each attendee’s responses
  3. Discover related attendees with a vector similarity search

All based mostly on OpenAI embeddings. It price me about $1 to run this for a number of thousand rows of information.

Prep the Supabase DB for pgvector

Supabase comes pre-installed with the pgvector plugin. It’s a must to allow the plugin and create an embeddings desk.

Question to allow the plugin:


Any Postgres server ought to assist this after you put in pgvector on the system.

With pgvector enabled, you create an embeddings desk like this:

create desk

public."UserEmbedding" (

id serial,

"userProfileId" textual content not null,

embedding extensions.vector null,

constraint UserEmbedding_pkey major key (id),

constraint UserEmbedding_userProfileId_key distinctive ("userProfileId"),

constraint UserEmbedding_userProfileId_fkey international key ("userProfileId") references "UserProfile" (id) on delete prohibit

) tablespace pg_default;

The question creates a UserEmbedding desk with an auto-incrementing id discipline, a singular userProfileId discipline that factors on the UserProfile desk, and an embedding vector discipline.

We’ll add extra vector columns sooner or later so we are able to run various kinds of comparisons. The embedding column identify wants a greater identify too.

Compute and store embeddings for every attendee

We’ll want embedding vectors for every attendee to run similarity queries. Working with the belief that giving related solutions to survey questions means you have got related pursuits.

The code first connects to the DB and inits an OpenAI consumer.

const Consumer = require("pg");

const OpenAI = require("openai");

const db = new Consumer(

connectionString: course of.env.SUPABASE_URL,


const openai = new OpenAI(course of.env.OPENAI_API_KEY);

await db.join();

Then it reads an inventory of customers and their solutions from the DB:

const res = await db.question(

`SELECT * FROM "UserProfile" JOIN "SurveyAnswer" ON "UserProfile".id = "SurveyAnswer"."userProfileId"`


The result’s an array of rows, one per survey reply. Every consumer solutions a number of questions so we’ll course of them a number of occasions. It is high quality 🙂

For every consumer we:

  1. Accumulate all solutions right into a string
  2. Compute an embedding
  3. Upsert the embedding into UserEmbeddings

for (const row of res.rows)

const solutions = res.rows

.filter((r) => r.userProfileId === row.userProfileId)

.map((r) => `$r.query: $r.reply`)

.be part of(" ");

console.log(`Processing $row.userProfileId...`);

const embeddingRes = await openai.embeddings.create(

mannequin: "text-embedding-ada-002",

enter: solutions,


const embedding = embeddingRes.information[0].embedding;

const upsertQuery = `

INSERT INTO "UserEmbedding" ("userProfileId", embedding)

VALUES ($1, $2)

ON CONFLICT ("userProfileId")

DO UPDATE SET embedding = EXCLUDED.embedding


await db.question(upsertQuery, [row.userProfileId, JSON.stringify(embedding)]);

It’s a must to stringify the embedding vector as a result of pgvector expects vectors to start out with an [ and the node-postgres library converts JavaScript arrays into Postgres sets by default. Those start with {.

Using ON CONFLICT DO UPDATE turns our insert into an upsert.

Even better would’ve been to avoid processing each user multiple times ¯_(ツ)_/¯

Once you’ve got a database table populated with vectors, you can leverage the full power of pgvector. The biggest benefit is that you can use JOINs to read your other data all in 1 query.

ChatGPT came up with this function to find the 5 most similar attendees to a given userProfileId. The function reads and prints matches and their answers so we can evaluate the result.

const findSimilarUsers = async (userProfileId) => {


await client.connect();

const nearestQuery = `

SELECT * FROM "UserEmbedding"

WHERE "userProfileId" != $1

ORDER BY embedding <-> (

SELECT embedding FROM "UserEmbedding" WHERE "userProfileId" = $1

) LIMIT 5;


const res = await client.query(nearestQuery, [userProfileId]);

const similarUsers = res.rows;

for (const consumer of similarUsers)

const infoQuery = `

SELECT "UserProfile".id, "UserProfile"."firstName", "UserProfile"."lastName", "SurveyAnswer".query, "SurveyAnswer".reply

FROM "UserProfile"

INNER JOIN "SurveyAnswer" ON "UserProfile".id = "SurveyAnswer"."userProfileId"

WHERE "UserProfile".id = $1;


const userInfo = await consumer.question(infoQuery, [user.userProfileId]);

console.log(`First Identify: $userInfo.rows[0].firstName`);

console.log("Survey Solutions:");

userInfo.rows.forEach((row) =>

console.log(` $row.query: $row.reply`);



catch (err)

console.error("Error:", err);


await consumer.finish();


First question holds the pgvector magic. That <-> operator means euclidean distance between vectors. You should use <#> for detrimental interior product and <=> for cosine distance.

Which means you may learn SELECT * FROM "UserEmbedding" WHERE ... ORDER BY embedding <-> (SELECT embedding ...) LIMIT 5 as choose all properties from the primary 5 outcomes from UserEmbedding the place userProfileId is not the enter ordered by distance between embedding vectors in UserEmbedding.

And it is quick!

If default efficiency is not quick sufficient for you, pgvector helps indexing on vector columns. That provides you one other few orders of magnitude.

I’ve heard rumors that “pgvector does not scale”, however I feel that kicks in upon getting tens of millions of rows. Most of us do not have that downside.

The most important profit is that pgvector helps you to preserve vector information subsequent to your different enterprise information. This lets you run JOINs (such as you see above) and all types of queries with out worrying about entry patterns or writing further code. An excellent profit at first of any venture!

Maintaining information collectively additionally cuts down on overhead. Any efficiency profit you get from utilizing a pure vector database is more likely to be overshadowed by what you lose in pulling from a number of information sources and becoming a member of information with code.

At the very least whereas your information is small.

Utilizing me for example, you get these matches:

First Identify: Rajeev

Survey Solutions:

BUILDING_WHAT: Getting began on the AI engineering journey!

WANT_TO_LEARN: Utilizing LLMs for fixing enterprise issues



First Identify: Garth

Survey Solutions:


WANT_TO_LEARN: Sensible methods to construct AI flows to assist companies develop into extra profitable by using AI bots and LLMs.



First Identify: Naga

Survey Solutions:


WANT_TO_LEARN: Studying about LLMs, AI Brokers, Instruments and so on



First Identify: David

Survey Solutions:

BUILDING_WHAT: Private Tutor

WANT_TO_LEARN: Development of AI brokers, LLM finetuning, and Open Supply LLM efficiency.



First Identify: Scott

Survey Solutions:

BUILDING_WHAT: Making an attempt to generate higher content material with LLMs

WANT_TO_LEARN: The way to be a greater AI Engineer


Appears good. As a fellow newb, I might love to talk with these people.

Will I see you at the conference? Heck I will purchase you a beer if we get matched … or even when we do not, the beer’s free 😉


Did you take pleasure in this text?

Revealed on September 4th, 2023 in Synthetic Intelligence, LLM, Supabase, pgvector,

Senior Mindset Guide

Get promoted, earn a much bigger wage, work for high corporations

Be taught extra

Have a burning query that you just assume I can reply? Hit me up on twitter and I will do my finest.

Who am I and who do I assist? I am Swizec Teller and I flip coders into engineers with “Uncooked and trustworthy from the guts!” writing. No bullshit. Actual insights into the profession and abilities of a contemporary software program engineer.

Wish to develop into a true senior engineer? Take possession, have autonomy, and be a pressure multiplier in your group. The Senior Engineer Mindset e book will help These are the shifts in mindset that unlocked my profession.

Interested in Serverless and the fashionable backend? Try Serverless Handbook, for frontend engineers

Wish to Cease copy pasting D3 examples and create information visualizations of your individual? Discover ways to construct scalable dataviz React parts your complete group can perceive
with React for Data Visualization

Wish to get my finest emails on JavaScript, React, Serverless, Fullstack Internet, or Indie Hacking? Try

Did somebody wonderful share this letter with you? Great! You possibly can join my weekly letters for software program engineers on their path to greatness, right here:

Wish to brush up in your fashionable JavaScript syntax? Try my interactive cheatsheet:

By the way in which, simply in case nobody has advised you it but right now: I really like and respect you for who you might be

#Similarity #search #pgvector #Supabase #Swizec #Teller

Continue to the category


Please enter your comment!
Please enter your name here

- Advertisment -spot_img

Most Popular

Recent Comments