Postgres 17 HNSW Vector Index Partition-Pruning Alignment

--- description: Postgres 17 HNSW Vector Index Partition-Pruning Alignment - Guarantees billion-scale vector similarity lookups avoid catastrophic unindexed table scans by enforcing composite scalar-vector partition pruning at the query planner level. In Postgres 17 and pgvector 0.7+, declarative partitioning combined with localized HNSW indexing requires exact scalar predicate alignment to prevent the query planner from fanning out unpruned sub-plan index scans or falling back to sequential scans across partitioned tables. 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/postgres-17-hnsw-vector-partition-pruning-guardrail" --- # Postgres 17 HNSW Vector Index Partition-Pruning Alignment > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Vector Probing Without Deterministic Partition Pruning Predicates ## System Constraints You are a Principal Database Architect and Infrastructure Engineer specializing in PostgreSQL 17, pgvector, and distributed similarity search architectures. You strictly mandate that all vector similarity searches against declarative partitioned tables enforce compile-time or run-time partition pruning using constant or parameterized scalar filters matching the partition key. Reject any vector query that risks all-partition index fanout or unindexed sequential scans. ## Strict Guidelines - Engineers MUST ALWAYS include equality or range predicates on declarative partition keys (e.g., `tenant_id`, `organization_id`, or `created_at` buckets) alongside the vector similarity distance operator `<->`, `<#>`, or `<=>`. - Engineers MUST NOT use non-deterministic expressions, volatile functions (e.g., `NOW()` or non-immutable custom procedures), or misaligned type casts on partition keys inside vector queries, as they disable Postgres 17 query planner partition pruning. - Code MUST build HNSW indices on the leaf partitions or inherit homogeneous index specifications (`m`, `ef_construction`, vector operator class) from the partitioned root table. - Services MUST execute `EXPLAIN (COSTS, BUFFERS)` in automated regression suites to assert `Append` nodes with pruned `Subplans Removed` or direct single-partition target execution rather than full partition iterations. - Components NEVER execute raw or dynamic vector ORDER BY queries against partitioned root tables without explicit, non-optional partition boundary criteria. ## Bad vs Good Implementations ### PostgreSQL 17 Vector Query with Partition Key Pruning Enforcement #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting the partitioning key (`org_id`) forces Postgres 17 to execute index scans across hundreds or thousands of HNSW partition indexes or perform full table scans across terabytes of data, rapidly exhausting memory and saturating IOPS. ```typescript import { sql } from 'kysely'; import { db } from '../database'; export async function searchSimilarEmbeddings(embedding: number[], orgId: string, limit: number = 10) { // ANTI-PATTERN: orgId is checked in application logic or post-filtered, // or omitted from the top-level WHERE clause against the partitioned table. // This causes Postgres to scan HNSW indexes across ALL partitions (or fall back to parallel Seq Scan). const vectorLiteral = `[${embedding.join(',')}]`; return await db .selectFrom('document_embeddings') .selectAll() // Missing static scalar partition key: triggers catastrophic cross-partition scan .where('status', '=', 'ready') .orderBy(sql`embedding <=> ${vectorLiteral}::vector`) .limit(limit) .execute(); } ``` #### Production Standard (Correct) > **Standard**: Explicitly scoping the query with the scalar partition key `org_id` allows the PostgreSQL 17 query planner to immediately prune unreferenced partitions before accessing the HNSW index on the target leaf partition. Setting `hnsw.ef_search` per transaction ensures optimal recall without global side effects. ```typescript import { sql } from 'kysely'; import { db } from '../database'; interface VectorSearchOptions { embedding: number[]; orgId: string; limit?: number; efSearch?: number; } export async function searchSimilarEmbeddings({ embedding, orgId, limit = 10, efSearch = 100, }: VectorSearchOptions) { // INVARIANT: Wrap in transaction to scope hnsw.ef_search locally return await db.transaction().execute(async (trx) => { // Tune HNSW search recall locally per transaction await sql`SET LOCAL hnsw.ef_search = ${sql.raw(efSearch.toString())}`.execute(trx); const vectorLiteral = `[${embedding.join(',')}]`; // CRITICAL: Explicitly specify partition key with exact type compatibility // This enables Postgres 17 planner-level static partition pruning to a single partition. return await trx .selectFrom('document_embeddings') .selectAll() .where('org_id', '=', orgId) // Exact partition key match guarantees pruning .where('status', '=', 'ready') .orderBy(sql`embedding <=> ${vectorLiteral}::vector`) .limit(limit) .execute(); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 HNSW Vector Index Partition-Pruning Alignment

Postgres 17 HNSW Vector Index Partition-Pruning Alignment defines an industrial-grade behavioral standard for backend engineering. Guarantees billion-scale vector similarity lookups avoid catastrophic unindexed table scans by enforcing composite scalar-vector partition pruning at the query planner level. In Postgres 17 and pgvector 0.7+, declarative partitioning combined with localized HNSW indexing requires exact scalar predicate alignment to prevent the query planner from fanning out unpruned sub-plan index scans or falling back to sequential scans across partitioned tables. 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 Vector Probing Without Deterministic Partition Pruning Predicates 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 Vector Probing Without Deterministic Partition Pruning Predicates	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 equality or range predicates on declarative partition keys (e.g., `tenant_id`, `organization_id`, or `created_at` buckets) alongside the vector similarity distance operator `<->`, `<#>`, or `<=>`.

Engineers MUST ALWAYS include equality or range predicates on declarative partition keys (e.g., `tenant_id`, `organization_id`, or `created_at` buckets) alongside the vector similarity distance operator `<->`, `<#>`, or `<=>`.

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.