Deterministic HNSW Index Memory-Locking & Partition Pruning Standard

--- description: Deterministic HNSW Index Memory-Locking & Partition Pruning Standard - Enforces strict partition key propagation and pre-warmed HNSW index residency in PostgreSQL 17 with pgvector 0.8+. Prevents unpruned multi-partition graph traversals and memory cache thrashing during high-concurrency approximate nearest neighbor (ANN) vector queries. globs: src/repositories/**/*.ts, src/database/**/*.ts, src/services/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pgvector-08-hnsw-resident-set-partition-pruning" --- # Deterministic HNSW Index Memory-Locking & Partition Pruning Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unpartitioned HNSW Traversal with Cache Eviction Cascades ## System Constraints You are a Principal Database Architect enforcing pgvector 0.8 HNSW index invariants on PostgreSQL 17. You must ensure all vector queries strictly enforce declarative partition pruning, manage transaction-local `hnsw.ef_search` values, and verify active partition indexes are prewarmed into shared buffers to completely prevent out-of-core disk thrashing. ## Strict Guidelines - Engineers MUST ALWAYS pass declarative partition routing keys (e.g., `tenant_id`, `created_at` date ranges) as strict equality or bound predicates in vector similarity queries to ensure PostgreSQL executes partition pruning prior to index graph traversal. - Engineers MUST NOT execute vector distance queries directly against partitioned root relations without explicit partition constraints, as this forces all sub-partition HNSW graphs into `shared_buffers`. - Services MUST execute `pg_prewarm(index_rel, 'buffer', 'main')` on newly populated or activated partition HNSW indexes before routing production traffic to guarantee resident-set pinning. - Code MUST set `hnsw.ef_search` using `SET LOCAL` scoped strictly to the current transaction, preventing global session state leakage and unbounded memory allocation. - Database schemas MUST build HNSW indexes with explicit vector distance operators (e.g., `vector_l2_ops`, `vector_cosine_ops`, or `vector_ip_ops`) with tuned `m` and `ef_construction` parameters matched to the target partition buffer slice. ## Bad vs Good Implementations ### Unbounded Root Table Vector Scan vs. Partition-Pruned Prewarmed Traversal #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Querying the partitioned parent table without the partition key (e.g., `tenant_id` or date bucket) forces PostgreSQL to load and scan the HNSW index of every single partition. Under high concurrency, this triggers violent buffer pool thrashing, random disk reads, and latency spikes. ```typescript import { Pool } from 'pg'; export async function searchEmbeddings( pool: Pool, queryEmbedding: number[], limit: number ) { // ANTI-PATTERN: Searches root table without partition key. // Bypasses partition pruning, forcing PostgreSQL 17 to load HNSW // graphs for all 50+ partitions into shared_buffers, displacing page cache. const query = ` SELECT id, document_id, content, embedding <=> $1::vector AS distance FROM document_embeddings ORDER BY distance ASC LIMIT $2; `; const { rows } = await pool.query(query, [JSON.stringify(queryEmbedding), limit]); return rows; } ``` #### Production Standard (Correct) > **Standard**: Enforces strict partition keys (`tenant_id` and `bucket_year`) ensuring static run-time partition pruning down to a single partition whose HNSW index is resident in `shared_buffers`. Applies `SET LOCAL hnsw.ef_search` to avoid global memory pollution. ```typescript import { Pool } from 'pg'; interface VectorSearchOptions { tenantId: string; bucketYear: number; queryEmbedding: number[]; limit: number; efSearch?: number; } export async function searchEmbeddings( pool: Pool, options: VectorSearchOptions ) { const client = await pool.connect(); try { await client.query('BEGIN'); // Scope ef_search strictly to current transaction (pgvector 0.8+) const efSearch = options.efSearch ?? 40; await client.query('SET LOCAL hnsw.ef_search = $1', [efSearch]); // Invariant: Exact partition pruning predicates match composite key // PostgreSQL 17 prunes all inactive partition indexes before planning HNSW traversal const query = ` SELECT id, document_id, content, embedding <=> $1::vector AS distance FROM document_embeddings WHERE tenant_id = $2::text AND bucket_year = $3::int ORDER BY embedding <=> $1::vector ASC LIMIT $4; `; const { rows } = await client.query(query, [ JSON.stringify(options.queryEmbedding), options.tenantId, options.bucketYear, options.limit, ]); await client.query('COMMIT'); return rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Deterministic HNSW Index Memory-Locking & Partition Pruning Standard

Deterministic HNSW Index Memory-Locking & Partition Pruning Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict partition key propagation and pre-warmed HNSW index residency in PostgreSQL 17 with pgvector 0.8+. Prevents unpruned multi-partition graph traversals and memory cache thrashing during high-concurrency approximate nearest neighbor (ANN) vector queries. 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 Unpartitioned HNSW Traversal with Cache Eviction Cascades 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 Unpartitioned HNSW Traversal with Cache Eviction Cascades	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 pass declarative partition routing keys (e.g., `tenant_id`, `created_at` date ranges) as strict equality or bound predicates in vector similarity queries to ensure PostgreSQL executes partition pruning prior to index graph traversal.

Engineers MUST ALWAYS pass declarative partition routing keys (e.g., `tenant_id`, `created_at` date ranges) as strict equality or bound predicates in vector similarity queries to ensure PostgreSQL executes partition pruning prior to index graph traversal.

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.