PostgreSQL 17 Adaptive work_mem and Parallel Hash Join OOM Guardrail

--- description: PostgreSQL 17 Adaptive work_mem and Parallel Hash Join OOM Guardrail - Enforces dynamic, worker-aware work_mem bounding and concurrency limits for PostgreSQL 17 parallel hash joins. Prevents cumulative per-node memory multiplication across parallel workers from breaching container cgroup ceilings and triggering kernel OOM-killer terminations. globs: src/**/*.ts, src/**/*.service.ts, src/**/*.repository.ts, lib/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-adaptive-workmem-parallel-hash-guardrail" --- # PostgreSQL 17 Adaptive work_mem and Parallel Hash Join OOM Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded work_mem Sizing with High Parallel Worker Concurrency Causing Container OOM Terminations ## System Constraints You are a Principal Database Infrastructure Architect enforcing memory bounds on PostgreSQL 17 query execution. In PostgreSQL, work_mem is not a per-query limit; it is allocated per operation (hash join, sort) per parallel worker. When max_parallel_workers_per_gather executes parallel hash joins, memory scales as (workers + 1) * work_mem * hash_nodes. You must mandate dynamic calculation, strict worker limits, and transactional configuration clamping so cumulative memory remains safely below container cgroup boundaries. ## Strict Guidelines - Engineers MUST ALWAYS calculate and clamp transactional work_mem dynamically based on max_parallel_workers_per_gather and container cgroup memory limits before executing analytical join queries. - Engineers MUST NOT rely on static, high connection-pool work_mem or unbounded SET LOCAL work_mem during analytical queries containing multi-node parallel hash joins. - Code SHOULD pair every dynamic SET LOCAL work_mem invocation with a corresponding SET LOCAL max_parallel_workers_per_gather inside a dedicated transaction block. - Services MUST enforce query-level timeouts and fallback to disk-based hash batching (spilling) rather than raising work_mem beyond the safe per-container headroom ratio. - Components NEVER allow multi-tenant or concurrent analytical queries to independently elevate work_mem without acquiring an explicit concurrency token or memory semaphore. ## Bad vs Good Implementations ### Adaptive work_mem and Worker Clamping for Analytical Queries #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Allocating 512MB work_mem without constraining worker count causes memory usage to multiply across each parallel worker and hash join node. On concurrent queries, memory breaches container cgroup thresholds, triggering an OS-level SIGKILL. ```typescript import { PoolClient } from 'pg'; export async function runAnalyticalReport(client: PoolClient, tenantId: string): Promise<any[]> { // Anti-pattern: Indiscriminately inflating work_mem globally or per-session. // In PG17 with 4 parallel workers and 3 hash joins, this can consume: // (4 workers + 1 leader) * 3 hash joins * 512MB = 7.5GB RAM, instantly killing a 4GB/8GB container. await client.query("SET LOCAL work_mem = '512MB'"); const result = await client.query(` SELECT o.id, c.name, SUM(i.price) FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items i ON o.id = i.order_id JOIN inventory inv ON i.sku = inv.sku WHERE o.tenant_id = $1 GROUP BY o.id, c.name `, [tenantId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Calculates an explicit, non-overflowing work_mem per execution node clamped to available cgroup memory, sets max_parallel_workers_per_gather within a local transaction, and permits controlled disk spilling if hash batches exceed the memory envelope. ```typescript import { PoolClient } from 'pg'; interface MemoryBudgetConfig { maxContainerMemoryMb: number; activeQueryConcurrencyEstimate: number; safetyHeadroomRatio?: number; } export async function runAnalyticalReport( client: PoolClient, tenantId: string, config: MemoryBudgetConfig ): Promise<any[]> { const safetyRatio = config.safetyHeadroomRatio ?? 0.7; // Reserve 30% for OS and shared_buffers const estimatedHashNodes = 3; const maxWorkers = 2; // Hard-clamp parallel workers for this analytical task // Calculate maximum safe work_mem per node: Budget / (concurrency * (workers + 1) * hashNodes) const totalAvailableRamMb = config.maxContainerMemoryMb * safetyRatio; const perSlotBudgetMb = Math.floor( totalAvailableRamMb / (config.activeQueryConcurrencyEstimate * (maxWorkers + 1) * estimatedHashNodes) ); const safeWorkMemMb = Math.max(16, Math.min(perSlotBudgetMb, 64)); // Clamp within [16MB, 64MB] await client.query('BEGIN TRANSACTION READ ONLY'); try { // Bound both workers and memory inside the local transaction await client.query(`SET LOCAL max_parallel_workers_per_gather = ${maxWorkers}`); await client.query(`SET LOCAL work_mem = '${safeWorkMemMb}MB'`); await client.query("SET LOCAL statement_timeout = '15s'"); const result = await client.query(` SELECT o.id, c.name, SUM(i.price) FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items i ON o.id = i.order_id JOIN inventory inv ON i.sku = inv.sku WHERE o.tenant_id = $1 GROUP BY o.id, c.name `, [tenantId]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK'); throw error; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Adaptive work_mem and Parallel Hash Join OOM Guardrail

PostgreSQL 17 Adaptive work_mem and Parallel Hash Join OOM Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces dynamic, worker-aware work_mem bounding and concurrency limits for PostgreSQL 17 parallel hash joins. Prevents cumulative per-node memory multiplication across parallel workers from breaching container cgroup ceilings and triggering kernel OOM-killer terminations. 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 work_mem Sizing with High Parallel Worker Concurrency Causing Container OOM Terminations 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 work_mem Sizing with High Parallel Worker Concurrency Causing Container OOM Terminations	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 calculate and clamp transactional work_mem dynamically based on max_parallel_workers_per_gather and container cgroup memory limits before executing analytical join 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.