pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail | AgentRules.org

Rules Stacks Clients Auditor Pruner Self-Heal RFCs Benchmark CLI CI/CD Authors Submit

This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →

backendcritical PriorityID: pgvector-hnsw-workmem-isolation-standard

pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail

Enforces strict isolation of maintenance_work_mem and session work_mem during pgvector 0.8+ HNSW graph construction and parallel query execution. Prevents Linux OOM-killer invocations, buffer pool cache eviction, and OLTP transaction starvation by decoupling vector index builds into isolated administrative sessions with deterministic memory bounds.

Prohibited Anti-Pattern: Global maintenance_work_mem Inflation and Inline DDL Execution in Transactional OLTP Connection Pools

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail - Enforces strict isolation of maintenance_work_mem and session work_mem during pgvector 0.8+ HNSW graph construction and parallel query execution. Prevents Linux OOM-killer invocations, buffer pool cache eviction, and OLTP transaction starvation by decoupling vector index builds into isolated administrative sessions with deterministic memory bounds.
globs: src/db/**/*.ts, src/migrations/**/*.ts, src/repositories/**/*.ts, prisma/migrations/**/*.sql, src/services/vector/**/*.ts
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pgvector-hnsw-workmem-isolation-standard"
---

# pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Global maintenance_work_mem Inflation and Inline DDL Execution in Transactional OLTP Connection Pools

## System Constraints
You are a Principal Database Architect and Infrastructure Engineer enforcing memory isolation, SIMD optimization, and transactional protection for pgvector 0.8+ HNSW indexes. Never permit concurrent index builds or maintenance operations inside standard OLTP connection pools. Guarantee all HNSW index builds use CONCURRENTLY, scoped session-level maintenance_work_mem, and restricted max_parallel_maintenance_workers to eliminate out-of-memory cascades and shared buffer evictions.

## Strict Guidelines
- Engineers MUST ALWAYS isolate HNSW index creation and reindexing inside dedicated, single-use administrative database connections outside the OLTP connection pool.
- Engineers MUST ALWAYS specify `CONCURRENTLY` on all `CREATE INDEX` and `REINDEX` operations targeting vector columns in production environments.
- Engineers MUST ALWAYS set `maintenance_work_mem` at the session level (`SET LOCAL maintenance_work_mem = '...'`) immediately before index creation, rather than altering global cluster settings.
- Engineers MUST calculate `maintenance_work_mem` explicitly based on vector dimension (d), graph connectivity (m), and row count (N), ensuring (N * (d * 4 + m * 8) * 1.25) does not exceed 75% of available free RAM.
- Engineers MUST constrain `max_parallel_maintenance_workers` to prevent `maintenance_work_mem * parallel_workers` multiplicative memory allocation from triggering the OS OOM killer.
- Engineers MUST NEVER execute vector index DDL statements inside an active multi-statement transactional block containing business logic or OLTP queries.
- Engineers MUST ensure `hnsw.ef_search` is configured per query or session rather than using excessive cluster-wide defaults that degrade parallel query-time buffer caches.

## Bad vs Good Implementations
### HNSW Index Build in Isolated Maintenance Connection

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Executing non-concurrent HNSW creation inside a transaction block locks the table against writes. Running it on the shared OLTP pool without session-scoped memory controls either spills graph construction to slow temporary files (causing severe I/O latency) or allocates unchecked memory across workers, risking OOM termination of the database cluster.
```typescript
import { Pool } from 'pg';

// ANTI-PATTERN: Running non-concurrent HNSW index build inside standard shared OLTP pool.
// Altering system parameters globally or relying on default maintenance_work_mem causes disk spills or OOM.
export async function createVectorIndex(pool: Pool): Promise<void> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Blocking DDL locks table against writes, exhausts shared memory, and lacks concurrency
    await client.query(`
      CREATE INDEX idx_embeddings_hnsw ON document_embeddings 
      USING hnsw (embedding vector_cosine_ops)
      WITH (m = 16, ef_construction = 64);
    `);
    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}
```

#### Production Standard (Correct)
> **Standard**: Creates a distinct administrative connection completely disjoint from the OLTP pool, sets session-level memory boundaries and worker bounds to prevent OOM amplification, and applies CREATE INDEX CONCURRENTLY outside any transaction wrapper, keeping the table accessible for concurrent read/write OLTP traffic.
```typescript
import { Client } from 'pg';

interface VectorIndexOptions {
  m: number;
  efConstruction: number;
  maintenanceMemMb: number;
  maxWorkers: number;
}

// PRODUCTION FIX: Isolated dedicated connection with scoped session parameters and CONCURRENT execution
export async function buildHnswIndexIsolated(
  adminConnectionUrl: string,
  options: VectorIndexOptions = { m: 16, efConstruction: 64, maintenanceMemMb: 2048, maxWorkers: 2 }
): Promise<void> {
  const adminClient = new Client({ connectionString: adminConnectionUrl });
  await adminClient.connect();

  try {
    // 1. Enforce strict session-scoped allocation to prevent multi-worker memory multiplication
    await adminClient.query(`SET max_parallel_maintenance_workers = ${options.maxWorkers};`);
    await adminClient.query(`SET maintenance_work_mem = '${options.maintenanceMemMb}MB';`);
    
    // 2. CREATE INDEX CONCURRENTLY cannot run inside a transaction block
    await adminClient.query(`
      CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_embeddings_hnsw 
      ON document_embeddings 
      USING hnsw (embedding vector_cosine_ops)
      WITH (m = ${options.m}, ef_construction = ${options.efConstruction});
    `);
  } finally {
    // 3. Immediately reset and close dedicated administration socket
    await adminClient.query('RESET maintenance_work_mem;');
    await adminClient.query('RESET max_parallel_maintenance_workers;');
    await adminClient.end();
  }
}
```

Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL Context Server

Inspects running PostgreSQL schema, active index creation locks, and server runtime memory parameters.

Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

pgvector Index Optimization Tool

Validates vector memory estimation formulas and calculates theoretical HNSW index bounds.

Architectural Specification & Invariant Rationale

Architectural Invariant Design: pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail

pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict isolation of maintenance_work_mem and session work_mem during pgvector 0.8+ HNSW graph construction and parallel query execution. Prevents Linux OOM-killer invocations, buffer pool cache eviction, and OLTP transaction starvation by decoupling vector index builds into isolated administrative sessions with deterministic memory bounds. 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 maintenance_work_mem Inflation and Inline DDL Execution in Transactional OLTP Connection Pools 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 maintenance_work_mem Inflation and Inline DDL Execution in Transactional OLTP Connection Pools	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:

1

Engineers MUST ALWAYS isolate HNSW index creation and reindexing inside dedicated, single-use administrative database connections outside the OLTP connection pool.

Engineers MUST ALWAYS isolate HNSW index creation and reindexing inside dedicated, single-use administrative database connections outside the OLTP connection pool.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

2

Engineers MUST ALWAYS specify `CONCURRENTLY` on all `CREATE INDEX` and `REINDEX` operations targeting vector columns in production environments.

Engineers MUST ALWAYS specify `CONCURRENTLY` on all `CREATE INDEX` and `REINDEX` operations targeting vector columns in production environments.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

3

Engineers MUST ALWAYS set `maintenance_work_mem` at the session level (`SET LOCAL maintenance_work_mem = '...'`) immediately before index creation, rather than altering global cluster settings.

Engineers MUST ALWAYS set `maintenance_work_mem` at the session level (`SET LOCAL maintenance_work_mem = '...'`) immediately before index creation, rather than altering global cluster settings.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

4

Engineers MUST calculate `maintenance_work_mem` explicitly based on vector dimension (d), graph connectivity (m), and row count (N), ensuring (N * (d * 4 + m * 8) * 1.25) does not exceed 75% of available free RAM.

Engineers MUST calculate `maintenance_work_mem` explicitly based on vector dimension (d), graph connectivity (m), and row count (N), ensuring (N * (d * 4 + m * 8) * 1.25) does not exceed 75% of available free RAM.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

5

Engineers MUST constrain `max_parallel_maintenance_workers` to prevent `maintenance_work_mem * parallel_workers` multiplicative memory allocation from triggering the OS OOM killer.

Engineers MUST constrain `max_parallel_maintenance_workers` to prevent `maintenance_work_mem * parallel_workers` multiplicative memory allocation from triggering the OS OOM killer.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

6

Engineers MUST NEVER execute vector index DDL statements inside an active multi-statement transactional block containing business logic or OLTP queries.

Engineers MUST NEVER execute vector index DDL statements inside an active multi-statement transactional block containing business logic or OLTP queries.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

7

Engineers MUST ensure `hnsw.ef_search` is configured per query or session rather than using excessive cluster-wide defaults that degrade parallel query-time buffer caches.

Engineers MUST ensure `hnsw.ef_search` is configured per query or session rather than using excessive cluster-wide defaults that degrade parallel query-time buffer caches.

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.

Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.

Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is pgvector 0.8 HNSW Index Maintenance and WorkMem Isolation Guardrail critical for AI coding assistants?

Enforces strict isolation of maintenance_work_mem and session work_mem during pgvector 0.8+ HNSW graph construction and parallel query execution. Prevents Linux OOM-killer invocations, buffer pool cache eviction, and OLTP transaction starvation by decoupling vector index builds into isolated administrative sessions with deterministic memory bounds. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Global maintenance_work_mem Inflation and Inline DDL Execution in Transactional OLTP Connection Pools) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q:Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q:Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

pgvector