Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

An In-Depth Study of Filter-Agnostic Vector Search on a PostgreSQL Database System: [Experiments and Analysis]

Duo Lu, Helena Caminal, Manos Chatzakis, Yannis Papakonstantinou, Yannis Chronis, Vaibhav Jain, Fatma Özcan

Abstract

Filtered Vector Search (FVS) is critical for supporting semantic search and GenAI applications in modern database systems. However, existing research most often evaluates algorithms in specialized libraries, making optimistic assumptions that do not align with enterprise-grade database systems. Our work challenges this premise by demonstrating that in a production-grade database system, commonly made assumptions do not hold, leading to performance characteristics and algorithmic trade-offs that are fundamentally different from those observed in isolated library settings. This paper presents the first in-depth analysis of filter-agnostic FVS algorithms within a production PostgreSQL-compatible system. We systematically evaluate post-filtering and inline-filtering strategies across a wide range of selectivities and correlations. Our central finding is that the optimal algorithm is not dictated by the cost of distance computations alone, but that system-level overheads that come from both distance computations and filter operations (like page accesses and data retrieval) play a significant role. We demonstrate that graph-based approaches (such as NaviX/ACORN) can incur prohibitive numbers of filter checks and system-level overheads, compared with clustering-based indexes such as ScaNN, often canceling out their theoretical benefits in real-world database environments. Ultimately, our findings provide the database community with crucial insights and practical guidelines, demonstrating that the optimal choice for a filter-agnostic FVS algorithm is not absolute, but rather a system-aware decision contingent on the interplay between workload characteristics and the underlying costs of data access in a real-world database architecture.

An In-Depth Study of Filter-Agnostic Vector Search on a PostgreSQL Database System: [Experiments and Analysis]

Abstract

Filtered Vector Search (FVS) is critical for supporting semantic search and GenAI applications in modern database systems. However, existing research most often evaluates algorithms in specialized libraries, making optimistic assumptions that do not align with enterprise-grade database systems. Our work challenges this premise by demonstrating that in a production-grade database system, commonly made assumptions do not hold, leading to performance characteristics and algorithmic trade-offs that are fundamentally different from those observed in isolated library settings. This paper presents the first in-depth analysis of filter-agnostic FVS algorithms within a production PostgreSQL-compatible system. We systematically evaluate post-filtering and inline-filtering strategies across a wide range of selectivities and correlations. Our central finding is that the optimal algorithm is not dictated by the cost of distance computations alone, but that system-level overheads that come from both distance computations and filter operations (like page accesses and data retrieval) play a significant role. We demonstrate that graph-based approaches (such as NaviX/ACORN) can incur prohibitive numbers of filter checks and system-level overheads, compared with clustering-based indexes such as ScaNN, often canceling out their theoretical benefits in real-world database environments. Ultimately, our findings provide the database community with crucial insights and practical guidelines, demonstrating that the optimal choice for a filter-agnostic FVS algorithm is not absolute, but rather a system-aware decision contingent on the interplay between workload characteristics and the underlying costs of data access in a real-world database architecture.
Paper Structure (38 sections, 1 equation, 13 figures, 7 tables)

This paper contains 38 sections, 1 equation, 13 figures, 7 tables.

Table of Contents

  1. Introduction
  2. Background on Filtered Vector Search
  3. Execution Strategies for FVS Queries
  4. Inline Filter-Agnostic Approaches
  5. Filter-Agnostic FVS Algorithms
  6. Unfiltered HNSW Search
  7. Graph-based Filtered Search Algorithms
  8. ACORN
  9. NaviX
  10. Sweeping
  11. Iterative Scan
  12. Filtered ScaNN
  13. FVS Algorithms in PostgreSQL Database
  14. Graph-based Filter-First Methods
  15. Graph-based Traversal-First Methods
  16. ...and 23 more sections

Figures (13)

  • Figure 1: Average latency for 100 filtered vector search queries on OpenAI-1M: HNSWLib (solid) vs. PGVector (dashed). System latency can be up to 10× higher, and the gap varies with selectivity, motivating an in-depth comparison of FVS algorithms in a real DBMS.
  • Figure 2: Execution strategies for an FVS query.
  • Figure 3: Filter-first execution methods for an FVS query.
  • Figure 4: Traversal-first execution methods for an FVS query.
  • Figure 5: Execution methods for an FVS query on ScaNN.
  • ...and 8 more figures