pgvector 0.8+ / Postgres 17 Partition-Pruned Iterative HNSW Scan Invariant

--- description: pgvector 0.8+ / Postgres 17 Partition-Pruned Iterative HNSW Scan Invariant - Requires strict partition boundary pushdown and explicit iterative HNSW scan configuration in pgvector 0.8+ and Postgres 17. Omitting partition keys or relying on unindexed metadata predicates forces HNSW iterative traversal to exhaust candidate queues and fall back to multi-second unindexed sequential scans. globs: src/**/*.repository.ts, src/**/*.service.ts, src/db/**/*.sql, src/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pgvector-hnsw-partition-iterative-filter-pushdown" --- # pgvector 0.8+ / Postgres 17 Partition-Pruned Iterative HNSW Scan Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unpartitioned Vector Search with Unindexed Post-Filters Triggering Sequential Scans ## System Constraints You are a Principal Database Reliability Engineer specializing in Postgres 17 and pgvector 0.8+. Mandate strict partition pruning and iterative HNSW scan controls on all vector similarity queries. Prevent catastrophic planner fallbacks to sequential scans by requiring partition keys in query predicates, configuring session-level iterative scan parameters, and validating query execution plans. ## Strict Guidelines - Engineers MUST ALWAYS include the physical partition key (e.g., tenant_id, org_id, or temporal bucket) as an explicit equality or bounded range predicate in the WHERE clause to enable Postgres 17 static partition pruning before vector index evaluation. - Engineers MUST configure 'SET LOCAL hnsw.iterative_scan = relaxed' or 'SET LOCAL hnsw.iterative_scan = strict' within the transaction for filtered vector searches in pgvector 0.8+, avoiding default 'off' behavior that discards candidate nodes. - Engineers MUST NOT execute vector similarity queries with high-selectivity scalar filters against monolithic, unpartitioned HNSW tables without explicit 'hnsw.max_scan_tuples' limits, which prevents unbounded graph traversal. - Services MUST execute filtered vector queries within a dedicated read-only transaction block when applying custom index tuning parameters ('hnsw.ef_search', 'hnsw.iterative_scan') to prevent cross-request session contamination. - Components NEVER execute ad-hoc JSONB or arbitrary unindexed metadata filters in the same WHERE clause as the vector cosine/L2 operator without a matching composite or partition boundary. ## Bad vs Good Implementations ### Filtered Vector Search with Iterative HNSW Pushdown and Partition Pruning #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The query fails to configure Postgres 17 / pgvector 0.8+ iterative scan knobs ('hnsw.iterative_scan'). If org_id is not a partition key or if 'status' has high selectivity, HNSW traversal runs out of candidate vectors within ef_search and Postgres defaults to a full table sequential scan. ```typescript import { Pool } from 'pg'; export async function searchEmbeddings(pool: Pool, orgId: string, embedding: number[], limit: number) { // FAILS: Unpartitioned scan, missing iterative_scan configuration. // When status != 'active' or orgId selectivity is low, pgvector drops graph candidates // and Postgres falls back to an unindexed sequential scan across millions of vectors. const query = ` SELECT id, document_id, content, 1 - (embedding <=> $1::vector) AS similarity FROM document_embeddings WHERE org_id = $2 AND status = 'active' ORDER BY embedding <=> $1::vector LIMIT $3; `; const result = await pool.query(query, [JSON.stringify(embedding), orgId, limit]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Explicitly scopes transaction-level parameters ('hnsw.iterative_scan = relaxed', 'hnsw.max_scan_tuples', 'hnsw.ef_search') within a read-only transaction, ensuring pgvector 0.8+ explores additional index nodes dynamically rather than falling back to a sequential scan. Enforces partition key 'org_id' in the WHERE clause for Postgres 17 declarative partition pruning. ```typescript import { Pool, PoolClient } from 'pg'; export interface SearchResult { id: string; documentId: string; content: string; similarity: number; } export async function searchEmbeddings( pool: Pool, orgId: string, embedding: number[], limit: number ): Promise<SearchResult[]> { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // pgvector 0.8+ iterative scan handles filtered vector queries without full graph exhaustion await client.query("SET LOCAL hnsw.iterative_scan = 'relaxed'"); await client.query('SET LOCAL hnsw.ef_search = 100'); await client.query('SET LOCAL hnsw.max_scan_tuples = 20000'); // Table document_embeddings_partitioned is partitioned by LIST (org_id) // Postgres 17 performs direct partition pruning before evaluating the local HNSW index const query = ` SELECT id, document_id AS "documentId", content, 1 - (embedding <=> $1::vector) AS similarity FROM document_embeddings_partitioned WHERE org_id = $2 AND status = 'active' ORDER BY embedding <=> $1::vector LIMIT $3; `; const result = await client.query(query, [ JSON.stringify(embedding), orgId, limit ]); 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: pgvector 0.8+ / Postgres 17 Partition-Pruned Iterative HNSW Scan Invariant

pgvector 0.8+ / Postgres 17 Partition-Pruned Iterative HNSW Scan Invariant defines an industrial-grade behavioral standard for backend engineering. Requires strict partition boundary pushdown and explicit iterative HNSW scan configuration in pgvector 0.8+ and Postgres 17. Omitting partition keys or relying on unindexed metadata predicates forces HNSW iterative traversal to exhaust candidate queues and fall back to multi-second unindexed sequential scans. 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 Vector Search with Unindexed Post-Filters Triggering Sequential Scans 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 Vector Search with Unindexed Post-Filters Triggering Sequential Scans	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 include the physical partition key (e.g., tenant_id, org_id, or temporal bucket) as an explicit equality or bounded range predicate in the WHERE clause to enable Postgres 17 static partition pruning before vector index evaluation.

Engineers MUST ALWAYS include the physical partition key (e.g., tenant_id, org_id, or temporal bucket) as an explicit equality or bounded range predicate in the WHERE clause to enable Postgres 17 static partition pruning before vector index evaluation.

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.