Why our AI search slowed as customers grew, why a hosted store didn't fit, how to move without betting on one database, and what we found after the switch.
Our AI search got slower every time a customer added documents. The obvious fix would have left some customers with no AI search at all.
I moved the search off Postgres and onto LanceDB, in stages and without downtime. This is why it slowed, why the obvious fix didn’t fit, how to move a vector store without betting the product on one database, and what we found after the switch.
Why search slowed as companies grew
BHyve answers questions from a company’s own documents. Each document is cut into chunks, short passages that get searched on their own. Each chunk gets an embedding: a list of numbers that captures what the text means, so similar meanings get similar numbers. To answer a question, we embed the question too and look for the chunks whose numbers sit closest to it.
Those embeddings lived in pgvector, a Postgres extension that adds a vector column and similarity search. Each tenant, meaning each customer company, had its own tables in its own schema.
pgvector can search in two ways. Exact search compares the question with every row. It always finds the true closest matches, and its cost grows with every row you add. An approximate index skips most rows, and trades a little accuracy for a lot of speed.
The catch is that Postgres only uses that index for one query shape: sort by the distance itself, closest first, and stop after a fixed number of rows. In SQL, that’s an ORDER BY on a distance operator with a LIMIT. Turn the distance into a score inside the sort, or leave out the limit, and Postgres quietly goes back to comparing every row. Nothing errors. The query just gets slower as the table grows.
That is what happened to us. The way our queries were written, the index couldn’t help, so every question was compared with every chunk the user could see. That’s why the slowdown followed the data. Twice the chunks meant twice the comparisons, on every question. Small companies never noticed. Companies with thousands of documents did.
If you suspect the same thing, run EXPLAIN on your real search query. If the plan shows a sequential scan, meaning Postgres reads every row, instead of your vector index, nothing is skipping rows.
Why the hosted fix didn’t fit
The obvious fix was a hosted vector database. We used Turbopuffer, a hosted service for vector and full-text search that keeps its data in object storage, the bucket storage every cloud sells.
For us, it ran in one cloud region. Some customers host BHyve in their own cloud, and a store in a vendor’s region sits outside it. For those customers, a hosted store meant no AI search at all.
So the real question wasn’t which database was fastest. Different customers needed their vectors in different places, and any single choice would leave someone out.
The principle: make the store a part you can swap
If no one store fits every customer, stop choosing one for everyone. I put every vector store behind one small interface: the handful of operations any vector store needs, like adding rows, deleting them and searching with a filter. The rest of the product only talks to that interface. It never knows which database is underneath.
That changes what the decision is. “Which database?” stops being a bet you make once for every customer, and becomes a setting per tenant. Each tenant can run on LanceDB or Turbopuffer, in our cloud or theirs. Moving a tenant becomes a data migration, not a rewrite.
Two rules came with it. A new store runs next to the old one, as an A/B test, before we trust it. And the old path stays alive until the new one has been measured.
How to move a vector store without betting on it
-
Copy the vectors, don’t rebuild them. I backfilled the existing vectors into Turbopuffer without re-chunking or re-embedding. Both stores then held identical data, so any difference in results had to come from the store, not from the model or the chunking. Change one thing at a time.
-
Compare quality, not just speed. A new store can be fast and still return different chunks. Speed is easy to see. Whether the right chunks come back isn’t. So I built an A/B test that scored both stores on three measures:
- Recall: of the chunks that should come back, how many did.
- nDCG (normalised discounted cumulative gain): how good the whole ranking is, with more credit for good results near the top.
- MRR (mean reciprocal rank): how high the first good result sits, on average. First place scores 1, second a half, third a third.
-
Remove the fallback once the new path holds. When it held, I removed the Postgres fallback, so there was only one way to answer a question. A fallback feels safe. But while it exists you have two systems to keep correct, and when an answer looks wrong you first have to work out which one gave it.
-
Pick a store that can go where the customer is. That’s LanceDB. It’s an embedded vector database, meaning it runs as a library inside your program, the way SQLite does, instead of as a separate server. Its data is plain files in the Lance format, which stores each column together, so a query can read just the vectors or just the metadata it needs. The files can sit on a local disk or in object storage. If you can give it a disk, you can run it, in our cloud or a customer’s.
-
Treat a store on a disk as state. The LanceDB service I built was the first stateful workload on our platform. Stateful means it keeps data that has to survive restarts. Until then, anything on the platform could be thrown away and recreated. That changes the rules:
- One writer. LanceDB supports concurrent writers, but its docs warn that too many can make writes fail, because each writer retries a conflicting commit only a limited number of times. One writer takes that problem off the table.
- A disk that outlives the pod. A pod is the smallest thing Kubernetes runs. The service’s storage keeps the disk if the pod is deleted, so a restart never takes the data with it.
- Daily snapshots. A snapshot is a point-in-time copy of the disk, so there is always a recent one to go back to.
There’s more on this in running a database on Kubernetes.
-
Test the property you lost. Postgres slowed as data grew, so growth was the thing to test. I load-tested the service at 100k rows and at 500k, five times as much.
-
Move the customer who needed it, and check every batch. I helped move one enterprise customer’s search into its own cloud, the case a hosted store in one region couldn’t handle. Every batch was checked against what was sent. A copy that silently drops rows looks fine until someone asks the question whose answer went missing.
Along the way we also changed the embedding model. A new model means re-embedding every chunk, so it rolled out one environment at a time: dev, then staging, then production. How we tested it is in how we choose and test embedding models.
What it changed
- Query time stopped following the data. Vector search took 5.3 ms at 100k rows and 6.2 ms at 500k. Five times the data cost less than a millisecond more.
- Memory stayed flat, near 1 GB. Only the disk grew with the data.
- Access checks stayed cheap. Users only see chunks from documents they’re allowed to open, so every search filters by access. In the worst case, that filtering cost at most 37 ms.
- Every production tenant now runs on LanceDB, and self-hosted customers get the same AI search as everyone else.
The store is still a setting, not a bet. If you’re choosing between these stores yourself, I compared them in pgvector vs LanceDB vs Turbopuffer.
What we found after the switch
Fast vector indexes often compress the vectors so more fit in memory, which makes every distance an estimate. Recall@10 asks: of the 10 chunks that are truly closest to a question, how many made it into the 10 we returned?
After the switch, I measured it on large tenants. With a compressed index and no refine step, recall@10 was 0.29. A refine step re-checks a longer shortlist against the full vectors. Adding it brought recall@10 back to 0.83, for about 8 ms. That fix is documented and next on the list. The details are in the recall you lose to compressed vector indexes.
This is why measuring can’t stop at the cutover. A load test measures speed, and on a latency chart a search that’s fast and wrong looks exactly like one that’s fast and right. Only a check against exact search shows the difference.
The lesson
Migrate in stages and keep the old path alive until the new one has been measured. Then keep measuring after the switch: that’s how I found the recall gap.
All the numbers from this migration are in one place in the case study.
Quick answers
- Why does vector search get slower as data grows?
- Usually because nothing is skipping rows. Exact search compares the question with every stored vector, so the work grows with the data. An approximate index reads only a small part of it, but only when the query can use the index.
- Why is my pgvector query not using the index?
- pgvector only uses an approximate index when the query orders by a distance operator in ascending order and has a LIMIT. If the distance is wrapped in an expression, or there is no LIMIT, Postgres compares the question with every row.
- What is LanceDB?
- LanceDB is an open-source vector database that runs inside your application as a library, like SQLite, and stores its data as files in the Lance columnar format on a local disk or in object storage.
- Can you move vectors to a new vector database without re-embedding?
- Yes, as long as you keep the same embedding model. Copy the stored vectors and their metadata as they are. Both stores then hold identical data, which also makes the comparison fair.
- Can LanceDB run in a customer's own cloud?
- Yes. LanceDB keeps its data in files, so it runs wherever you can give it a disk or a bucket. We used it to move one enterprise customer's AI search into that customer's own cloud.