Postgres 17 Partitioned HNSW Vector Scan Concurrency and Memory Guardrails

--- description: Postgres 17 Partitioned HNSW Vector Scan Concurrency and Memory Guardrails - Enforces strict query-level work memory limits, sub-partition pruning invariants, and worker concurrency caps for Postgres 17 pgvector HNSW index scans. Prevents shared buffer lock convoying, out-of-memory worker terminations, and excessive buffer pin contention during hybrid RAG vector searches. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/rag/**/*.ts, src/vector/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-partitioned-hnsw-concurrency-control" --- # Postgres 17 Partitioned HNSW Vector Scan Concurrency and Memory Guardrails > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Scatter-Gather HNSW Index Scans Across Partitions Inducing Buffer Lock Convoys ## System Constraints You are a Principal Database Architect and Performance Engineer enforcing Postgres 17 Partitioned HNSW Vector Scan Concurrency Control. You must ensure that all pgvector similarity queries operating on partitioned relational schemas explicitly constrain parallel worker fan-out, set transaction-scoped `hnsw.ef_search` and `work_mem` boundaries, enforce static sub-partition elimination, and prevent lock convoying under high-concurrency hybrid RAG search traffic. ## Strict Guidelines - Engineers MUST ALWAYS enforce static partition pruning via explicit partition-key predicates in the WHERE clause prior to evaluating vector distance operators (<=>, <#>, <+>). - Engineers MUST ALWAYS execute partitioned vector similarity searches inside scoped transactions using 'SET LOCAL max_parallel_workers_per_gather' and 'SET LOCAL hnsw.ef_search' to prevent global worker starvation and shared buffer pin contention. - Engineers MUST NOT permit unbounded parallel worker fan-out across multiple partitioned HNSW sub-indexes; workers per gather MUST be bounded to match available non-convoying vCPU quotas (typically 2 to 4 workers). - Services MUST explicitly set 'SET LOCAL work_mem' within vector search transactions to accommodate intermediate Top-K priority queues without inducing OS page thrashing or lock convoys across partition boundaries. - Code SHOULD combine relational scalar metadata pre-filtering with partitioned vector queries using two-stage CTEs or strict indexable sub-queries to prune partitions before computing vector embeddings distance. ## Bad vs Good Implementations ### Unbounded Cross-Partition Parallel Vector Search vs Controlled Partition-Pruned HNSW Scan #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Failing to constrain worker parallelism and omit the partition key (e.g., organization_id or createdAt bucket) forces Postgres 17 to initiate a scatter-gather parallel search across all physical partition HNSW indices. Each parallel worker attempts to hold buffer pins on multiple HNSW index pages simultaneously, resulting in catastrophic lock convoying, buffer pool eviction thrashing, and elevated p99 latencies under concurrent RAG load. ```typescript import { Pool } from 'pg'; export async function searchHybridRAG( pool: Pool, tenantId: string, embedding: number[], matchCount: number ) { // ANTI-PATTERN: No partition key pruning on the root table, default global worker parallelism, // unbounded hnsw.ef_search, causing severe parallel worker lock convoying across all sub-partitions. const query = ` SELECT id, document_id, content, embedding <=> $1::vector AS distance FROM knowledge_documents_partitioned WHERE status = 'active' ORDER BY embedding <=> $1::vector LIMIT $2; `; const result = await pool.query(query, [JSON.stringify(embedding), matchCount]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: The invariant solution leverages a scoped read-only transaction applying 'SET LOCAL' for 'max_parallel_workers_per_gather', 'hnsw.ef_search', and 'work_mem'. The query forces deterministic partition pruning via partition keys ('org_id', 'partition_year'), restricting parallel HNSW index traversals to precisely the designated sub-partitions and eliminating lock convoys across the storage engine. ```typescript import { Pool, PoolClient } from 'pg'; interface VectorSearchOptions { orgId: string; partitionYear: number; embedding: number[]; matchCount: number; efSearch?: number; maxWorkers?: number; } export async function searchHybridRAG( pool: Pool, options: VectorSearchOptions ) { const { orgId, partitionYear, embedding, matchCount, efSearch = 40, maxWorkers = 2 } = options; const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // 1. Isolate and bound vector index concurrency variables to this local transaction await client.query(` SET LOCAL max_parallel_workers_per_gather = ${maxWorkers}; SET LOCAL hnsw.ef_search = ${efSearch}; SET LOCAL work_mem = '32MB'; SET LOCAL enable_partitionwise_join = on; `); // 2. Query target partition using explicit pruning keys (org_id, partition_year) // alongside an indexed scalar predicate before calculating distance const query = ` WITH pruned_documents AS ( SELECT id, document_id, content, embedding FROM knowledge_documents_partitioned WHERE org_id = $1 AND partition_year = $2 AND status = 'active' ) SELECT id, document_id, content, embedding <=> $3::vector AS distance FROM pruned_documents ORDER BY embedding <=> $3::vector LIMIT $4; `; const vectorParam = `[${embedding.join(',')}]`; const result = await client.query(query, [ orgId, partitionYear, vectorParam, matchCount ]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Partitioned HNSW Vector Scan Concurrency and Memory Guardrails

Postgres 17 Partitioned HNSW Vector Scan Concurrency and Memory Guardrails defines an industrial-grade behavioral standard for backend engineering. Enforces strict query-level work memory limits, sub-partition pruning invariants, and worker concurrency caps for Postgres 17 pgvector HNSW index scans. Prevents shared buffer lock convoying, out-of-memory worker terminations, and excessive buffer pin contention during hybrid RAG vector searches. 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 Scatter-Gather HNSW Index Scans Across Partitions Inducing Buffer Lock Convoys 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 Scatter-Gather HNSW Index Scans Across Partitions Inducing Buffer Lock Convoys	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 enforce static partition pruning via explicit partition-key predicates in the WHERE clause prior to evaluating vector distance operators (<=>, <#>, <+>).

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.