254 words, 2 min read
⚠️ This post links to an external website. ⚠️
Vectors have exploded in popularity as the datatype enabling semantic search, which powers all kinds of AI applications ranging from standard RAG systems to fully agentic applications. Our job and candidate recommendation algorithms at Clarvo also get their input via filtered vector queries, which can, however, be notoriously difficult to work with due to how differently vector indexes work in comparison to the regular B-trees, hash maps, GIN indexes etc.
In fact, effectively filtering vector queries can be so tricky that despite many of its major benefits, including no additional costs, a simple getting started process and direct integration into PostgreSQL, [some argue against using pgvecto…
254 words, 2 min read
⚠️ This post links to an external website. ⚠️
Vectors have exploded in popularity as the datatype enabling semantic search, which powers all kinds of AI applications ranging from standard RAG systems to fully agentic applications. Our job and candidate recommendation algorithms at Clarvo also get their input via filtered vector queries, which can, however, be notoriously difficult to work with due to how differently vector indexes work in comparison to the regular B-trees, hash maps, GIN indexes etc.
In fact, effectively filtering vector queries can be so tricky that despite many of its major benefits, including no additional costs, a simple getting started process and direct integration into PostgreSQL, some argue against using pgvector altogether and opting for dedicated vector databases instead, partially on that basis.
We were adding a lot of candidate profiles for recruiters to search, happily relying on our HNSW vector indexes in combination with various other filters to obtain only the most relevant results until we noticed our query times increasing linearly with the number of profiles in our database.
This triggered a rush to fix the SQL queries to eliminate a crucial bottleneck that would sometimes even time out some of the most complex queries, which resulted from the fact that the HNSW vector index was not actually being used by any of the queries almost ever due to incorrect SQL query structure and excessive complexity.
Here are some of the best practices I learned for working with the pgvector PostgreSQL extension while optimizing our queries’ performance from up to tens of seconds down to single-digit milliseconds.
If this post was enjoyable or useful for you, please share it! If you have comments, questions, or feedback, you can email my personal email. To get new posts, subscribe use the RSS feed.