Postgres 17 pgvector Partition-Local HNSW Index Memory Bounding

--- description: Postgres 17 pgvector Partition-Local HNSW Index Memory Bounding - Enforces strict partition-local memory isolation and dynamic maintenance_work_mem budgeting when generating Hierarchical Navigable Small World (HNSW) vector graph indexes on partitioned tables in Postgres 17. Prevents host kernel out-of-memory (OOM) killer invocations by disallowing global index creation across parent partitioned tables and bounding per-worker memory during parallel backfills. globs: src/db/**/*.ts, src/migrations/**/*.ts, src/repositories/**/*.ts, prisma/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pgvector-partition-hnsw-memory-bounding-production-standard" --- # Postgres 17 pgvector Partition-Local HNSW Index Memory Bounding > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unbounded HNSW Indexing on Parent Partitioned Tables Triggering OOM Terminations ## System Constraints You are a Principal Database Architect and Infrastructure Reliability Engineer enforcing Postgres 17 and pgvector (0.7.0+) memory-bounding invariants. You MUST strictly ensure that all vector indexes on high-throughput partitioned embedding tables are created partition-locally with explicit session-scoped maintenance_work_mem, concurrency limits, and max_parallel_maintenance_workers boundaries to guarantee zero OOM process terminates. ## Strict Guidelines - Engineers MUST ALWAYS create HNSW indexes on individual leaf partitions rather than executing CREATE INDEX on the root partitioned table without memory bounds. - Engineers MUST execute `SET LOCAL maintenance_work_mem` inside an isolated transaction or dedicated session specifically sized to `(available_ram * 0.5) / max_parallel_maintenance_workers` prior to running `CREATE INDEX CONCURRENTLY`. - Engineers MUST NOT allow unindexed active ingestion partitions; index creation MUST be staged asynchronously on closed, historical, or pre-allocated partitions before traffic cutover. - Code SHOULD verify `m` and `ef_construction` parameters in index creation scripts: high `m` (e.g., > 64) and `ef_construction` (e.g., > 200) dramatically inflate working graph memory and MUST be constrained per partition scale. - Services MUST execute index builds sequentially across partitions or throttle concurrency using an explicit advisory lock or worker queue to prevent aggregate maintenance memory blowouts. - Components NEVER execute DDL index migrations through standard connection pools without dropping pool connection lifetime or resetting maintenance session variables immediately after build completion. ## Bad vs Good Implementations ### Unbounded Root Partition HNSW Indexing vs Partition-Local Bounded Ingestion Indexing #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing CREATE INDEX directly on a parent partitioned table cascades down to all partitions simultaneously. In pgvector on Postgres, this can spawn uncontrolled graph builds whose cumulative working graph memory exceeds system limits, causing catastrophic backend termination. ```typescript import { Pool } from 'pg'; export async function createVectorIndexes(pool: Pool): Promise<void> { // ANTI-PATTERN: Creates HNSW index globally on parent table. // This triggers concurrent graph building across all underlying partitions, // ignoring per-partition boundaries and multiplying maintenance_work_mem consumption, // causing Linux OOM killer to terminate the Postgres backend process. await pool.query(` CREATE INDEX idx_document_embeddings_hnsw ON document_embeddings USING hnsw (embedding vector_cosine_ops) WITH (m = 64, ef_construction = 256); `); } ``` #### Production Standard (Correct) > **Standard**: The invariant solution targets the leaf partition directly, restricts concurrency via `max_parallel_maintenance_workers`, explicitly budgets `maintenance_work_mem`, builds non-blockingly using `CONCURRENTLY`, and ensures session isolation before returning the client to the connection pool. ```typescript import { Pool, PoolClient } from 'pg'; interface PartitionIndexConfig { partitionTableName: string; indexName: string; maxWorkers: number; maintenanceMemMb: number; m: number; efConstruction: number; } export async function createPartitionLocalHnswIndex( pool: Pool, config: PartitionIndexConfig ): Promise<void> { const client: PoolClient = await pool.connect(); try { // Validate identifier hygiene to prevent SQL injection in DDL if (!/^[a-z0-9_]+$/i.test(config.partitionTableName) || !/^[a-z0-9_]+$/i.test(config.indexName)) { throw new Error('Invalid partition or index identifier'); } // Bound per-worker memory and limit worker count strictly to this session await client.query(`SET max_parallel_maintenance_workers = ${config.maxWorkers};`); await client.query(`SET maintenance_work_mem = '${config.maintenanceMemMb}MB';`); // Create index CONCURRENTLY directly on the leaf partition const query = ` CREATE INDEX CONCURRENTLY IF NOT EXISTS ${config.indexName} ON ${config.partitionTableName} USING hnsw (embedding vector_cosine_ops) WITH (m = ${config.m}, ef_construction = ${config.efConstruction}); `; await client.query(query); } finally { // Reset session variables before returning connection to pool await client.query('RESET maintenance_work_mem;'); await client.query('RESET max_parallel_maintenance_workers;'); client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 pgvector Partition-Local HNSW Index Memory Bounding

Postgres 17 pgvector Partition-Local HNSW Index Memory Bounding defines an industrial-grade behavioral standard for backend engineering. Enforces strict partition-local memory isolation and dynamic maintenance_work_mem budgeting when generating Hierarchical Navigable Small World (HNSW) vector graph indexes on partitioned tables in Postgres 17. Prevents host kernel out-of-memory (OOM) killer invocations by disallowing global index creation across parent partitioned tables and bounding per-worker memory during parallel backfills. 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 HNSW Indexing on Parent Partitioned Tables Triggering OOM Terminations 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 HNSW Indexing on Parent Partitioned Tables Triggering OOM Terminations	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 create HNSW indexes on individual leaf partitions rather than executing CREATE INDEX on the root partitioned table without memory bounds.

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.