How to add semantic search to an existing app using an embedding model and pgvector
Adding the most basic form of "AI search" to an existing app is three changes:
- Add a vector column to the table you want to search.- When a row is created, send its text to an embedding model and store the numbers it returns in that column.- On search, embed the search term the same way and ask the database which rows are closest.
I have a super simple todo app written in Next.js connected to a Postgres database.
Aside from the usual CRUD operations, this simple todo app has search. It was the naive one everybody writes first: lowercase the query, lowercase the title, check includes. Type "apple" and you get "Purchase some apples"; type "laundry" and you get "Laundry day". It looks like it works, as long as you already know the words in the title.
Now search that same list for "groceries". Nothing, even though "Purchase some apples" and "Buy bread and eggs" are sitting right there. Same for "cleaning" against "Laundry day" and "Do the dishes". The search is not looking at what the todos mean; it is looking at which letters they contain, and groceries is not a substring of anything.
That is the gap people reach for "AI" to fill. The surprise is how little is involved. The part that does the matching is not a model at all. It is arithmetic in your database.
If you would rather try the app than read about it, both versions are on GitHub:
To run either branch on your machine you only need two things: a Postgres database with pgvector support and an OpenAI API key. Put them in .env as DATABASE_URL and OPENAI_API_KEY, install the dependencies, and start the app.
The diff between the two branches is, genuinely, the entire feature.
Forget training, weights, and prompts for a minute.
An embedding model is a function. Text goes in, a fixed-length list of numbers comes out:
"Purchase some apples" -> [0.021, -0.043, 0.118, ... ] (1536 numbers)
"groceries" -> [0.019, -0.038, 0.121, ... ] (1536 numbers)
Think of those numbers as a location on a map. On a real map, two places with similar coordinates are close to each other. Same idea here, except this map has 1536 directions instead of two. You cannot picture that, and you do not need to. Only the rule matters: text that means similar things ends up close together.
1536 is not a universal number. It is just the output width of the model I picked. Other models give you 768, 1024, 3072, and some let you ask for a shorter output. Whatever you pick becomes part of your schema, so treat it as a decision and not a constant.
So "Purchase some apples" sits near "groceries" and far from "renew passport". Nobody programmed that. The model was trained on a very large amount of text, and that placement is the leftover shape of the language it read.
Here is the part worth internalizing:
Once the text is numbers, matching is just measuring a distance. Your database does that. The AI ended at the point where you got the numbers back.
That is it. That is the whole trick. Everything below is plumbing.
Postgres cannot store a list of 1536 floats usefully on its own, so we use pgvector, an extension that adds a vector type and, crucially, distance operators that work in order by.
-- migrations/002_embeddings.sql
-- pgvector is not part of stock Postgres. On Supabase it is available
-- but not enabled until you ask for it.
create extension if not exists vector;
-- 1536 is the native output width of OpenAI's text-embedding-3-small.
alter table todos add column if not exists embedding vector(1536);
Postgres is not the only place you can keep vectors, it just happens to be where my app already lived. I use Supabase, which is free and ships pgvector out of the box. If you run Postgres yourself, note that docker run postgres:17 does not include the extension; use pgvector/pgvector:pg17 instead. Where to store embeddings compares the other options in my notes.
Setting that up is not really part of this post, so it lives in my notes instead:
Either way you end up with a DATABASE_URL and a database that understands vector. The rest of this post does not care which one you picked.
Two reasons, and both come up in any real app:
The entire "AI dependency" is one HTTP POST. No SDK required.
// src/lib/embeddings.ts
const ENDPOINT = 'https://api.openai.com/v1/embeddings';
export const EMBEDDING_MODEL = 'text-embedding-3-small';
export async function embed(text: string): Promise<number[]> {
const response = await fetch(ENDPOINT, {
method: 'POST',
headers: {
'Content-Type': 'application/json',
Authorization: `Bearer ${process.env.OPENAI_API_KEY}`,
},
body: JSON.stringify({model: EMBEDDING_MODEL, input: text}),
// A hung provider must not pin a request open forever.
signal: AbortSignal.timeout(10_000),
});
if (!response.ok) {
throw new Error(`OpenAI embeddings failed: ${response.status}`);
}
const payload = (await response.json()) as {
data: {index: number; embedding: number[]}[];
};
return payload.data[0].embedding;
}
That is the AI in "AI search". A string in, an array of numbers out.
OPENAI_API_KEY is the only credential involved. Getting one is a five minute detour, and the API platform is billed separately from ChatGPT Plus, which trips up most people the first time.
You do not have to use OpenAI. Any embedding model works, as long as it takes text and gives back numbers. Google, Voyage, Cohere, or a model running locally through Ollama all fit in the same function. The alternatives and what changes when you switch is its own note. The two things that move are the dimension count in your schema and the fact that every vector you have already stored becomes stale.
One small detail: pgvector accepts a vector written as a plain JSON array, so sending one from your code is just the array turned into a string, with an explicit ::vector cast in the query. In the snippets below that is the toVector helper.
The important decision here is where the call goes. Creating a todo is the core feature; embedding it is not. If you await OpenAI before inserting the row, an OpenAI outage takes down todo creation.
So the row is inserted and returned first, and the embedding is written after the response has already gone out. Next.js gives you after() for exactly this:
// src/app/api/todos/route.ts
import {NextResponse, after} from 'next/server';
import {sql} from '@/db';
import {embed, toVector} from '@/lib/embeddings';
export async function POST(request: Request) {
const {title} = createTodoSchema.parse(await request.json());
const [created] = await sql`
insert into todos (title)
values (${title})
returning id, title, completed, created_at
`;
after(async () => {
try {
const embedding = await embed(created.title);
await sql`
update todos
set embedding = ${toVector(embedding)}::vector
where id = ${created.id}
`;
} catch (error) {
console.error(`Failed to embed todo ${created.id}`, error);
}
});
return NextResponse.json(created, {status: 201});
}
after() is best-effort, not a queue. No retries, and if the process dies mid-callback the work is lost. That is fine for a demo and not fine for production. See the caveats at the end.
This is the symmetry that makes the whole thing work, and it is the one sentence I would want a reader to keep:
The search term goes through the exact same embedding call as the stored text. Then you ask the database which stored vectors are nearest to that one.
// src/app/api/todos/search/route.ts
import {NextResponse} from 'next/server';
import {sql} from '@/db';
import {embed, toVector} from '@/lib/embeddings';
const MAX_DISTANCE = 0.6;
const MAX_RESULTS = 20;
export async function GET(request: Request) {
const q = new URL(request.url).searchParams.get('q')?.trim();
const queryVector = toVector(await embed(q));
const rows = await sql`
select
id, title, completed, created_at,
1 - (embedding <=> ${queryVector}::vector) as similarity
from todos
where embedding is not null
and (embedding <=> ${queryVector}::vector) < ${MAX_DISTANCE}
order by embedding <=> ${queryVector}::vector
limit ${MAX_RESULTS}
`;
return NextResponse.json(rows);
}
<=> is pgvector's cosine distance operator. It answers one question about two lists of numbers: how far apart do they point?
0: same direction, effectively the same meaning. 1: unrelated. 2: opposite.
So order by embedding <=> $query is literally "closest first", and 1 - distance gives you a similarity between 0 and 1 that is friendlier to show and to reason about.
Notice what is not in that query: no model, no prompt, no API call. By the time Postgres is involved, the AI part is over. This is ordinary maths over a column, and it is why the feature is fast and cheap to run.
Nearest-neighbour search has no concept of "no results". Ask it for the top 20 and it hands you 20 rows, however unrelated, confidently ranked. Without MAX_DISTANCE, searching for asdfgh returns your entire todo list.
0.6 is a magic number picked by eye. It depends on your data, since short todo titles behave nothing like paragraphs of prose. That is why the endpoint returns similarity on every result: run a few searches with curl, see where the useful results stop, and move the number.
Remember the two searches that returned nothing at the start of the post? Here they are again, on the same todo list, with the old search and the new one side by side:
"groceries" -> (nothing)
"cleaning" -> (nothing)
"groceries" -> Purchase some apples 0.71
Buy bread and eggs 0.68
"cleaning" -> Do the dishes 0.66
Laundry day 0.64
The letters still do not match. groceries is nowhere in "Purchase some apples". But the two sit close together on that map, so the row comes back anyway, with a number telling you how close:
Notice that I did not replace the old search. I added a button next to the search box that toggles "AI" search on and off, so you can run the same query both ways and see the difference. The toggle is the feature, and that is not just for the demo.
Vector search is bad at exact terms. Ticket IDs, product codes, names, acronyms, anything rare. Search for TODO-1234 and it will happily return four todos that feel vaguely related and none that match. Substring search gets that right every time.
So the two are not rivals. Keyword wins on exact hits, vectors win on meaning, and keeping both is the honest setup. The usual next step is to stop making the user choose: run both and merge the results. That is called hybrid search.
One small detail: both modes only search when you submit, not as you type. Use whatever strategy you like here. I just did not want to fire an embedding call on every keystroke.
The search query skips rows where embedding is null, so a todo without an embedding is invisible to AI search. Two things put rows in that state:
Both are fixed the same way: select the rows with a null embedding, embed them, write the vectors back. Here it is as an API route, using an embedMany variant of the earlier embed function that sends an array in one request:
// src/app/api/todos/backfill/route.ts
import {NextResponse} from 'next/server';
import {sql} from '@/db';
import {embedMany, toVector} from '@/lib/embeddings';
export async function POST() {
const pending = await sql`
select id, title from todos
where embedding is null
order by created_at
limit 100
`;
// The API takes an array, so 100 todos is one round trip, not 100.
const embeddings = await embedMany(pending.map(todo => todo.title));
for (const [index, todo] of pending.entries()) {
await sql`
update todos
set embedding = ${toVector(embeddings[index])}::vector
where id = ${todo.id}
`;
}
return NextResponse.json({embedded: pending.length});
}
curl -X POST localhost:3000/api/todos/backfill
The route is just the easiest trigger. A one-off script, a cron job, a queue worker, or a button in your admin page do the same job. The limit keeps one run bounded, so call it until it returns 0.
An embedding is derived data. It is a function of the text and the model that produced it.
Switch to text-embedding-3-large, shorten the output to 512 dimensions, or move to another provider, and every stored vector becomes stale. Nothing errors, because the old vectors still look like valid numbers. They are simply no longer comparable to the vectors your new queries produce, so search quietly gets worse.
So write the backfill as something you can run again, not as a one-time migration.
Backfilling is the first time you send a lot of text to the model at once, so this is the right moment to talk about the bill.
You are billed per input token, and only on the way in. There is no output cost, because the output is a vector and not text.
For an app this size the numbers are barely real. A todo title is about 8 tokens, so embedding 10,000 of them is around 80,000 tokens, which is under a fifth of a cent on text-embedding-3-small. Searches are even smaller.
So the thing to watch is not the price per token. It is re-embedding text that did not change, embedding on every keystroke, and hitting the tokens-per-minute rate limit in the middle of a backfill. How to count tokens and estimate the bill is in my notes, including the input length cap you will hit the moment your text is longer than a todo title.
This is a demo app, so here is what I skipped:
0.6 is tuned to my data, not yours.where user_id = ..., filtered vector search gets tricky, because the index finds the nearest rows globally and your filter then throws most of them away.
The demo app went from "search only finds words you already typed correctly" to "search finds what you meant" with one migration, one embedding call on insert, and one order by distance query. No new service, no separate search engine, no rewrite. The database you already have does the hard part.
The piece worth keeping in your head is that the model is doing one job: turning text into a position on a map. Everything else in this post is plumbing around that. Once the numbers are in a column, groceries finding "Purchase some apples" is just arithmetic.