Postgres 17 Adaptive Parallel Hash Join WorkMem Allocation and OOM Circuit Breaker

--- description: Postgres 17 Adaptive Parallel Hash Join WorkMem Allocation and OOM Circuit Breaker - Enforces deterministic per-worker memory quotas and adaptive query-level bounds during parallel hash joins in PostgreSQL 17. Prevents explosive memory growth across concurrent worker threads by dynamically scaling transaction-local work_mem and hash_mem_multiplier to force safe disk spilling rather than invoking the Linux kernel Out-Of-Memory (OOM) killer. globs: src/db/**/*.ts, src/infra/database/**/*.ts, src/services/**/*.ts, src/repositories/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-workmem-spill-circuit-breaker" --- # Postgres 17 Adaptive Parallel Hash Join WorkMem Allocation and OOM Circuit Breaker > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Global WorkMem Multiplied Across Parallel Workers Triggering Kernel OOM Cascades ## System Constraints You are a Principal Database Reliability Engineer enforcing memory safety guardrails for Postgres 17. Under sudden concurrency spikes, parallel hash joins allocate work_mem per-operator and per-worker, which can trigger host-level Linux kernel OOM kills. You must enforce transaction-local, concurrency-aware work_mem calculation, bound parallel gather workers, leverage Postgres 17 hash_mem_multiplier controls, and ensure circuit breakers throttle or divert queries when pool concurrency exceeds safe memory thresholds. ## Strict Guidelines - Engineers MUST ALWAYS compute session-level or transaction-level work_mem dynamically via 'SET LOCAL work_mem' when executing complex analytical joins, factoring in active connection count. - Engineers MUST NOT rely on static, oversized global work_mem settings in postgresql.conf across pooled web requests. - Services MUST enforce strict 'hash_mem_multiplier' boundaries for parallel joins to prevent hash table allocations from bypassing conservative base work_mem limits. - Code SHOULD clamp 'max_parallel_workers_per_gather' to 0 or 1 dynamically when connection pool saturation exceeds 70% of max capacity. - Database access layers MUST wrap join queries in a concurrency-aware circuit breaker that falls back to sequential scans or temp-file disk spilling rather than risking process termination. ## Bad vs Good Implementations ### Unsafe Static Join Execution vs. Adaptive Postgres 17 WorkMem Circuit Breaker #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing parallel hash joins without session-scoped memory caps allows aggregate worker allocations to exceed physical RAM under concurrency surges, prompting the OS kernel OOM killer to crash the Postgres primary. ```typescript import { PoolClient } from 'pg'; export async function runAnalyticalJoin(client: PoolClient, tenantId: string): Promise<any[]> { // ANTI-PATTERN: Inherits high static work_mem with default unbounded parallel workers. // If 50 parallel queries arrive with 4 gather workers and 3 hash joins each: // 50 * 4 workers * 3 joins * 64MB work_mem = ~24GB allocated instantly -> OOM Killer terminates Postgres. const query = ` SELECT o.id, o.amount, c.risk_score, a.history FROM large_orders o JOIN customer_profiles c ON o.customer_id = c.id JOIN audit_events a ON o.id = a.order_id WHERE o.tenant_id = $1; `; const result = await client.query(query, [tenantId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Calculates adaptive transaction-scoped work_mem based on live pool pressure, caps hash_mem_multiplier to 1.0, and sheds parallel gather workers under high load, safely forcing hash tables to spill to disk instead of triggering kernel OOM kills. ```typescript import { PoolClient } from 'pg'; interface MemoryBudgetConfig { totalPoolReserveMb: number; maxActiveConnections: number; minWorkMemMb: number; } const MEMORY_BUDGET: MemoryBudgetConfig = { totalPoolReserveMb: 4096, // 4GB dedicated to concurrent analytical workloads maxActiveConnections: 100, minWorkMemMb: 8 }; export async function runAnalyticalJoin( client: PoolClient, tenantId: string, currentActiveConnections: number ): Promise<any[]> { // Adaptive quota: Scale work_mem down as concurrency spikes const safeActiveConnections = Math.max(1, currentActiveConnections); const perConnectionBudgetMb = Math.max( MEMORY_BUDGET.minWorkMemMb, Math.floor(MEMORY_BUDGET.totalPoolReserveMb / safeActiveConnections) ); // Circuit breaker: Disable parallel gather workers if pool capacity is strained (>75%) const isUnderHeavyLoad = currentActiveConnections > MEMORY_BUDGET.maxActiveConnections * 0.75; const parallelWorkers = isUnderHeavyLoad ? 0 : 2; return await client.query('BEGIN').then(async () => { try { // Enforce deterministic per-worker memory constraints in Postgres 17 await client.query(`SET LOCAL work_mem = '${perConnectionBudgetMb}MB';`); await client.query(`SET LOCAL hash_mem_multiplier = 1.0;`); // Prevent hash joins from over-allocating await client.query(`SET LOCAL max_parallel_workers_per_gather = ${parallelWorkers};`); await client.query(`SET LOCAL statement_timeout = '30000';`); // Prevent runaway temp-file disk bloat const query = ` SELECT /*+ AdaptiveSpillEnforced */ o.id, o.amount, c.risk_score, a.history FROM large_orders o JOIN customer_profiles c ON o.customer_id = c.id JOIN audit_events a ON o.id = a.order_id WHERE o.tenant_id = $1; `; const result = await client.query(query, [tenantId]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK'); throw error; } }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Adaptive Parallel Hash Join WorkMem Allocation and OOM Circuit Breaker

Postgres 17 Adaptive Parallel Hash Join WorkMem Allocation and OOM Circuit Breaker defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic per-worker memory quotas and adaptive query-level bounds during parallel hash joins in PostgreSQL 17. Prevents explosive memory growth across concurrent worker threads by dynamically scaling transaction-local work_mem and hash_mem_multiplier to force safe disk spilling rather than invoking the Linux kernel Out-Of-Memory (OOM) killer. 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 Global WorkMem Multiplied Across Parallel Workers Triggering Kernel OOM 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	Unbounded Global WorkMem Multiplied Across Parallel Workers Triggering Kernel OOM 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 compute session-level or transaction-level work_mem dynamically via 'SET LOCAL work_mem' when executing complex analytical joins, factoring in active connection count.

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.