pgvector 0.8 HNSW Iterative Scan Memory & work_mem Sizing Standard

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded HNSW Iterative Scans under Default/Excessive work_mem Inducing Worker Process OOM Kills) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

--- description: pgvector 0.8 HNSW Iterative Scan Memory & work_mem Sizing Standard - Enforces transaction-scoped work_mem budgeting and strict hnsw.max_scan_tuples bounds when using pgvector 0.8+ iterative index scans. Prevents PostgreSQL backend worker OOM termination caused by unbounded candidate queues and memory accumulation during high-concurrency filtered RAG retrieval. globs: src/**/*.ts, src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, lib/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pgvector-hnsw-iterative-search-memory-standard" --- # pgvector 0.8 HNSW Iterative Scan Memory & work_mem Sizing Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded HNSW Iterative Scans under Default/Excessive work_mem Inducing Worker Process OOM Kills ## System Constraints You are a Principal Database Architect enforcing pgvector 0.8 HNSW Iterative Index Search Memory Sizing Standards. Mandate transaction-scoped work_mem allocations, explicit bounds on hnsw.max_scan_tuples, and proper scan mode configurations (relaxed_order vs strict_order). Reject any vector search implementation that exposes PostgreSQL worker backends to unbounded candidate queuing or shared connection-level work_mem bloat under concurrent RAG workloads. ## Strict Guidelines - Engineers MUST ALWAYS isolate iterative vector scans inside an explicit transaction or query-level scope using SET LOCAL to avoid persistent connection-level memory pollution. - Engineers MUST ALWAYS specify a finite hnsw.max_scan_tuples limit (e.g., 20,000) when hnsw.iterative_scan is enabled to prevent pathological sequential index traversals on selective relational filters. - Engineers MUST NOT rely on instance-level or user-level global work_mem for vector workloads; work_mem MUST be explicitly budgeted via SET LOCAL work_mem based on ef_search and expected concurrent queries (Concurrent_Queries * work_mem <= Available_RAM * 0.7). - Code SHOULD prefer hnsw.iterative_scan = 'relaxed_order' over 'strict_order' for RAG semantic searches where sub-millisecond candidate re-ranking tolerates minor distance discrepancies, reducing memory overhead. - Services MUST monitor and fail fast on vector queries where hnsw.max_scan_tuples is reached without satisfying LIMIT rather than falling back to unindexed sequential scans in latency-sensitive paths. - Components NEVER execute raw vector similarity operators across filtered tables without verifying that an HNSW index is actively selected via EXPLAIN (FORMAT JSON) analysis. ## Bad vs Good Implementations ### Configuring Transaction-Scoped HNSW Iterative Search with Safe Memory Budgeting #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing filtered HNSW searches without scoping work_mem and setting hnsw.max_scan_tuples allows the iterative index scan to consume unbounded backend memory if the filter matches few rows, causing Postgres backend worker termination (Linux OOM killer) and cascading connection pool drops under concurrent traffic. ```typescript import { Pool } from 'pg'; const pool = new Pool(); export async function searchEmbeddings(tenantId: string, queryEmbedding: number[], limit = 10) { // ANTI-PATTERN: Relies on global connection configuration. // If hnsw.iterative_scan is enabled globally without hnsw.max_scan_tuples, // a selective tenant_id filter will force pgvector to scan millions of index // tuples into work_mem, exhausting backend memory under concurrent load. const query = ` SELECT id, document_chunk FROM rag_documents WHERE tenant_id = $1 ORDER BY embedding <=> $2::vector LIMIT $3; `; const { rows } = await pool.query(query, [tenantId, JSON.stringify(queryEmbedding), limit]); return rows; } ``` #### Production Standard (Correct) > **Standard**: Wraps the retrieval in a read-only transaction using SET LOCAL to explicitly cap work_mem at 32MB, enables relaxed-order iterative scans, and halts candidate collection at 20,000 scan tuples. This guarantees that backend memory consumption is strictly bounded and leaves connection pool states clean upon release. ```typescript import { Pool, PoolClient } from 'pg'; const pool = new Pool(); export interface SearchOptions { tenantId: string; queryEmbedding: number[]; limit?: number; workMemMb?: number; maxScanTuples?: number; efSearch?: number; } export async function searchEmbeddings({ tenantId, queryEmbedding, limit = 10, workMemMb = 32, maxScanTuples = 20000, efSearch = 100, }: SearchOptions) { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // Bound memory allocations to this isolated transaction scope await client.query(`SET LOCAL work_mem = '${workMemMb}MB'`); await client.query(`SET LOCAL hnsw.ef_search = ${efSearch}`); await client.query("SET LOCAL hnsw.iterative_scan = 'relaxed_order'"); await client.query(`SET LOCAL hnsw.max_scan_tuples = ${maxScanTuples}`); const query = ` SELECT id, document_chunk, (embedding <=> $2::vector) AS distance FROM rag_documents WHERE tenant_id = $1 ORDER BY embedding <=> $2::vector LIMIT $3; `; const { rows } = await client.query(query, [ tenantId, JSON.stringify(queryEmbedding), limit, ]); await client.query('COMMIT'); return rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: pgvector 0.8 HNSW Iterative Scan Memory & work_mem Sizing Standard

pgvector 0.8 HNSW Iterative Scan Memory & work_mem Sizing Standard defines an industrial-grade behavioral standard for backend engineering. Enforces transaction-scoped work_mem budgeting and strict hnsw.max_scan_tuples bounds when using pgvector 0.8+ iterative index scans. Prevents PostgreSQL backend worker OOM termination caused by unbounded candidate queues and memory accumulation during high-concurrency filtered RAG retrieval. 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 HNSW Iterative Scans under Default/Excessive work_mem Inducing Worker Process OOM Kills 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 HNSW Iterative Scans under Default/Excessive work_mem Inducing Worker Process OOM Kills	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 iterative vector scans inside an explicit transaction or query-level scope using SET LOCAL to avoid persistent connection-level memory pollution.

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.