First, What Are We Even Searching?
Let's ground this in one real example and stick with it the whole way through: you're building a "find similar support tickets" feature. A customer submits a new ticket, and you want to show them five old tickets that are about the same problem, even if they used totally different words to describe it.
To do that, you can't search for matching keywords, because "my payment failed" and "checkout won't go through" mean the same thing but share zero words. So instead, every ticket gets converted into a list of a few hundred (or a few thousand) numbers, called an embedding, using an AI model. Two tickets about similar problems end up with number-lists that are mathematically close to each other. Two tickets about unrelated problems end up far apart.
That list of numbers is what Postgres calls a vector. "Vector search" just means: given one ticket's list of numbers, find the other tickets whose lists of numbers are closest to it. pgvector is the Postgres extension that lets you store these number-lists in a normal table column and search through them with plain SQL.
The Part Every Tutorial Skips
Every "add AI to your app" tutorial has the same steps: install pgvector, add a vector column, run one search query, done. And for a demo with a thousand tickets, that's genuinely all it takes. It just works.
Here's the part tutorials skip: once your support-ticket table grows to a few hundred thousand rows, that same search query, which used to take 8 milliseconds, now takes 900 milliseconds. Someone on your team says "just add an index," like that fixes it the way adding an index fixes a normal slow query. It doesn't, not in the same simple way. Picking the right kind of index means giving up a small amount of accuracy in exchange for speed, and how much accuracy you give up is the whole subject of this post.
Why a Normal Index Doesn't Work Here
A normal Postgres index (a B-tree) is built for questions like "give me every ticket where status = 'open'." That's an exact match: a row either satisfies it or it doesn't.
A vector search question is different in kind: "of these 2 million tickets, which five have number-lists closest to this new ticket's number-list?" There's no exact match to look up. Every single row has to have its distance calculated and compared.
Postgres can do this the honest way: calculate the distance from your new ticket to every other ticket in the table, one by one, then sort and keep the closest five. This is called an exact search, and it's exactly what your thousand-row demo was quietly doing. At a thousand rows, checking every single one is instant, so you never noticed.
Why this matters in production: the time this takes grows in a straight line with the number of rows. Double your tickets, double the search time. Forever. There's no clever Postgres trick, no ANALYZE, no EXPLAIN setting that fixes this, because the problem isn't that Postgres is being inefficient. It's that checking every row really is the only way to get a guaranteed-correct answer. The only way to get faster is to stop insisting on a guaranteed-correct answer, and accept "almost certainly correct" instead.
Two Ways to Cheat (on Purpose): IVFFlat and HNSW
pgvector gives you two index types that both make this trade deliberately: they return an answer that's usually the true closest five tickets, but occasionally misses one, in exchange for being dramatically faster. This is called approximate nearest neighbor search, or ANN for short. Think of it like asking a knowledgeable local for the nearest coffee shop instead of checking every coffee shop in the city yourself: almost always right, occasionally not the actual single closest one, but you get an answer in two seconds instead of an hour.
The two options, IVFFlat and HNSW, cheat in different ways, and picking between them is the actual skill here.
IVFFlat: Sort Tickets Into Labeled Bins First
Picture a post office that pre-sorts mail into bins by zip code before a carrier ever looks at it. IVFFlat does the same thing to your tickets, before any search happens:
- When you build the index, Postgres groups all your existing tickets into a fixed number of bins (you choose how many, say 100), based on which tickets' number-lists are similar to each other. Each bin gets a "center point" that represents everything in it, the same way a zip code represents a neighborhood.
- When a new ticket comes in and you search, Postgres doesn't check all 100 bins. It only checks a handful of the bins whose center point is closest to your new ticket (you choose how many to check, say 10).
- Inside just those 10 bins, it does the slow, exact, check-every-row search, which is now fast because there are far fewer rows to check.
The catch nobody mentions in the quickstart: those bins are decided once, on the day you build the index, based on the tickets that existed that day. Six months later, your product has a whole new feature, and a wave of brand-new kinds of tickets start coming in that don't look like anything in your original bins. Postgres still has to put them somewhere, so it shoves each new ticket into whichever old bin is the "least bad fit," even if that bin isn't really a good match. Slowly, quietly, your search results get worse, and there's no error message telling you this is happening. The fix is to periodically rebuild the index (REINDEX) so the bins get redrawn using current data, which on a big table is a real maintenance job, not a background setting you flip once.
HNSW: Build a Map With Highways and Side Streets
HNSW works completely differently: instead of bins, it builds a connected map between all your tickets, like a road network, with some very long "highways" connecting far-apart regions and lots of short local roads connecting nearby tickets.
Think about how you'd actually travel from a small town to another small town on the far side of the country. You wouldn't drive the whole way on back roads. You'd take a short local road to a highway, drive the highway most of the way, then take local roads again at the other end. HNSW searches the exact same way: start on the "highway" layer where a few big jumps get you into the right general area fast, then drop down onto smaller, denser layers to fine-tune and land on the actual closest tickets.
m is roughly "how many roads does each ticket connect to." More roads means better odds of finding the true closest tickets, but the map itself takes up more memory. ef_search is roughly "how much of the map do you explore before giving up and answering." Explore more, and you get a more accurate answer, but it takes longer.
Why this matters in production: because HNSW never sorts tickets into fixed bins, it doesn't go stale the way IVFFlat does. New kinds of tickets just get woven into the existing map naturally. But that map has real costs. Every single ticket stores a list of which other tickets it's "connected to" by road, and that list has to live somewhere, so memory use grows with how many roads each point has (m) and how many tickets you have, not just with how big your rows are. A table that used to fit comfortably in memory as plain rows can suddenly need a lot more RAM once this road-map is layered on top. Also, building this map isn't a quick one-time calculation like a normal index: Postgres has to insert each ticket into the map one at a time, figuring out its connections as it goes. On a table with tens of millions of tickets, that build can take hours, and depending on your Postgres version it can hold a lock that blocks other people from writing to that table the whole time. If tickets are constantly being added, that's a real scheduling problem you need to plan around, not something to discover mid-build.
Putting Both Side by Side
| Exact search (no index) | IVFFlat (labeled bins) | HNSW (road map) | |
|---|---|---|---|
| Always finds the true closest matches? | Yes | No, usually close | No, usually close |
| Gets slower as the table grows? | Yes, in a straight line | Yes, but much more slowly | Yes, but even more slowly |
| Handles brand-new kinds of data well? | Doesn't matter, always exact | Poorly, needs periodic rebuilding | Well, no fixed bins to outgrow |
| Cost to build the index | None | Cheap, one quick grouping pass | Expensive, inserts one ticket at a time |
| Extra memory needed | None | A little, just the bin centers | More, scales with how many "roads" each ticket has |
| Good for tables with constant new writes? | Fine, just slow | Accuracy quietly drifts over time | Handles it well, but writes slow down as the map grows |
Neither one is simply "the better index." If your ticket table is built once and doesn't change character much over time, like a fixed product catalog, IVFFlat's cheap build and simple bins are the sensible default. If your table keeps growing and keeps having new, different kinds of data written to it all the time, like a live support-ticket stream, HNSW's steadier accuracy is usually worth its slower, more expensive build.
The One Question That Actually Matters
Before shipping either index, the important question isn't "which one is faster," because both are fast enough for almost any real app. The real question is: how often is it okay for this search to miss the actual best match, and have you ever checked?
That "miss rate" is called recall: the percentage of the true best matches your approximate index actually returns. It doesn't show up anywhere. It won't throw an error, and EXPLAIN ANALYZE won't flag it. The only way anyone finds out recall has gotten bad is a vague complaint months later, something like "the suggested tickets don't feel as relevant as they used to," long after the table has grown large enough for the approximation to start visibly slipping.
The fix is simple to say and easy to skip: while your table is still small enough to run a true exact search for comparison, run one, and compare its results against what your approximate index returns. That tells you your real recall number. Do this early, because once the table is big enough that an exact search is too slow to run anymore, you've lost your only way of checking whether the fast index is still giving you good answers.