Postgres 17 pgvector Iterative HNSW Work-Mem & Graph Scan Guardrail

--- description: Postgres 17 pgvector Iterative HNSW Work-Mem & Graph Scan Guardrail - Enforces transaction-scoped memory bounds (hnsw.max_scan_tuples, hnsw.iterative_scan, and work_mem) on Postgres 17 pgvector semantic queries. Prevents noisy-neighbor OOM crashes and server process terminations triggered by unbounded graph traversal across filtered multi-tenant vector indexes. globs: src/**/*.repository.ts, src/**/*.service.ts, src/**/*.dao.ts, src/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-pgvector-iterative-hnsw-workmem-protection" --- # Postgres 17 pgvector Iterative HNSW Work-Mem & Graph Scan Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Iterative HNSW Index Traversal Under Filtered Multi-Tenant Queries ## System Constraints You are a Principal Database Architect and Infrastructure Engineer enforcing memory limits for Postgres 17 and pgvector 0.7+. Mandate transactional isolation for all iterative HNSW index scans with SET LOCAL constraints on scan tuples and work_mem. ## Strict Guidelines - Engineers MUST ALWAYS isolate filtered pgvector queries within an explicit SQL transaction using SET LOCAL for GUC configurations to prevent connection pool poisoning. - Engineers MUST ALWAYS bound graph exploration by configuring 'SET LOCAL hnsw.max_scan_tuples' and 'SET LOCAL hnsw.iterative_scan = relaxed' (or strict) on filtered HNSW searches in Postgres 17. - Engineers MUST NOT execute session-level 'SET work_mem' or 'SET hnsw.ef_search' on persistent poolers (e.g., PgBouncer in transaction mode) without transaction-scoped resetting. - Queries MUST specify an explicit, aggressive 'SET LOCAL statement_timeout' alongside vector graph searches to fail fast before memory escalation can trigger the OS OOM killer. - Data access layers MUST enforce a hard ceiling on user-provided LIMIT clauses (maximum 100 tuples) when executing cosine (<=>), L2 (<->), or inner product (<#>) operators. ## Bad vs Good Implementations ### Unbounded Iterative HNSW Traversal vs. Safe Transaction-Local Parameterization #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Mutating GUCs with session-scoped SET poisons recycled connections in transaction-mode poolers. If the tenant filter is sparse, pgvector 0.7+/Postgres 17 iterative scan will endlessly traverse the HNSW graph attempting to satisfy the LIMIT, consuming unchecked memory until an OOM crash occurs. ```typescript import { Pool } from 'pg'; export class TenantVectorRepository { constructor(private readonly pool: Pool) {} async searchDocuments(tenantId: string, embedding: number[], limit: number) { // VIOLATION: Global session mutation leaks into pooler; // unbounded iterative scan traverses the entire index if tenantId matches few rows; // unconstrained work_mem and absent statement_timeout risk database OOM termination. await this.pool.query('SET hnsw.ef_search = 200;'); await this.pool.query('SET work_mem = "512MB";'); const query = ` SELECT id, content, (embedding <=> $1::vector) AS distance FROM tenant_documents WHERE tenant_id = $2 ORDER BY embedding <=> $1::vector LIMIT $3; `; const res = await this.pool.query(query, [JSON.stringify(embedding), tenantId, limit]); return res.rows; } } ``` #### Production Standard (Correct) > **Standard**: Wraps vector search in a distinct transaction using SET LOCAL. Strictly bounds graph iterations via hnsw.max_scan_tuples and hnsw.iterative_scan = relaxed, constrains work_mem to 32MB, and enforces an explicit statement timeout to shield multi-tenant nodes from memory exhaustion. ```typescript import { Pool, PoolClient } from 'pg'; export class TenantVectorRepository { private static readonly MAX_LIMIT = 100; private static readonly MAX_SCAN_TUPLES = 20_000; private static readonly STATEMENT_TIMEOUT_MS = 2_500; constructor(private readonly pool: Pool) {} async searchDocuments(tenantId: string, embedding: number[], limit: number) { const safeLimit = Math.min(Math.max(1, limit), TenantVectorRepository.MAX_LIMIT); const client: PoolClient = await this.pool.connect(); try { await client.query('BEGIN;'); // Enforce transaction-local safety limits to protect cluster memory await client.query('SET LOCAL hnsw.iterative_scan = relaxed;'); await client.query(`SET LOCAL hnsw.max_scan_tuples = ${TenantVectorRepository.MAX_SCAN_TUPLES};`); await client.query('SET LOCAL hnsw.ef_search = 100;'); await client.query('SET LOCAL work_mem = "32MB";'); await client.query(`SET LOCAL statement_timeout = ${TenantVectorRepository.STATEMENT_TIMEOUT_MS};`); const query = ` SELECT id, content, (embedding <=> $1::vector) AS distance FROM tenant_documents WHERE tenant_id = $2 ORDER BY embedding <=> $1::vector LIMIT $3; `; const res = await client.query(query, [JSON.stringify(embedding), tenantId, safeLimit]); await client.query('COMMIT;'); return res.rows; } catch (error) { await client.query('ROLLBACK;').catch(() => {}); throw error; } finally { client.release(); } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 pgvector Iterative HNSW Work-Mem & Graph Scan Guardrail

Postgres 17 pgvector Iterative HNSW Work-Mem & Graph Scan Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces transaction-scoped memory bounds (hnsw.max_scan_tuples, hnsw.iterative_scan, and work_mem) on Postgres 17 pgvector semantic queries. Prevents noisy-neighbor OOM crashes and server process terminations triggered by unbounded graph traversal across filtered multi-tenant vector indexes. 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 Index Traversal Under Filtered Multi-Tenant Queries 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 Index Traversal Under Filtered Multi-Tenant Queries	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 isolate filtered pgvector queries within an explicit SQL transaction using SET LOCAL for GUC configurations to prevent connection pool poisoning.

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.