Optimizing filtered vector queries from tens of seconds to single-digit milliseconds in PostgreSQL

Posted by m1r0k3@reddit | programming | View on Reddit | 3 comments

We actively use pgvector in a production setting for maintaining and querying HNSW vector indexes used to power our recommendation algorithms. A couple of weeks ago, however, as we were adding many more candidates into our database, we suddenly noticed our query times increasing linearly with the number of profiles, which turned out to be a result of incorrectly structured and overly complicated SQL queries.

Turns out that I hadn't fully internalized how filtering vector queries really worked. I knew vector indexes were fundamentally different from B-trees, hash maps, GIN indexes, etc., but I had not understood that they were essentially incompatible with more standard filtering approaches in the way that they are typically executed.

I searched through google until page 10 and beyond with various different searches, but struggled to find thorough examples addressing the issues I was facing in real production scenarios that I could use to ground my expectations and guide my implementation.

Now, I wrote a blog post about some of the best practices I learned for filtering vector queries using pgvector with PostgreSQL based on all the information I could find, thoroughly tried and tested, and currently in deployed in production use. In it I try to provide:

- Reference points to target when optimizing vector queries' performance
- Clarity about your options for different approaches, such as pre-filtering, post-filtering and integrated filtering with pgvector
- Examples of optimized query structures using both Python + SQLAlchemy and raw SQL, as well as approaches to dynamically building more complex queries using SQLAlchemy
- Tips and tricks for constructing both indexes and queries as well as for understanding them
- Directions for even further optimizations and learning

Hopefully it helps, whether you're building standard RAG systems, fully agentic AI applications or good old semantic search!

https://www.clarvo.ai/blog/optimizing-filtered-vector-queries-from-tens-of-seconds-to-single-digit-milliseconds-in-postgresql

Let me know if there is anything I missed or if you have come up with better strategies!