My benchmark fooled me three times
Last week I told people my vector index was about twice as fast as pgvector. A few days later I found out that pgvector had been running with one hand tied behind its back.
It was the third time in two weeks that my own benchmark had fooled me. This is the story of all three, and of what is left once you remove them.
Why I started
At work, I was optimizing an Aurora PostgreSQL cluster that runs pgvector. pgvector stores embeddings next to the rest of your data and searches them with an HNSW index, a graph that finds the nearest vectors quickly.
The catch is memory. HNSW is fast when most of the graph stays in memory. With millions of vectors, that memory becomes the expensive part of the bill.
So I started building my own index inside PostgreSQL, to see if there was another way. The results below are early, from a shared server, and I’m keeping the method for the paper.
To compare the two fairly, I wrote a benchmark: same table, same queries, same accuracy target (about 99% of the true nearest neighbours found), and what I believed were pgvector’s best settings.
The “true” nearest neighbours come from an exact brute-force search over the same rows, using 1,000 queries kept separate from the indexed data. Both indexes are scored against that same answer key, by the same code.
That benchmark is where things went wrong.
Lie 1: I was timing two different things
On 100,000 vectors, my first chart said pgvector took 6.8 ms per query and my index 1.4 ms. Five times faster.
Those were the times spent inside the index only. A real query also has to read rows, sort them and send them back. The full query in the same log took 8.2 ms against 2.5 ms.
About three times faster. Still a good result, just not the one I almost published.
The fix was simple: always time the whole query, the way an application would see it.
Lie 2: I built pgvector’s index with too little memory
My first build-time number said my index built 20 times faster.
Then I looked at how pgvector had been built. PostgreSQL gives an index build a fixed amount of working memory, and I had left it at 2 GB. pgvector’s graph for a million vectors needs more than 6 GB while it builds: the vectors alone are 6 GB, before the links between them. It didn’t fit, so most of the build ran in a much slower mode that works on disk.
With enough memory and the same number of workers on both sides, pgvector builds that index in about 35 minutes. Mine takes about 4.
Build time with pgvector given 16 GB and 7 parallel workers. Shared server, preliminary.
Still a large gap. But about 8 times, not 20.
Lie 3: I searched pgvector’s index with too little memory
This one I only found last week, and it changed the headline.
Get Esteban Puello’s stories in your inbox
Join Medium for free to get updates from this writer.
PostgreSQL keeps a cache of data in memory, called shared buffers. My tests used 2 GB, a normal value. But pgvector’s index for a million vectors is 8.2 GB. It could never fit in that cache, so every query had to fetch thousands of index pages from outside it.
My index is under 1 GB. It fit easily.
So the “twice as fast” I had reported was partly a measure of who fit in memory. I ran the test again with 2, 4, 8, 16 and 32 GB of cache.
Query time against PostgreSQL’s cache size, 1 million vectors, about 99% recall. The dotted lines mark where each pgvector index fits in memory.
With 2 GB, my index is about twice as fast. Once pgvector’s index fits, at 4 GB for its compressed version and 8 GB for the full one, the typical query takes about the same time on both: 8 to 10 milliseconds. On the slowest queries, pgvector is now ahead.
That stung. It also made the result clearer than before.
What still holds
Take away all three mistakes and this is what’s left.
Index size on disk. Shared server, preliminary.
My index is about a tenth of the size: 856 MB against 8.2 GB for a million vectors. To answer at full speed, pgvector needs memory for its whole index. Mine needs less than 1 GB.
It builds about 8 times faster, and inserting a new row costs a quarter or less of what it costs in pgvector.
So the honest summary is: same speed when memory is plentiful, much faster when it isn’t, and a tenth of the memory either way. Since memory was the reason I started, that’s the result I care about.
What I check now before I trust a benchmark
- Time what the user waits for: the full query, not one stage of it.
- Give the other side its best settings, then check them again. Read how it was built, not only how it was searched.
- Treat memory as a variable. Run the test at more than one cache size, and say which one you used.
- Report the slow queries too. A median can hide a bad tail.
- Run it more than once and report ranges. On my shared server, build times moved by 10 to 25% between runs.
- Before publishing, ask one question: what would make the other side win? Then go and test that.
Each of the three mistakes made my index look better. None of them was on purpose. That’s what makes them dangerous: when the numbers say what you hoped, you stop looking.
If you benchmark databases: which setting has fooled you the most ?
