Postgres 17 Streaming HNSW Vector Index Memory-Budget Guardrail

--- description: Postgres 17 Streaming HNSW Vector Index Memory-Budget Guardrail - Enforces strict bounded memory allocation and concurrency constraints for pgvector HNSW indexing during active database workloads. Prevents aggressive buffer pool thrashing, query-path latency spikes, and vector recall degradation caused by unconstrained maintenance work memory and parallel worker oversubscription. globs: src/infrastructure/database/**/*.ts, src/db/migrations/**/*.ts, src/db/indexing/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-hnsw-memory-budget-guardrail" --- # Postgres 17 Streaming HNSW Vector Index Memory-Budget Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Parallel Maintenance Memory Exhausting Shared Buffers and Starving Query Paths ## System Constraints You are a Principal Software Architect enforcing production reliability standards for PostgreSQL 17 and pgvector. When building or reindexing HNSW vector graphs under continuous ingestion, you must enforce explicit per-session memory budgets (maintenance_work_mem, max_parallel_maintenance_workers) and parameter pinning (m, ef_construction). Never permit unconstrained parallel maintenance allocations that evict active shared_buffers or trigger OS out-of-memory kills. ## Strict Guidelines - Engineers MUST ALWAYS compute aggregate build memory budget: (1 + max_parallel_maintenance_workers) * maintenance_work_mem MUST NOT exceed 25% of unreserved system RAM. - Engineers MUST NOT execute HNSW index builds on shared application pool connections without strict local session limits and guaranteed post-execution resets. - Engineers MUST ALWAYS use CREATE INDEX CONCURRENTLY when indexing in active production environments to prevent exclusive write locks on hot ingestion tables. - Code MUST explicitly define HNSW hyperparameters (m, ef_construction) rather than relying on server defaults to prevent nondeterministic graph recall decay under high concurrency. - Services MUST isolate vector index maintenance into an out-of-band maintenance worker connection with explicit statement timeouts and worker quotas. - Components NEVER execute global ALTER SYSTEM commands for vector indexing operations; all overrides MUST be applied locally via session-scoped SET parameters. ## Bad vs Good Implementations ### Unbounded HNSW Index Creation vs Guarded Bounded Build Session #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing CREATE INDEX CONCURRENTLY without pinning session-level maintenance memory and parallel workers allows Postgres to spawn workers that each allocate maintenance_work_mem. Under streaming ingestion, this induces severe buffer pool cache thrashing, evicting warm pages from shared_buffers, inflating query latencies, and potentially causing OS-level OOM terminations. ```typescript import { Pool } from 'pg'; export async function createVectorIndex(pool: Pool): Promise<void> { // Flaw: Executes on arbitrary pooled connection with server defaults. // Can spawn default max_parallel_maintenance_workers, allocating unconstrained // maintenance_work_mem per worker. This evicts shared_buffers, tanks cache hit rates, // and starves p99 read paths during continuous streaming ingestion. await pool.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_items_embedding_hnsw ON items USING hnsw (embedding vector_cosine_ops); `); } ``` #### Production Standard (Correct) > **Standard**: Explicitly budgets total maintenance RAM across leader and worker threads, bounds parallel workers, sets deterministic HNSW hyperparameters (m, ef_construction), and enforces session cleanup upon completion to prevent memory setting leakage into pooled connections. ```typescript import { PoolClient } from 'pg'; interface HnswBudgetOptions { maxParallelWorkers: number; perWorkerMemMb: number; m: number; efConstruction: number; } const PRODUCTION_HNSW_BUDGET: HnswBudgetOptions = { maxParallelWorkers: 2, perWorkerMemMb: 1024, // 1GB per worker m: 16, efConstruction: 64, }; export async function createVectorIndexGuarded( client: PoolClient, budget: HnswBudgetOptions = PRODUCTION_HNSW_BUDGET ): Promise<void> { // Invariant: Total maintenance memory must be strictly bounded const totalPlannedMemMb = budget.perWorkerMemMb * (1 + budget.maxParallelWorkers); const MAX_PERMISSIBLE_RAM_MB = 4096; if (totalPlannedMemMb > MAX_PERMISSIBLE_RAM_MB) { throw new Error( `HNSW budget violation: Planned memory ${totalPlannedMemMb}MB exceeds safety ceiling of ${MAX_PERMISSIBLE_RAM_MB}MB` ); } try { // Pin session-scoped memory constraints to avoid evicting shared_buffers await client.query(`SET LOCAL maintenance_work_mem = '${budget.perWorkerMemMb}MB'`); await client.query(`SET LOCAL max_parallel_maintenance_workers = ${budget.maxParallelWorkers}`); await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_items_embedding_hnsw ON items USING hnsw (embedding vector_cosine_ops) WITH (m = ${budget.m}, ef_construction = ${budget.efConstruction}) `); } finally { // Ensure connection returns to pool in clean state await client.query('RESET maintenance_work_mem'); await client.query('RESET max_parallel_maintenance_workers'); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Streaming HNSW Vector Index Memory-Budget Guardrail

Postgres 17 Streaming HNSW Vector Index Memory-Budget Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict bounded memory allocation and concurrency constraints for pgvector HNSW indexing during active database workloads. Prevents aggressive buffer pool thrashing, query-path latency spikes, and vector recall degradation caused by unconstrained maintenance work memory and parallel worker oversubscription. 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 Parallel Maintenance Memory Exhausting Shared Buffers and Starving Query Paths 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 Parallel Maintenance Memory Exhausting Shared Buffers and Starving Query Paths	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 compute aggregate build memory budget

Engineers MUST ALWAYS compute aggregate build memory budget: (1 + max_parallel_maintenance_workers) * maintenance_work_mem MUST NOT exceed 25% of unreserved system RAM.

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.