PostgreSQL 17 Parallel Hash Join Memory Spill-Quota & Temp-File Limit Guardrail

--- description: PostgreSQL 17 Parallel Hash Join Memory Spill-Quota & Temp-File Limit Guardrail - Enforces strict per-query work_mem and temp_file_limit quotas during multi-batch parallel hash joins in PostgreSQL 17. Unconstrained parallel worker allocation multiplying against unbudgeted work_mem causes catastrophic multi-batch disk spills to pgsql_tmp, saturating NVMe IOPS and starving primary OLTP read/write paths. globs: src/database/**/*.ts, src/repositories/**/*.ts, src/analytics/**/*.ts, src/infra/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-parallel-hash-join-spill-quota-guardrail" --- # PostgreSQL 17 Parallel Hash Join Memory Spill-Quota & Temp-File Limit Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Parallel Multi-Batch Hash Joins Spilling Uncapped Gigabytes to Disk ## System Constraints You are a Principal Database Reliability Engineer enforcing the PostgreSQL 17 Parallel Hash Join Memory Spill-Quota Invariant. Mandate that all heavy analytical queries executing multi-worker parallel hash joins must set explicit transaction-scoped work_mem, temp_file_limit, and parallel worker constraints to eliminate storage bus contention and noisy-neighbor latency spikes. ## Strict Guidelines - Engineers MUST ALWAYS wrap analytical parallel join executions in dedicated transactions applying SET LOCAL temp_file_limit to abort runaway disk spills (error code 53400). - Engineers MUST NOT permit unbounded max_parallel_workers_per_gather combined with default or escalated work_mem without calculating total worst-case allocation (workers * work_mem * hash_nodes). - Code SHOULD catch configuration_limit_exceeded (SQLSTATE 53400) specifically and route failed join operations to an asynchronous background worker or partitioned batch queue. - Services MUST explicitly enforce SET LOCAL statement_timeout in the same transactional context as hash join configuration overrides. - Components NEVER execute ad-hoc heavy joins against primary transactional read-write pools without read-replica routing or strict temp file quotas. ## Bad vs Good Implementations ### Unbounded Multi-Worker Analytical Join vs Quota-Enforced Transaction Wrapper #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing analytical hash joins without explicit worker limits, temp_file_limit, and statement_timeout risks massive unbounded disk spills to pgsql_tmp when hash tables exceed available work_mem across parallel workers, exhausting shared storage throughput. ```typescript import { Pool } from 'pg'; export async function runAnalyticalReport(pool: Pool, tenantId: string): Promise<any[]> { // ANTI-PATTERN: Default connection pool settings used for heavy analytical parallel join. // In PG 17, parallel workers each allocate work_mem. If multi-batch partitioning triggers, // dozens of workers write gigabytes of unconstrained temp files into pgsql_tmp concurrently, // freezing primary I/O throughput and spiking p99 transactional latencies cluster-wide. const query = ` SELECT o.id, sum(oi.price * oi.quantity) as total FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.tenant_id = $1 GROUP BY o.id; `; const { rows } = await pool.query(query, [tenantId]); return rows; } ``` #### Production Standard (Correct) > **Standard**: Configures an isolated read-only transaction with scoped SET LOCAL directives constraining temp_file_limit, work_mem, parallel workers, and query timeout. Specifically handles error 53400 to prevent runaway disk I/O lockup while safeguarding concurrent workloads. ```typescript import { Pool, PoolClient } from 'pg'; interface SafeJoinOptions { maxSpillDiskKb?: number; // e.g., 524288 = 512MB workMemMb?: number; maxWorkers?: number; timeoutMs?: number; } export async function runAnalyticalReport( pool: Pool, tenantId: string, opts: SafeJoinOptions = {} ): Promise<any[]> { const client: PoolClient = await pool.connect(); const maxSpillKb = opts.maxSpillDiskKb ?? 512 * 1024; // 512MB limit const workMem = `${opts.workMemMb ?? 32}MB`; const maxWorkers = opts.maxWorkers ?? 2; const timeoutMs = opts.timeoutMs ?? 15000; try { await client.query('BEGIN READ ONLY'); // Enforce strict local limits for this query context await client.query(`SET LOCAL statement_timeout = ${timeoutMs}`); await client.query(`SET LOCAL work_mem = '${workMem}'`); await client.query(`SET LOCAL max_parallel_workers_per_gather = ${maxWorkers}`); await client.query(`SET LOCAL temp_file_limit = ${maxSpillKb}`); const query = ` SELECT o.id, sum(oi.price * oi.quantity) as total FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.tenant_id = $1 GROUP BY o.id; `; const { rows } = await client.query(query, [tenantId]); await client.query('COMMIT'); return rows; } catch (error: any) { await client.query('ROLLBACK'); if (error.code === '53400') { // SQLSTATE 53400: configuration_limit_exceeded (temp_file_limit hit) throw new Error(`Report aborted: temp file spill exceeded ${maxSpillKb} KB quota.`); } throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Parallel Hash Join Memory Spill-Quota & Temp-File Limit Guardrail

PostgreSQL 17 Parallel Hash Join Memory Spill-Quota & Temp-File Limit Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict per-query work_mem and temp_file_limit quotas during multi-batch parallel hash joins in PostgreSQL 17. Unconstrained parallel worker allocation multiplying against unbudgeted work_mem causes catastrophic multi-batch disk spills to pgsql_tmp, saturating NVMe IOPS and starving primary OLTP read/write paths. 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 Parallel Multi-Batch Hash Joins Spilling Uncapped Gigabytes to Disk 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 Parallel Multi-Batch Hash Joins Spilling Uncapped Gigabytes to Disk	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 wrap analytical parallel join executions in dedicated transactions applying SET LOCAL temp_file_limit to abort runaway disk spills (error code 53400).

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.