PostgreSQL 17 Adaptive WorkMem Capping and Hash Join Spilling Guardrail

--- description: PostgreSQL 17 Adaptive WorkMem Capping and Hash Join Spilling Guardrail - Enforces strict transaction-scoped memory budgeting for PostgreSQL 17 hash joins and hybrid vector-analytical workloads. Prevents Linux kernel Out-Of-Memory (OOM) killer terminations by replacing static global session work_mem overrides with dynamic, concurrency-aware SET LOCAL boundaries that force deterministic batch spilling to disk. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/infrastructure/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-adaptive-workmem-hash-join-spill-guardrail" --- # PostgreSQL 17 Adaptive WorkMem Capping and Hash Join Spilling Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Session-Level work_mem Escalation During Concurrent Parallel Hash Joins ## System Constraints You are a Principal Software Architect enforcing production resilience for PostgreSQL 17 workloads. You must prevent Linux OOM-killer invocations by strictly regulating execution memory (work_mem). Mandate dynamic transaction-scoped work_mem allocations that compute available cgroup memory budgets, account for parallel worker multipliers in hash joins and vector operations, and enforce graceful multi-batch disk spilling via PostgreSQL 17 optimized Hash Join spill mechanics. ## Strict Guidelines - Engineers MUST NEVER execute global or session-scoped 'SET work_mem' statements, as they persist across pooled connections and compound under multi-client concurrency. - Engineers MUST ALWAYS scope memory escalation to an explicit transaction block using 'SET LOCAL work_mem = ...' or within a localized transaction wrapper. - Engineers MUST calculate dynamic work_mem budgets taking into account 'max_parallel_workers_per_gather' and active connection pool concurrency: Budget = Available_Memory / (Active_Workers * Parallel_Nodes * Safety_Factor). - Services MUST enable and monitor 'log_temp_files' and 'temp_file_limit' per query to detect catastrophic query spillages before disk exhaustion occurs. - Queries executing hybrid pgvector HNSW/IVFFlat scans joined with relational metadata MUST force planner disk-spill paths for hash joins rather than ballooning shared and private RAM buffers. - Components NEVER execute unbounded analytical batch queries on primary OLTP connection pools; dedicated query pools with explicit work_mem ceilings must be enforced. ## Bad vs Good Implementations ### Session-Scoped work_mem Escalation in Connection Pool #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing 'SET work_mem' at the session level leaks high memory settings back to the pool. When concurrent queries execute parallel hash joins, each parallel worker allocates up to 2GB independently (2GB * N workers * M connections), causing rapid resident memory explosion and fatal SIGKILL from the OS OOM manager. ```typescript import { Pool } from 'pg'; export async function runHybridAnalyticsQuery(pool: Pool, embedding: number[], tenantId: string) { const client = await pool.connect(); try { // ANTI-PATTERN: Session-level override mutates the pooled connection permanently // Multiplied by parallel workers and concurrent requests, this quickly triggers the Linux OOM-killer await client.query("SET work_mem = '2GB';"); const result = await client.query(` SELECT doc.id, doc.title, v.embedding <-> $1 AS distance FROM document_vectors v JOIN documents doc ON doc.id = v.document_id WHERE doc.tenant_id = $2 ORDER BY distance ASC LIMIT 100; `, [JSON.stringify(embedding), tenantId]); return result.rows; } finally { client.release(); // Connection released with 2GB work_mem still set for next unsuspecting query } } ``` #### Production Standard (Correct) > **Standard**: Wraps query execution within an explicit transaction using 'SET LOCAL work_mem'. Dynamically calculates the memory ceiling based on cgroup allocations, active pool capacity, and parallel worker counts. PostgreSQL 17 hash join engine gracefully splits hash tables into batches and spills excess tuples to temp storage within defined limits without threatening system stability. ```typescript import { Pool, PoolClient } from 'pg'; interface MemoryBudgetOptions { cgroupLimitBytes: number; maxActiveQueries: number; maxParallelWorkers: number; safetyMarginRatio?: number; } function calculateAdaptiveWorkMem(options: MemoryBudgetOptions): string { const safetyRatio = options.safetyMarginRatio ?? 0.65; // Reserve 35% for shared_buffers and OS const safeAvailableBytes = options.cgroupLimitBytes * safetyRatio; const totalConcurrentOperators = options.maxActiveQueries * (options.maxParallelWorkers + 1); // Calculate capped allocation per operator node; minimum 8MB, max safe boundary 128MB const budgetPerOp = Math.floor(safeAvailableBytes / totalConcurrentOperators); const clampedBudgetMb = Math.max(8, Math.min(128, Math.floor(budgetPerOp / (1024 * 1024)))); return `${clampedBudgetMb}MB`; } export async function runHybridAnalyticsQuery( pool: Pool, embedding: number[], tenantId: string, systemLimits: MemoryBudgetOptions ) { const client: PoolClient = await pool.connect(); const targetWorkMem = calculateAdaptiveWorkMem(systemLimits); try { await client.query('BEGIN;'); // 1. Transaction-scoped override resets automatically on COMMIT/ROLLBACK // 2. PostgreSQL 17 adaptive hash joins gracefully spill to temp files if tuples exceed this target await client.query(`SET LOCAL work_mem = '${targetWorkMem}';`); await client.query(`SET LOCAL statement_timeout = '15000';`); // 15s circuit breaker await client.query(`SET LOCAL temp_file_limit = '1048576';`); // 1GB disk spill limit const result = await client.query(` SELECT doc.id, doc.title, v.embedding <-> $1 AS distance FROM document_vectors v JOIN documents doc ON doc.id = v.document_id WHERE doc.tenant_id = $2 ORDER BY distance ASC LIMIT 100; `, [JSON.stringify(embedding), tenantId]); await client.query('COMMIT;'); return result.rows; } catch (error) { await client.query('ROLLBACK;').catch(() => {}); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Adaptive WorkMem Capping and Hash Join Spilling Guardrail

PostgreSQL 17 Adaptive WorkMem Capping and Hash Join Spilling Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict transaction-scoped memory budgeting for PostgreSQL 17 hash joins and hybrid vector-analytical workloads. Prevents Linux kernel Out-Of-Memory (OOM) killer terminations by replacing static global session work_mem overrides with dynamic, concurrency-aware SET LOCAL boundaries that force deterministic batch spilling to disk. 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 Session-Level work_mem Escalation During Concurrent Parallel Hash Joins 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 Session-Level work_mem Escalation During Concurrent Parallel Hash Joins	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 NEVER execute global or session-scoped 'SET work_mem' statements, as they persist across pooled connections and compound under multi-client concurrency.

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.