Postgres 17 Parallel HNSW Index Build Memory & Worker Saturation Guardrail

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded Parallel Workers and Maintenance Memory in Vector Re-Indexing Starving Live RAG Workloads) 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: Postgres 17 Parallel HNSW Index Build Memory & Worker Saturation Guardrail - Governs parallel HNSW vector index construction in PostgreSQL 17 to prevent multi-tenant query latency spikes and Linux OOM killer invocation. Enforces strict session-scoped bounds on maintenance_work_mem and max_parallel_maintenance_workers proportional to system headroom. globs: src/db/**/*.ts, src/migrations/**/*.ts, src/services/vector/**/*.ts, migrations/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-parallel-hnsw-memory-budgeting" --- # Postgres 17 Parallel HNSW Index Build Memory & Worker Saturation Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Parallel Workers and Maintenance Memory in Vector Re-Indexing Starving Live RAG Workloads ## System Constraints You are a Principal Database Reliability Engineer enforcing strict operational boundaries for PostgreSQL 17 pgvector HNSW parallel index builds in multi-tenant RAG systems. You must mandate dynamic memory budgeting, worker core isolation, and concurrent execution safeguards. ## Strict Guidelines - Engineers MUST ALWAYS scope 'maintenance_work_mem' and 'max_parallel_maintenance_workers' to an isolated dedicated session using explicit connection-level 'SET' directives, NEVER mutating global postgresql.conf settings. - Engineers MUST budget 'maintenance_work_mem' such that (max_parallel_maintenance_workers + 1) * maintenance_work_mem <= 25% of unreserved system RAM to avoid Postgres worker OOM termination. - Engineers MUST cap 'max_parallel_maintenance_workers' to <= 25% of available vCPUs (and at most 2 to 4 workers in shared multi-tenant instances) to preserve compute for concurrent ANN search queries. - Code MUST ALWAYS invoke 'CREATE INDEX CONCURRENTLY' for live production vector tables, setting an aggressive 'lock_timeout' (<= 5000ms) to avoid queueing locks behind long-running analytical queries. - Services MUST execute maintenance jobs through a direct TCP connection bypassing transaction-pooling proxies like PgBouncer to prevent connection state corruption or pool starvation. ## Bad vs Good Implementations ### HNSW Parallel Index Build Configuration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executes non-concurrent index creation via a shared connection pool without capping maintenance workers or session memory. This locks the table against writes, exhausts host memory across parallel workers, and spikes RAG query p99 latency to timeouts. ```typescript import { Pool } from 'pg'; export async function reindexTenantVectors(pool: Pool, tableName: string) { // BAD: Executing over standard pooled connection with default/global settings. // In PG17 + pgvector, parallel workers each allocate maintenance_work_mem. // If maintenance_work_mem=8GB and max_parallel_maintenance_workers=8, // this consumes ~72GB RAM, saturates all CPUs, and triggers OOM killer. await pool.query(` CREATE INDEX idx_tenant_vectors_embedding ON ${tableName} USING hnsw (embedding vector_cosine_ops) WITH (m = 24, ef_construction = 200); `); } ``` #### Production Standard (Correct) > **Standard**: Uses a dedicated direct connection with mathematically budgeted maintenance_work_mem to bound cumulative worker memory beneath the OOM threshold, limits worker threads to 25% of vCPUs to prevent RAG query starvation, and uses CREATE INDEX CONCURRENTLY with a lock timeout. ```typescript import { Client } from 'pg'; interface IndexBudgetOptions { availableRamMb: number; vCpuCount: number; tableName: string; indexName: string; } export async function buildHnswIndexConcurrently( directAdminClient: Client, options: IndexBudgetOptions ): Promise<void> { // GOOD: Calculate deterministic headroom (<= 25% RAM and <= 25% vCPUs) const workerLimit = Math.max(1, Math.min(4, Math.floor(options.vCpuCount * 0.25))); const totalAllowedMemoryMb = Math.floor(options.availableRamMb * 0.25); // PostgreSQL allocates maintenance_work_mem per parallel worker + leader const perWorkerMemoryMb = Math.floor(totalAllowedMemoryMb / (workerLimit + 1)); // Run in dedicated session outside of PgBouncer transaction pooling await directAdminClient.query(` SET lock_timeout = '5s'; SET statement_timeout = '0'; SET max_parallel_maintenance_workers = ${workerLimit}; SET maintenance_work_mem = '${perWorkerMemoryMb}MB'; `); try { await directAdminClient.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS ${options.indexName} ON ${options.tableName} USING hnsw (embedding vector_cosine_ops) WITH (m = 24, ef_construction = 200); `); } finally { await directAdminClient.query(` RESET lock_timeout; RESET statement_timeout; RESET max_parallel_maintenance_workers; RESET maintenance_work_mem; `); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Parallel HNSW Index Build Memory & Worker Saturation Guardrail

Postgres 17 Parallel HNSW Index Build Memory & Worker Saturation Guardrail defines an industrial-grade behavioral standard for backend engineering. Governs parallel HNSW vector index construction in PostgreSQL 17 to prevent multi-tenant query latency spikes and Linux OOM killer invocation. Enforces strict session-scoped bounds on maintenance_work_mem and max_parallel_maintenance_workers proportional to system headroom. 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 Workers and Maintenance Memory in Vector Re-Indexing Starving Live RAG Workloads 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 Workers and Maintenance Memory in Vector Re-Indexing Starving Live RAG Workloads	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 scope 'maintenance_work_mem' and 'max_parallel_maintenance_workers' to an isolated dedicated session using explicit connection-level 'SET' directives, NEVER mutating global postgresql.conf settings.

Engineers MUST ALWAYS scope 'maintenance_work_mem' and 'max_parallel_maintenance_workers' to an isolated dedicated session using explicit connection-level 'SET' directives, NEVER mutating global postgresql.conf settings.

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.