Postgres 17 Vector HNSW Memory Budgeting & Partition Pruning Standard

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded Parallel HNSW Indexing on Parent Partition Tables with Default System Memory Budgets) 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 Vector HNSW Memory Budgeting & Partition Pruning Standard

--- description: Postgres 17 Vector HNSW Memory Budgeting & Partition Pruning Standard - Enforces strict session-level maintenance_work_mem quotas and deterministic partition pruning for HNSW vector indexes on Postgres 17. Prevents kernel OOM killer panics caused by multi-worker graph construction across partitioned billion-scale vector collections. Guarantees predictable memory footprints during concurrent index builds and hybrid semantic search executions. globs: src/db/**/*.ts, src/migrations/**/*.ts, src/repositories/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-vector-hnsw-memory-partition-pruning-standard" --- # Postgres 17 Vector HNSW Memory Budgeting & Partition Pruning Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Parallel HNSW Indexing on Parent Partition Tables with Default System Memory Budgets ## System Constraints You are a Principal Software Architect enforcing production reliability standards for Postgres 17 and pgvector (v0.7.0+). You must reject any code or migration that executes HNSW index builds on partitioned vector tables without explicit session-scoped memory budgeting and parallel worker clamps. Enforce partition-aware indexing, verify partition pruning predicates on hybrid queries, and eliminate unconstrained maintenance_work_mem allocations that trigger OS OOM killer invocations. ## Strict Guidelines - Engineers MUST ALWAYS set session-scoped 'maintenance_work_mem' immediately prior to running 'CREATE INDEX ... USING hnsw' and calculate total allocation as (maintenance_work_mem * (max_parallel_maintenance_workers + 1)) <= Available_Container_RAM * 0.4. - Engineers MUST NEVER execute 'CREATE INDEX CONCURRENTLY' directly on unpartitioned tables exceeding 50 million vector rows without temporal or tenant range partitioning. - Engineers MUST index vector partitions individually in sequential order or via a rate-limited task queue rather than issuing concurrent multi-partition index generation against the parent relation. - Engineers MUST ALWAYS verify partition pruning via EXPLAIN (ANALYZE, COSTS OFF) ensuring 'Partitions Removed' is non-zero when querying hybrid metadata and vector spaces. - Code SHOULD explicitly define 'hnsw.ef_search' at transaction or query level rather than altering global database configurations. - Engineers MUST NOT allow queries against hybrid vector partitions without static or parameterized partition keys in the WHERE clause, preventing sequential scans across unindexed or high-overhead partitions. - Services MUST monitor pg_stat_activity and lock timeouts during partition maintenance, enforcing 'SET lock_timeout = '10s'' and 'SET statement_timeout' on all DDL operations. ## Bad vs Good Implementations ### Unbounded HNSW Index Build on Partitioned Table (OOM Risk) vs Guarded Per-Partition Builder #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing an HNSW index build directly on a parent partitioned table or using default global maintenance_work_mem across multiple parallel workers spawns unconstrained memory allocations. On high-dimension vectors (e.g., 1536 dims), (maintenance_work_mem * max_parallel_maintenance_workers) quickly exceeds physical RAM limits, causing the host operating system to invoke the OOM killer and abruptly terminate the primary PostgreSQL process. ```typescript import { Pool } from 'pg'; export async function indexVectorData(pool: Pool) { // Anti-Pattern: Triggers unbounded maintenance memory consumption across parallel workers // Running index build on root partitioned table causes worker explosion and OOM const client = await pool.connect(); try { await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_embeddings_hnsw ON semantic_embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 32, ef_construction = 128); `); } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: The solution explicitly constrains max_parallel_maintenance_workers and maintenance_work_mem at the session level before performing the index build, bounding peak graph-construction memory. By operating on leaf partitions rather than the parent table and specifying explicit partition keys ('tenant_id') in vector queries, it leverages Postgres 17 runtime partition pruning and preserves operational stability. ```typescript import { Pool, PoolClient } from 'pg'; interface IndexBuildBudget { maxParallelWorkers: number; maintenanceWorkMemMb: number; } export async function indexVectorPartitionGuarded( pool: Pool, partitionName: string, budget: IndexBuildBudget = { maxParallelWorkers: 2, maintenanceWorkMemMb: 1024 } ): Promise<void> { const client: PoolClient = await pool.connect(); try { // Clamp execution safety invariants at the dedicated session level await client.query('BEGIN;'); await client.query(`SET LOCAL lock_timeout = '10s';`); await client.query(`SET LOCAL statement_timeout = '2h';`); await client.query(`SET LOCAL max_parallel_maintenance_workers = ${budget.maxParallelWorkers};`); await client.query(`SET LOCAL maintenance_work_mem = '${budget.maintenanceWorkMemMb}MB';`); await client.query('COMMIT;'); // Build index specifically on leaf partition with tuned graph parameters const indexName = `idx_${partitionName}_hnsw_vec`; await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS ${indexName} ON ${partitionName} USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); `); } finally { client.release(); } } export async function queryWithPartitionPruning( client: PoolClient, tenantId: string, queryVector: number[], topK: number ) { // Invariant: Enforce partition key predicate alongside vector similarity operator // Ensures Postgres 17 partition pruning bypasses non-matching partitions entirely await client.query(`SET LOCAL hnsw.ef_search = 40;`); const sql = ` EXPLAIN (ANALYZE, BUFFERS) SELECT id, tenant_id, embedding <=> $1::vector AS distance FROM semantic_embeddings WHERE tenant_id = $2 ORDER BY distance LIMIT $3; `; return await client.query(sql, [JSON.stringify(queryVector), tenantId, topK]); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Vector HNSW Memory Budgeting & Partition Pruning Standard

Postgres 17 Vector HNSW Memory Budgeting & Partition Pruning Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict session-level maintenance_work_mem quotas and deterministic partition pruning for HNSW vector indexes on Postgres 17. Prevents kernel OOM killer panics caused by multi-worker graph construction across partitioned billion-scale vector collections. Guarantees predictable memory footprints during concurrent index builds and hybrid semantic search executions. 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 HNSW Indexing on Parent Partition Tables with Default System Memory Budgets 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 HNSW Indexing on Parent Partition Tables with Default System Memory Budgets	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 set session-scoped 'maintenance_work_mem' immediately prior to running 'CREATE INDEX ... USING hnsw' and calculate total allocation as (maintenance_work_mem * (max_parallel_maintenance_workers + 1)) <= Available_Container_RAM * 0.4.

Engineers MUST ALWAYS set session-scoped 'maintenance_work_mem' immediately prior to running 'CREATE INDEX ... USING hnsw' and calculate total allocation as (maintenance_work_mem * (max_parallel_maintenance_workers + 1)) <= Available_Container_RAM * 0.4.

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.