SQL Touted as Vector Alternative
A developer on X suggested that for agentic AI systems, traditional SQL queries combined with ranking functions like BM25 can produce better results than vector search. The argument is that agents "think in keywords," making structured and keyword-based retrieval more effective. This challenges the prevailing assumption that vector databases are the default solution for all AI retrieval tasks.
- Vector search excels at understanding the semantic meaning and context behind a query, allowing it to find relevant results even if the keywords don't match exactly. This is achieved by converting text into numerical representations called embeddings and finding items that are close in "vector space." In contrast, keyword-based search, like that using BM25, looks for exact word matches. - BM25, which stands for "Best Matching 25," is a ranking algorithm used by search engines for decades to determine the relevance of documents to a specific query. It improves upon older methods by considering factors like term frequency (how often a word appears in a document) and inverse document frequency (giving more weight to rarer words). - A significant limitation of vector-only search is its potential for "fuzziness," where it might return semantically similar but factually incorrect results. For example, a search for "Error Code 503" could return results for "Error Code 404" because they are both server errors. Furthermore, the accuracy of vector-based retrieval can decrease as the size of the dataset grows. - The argument for using SQL with BM25 is particularly strong in technical domains like medicine or law, where precise terminology is crucial. In these cases, the exact matching of keyword-based systems can be more reliable than the conceptual understanding of vector search. - Many developers are adopting a "hybrid search" approach that combines the strengths of both methods. This can involve running both keyword and vector searches and then merging the results, often using a weighting system to prioritize one over the other depending on the type of query. This allows systems to benefit from both the precise matching of BM25 and the contextual understanding of vector search. - The infrastructure requirements for these two methods differ significantly. Vector search often necessitates specialized vector databases and GPUs for generating embeddings. In contrast, BM25 can be implemented on more traditional search infrastructure, including popular databases like PostgreSQL and SQL Server, which have started to incorporate it as a feature.
