pgvector 0.8+ HNSW Filtered Query Iterative Search Guardrail

--- description: pgvector 0.8+ HNSW Filtered Query Iterative Search Guardrail - Enforces strict bounds on dynamic search-list expansion and mandatory fallback routing for filtered HNSW index scans in pgvector 0.8+. Prevents catastrophic tail-latency blowups caused by iterative index scanning exhausting ef_search when filter selectivity drops below index partition thresholds. globs: src/repositories/**/*.{ts,js,py,go}, src/services/**/*.{ts,js,py,go}, src/db/**/*.{ts,js,py,go}, prisma/schema.prisma, migrations/**/*.{sql,ts} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pgvector-08-hnsw-filtered-query-guardrail" --- # pgvector 0.8+ HNSW Filtered Query Iterative Search Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Iterative HNSW Traversal on High-Selectivity Predicates Without Fallbacks ## System Constraints You are a Principal Database Infrastructure Engineer enforcing strict pgvector 0.8+ HNSW filtered search safety standards. Filtered ANN queries with pgvector 0.8 use iterative index scanning that can degrade into exponential page fetches or sequential scan blowouts when filter predicates are overly selective. Mandate deterministic limits on hnsw.ef_search, hnsw.max_scan_tuples, transaction-level statement timeouts, and selective pre-filter routing. ## Strict Guidelines - Engineers MUST ALWAYS configure local transaction-scoped `hnsw.max_scan_tuples` and `hnsw.ef_search` alongside `statement_timeout` before executing filtered vector queries. - Engineers MUST NOT rely on default global HNSW index parameters when filtering on sparse metadata attributes (e.g., tenant_id, status, user_id). - Services MUST implement selectivity-aware query routing: if estimated candidate rows are less than 1% of the total dataset or below `hnsw.ef_search * 2`, bypass HNSW and route directly to an exact flat scan (`ORDER BY vector <=> $1 LIMIT k`) via a btree/partial index. - Engineers MUST NEVER execute filtered vector queries within long-lived shared connection pools without resetting transaction-level pgvector session variables (`SET LOCAL`). - Queries SHOULD utilize composite partial HNSW indexes (`CREATE INDEX ... ON items USING hnsw (embedding vector_cosine_ops) WHERE tenant_id = '...'`) when multi-tenancy filters are strict and predictable. ## Bad vs Good Implementations ### PostgreSQL Transaction-Safe Filtered HNSW Execution with Adaptive Fallback #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: In pgvector 0.8+, filtered HNSW queries perform iterative scans up to hnsw.max_scan_tuples (which defaults to 20,000) while expanding ef_search. For highly restrictive filters (e.g. low tenant counts), this causes the database to exhaustively traverse graph nodes that fail the predicate, leading to massive disk I/O, cache thrashing, and p99 latency spikes exceeding multiple seconds. ```typescript import { sql } from 'kysely'; import { db } from './database'; export async function searchTenantItems(tenantId: string, embedding: number[], limit: number) { // Anti-pattern: Unbounded iterative scan on high-selectivity filter // When tenant has few records, pgvector 0.8 iteratively scans the entire graph, // causing severe lock contention and catastrophic tail-latency (O(N) random disk reads). return await db.selectFrom('items') .selectAll() .where('tenant_id', '=', tenantId) .where('is_deleted', '=', false) .orderBy(sql`embedding <=> ${JSON.stringify(embedding)}::vector`) .limit(limit) .execute(); } ``` #### Production Standard (Correct) > **Standard**: This pattern bounds runtime execution with `hnsw.max_scan_tuples` and `statement_timeout` scoped strictly to a single transaction using `SET LOCAL`. It evaluates predicate selectivity and switches to an exact sequential scan on the pre-filtered subset when candidate density is low, preventing the HNSW graph traversal from degrading into catastrophic random-page IO. ```typescript import { sql } from 'kysely'; import { db } from './database'; interface VectorSearchOptions { tenantId: string; embedding: number[]; limit: number; maxScanTuples?: number; efSearch?: number; statementTimeoutMs?: number; } export async function searchTenantItemsSafely(options: VectorSearchOptions) { const { tenantId, embedding, limit, maxScanTuples = 5000, efSearch = 100, statementTimeoutMs = 1500 } = options; const vectorParam = JSON.stringify(embedding); return await db.transaction().execute(async (trx) => { // 1. Enforce strict bounded execution parameters via SET LOCAL await sql`SET LOCAL statement_timeout = ${sql.raw(statementTimeoutMs.toString())}`.execute(trx); await sql`SET LOCAL hnsw.ef_search = ${sql.raw(efSearch.toString())}`.execute(trx); await sql`SET LOCAL hnsw.max_scan_tuples = ${sql.raw(maxScanTuples.toString())}`.execute(trx); // 2. Sample tenant record count to determine selectivity routing threshold const countResult = await trx.selectFrom('items') .select(sql<number>`count(*)::int`.as('count')) .where('tenant_id', '=', tenantId) .where('is_deleted', '=', false) .executeTakeFirstOrThrow(); // 3. Fallback routing: Exact flat scan if candidate pool is too small for index traversal if (countResult.count <= efSearch * 2) { await sql`SET LOCAL enable_indexscan = off`.execute(trx); } return await trx.selectFrom('items') .selectAll() .where('tenant_id', '=', tenantId) .where('is_deleted', '=', false) .orderBy(sql`embedding <=> ${vectorParam}::vector`) .limit(limit) .execute(); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: pgvector 0.8+ HNSW Filtered Query Iterative Search Guardrail

pgvector 0.8+ HNSW Filtered Query Iterative Search Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict bounds on dynamic search-list expansion and mandatory fallback routing for filtered HNSW index scans in pgvector 0.8+. Prevents catastrophic tail-latency blowups caused by iterative index scanning exhausting ef_search when filter selectivity drops below index partition thresholds. Modern software architectures require strict adherence to deterministic patterns to guarantee high availability, maintainability, and zero security vulnerabilities.

Framework Paradigm Shift & Drift MechanicsLLMs are trained on millions of open-source repositories containing outdated code, legacy idioms, and unpatched antipatterns. When coding assistants generate implementations for this stack, they default to common statistical tokens: Unbounded Iterative HNSW Traversal on High-Selectivity Predicates Without Fallbacks Without an explicit system instruction file, developers spend hours debugging silent failures.

Failure Modes & Production Blast Radius

Hazard Analysis: What Breaks Without This Rule

The following failure modes have been cataloged from hundreds of hours of production code reviews where AI assistants produced non-compliant implementations:

Failure Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Unbounded Iterative HNSW Traversal on High-Selectivity Predicates Without Fallbacks	Critical	Violating this industrial standard introduces regression bugs, security risks, and unexpected runtime failures in production. Enforce explicit behavioral system directives across all AI coding assistants in your repository.
Silent Architecture Inconsistency	AI assistants produce mixed paradigms across files, creating fragmented codebase conventions.	Medium	Increases cognitive load during code reviews and leads to maintenance debt across engineering teams. Pin project conventions in .cursor/rules, CLAUDE.md, and AGENTS.md so all team agents follow identical guardrails.

Architectural Directives in Detail

Step-by-Step Production Directives

Each rule constraint below is injected into the AI agent's system prompt to establish deterministic boundaries:

Engineers MUST ALWAYS configure local transaction-scoped `hnsw.max_scan_tuples` and `hnsw.ef_search` alongside `statement_timeout` before executing filtered vector queries.

Rationale:AI models frequently generate unconstrained code here because statistical language distributions lack project-specific architectural context. Mandating this directive ensures that generated code adheres to verified industrial production requirements.