Restricted users can receive fewer than the requested number of relevant results from a pgvector search even when PostgreSQL correctly enforces their permissions. With an approximate HNSW or IVFFlat index, pgvector searches a limited set of candidates and applies ordinary filters afterward. Candidates excluded by permission conditions cannot fill the query’s LIMIT.
That is a retrieval-quality problem, not automatically an authorization failure. Row-level security (RLS) controls which rows a query may return or modify; approximate nearest-neighbor (ANN) search controls which candidates are examined. Neither guarantee substitutes for the other.
As an Amazon Associate I earn from qualifying purchases.
Why can a permission filter leave a vector query short?
pgvector uses exact nearest-neighbor search by default. Adding an HNSW or IVFFlat index enables approximate search, which trades some recall for speed. The index does not necessarily examine every vector: with approximate indexes, filtering is applied after the index scan. A query can therefore scan candidates, discard rows that fail its filter, and reach its LIMIT with fewer qualifying results than requested.
The pgvector documentation illustrates the effect with a filter matching 10% of rows and the default HNSW ef_search of 40: that setup yields four matching rows on average. This is an explanatory example, not a production benchmark or guarantee. Actual results depend on factors including data and query distribution, index settings, dead tuples, planner choices, and the shape and selectivity of the filter.
The pattern is especially visible when a user can access only a small share of a shared corpus and the candidate budget is limited. It is not a universal rule that the most restricted user always gets the fewest results: exact search, a suitably designed tenant-specific index or partition, and the corpus distribution can change the outcome.
Does PostgreSQL RLS filter vector search before or after the index scan?
RLS and ANN answer different questions. RLS policies determine which rows normal queries may return and which rows data-modification statements may insert, update, or delete. ANN indexing determines which candidate rows are examined and how closely the returned candidates approximate the true nearest neighbors. A correct RLS policy does not promise an exact nearest-neighbor search or a full top-k result set.
PostgreSQL says policy expressions are generally enforced before qualifications in a user query to help prevent inadvertent exposure through untrusted user-defined functions. That execution-order safeguard should not be confused with ANN recall: pgvector’s documented approximate-index behavior still applies filters after the index scan. The visible result can be both authorization-safe and shorter or less complete than expected.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →When RLS is enabled, the absence of an applicable policy means default deny: no rows are visible or modifiable. Superusers and roles with BYPASSRLS bypass row security. Table owners normally bypass it too, unless row security is forced for the table. Test with the production application role rather than relying only on an owner or developer account.
PostgreSQL 18: Row Security Policies · PostgreSQL 18: CREATE POLICY
How to diagnose short results without confusing recall and access control
- Reproduce the permission shapes. Create test data for broad-access and highly restricted users, using realistic tenant and document permissions.
- Run identical queries under the real application role. Use the same semantic query and requested
LIMITwith production-like policies. Record returned count and latency at several permission selectivities. - Compare ANN with an exact-search baseline. Compare result overlap or recall against exact results under the same authorization conditions. pgvector documents disabling index scans locally in a transaction as one way to obtain an exact-search comparison. Check the query plan so you know which method ran.
- Test candidate-expansion and index-layout options. Measure iterative scans, partial indexes, or partitioning against the same fixtures and queries; verify the actual plan rather than assuming a setting was used.
- Prove authorization separately. Confirm that unauthorized rows never reach the application response or prompt context. Include checks using the actual application role and account for owner or bypass-role behavior.
- Check the installed extension version. Iterative index scans are available starting with pgvector 0.8.0; do not assume settings for this feature work on older releases.
Which pgvector approaches can improve results for selective permissions?
Choose based on permission selectivity, recall versus exact search, p95 latency, index size and maintenance, the number of tenant or filter values, and isolation requirements. An exact-search baseline is useful even if the final design uses ANN.
| Approach | When it fits | Trade-offs and qualifications |
|---|---|---|
| Exact search on the authorized subset | Consider it when the eligible subset is small or permission filters are selective. A conventional index on filter columns can help make exact nearest-neighbor search practical. | Exact search has perfect recall. Measure its latency on the real workload rather than assuming ANN is faster for every subset. |
| Iterative ANN scans | Use when an approximate scan plus a selective filter often returns too few rows. | Available for HNSW and IVFFlat starting with pgvector 0.8.0. Scanning continues until enough qualifying rows are found or a configured limit is reached: hnsw.max_scan_tuples or ivfflat.max_probes. These limits mean a full result set is not guaranteed. Strict ordering preserves exact distance order for returned rows; relaxed ordering can improve recall while returning rows slightly out of order. More scanning increases work, so tune and measure. |
| Partial indexes | Useful when filters involve only a few fixed values. | Suitability depends on the actual set of filter values and query patterns. |
| List partitioning or separate tables | Consider for many filter values or when tenant isolation matters. | A shared approximate index can let one tenant’s vectors affect another tenant’s recall and speed. Separate layouts can address that concern but add design and maintenance costs. |
| HNSW versus IVFFlat | Compare both against workload-specific latency and recall requirements. | pgvector describes HNSW as generally offering a better speed–recall trade-off, with slower builds and higher memory use. IVFFlat builds faster and uses less memory, with lower query performance in that qualitative comparison. These are project-level descriptions, not workload-specific benchmark results. |
For exact tuning behavior and release-specific defaults, consult the pgvector project documentation.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
