Postgres 17 Bounded-Memory Parallel HNSW Index Build Guardrail

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Global Unbounded maintenance_work_mem and max_parallel_maintenance_workers Leading to Container Cgroup OOM Kills During HNSW Construction) 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.

Postgres 17 Bounded-Memory Parallel HNSW Index Build Guardrail

--- description: Postgres 17 Bounded-Memory Parallel HNSW Index Build Guardrail - Enforces strict mathematical bounds on parallel worker allocation and session-scoped maintenance_work_mem during pgvector HNSW index builds in Postgres 17. Prevents container OOM terminates by calculating max worker memory against container cgroup limits before triggering asynchronous index builds on high-cardinality embedding tables. globs: src/database/migrations/**/*.ts, src/database/migrations/**/*.sql, src/infrastructure/db/**/*.ts, src/jobs/index-maintenance/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-hnsw-parallel-memory-budget-standard" --- # Postgres 17 Bounded-Memory Parallel HNSW Index Build Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unbounded maintenance_work_mem and max_parallel_maintenance_workers Leading to Container Cgroup OOM Kills During HNSW Construction ## System Constraints You are a Principal Engineer enforcing Postgres 17 Memory-Bounded Parallel Index Build for pgvector. Mandate invariant mathematical partitioning of maintenance memory across worker processes to eliminate container OOM kills while indexing high-cardinality embedding tables. ## Strict Guidelines - Engineers MUST ALWAYS scope `maintenance_work_mem` and `max_parallel_maintenance_workers` inside an isolated transaction or session block when executing `CREATE INDEX CONCURRENTLY ... USING hnsw`. - Engineers MUST NEVER execute HNSW indexing with global server-level `maintenance_work_mem` scaled directly to container limit, as Postgres allocates `maintenance_work_mem` per worker in parallel index builds. - Engineers MUST enforce the invariant formula: `(maintenance_work_mem * (max_parallel_maintenance_workers + 1)) <= (container_memory_limit - postgres_shared_buffers - os_headroom)`. - Code SHOULD verify available OS cgroup memory dynamically via runtime queries or infrastructure constraints before provisioning workers for large vector dimensions (e.g., 1536d, 3072d). - Services MUST execute vector indexing outside of user-facing transactions, using background migration runners with automatic worker fallback when memory pressure exceeds 75% cgroup threshold. - Components NEVER issue unbounded `CONCURRENTLY` index builds on vector tables without setting `statement_timeout = '0'` and an explicit session-level bounded memory allocation. ## Bad vs Good Implementations ### Session-Bounded Dynamic Parallel HNSW Index Build Runner #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: maintenance_work_mem is assigned per parallel maintenance worker in Postgres 17 during parallel HNSW builds. Multiplying large values by the worker count quickly breaches the physical container cgroup memory limits, triggering the Linux OOM-killer. ```typescript import { Client } from 'pg'; export async function buildVectorIndexUnsafe(client: Client): Promise<void> { // FAILS: Assumes maintenance_work_mem is shared across all parallel workers. // In Postgres 17, parallel HNSW builds allocate up to maintenance_work_mem PER WORKER. // With 4 workers on an 8GB container with 4GB maintenance_work_mem, // memory consumption hits: 4GB * (4 + 1) = 20GB -> Instant cgroup OOM Kill. await client.query(`SET maintenance_work_mem = '4GB';`); await client.query(`SET max_parallel_maintenance_workers = 4;`); await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_embeddings_hnsw ON document_embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); `); } ``` #### Production Standard (Correct) > **Standard**: Mathematically divides the available cgroup memory (excluding shared_buffers and safety buffers) across the parallel maintenance workers plus the leader process, enforcing strict session-scoped containment with deterministic cleanup. ```typescript import { Client } from 'pg'; interface MemoryBudgetConfig { containerAvailableRamMb: number; sharedBuffersMb: number; safetyHeadroomMb: number; maxParallelWorkers: number; } export function calculateSafeWorkerMemory(config: MemoryBudgetConfig): { workerMemMb: number; workers: number } { const usablePoolMb = config.containerAvailableRamMb - config.sharedBuffersMb - config.safetyHeadroomMb; if (usablePoolMb <= 256) { throw new Error(`Insufficient headroom (${usablePoolMb}MB) for HNSW parallel index build.`); } // Formula: Usable Pool / (Workers + Leader Process) const totalProcesses = config.maxParallelWorkers + 1; const workerMemMb = Math.floor(usablePoolMb / totalProcesses); return { workerMemMb: Math.max(workerMemMb, 64), workers: config.maxParallelWorkers }; } export async function buildVectorIndexSafely(client: Client, budget: MemoryBudgetConfig): Promise<void> { const { workerMemMb, workers } = calculateSafeWorkerMemory(budget); // Bound memory explicitly to the dedicated session await client.query(`SET statement_timeout = 0;`); await client.query(`SET max_parallel_maintenance_workers = ${workers};`); await client.query(`SET maintenance_work_mem = '${workerMemMb}MB';`); try { await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_embeddings_hnsw ON document_embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); `); } finally { await client.query(`RESET maintenance_work_mem;`); await client.query(`RESET max_parallel_maintenance_workers;`); await client.query(`RESET statement_timeout;`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Bounded-Memory Parallel HNSW Index Build Guardrail

Postgres 17 Bounded-Memory Parallel HNSW Index Build Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict mathematical bounds on parallel worker allocation and session-scoped maintenance_work_mem during pgvector HNSW index builds in Postgres 17. Prevents container OOM terminates by calculating max worker memory against container cgroup limits before triggering asynchronous index builds on high-cardinality embedding tables. 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: Global Unbounded maintenance_work_mem and max_parallel_maintenance_workers Leading to Container Cgroup OOM Kills During HNSW Construction 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	Global Unbounded maintenance_work_mem and max_parallel_maintenance_workers Leading to Container Cgroup OOM Kills During HNSW Construction	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` inside an isolated transaction or session block when executing `CREATE INDEX CONCURRENTLY ... USING hnsw`.

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.