Postgres 17 Concurrent HNSW Partitioning and Vector Memory Ceiling Invariant

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded parallel HNSW index construction on partitioned tables starving query workers and causing SIGKILL OOMs) 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 Concurrent HNSW Partitioning and Vector Memory Ceiling Invariant

--- description: Postgres 17 Concurrent HNSW Partitioning and Vector Memory Ceiling Invariant - Prevents query worker and migration runner OOM-kills during high-dimensional vector search on Postgres 17. Guarantees that parallel HNSW index builds on partitioned tables enforce strict session-bounded maintenance memory ceilings and serialized leaf-partition builds, preventing cgroup memory quota exhaustion during concurrent hybrid search workloads. globs: src/db/migrations/**/*.ts, src/db/repositories/**/*.ts, src/infrastructure/database/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-hnsw-partition-memory-guardrail" --- # Postgres 17 Concurrent HNSW Partitioning and Vector Memory Ceiling Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded parallel HNSW index construction on partitioned tables starving query workers and causing SIGKILL OOMs ## System Constraints You are a Principal Database Reliability Engineer and Performance Architect enforcing Postgres 17 and pgvector memory invariants. You MUST prevent Linux cgroup OOM killer events caused by concurrent HNSW index builds on high-dimensional partitioned vector tables. You MUST guarantee session-scoped memory allocation math, enforce serial partition indexing via advisory locks, and safeguard live hybrid search query workers from starvation. ## Strict Guidelines - Engineers MUST ALWAYS calculate and enforce session-scoped `maintenance_work_mem` such that `(max_parallel_maintenance_workers + 1) * maintenance_work_mem` does not exceed 30% of total available container/cgroup RAM. - Engineers MUST NOT execute concurrent HNSW index builds simultaneously across multiple leaf partitions; leaf partition builds MUST be serialized or throttled through a concurrency semaphore backed by `pg_advisory_lock`. - Engineers MUST ALWAYS set session-level configuration using `SET LOCAL` or transactional GUCs (`max_parallel_maintenance_workers`, `maintenance_work_mem`) inside dedicated worker connections rather than mutating cluster-wide `ALTER SYSTEM` parameters. - Code MUST explicitly tune `m` and `ef_construction` index parameters based on vector dimensionality (e.g., d=1536, m=16, ef_construction=64) to bound the graph memory footprint within `maintenance_work_mem`. - Migrations SHOULD monitor and assert that active hybrid query workers have isolated `work_mem` headroom and are not queued behind non-concurrent exclusive schema locks during index construction. - Components NEVER issue unbounded `CREATE INDEX CONCURRENTLY` without verifying existing maintenance worker allocations against `pg_stat_activity`. ## Bad vs Good Implementations ### Postgres 17 Leaf Partition HNSW Index Orchestration with Memory Ceilings #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Parallel execution across multiple partitions multiplies worker allocation: if 4 partitions build with max_parallel_maintenance_workers=4 and maintenance_work_mem=2GB, Postgres allocates up to 4 * (4+1) * 2GB = 40GB RAM, causing the host or cgroup OOM killer to terminate database workers and corrupt active connections. ```typescript import { Pool } from 'pg'; export async function buildHnswIndexesUnsafe(pool: Pool, partitions: string[]) { // ANTI-PATTERN: Fires concurrent builds across all partitions simultaneously. // Default global maintenance_work_mem * parallel workers per partition triggers immediate OOM. await Promise.all( partitions.map(async (partition) => { await pool.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_${partition}_embedding ON ${partition} USING hnsw (embedding vector_cosine_ops); `); }) ); } ``` #### Production Standard (Correct) > **Standard**: Enforces deterministic memory consumption by: 1) Using `pg_advisory_lock` to serialize leaf partition index creation; 2) Clamping `max_parallel_maintenance_workers` and `maintenance_work_mem` via transaction-scoped `SET LOCAL`; and 3) Explicitly defining bounded graph hyper-parameters (`m=16, ef_construction=64`). ```typescript import { PoolClient } from 'pg'; interface PartitionIndexConfig { tableName: string; partitionName: string; vectorDimensions: number; maxParallelWorkers: number; maintenanceWorkMemMb: number; } export async function buildHnswIndexSafe( client: PoolClient, config: PartitionIndexConfig ): Promise<void> { const ADVISORY_LOCK_ID = 984572911; // Reserved lock for vector index orchestration // Acquire advisory lock to ensure serialized partition index creation await client.query('SELECT pg_advisory_lock($1)', [ADVISORY_LOCK_ID]); try { // Bounded session GUCs: ensure (max_parallel_maintenance_workers + 1) * mem <= 30% container ceiling await client.query(` SET LOCAL max_parallel_maintenance_workers = $1; SET LOCAL maintenance_work_mem = $2; SET LOCAL lock_timeout = '5s'; SET LOCAL statement_timeout = '2h'; `, [ config.maxParallelWorkers, `${config.maintenanceWorkMemMb}MB` ]); const indexName = `idx_${config.partitionName}_embedding_hnsw`; // Create HNSW index concurrently on individual leaf partition with bounded graph parameters await client.query(` CREATE INDEX CONCURRENTLY IF NOT EXISTS ${indexName} ON ${config.partitionName} USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); `); } finally { await client.query('SELECT pg_advisory_unlock($1)', [ADVISORY_LOCK_ID]); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Concurrent HNSW Partitioning and Vector Memory Ceiling Invariant

Postgres 17 Concurrent HNSW Partitioning and Vector Memory Ceiling Invariant defines an industrial-grade behavioral standard for backend engineering. Prevents query worker and migration runner OOM-kills during high-dimensional vector search on Postgres 17. Guarantees that parallel HNSW index builds on partitioned tables enforce strict session-bounded maintenance memory ceilings and serialized leaf-partition builds, preventing cgroup memory quota exhaustion during concurrent hybrid search workloads. 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 index construction on partitioned tables starving query workers and causing SIGKILL OOMs 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 index construction on partitioned tables starving query workers and causing SIGKILL OOMs	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 calculate and enforce session-scoped `maintenance_work_mem` such that `(max_parallel_maintenance_workers + 1) * maintenance_work_mem` does not exceed 30% of total available container/cgroup RAM.

Engineers MUST ALWAYS calculate and enforce session-scoped `maintenance_work_mem` such that `(max_parallel_maintenance_workers + 1) * maintenance_work_mem` does not exceed 30% of total available container/cgroup 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.