Our Vector Index Had Never Been Used Once

We spent a week comparing vector databases. We tested pgvector, Turbopuffer, pgvectorscale and HNSW, on the same questions and the same pages, and every comparison came back a tie inside the noise. That result was suspicious enough to keep pulling at, and pulling at it turned up something better than a winner.

We asked Postgres to explain how it was running our search. It answered with a sequential scan over 58,022 rows, taking between 575 and 845 milliseconds. The vector index was sitting right there in the table definition.

The planner was right

The obvious reaction is that the query planner made a mistake. It did not. We forced it to use the index and timed the result: 12.5 seconds, against 0.6 for reading the whole table. Refusing that index was the correct decision, made correctly, every time, for months.

The index was created with 20 lists, which is a reasonable choice for a few thousand vectors. The corpus grew to 58,000 and nobody revisited the number. At that size each list holds about 2,900 vectors, so the ten probes our retriever requests have to read roughly half of a 226MB index, in random order, on a virtual server with about 1.2GB of free memory. It thrashes. A linear read of the table is genuinely cheaper.

Then we looked at the usage counter, which is the moment the whole thing became clear:

      indexrelname       | idx_scan | idx_tup_read
-------------------------+----------+--------------
 document_chunks_pkey    |   262163 |     47091581
 idx_chunks_embedding    |        1 |            5

One lifetime scan of the vector index, five rows read. That was our own diagnostic, minutes earlier. The index had never once answered a question from a resident.

Why a bug this large can hide

Because a sequential scan is exact search. It compares the question against every chunk in the corpus and returns the genuinely closest ones. The answers were correct. The citations were correct. Our quality evaluations, which score relevance and groundedness, saw nothing wrong, because nothing was wrong with the output.

The only symptom was time, and time is exactly where this system hides its costs. A retrieval taking 300 milliseconds instead of 5 disappears inside a language model call that takes several seconds. Nobody watching the site would feel it. Nobody reading the logs would question it.

A bug that returns wrong answers gets found in an afternoon. A bug that returns right answers slowly can live for months, and this one did.

What it cost us

Not the milliseconds. What it cost was a decision.

In July we published a comparison finding Turbopuffer, a hosted vector database, about 20 times faster than pgvector at equal answer quality, and we moved our retrieval onto it. The measurement was real. It was also not a measurement of pgvector: 520 milliseconds was a sequential scan over the whole table. We had compared a purpose-built vector database against a database with no working index, and then made an architecture decision on the result.

With the index rebuilt at 240 lists and its probes setting swept, the honest comparison looks like this:

The third row scores highest on recall because a sequential scan is exact. It is the ceiling, not a competitor.

Seven pages out of 296 disagree between the first two rows, which a paired statistical test cannot separate from chance. Stronger still: of the 167 pages both stores found, every single one came back at the same rank. They are not merely scoring alike, they are returning the same ordering.

So we are moving retrieval back to Postgres. The remaining advantage of the hosted service is its tail, 35 milliseconds against 125 at the 95th percentile, which is real and which we are trading for keeping this capability on hardware we control. Ninety milliseconds at the 95th percentile, against an answer that takes several seconds to write, is not worth an external dependency for a project whose point is partly that this can be run without one.

How to check your own

Two queries, and the first takes seconds.

Read the usage counter. pg_stat_user_indexes carries an idx_scan column counting lifetime uses of each index. If the number beside your vector index is small while your application has served thousands of queries, it is not being used, whatever the table definition says.

Then run EXPLAIN ANALYZEon the query your application actually issues, with the vector bound as a parameter rather than written into the statement. That distinction matters: a hand-written test query using a subquery to fetch a vector produces a different plan shape than your application’s bound parameter does, and can send you looking in the wrong place. Check the plan says Index Scan.

And if you take one thing from this: the parameters that govern an approximate index are only meaningful relative to the size of your data. Ours was correct when it was written and became wrong without changing, because the corpus grew underneath it. Nothing in Postgres will tell you the day that happens.

Frequently asked questions

The corrected original is at Turbopuffer vs pgvector. A different measurement of ours that failed the same way, by comparing against a component that was not doing the work it appeared to be doing, is at My GPU Benchmark Was Measuring a Cold Model.