Postgres 17 Adaptive Hash-Join Memory Allocation and Spill Protection Guardrail

--- description: Postgres 17 Adaptive Hash-Join Memory Allocation and Spill Protection Guardrail - Enforces strict transaction-scoped memory budgeting and concurrency-aware work_mem calibration for Postgres 17 analytical hash joins. Prevents memory over-commit, OS OOM-killer interventions, recursive disk spilling, and buffer pool eviction storms caused by unconstrained multi-batch joins and parallel worker multiplication. globs: src/database/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/infrastructure/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-adaptive-hash-join-memory-spilling-guardrail" --- # Postgres 17 Adaptive Hash-Join Memory Allocation and Spill Protection Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global or Blind Per-Query work_mem Elevation on Concurrent Analytical Joins Leading to OOM or Temp Disk Thrashing ## System Constraints You are a Principal Software Architect enforcing Postgres 17 memory safety standards for high-cardinality joins. Mandate invariants that prevent memory starvation, NVMe temp disk saturation, and buffer pool thrashing. Ensure that any session- or transaction-level work_mem modification accounts for plan-level worker multiplication, enforces temp_file_limit boundaries, checks active concurrent analytics pools, and falls back gracefully to bounded adaptive spilling under Postgres 17 hash join improvements. ## Strict Guidelines - Engineers MUST ALWAYS isolate analytical joins into dedicated connection pools with strict connection limits and isolated statement timeouts. - Engineers MUST NEVER execute high-cardinality joins with unbounded session-level work_mem; adjustments MUST be scoped locally within a transaction block using SET LOCAL work_mem. - Code MUST calculate allowable work_mem dynamically by factoring in max_parallel_workers_per_gather and concurrent execution slots to prevent aggregate node OOM (total_memory = work_mem * join_nodes * parallel_workers). - Services MUST enforce strict temp_file_limit thresholds per role or transaction to fail-fast runaway spills before saturating disk I/O channels. - Engineers MUST configure hash join execution to leverage Postgres 17 streaming read capabilities by tuning effective_io_concurrency and maintenance_io_concurrency alongside work_mem sizing. - Components NEVER issue analytical join queries directly within primary transactional (OLTP) connections sharing pool resources with real-time operations. ## Bad vs Good Implementations ### Unbounded Session work_mem Elevation vs Concurrency-Aware Guarded Analytical Transaction #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Sets an uncontrolled 2GB work_mem globally on the pooled connection. A hash join query with two join nodes and 4 parallel workers can consume (2 * (4 + 1) * 2GB) = 20GB on a single request. Under concurrency, this causes kernel OOM panics and fails to isolate OLTP workloads. ```typescript import { PoolClient } from 'pg'; export async function runAnalyticalJoin(client: PoolClient, tenantId: string) { // ANTI-PATTERN: Blindly setting a massive work_mem on the session. // If parallel workers (e.g., 4) and multiple join nodes trigger across 20 concurrent // analytic requests, aggregate RAM demand will breach host limits and trigger OOM-killer. await client.query("SET work_mem = '2GB';"); const query = ` SELECT o.id, o.total, c.region, p.category FROM large_orders o JOIN customers c ON c.id = o.customer_id JOIN order_products p ON p.order_id = o.id WHERE o.tenant_id = $1 `; const result = await client.query(query, [tenantId]); // Leaving work_mem dirty on the pooled connection return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Computes work_mem deterministically based on parallel worker multipliers and analytical pool concurrency. Uses SET LOCAL within a read-only transaction to ensure settings reset on completion, and caps temp_file_limit to 5GB to prevent unconstrained multi-batch disk filling. ```typescript import { PoolClient } from 'pg'; interface AnalyticBudget { maxWorkers: number; joinNodes: number; availablePoolBudgetMb: number; maxActiveAnalytics: number; } function calculateAdaptiveWorkMem(budget: AnalyticBudget): string { // Formula: floor(pool_ram / (concurrent_slots * join_nodes * (workers + 1))) const workerMultiplier = budget.maxWorkers + 1; const safeSlotMb = Math.floor( budget.availablePoolBudgetMb / (budget.maxActiveAnalytics * budget.joinNodes * workerMultiplier) ); // Clamp between safe baseline (64MB) and upper ceiling (512MB) to allow controlled PG17 spilling const clampedMb = Math.min(Math.max(safeSlotMb, 64), 512); return `${clampedMb}MB`; } export async function runAnalyticalJoinGuarded(client: PoolClient, tenantId: string) { const safeWorkMem = calculateAdaptiveWorkMem({ maxWorkers: 2, joinNodes: 2, availablePoolBudgetMb: 4096, maxActiveAnalytics: 4, }); // INVARIANT: Wrap in explicit transaction using SET LOCAL to avoid connection pollution await client.query('BEGIN READ ONLY;'); try { // Cap memory and spill footprint, limit execution time await client.query(`SET LOCAL work_mem = '${safeWorkMem}';`); await client.query(`SET LOCAL max_parallel_workers_per_gather = 2;`); await client.query(`SET LOCAL temp_file_limit = '5GB';`); // Guard against runaway spill await client.query(`SET LOCAL statement_timeout = '30000';`); // 30s timeout const query = ` SELECT /*+ HashJoin(c o) HashJoin(p o) */ o.id, o.total, c.region, p.category FROM large_orders o JOIN customers c ON c.id = o.customer_id JOIN order_products p ON p.order_id = o.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 Hash-Join Memory Allocation and Spill Protection Guardrail

Postgres 17 Adaptive Hash-Join Memory Allocation and Spill Protection Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict transaction-scoped memory budgeting and concurrency-aware work_mem calibration for Postgres 17 analytical hash joins. Prevents memory over-commit, OS OOM-killer interventions, recursive disk spilling, and buffer pool eviction storms caused by unconstrained multi-batch joins and parallel worker multiplication. 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 or Blind Per-Query work_mem Elevation on Concurrent Analytical Joins Leading to OOM or Temp Disk Thrashing 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 or Blind Per-Query work_mem Elevation on Concurrent Analytical Joins Leading to OOM or Temp Disk Thrashing	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 isolate analytical joins into dedicated connection pools with strict connection limits and isolated statement timeouts.

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.