PostgreSQL 17 Adaptive WorkMem Hash-Join Memory Spilling Guardrail

--- description: PostgreSQL 17 Adaptive WorkMem Hash-Join Memory Spilling Guardrail - Enforces deterministic bounded memory limits for PostgreSQL 17 multi-batch hash joins across concurrent analytical workloads. Mandates session-level dynamic work_mem scaling, hash-spill plan verification, and resource group isolation to completely prevent OS OOM-killer invocations during concurrent batch join spikes. globs: src/database/**/*.{ts,js,sql}, src/repositories/**/*.{ts,js}, src/services/analytics/**/*.{ts,js}, infra/db/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-adaptive-workmem-hashjoin-spill-control" --- # PostgreSQL 17 Adaptive WorkMem Hash-Join Memory Spilling Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unbounded work_mem Sizing Combined with Uncalibrated Hash-Join Memory Accounting Causing OOM Crashes ## System Constraints You are a Principal Database Architect and Performance Engineer enforcing PostgreSQL 17 Adaptive WorkMem Hash-Join Memory Spilling Controls. PostgreSQL allocates work_mem per hash-join node and per parallel query worker, which multiplies linearly during multi-batch joins. In PostgreSQL 17, hash join memory management includes modernized spill heuristics and batch resizing. You must strictly mandate that all high-throughput analytical queries dynamically tune session work_mem or utilize PostgreSQL resource limits, enforce EXPLAIN (ANALYZE, BUFFERS, MEMORY) profiling to verify multi-batch disk spilling invariants, and prohibit unbounded connection-level work_mem escalation. ## Strict Guidelines - Engineers MUST ALWAYS calculate and clamp session-level `work_mem` dynamically based on available instance RAM, `max_parallel_workers_per_gather`, and concurrent active sessions before running heavy analytical hash joins. - Engineers MUST NOT configure static, inflated global `work_mem` values (e.g., > 128MB) in `postgresql.conf`, as parallel workers allocate this memory individually per join node, causing multiplicative spikes that trigger the Linux OOM-killer. - Analytical batch operations MUST verify that PostgreSQL 17 multi-batch hash joins gracefully spill partitions to temp tablespaces (`hash_mem_multiplier`, `work_mem`) without exceeding the container cgroup or host physical memory limits. - Services executing ad-hoc or large aggregations MUST set scoped local transactions using `SET LOCAL work_mem = '...'` rather than persisting altered memory parameters across shared connection poolers (e.g., PgBouncer transaction mode). - Query execution plans MUST be audited using `EXPLAIN (ANALYZE, BUFFERS, MEMORY)` to verify that hash table batches dynamically resize and properly log `Batches: N, Memory Usage: XkB, Storage: Disk` when data volumes exceed target memory thresholds. ## Bad vs Good Implementations ### Unsafe Global WorkMem vs. Deterministic Bounded Hash-Join Execution with Spill Verification #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Setting static 1GB work_mem at the session level without transaction scoping or parallel worker accounting allows multiple concurrent join operators across parallel workers to exhaust host RAM, crashing the PostgreSQL engine via OOM SIGKILL. ```typescript import { PoolClient } from 'pg'; export async function runAnalyticalAggregation(client: PoolClient, tenantId: string) { // ANTI-PATTERN: Unconditionally bumping global session work_mem to a high value // on a pooled connection. Multiplied by 4 parallel workers and multiple join nodes, // 10 concurrent requests consume > 16GB RAM, triggering the Linux OOM killer. await client.query('SET work_mem = "1GB";'); const query = ` SELECT o.status, COUNT(*), SUM(li.price * li.quantity) FROM orders o JOIN line_items li ON o.id = li.order_id WHERE o.tenant_id = $1 GROUP BY o.status; `; const result = await client.query(query, [tenantId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Calculates a deterministic memory budget clamping work_mem per operator and parallel worker. Scopes the configuration via SET LOCAL in an explicit transaction and leverages PostgreSQL 17 hash_mem_multiplier, guaranteeing that multi-batch hash joins safely spill excess records to disk instead of exhausting memory. ```typescript import { PoolClient } from 'pg'; interface MemoryBudgetOptions { systemAvailableMb: number; maxConcurrentQueries: number; maxWorkersPerGather: number; } function calculateSafeWorkMemMb(options: MemoryBudgetOptions): number { const safetyMargin = 0.70; // Reserve 30% for OS and shared buffers const workerMultiplication = 1 + options.maxWorkersPerGather; const allocatedTotal = (options.systemAvailableMb * safetyMargin) / options.maxConcurrentQueries; const perOperatorMem = Math.floor(allocatedTotal / workerMultiplication); // Clamp work_mem between 16MB and 256MB to force deterministic PG17 hash spilling return Math.max(16, Math.min(perOperatorMem, 256)); } export async function runAnalyticalAggregation( client: PoolClient, tenantId: string, budget: MemoryBudgetOptions ) { const workMemMb = calculateSafeWorkMemMb(budget); await client.query('BEGIN;'); try { // Ensure bounded memory allocation scoped ONLY to this transaction await client.query(`SET LOCAL work_mem = '${workMemMb}MB';`); // PostgreSQL 17 hash join memory multiplier control (prevents excessive overshoot before spill) await client.query('SET LOCAL hash_mem_multiplier = 1.5;'); const query = ` SELECT /*+ HashJoin(o li) */ o.status, COUNT(*), SUM(li.price * li.quantity) FROM orders o JOIN line_items li ON o.id = li.order_id WHERE o.tenant_id = $1 GROUP BY o.status; `; 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: PostgreSQL 17 Adaptive WorkMem Hash-Join Memory Spilling Guardrail

PostgreSQL 17 Adaptive WorkMem Hash-Join Memory Spilling Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic bounded memory limits for PostgreSQL 17 multi-batch hash joins across concurrent analytical workloads. Mandates session-level dynamic work_mem scaling, hash-spill plan verification, and resource group isolation to completely prevent OS OOM-killer invocations during concurrent batch join spikes. 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 Unbounded work_mem Sizing Combined with Uncalibrated Hash-Join Memory Accounting Causing OOM Crashes 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 Unbounded work_mem Sizing Combined with Uncalibrated Hash-Join Memory Accounting Causing OOM Crashes	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 session-level `work_mem` dynamically based on available instance RAM, `max_parallel_workers_per_gather`, and concurrent active sessions before running heavy analytical hash joins.

Engineers MUST ALWAYS calculate and clamp session-level `work_mem` dynamically based on available instance RAM, `max_parallel_workers_per_gather`, and concurrent active sessions before running heavy analytical hash joins.

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.