pashkinelfe

It's a thorough explanation for those who understand PostgreSQL internals. It probably needs some time to understand the ideas. They are elegant in my opinion. Thanks to the author!

I'll explore performance with this parameter. Thanks for the advice!

Author is here.

Tables occupying more space than expected is a common complaint from Postgres users. I explained in simple terms what could (or should) be done to avoid this in real working conditions. And what kinds of workloads need special attention.

But, nowadays disk space is cheap, so most of us can relax while Postgres does what is needed.

Pgvector 0.5.0 got HNSW indexes that is much faster for AI application than IVF in most cases. We measured pgvector performance in Supabase and present recommendations on its efficient usage.

- HNSW preserves index quality even after massive table updates and doesn't need to be rebuilt

- HNSW search is several times faster than IVF for high accuracy. For lower accuracy likely even more.

- Pgvector/HNSW became faster than qdrant

- IVFflat build time decreased two-times in 0.5.0

- HNSW index could be built incrementally, you don't need to add all embeddings before index build

Interesting thing about faster distance calculations is that a very small change in the dot product calculation makes code compile to much more efficient CPU instructions and up to 2 times speedup for the whole algo.

But the real jewel in the crown is HNSW support that makes pgvector much faster than the competitors (the measurements with thorough and transparent description of method are in the linked blog post).

If you have 10M or bigger dataset of real-world OpenAI- dimensional vectors, please share, I'll use it in the next benchmarks. Random datasets are too misleading for vector search benchmarks because all ANN engines make use of internal distributions in datasets to struggle with the curse of dimensionality. So I never use random datasets for ann indexed benchmarking. Using simplified less dimensional (eg 128 instead of 1536) vectors also changes performance trends.

Pgvector doesn't need to store everything in memory. It behaves similar to almost any Postgres AM's and store index data on disk. Performance-wise it's better to have enough memory for index data remain in shared memory buffers, but it is not a requirement for pgvector.

I like these measurements with ANN-benckmark! They allow to compare performance of different index implementations apples-to-apples i.e. at the same build parameters set rather than using some fixed settings (or, even worse, default settings that are different).

The blogpost is very thorough, lots of measurements of different datasets, including great 1536-dimensional 1M rows dbpedia-openai dataset. Furthermore, a very strong point is that all parameters and method is described and transparent.

I suppose that more CPU load is because more cores are loaded by doing tasks in parallel. Less CPU load is actually indicator that performance is limited by something else (IO, locks, memory coherency) that couldn't be scaled with the ease of CPU's adding.

So bigger CPU load and bigger performance mean that parallelising of tasks is more efficient, which is a clear benefit.