Tenant-safe nearest-neighbor retrieval in PostgreSQL/pgvector: a measured comparison of post-filtered HNSW, iterative scan, list-partitioning, and half-precision quantization
Background. Retrieval-augmented systems increasingly store vector embeddings in PostgreSQL using the pgvector extension, often with many tenants sharing one table under row-level isolation. Approximate nearest-neighbor (ANN) indexing and per-tenant isolation interact in a way that is easy to get wrong and hard to notice. The individual techniques involved (HNSW indexing, scalar/half-precision quantization, filtered ANN, and table partitioning) are all established; what practitioners lack is a like-for-like, reproducible measurement of them together in the specific per-tenant case, within one system. Objective. To provide that measurement: a single-system benchmark that quantifies (1) how a single global HNSW index fails for low-share tenants under post-filtering, and (2) the latency and recall of three isolation-preserving alternatives, yielding a concrete decision ladder. Methods. Read-only measurement on a local pgvector/pgvector:pg16 (0.8.0) container with synthetic clustered 384-dimensional vectors, at 1,000 to 300,000 vectors per tenant, with HNSW held at the pgvector defaults (m=16, ef_construction=64, ef_search=40) and query parallelism disabled. We measured recall@10 against an exact brute-force ground truth using held-out query vectors, p50/p95 query latency, and rows returned under a tenant predicate. Results. A single global HNSW index selects candidates across the whole index and applies the tenant filter afterward; at the default ef_search a 1%-share tenant receives about 0.8 rows for a LIMIT 10 query, recall@10 near 0.07, the known selective-post-filtering failure, here quantified for pgvector. The severity tracks the single ef_search knob: recall climbs from 0.02 at ef_search=10 to 0.61 at ef_search=500, the latter at roughly seven times the latency, so reaching usable recall this way costs most of the latency the index was meant to save. Three isolation-preserving alternatives were measured on one identical dataset. (A) pgvector's iterative scan refills the result set to a full 10 rows and removes the empty-result starvation, but at 1% share with default settings it recovers only about two-thirds of true recall (0.66 relaxed, 0.17 strict order), at a share-dependent scan cost (about 17 to 22 ms at 1% share versus about 2 ms at 10%); it is a stopgap, not a fix. (B) LIST-partitioning by tenant prunes a tenant-filtered query to its single partition at plan time, so such a query cannot retrieve another tenant's rows at any size (a query-planning property, not a substitute for access control); the planner exact-scans small partitions (recall 1.000) and uses the per-partition HNSW only where a partition is large enough to need it. (C) half-precision (halfvec) storage makes the exact per-tenant scan about 3.4 to 5.4 times faster at near-lossless recall (at or above 0.999 across all sizes), which roughly triples the per-tenant size at which the exact scan crosses 100 ms, from about 43,000 to about 145,000 vectors (both crossovers interpolated between measured sizes). Conclusions. For per-tenant vector retrieval in pgvector, the exact per-tenant scan is the correct floor; half-precision quantization is the cheapest way to extend its runway, and list-partitioning is the structural option at very large per-tenant scale. The contribution is the single-system comparison and the decision ladder, not any of the underlying techniques. Absolute recall values are directional (synthetic data); the latency and crossover findings are properties of the execution model on this platform.
Authors
- Pulkit Singhal (ORCID: https://orcid.org/0009-0009-0000-7468)
Publication Details
- Journal
- Zenodo (CERN European Organization for Nuclear Research)
- Published
- 2026-11-18
- DOI
- https://doi.org/10.5281/zenodo.24286318
- Primary Topic
- Data Management and Algorithms
- Type
- preprint