PostgreSQL 17 Spill-to-Disk Hash Join Memory Quota Enforcement

--- description: PostgreSQL 17 Spill-to-Disk Hash Join Memory Quota Enforcement - Enforces deterministic, transaction-scoped memory budgets and parallel worker limits for PostgreSQL 17 optimized hash joins during high-throughput batch operations. Prevents process-level host OOM killer termination by containing concurrent work_mem allocations and enforcing explicit temp_file_limit disk spill boundaries. globs: src/**/*.ts, src/**/*.service.ts, src/**/*.repository.ts, src/database/**/*.ts, infra/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-hashjoin-workmem-quota-guardrail" --- # PostgreSQL 17 Spill-to-Disk Hash Join Memory Quota Enforcement > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Session-Level work_mem and Parallel Worker Multiplication Triggering Host OOM Panics ## System Constraints You are a Principal Software Architect enforcing deterministic resource quotas on PostgreSQL 17 hash join executions. Mandate transactional isolation for memory amplification, enforce spill-to-disk predictability, cap parallel workers per analytical batch, and prevent Linux OOM-killer invocations across shared connection pools. ## Strict Guidelines - Engineers MUST ALWAYS isolate analytical and batch queries requiring non-default memory into explicit transactions using SET LOCAL work_mem to prevent connection-leakage across pooled sessions. - Engineers MUST ALWAYS calculate aggregate worst-case memory consumption prior to query execution using the formula: Total Memory = (work_mem * (max_parallel_workers_per_gather + 1) * Active_Hash_Nodes). - Engineers MUST NEVER execute parallel batch joins without setting an explicit, defensive SET LOCAL temp_file_limit to halt uncontrolled runaway queries before disk saturation. - Code SHOULD dynamically calibrate SET LOCAL max_parallel_workers_per_gather when escalating work_mem in PostgreSQL 17 to balance CPU parallelism against spill-to-disk thresholds. - Services MUST route heavy reporting and batch processing tasks to dedicated read-replica pools with restricted concurrency rather than mutating primary OLTP connection memory parameters. - Components NEVER execute raw ALTER DATABASE or ALTER USER commands to raise global work_mem to accommodate isolated large join workloads. ## Bad vs Good Implementations ### Configuring Transaction-Scoped Memory Budgets for PostgreSQL 17 Hash Joins #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Mutating global session variables within a pooled connection pollutes subsequent queries if cleanup fails. Elevating work_mem to 1GB alongside 8 parallel workers permits a single query to consume over 9GB RAM per hash join node, risking severe noisy-neighbor starvation and Linux OOM panics. ```typescript import { Pool } from 'pg'; export async function runAnalyticalJoinBatch(pool: Pool, tenantIds: string[]) { // Anti-Pattern: Alters connection-level session state permanently in connection pool. // If 20 parallel workers trigger, total memory usage = work_mem * workers * joins. // Can trigger OS OOM killer and evict the Postgres master process. const client = await pool.connect(); try { await client.query("SET work_mem = '1GB'"); await client.query("SET max_parallel_workers_per_gather = 8"); const result = await client.query(` SELECT a.id, b.metrics FROM large_events a JOIN event_metrics b ON a.id = b.event_id WHERE a.tenant_id = ANY($1) `, [tenantIds]); return result.rows; } finally { // Fails to guarantee state cleanup if unexpected connection termination occurs await client.query("RESET work_mem"); await client.query("RESET max_parallel_workers_per_gather"); client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Wraps the batch query in an explicit transaction utilizing `SET LOCAL`, guaranteeing that memory quotas and worker caps discard automatically on commit or rollback. Employs `temp_file_limit` to prevent runaway disk spill and sizes `work_mem` safely around PostgreSQL 17's improved spill-to-disk algorithms. ```typescript import { Pool, PoolClient } from 'pg'; interface QueryMemoryBudget { workMemMb: number; maxParallelWorkers: number; tempFileLimitMb: number; } const BATCH_GUARDRAILS: QueryMemoryBudget = { workMemMb: 64, // PostgreSQL 17 leverages efficient spill-to-disk batch hash joins maxParallelWorkers: 2, // Caps total process memory multiplier tempFileLimitMb: 2048, // Fails safely if disk spilling exceeds 2GB }; export async function runAnalyticalJoinBatch(pool: Pool, tenantIds: string[]) { const client: PoolClient = await pool.connect(); try { // Use explicit transaction boundaries with SET LOCAL for strict scope isolation await client.query('BEGIN TRANSACTION READ ONLY'); // Invariant: Enforce deterministic memory ceilings local to this transaction only await client.query(`SET LOCAL work_mem = '${BATCH_GUARDRAILS.workMemMb}MB'`); await client.query(`SET LOCAL max_parallel_workers_per_gather = ${BATCH_GUARDRAILS.maxParallelWorkers}`); await client.query(`SET LOCAL temp_file_limit = '${BATCH_GUARDRAILS.tempFileLimitMb}MB'`); const result = await client.query(` SELECT a.id, b.metrics FROM large_events a INNER HASH JOIN event_metrics b ON a.id = b.event_id WHERE a.tenant_id = ANY($1) `, [tenantIds]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { // Connection returned to pool cleanly without any lingering configuration leakage client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Spill-to-Disk Hash Join Memory Quota Enforcement

PostgreSQL 17 Spill-to-Disk Hash Join Memory Quota Enforcement defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic, transaction-scoped memory budgets and parallel worker limits for PostgreSQL 17 optimized hash joins during high-throughput batch operations. Prevents process-level host OOM killer termination by containing concurrent work_mem allocations and enforcing explicit temp_file_limit disk spill boundaries. 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 and Parallel Worker Multiplication Triggering Host OOM Panics 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 and Parallel Worker Multiplication Triggering Host OOM Panics	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 and batch queries requiring non-default memory into explicit transactions using SET LOCAL work_mem to prevent connection-leakage across pooled sessions.

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.