Postgres 17 Streaming Hash Join Memory & Spill Limit Enforcement

--- description: Postgres 17 Streaming Hash Join Memory & Spill Limit Enforcement - Enforces strict per-query memory limits and temporary disk spill bounds for Postgres 17 streaming and parallel hash joins. Prevents container cgroup OOM kills and disk exhaustion during multi-batch inner relation builds by dynamically scoping work_mem, hash_mem_multiplier, and temp_file_limit within transactional boundaries. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/analytics/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-streaming-hash-join-memory-limit-enforcement" --- # Postgres 17 Streaming Hash Join Memory & Spill Limit Enforcement > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing Large Parallel Hash Joins Under Global Pool Defaults Leading to Container cgroup OOM Kills ## System Constraints You are a Principal Software Architect enforcing Postgres 17 streaming hash join memory invariants. Parallel hash joins multiply memory consumption by the number of parallel workers and hash_mem_multiplier. You must guarantee that all analytical aggregations and large inner-relation hash joins explicitly calculate memory caps, apply transaction-scoped SET LOCAL limits for work_mem, hash_mem_multiplier, and temp_file_limit, and prevent cgroup memory exhaustion. ## Strict Guidelines - Engineers MUST ALWAYS isolate analytical aggregation queries within explicit transactions using dedicated single-client checkouts when adjusting memory settings. - Engineers MUST ALWAYS execute 'SET LOCAL work_mem', 'SET LOCAL hash_mem_multiplier', and 'SET LOCAL temp_file_limit' within the transaction to prevent configuration leakage across connection pools. - Engineers MUST calculate maximum possible memory consumption as: (work_mem * hash_mem_multiplier * (1 + max_parallel_workers_per_gather)) and ensure this budget remains strictly within container cgroup limits. - Engineers MUST NOT run unbounded multi-batch inner relation builds without bounding 'temp_file_limit' to prevent disk space saturation from unexpected spillover. - Code SHOULD inspect 'EXPLAIN (ANALYZE, BUFFERS, MEMORY)' outputs in integration tests to verify Postgres 17 streaming hash joins stay within single-batch or bounded multi-batch limits. - Services MUST gracefully catch and translate Postgres SQLSTATE '53400' (configuration_limit_exceeded) or '54000' (program_limit_exceeded) triggered by temp file threshold violations. ## Bad vs Good Implementations ### Enforcing Safe Memory and Temp File Spilling on Analytical Joins #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The query executes with standard connection pool settings. When Postgres 17 compiles a parallel hash join, the hash table build is replicated or partitioned across multiple workers. Memory usage scales by (workers * work_mem * hash_mem_multiplier), causing cgroup OOM termination under load. Furthermore, no temp_file_limit exists to stop rogue data skew from filling the volume. ```typescript import { Pool } from 'pg'; export async function runAnalyticalJoin(pool: Pool, tenantId: string) { // FAILS: Executes over default pool settings. With max_parallel_workers_per_gather = 4, // an unconstrained streaming hash join with default or elevated work_mem multiplies memory // allocations across all worker processes. Unbounded batches spill to disk without limits, // risking host disk exhaustion or triggering the kernel/cgroup OOM killer. const query = ` SELECT events.user_id, COUNT(events.id) AS event_count, AVG(metrics.latency) AS avg_latency FROM analytics_events events JOIN raw_device_metrics metrics ON events.device_id = metrics.device_id WHERE events.tenant_id = $1 GROUP BY events.user_id; `; const result = await pool.query(query, [tenantId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Checks out an individual PoolClient and executes within an explicit READ ONLY transaction. Dynamically configures 'work_mem', 'hash_mem_multiplier', and 'max_parallel_workers_per_gather' using 'SET LOCAL', guaranteeing predictable upper bounds on RSS memory to keep the process within cgroup margins. Sets 'temp_file_limit' to abort rogue queries (SQLSTATE 53400) before disk exhaustion occurs. ```typescript import { Pool, PoolClient } from 'pg'; interface AnalyticRow { user_id: string; event_count: string; avg_latency: number; } export async function runAnalyticalJoin( pool: Pool, tenantId: string ): Promise<AnalyticRow[]> { // Acquire an isolated client to ensure session parameters do not pollute the pool const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // Postgres 17 Hash Join Memory Guardrails: // Total budget: 64MB base * 1.5 multiplier * (1 + 2 workers) = ~288MB max allocation. // Guard against host cgroup OOM kill by strictly bounding worker count and allocation. await client.query("SET LOCAL max_parallel_workers_per_gather = '2'"); await client.query("SET LOCAL work_mem = '64MB'"); await client.query("SET LOCAL hash_mem_multiplier = '1.5'"); // Bound disk spill strictly to 1GB to prevent disk full scenarios during multi-batch fallbacks await client.query("SET LOCAL temp_file_limit = '1048576'"); // 1048576 kB = 1GB const query = ` SELECT events.user_id, COUNT(events.id) AS event_count, AVG(metrics.latency) AS avg_latency FROM analytics_events events JOIN raw_device_metrics metrics ON events.device_id = metrics.device_id WHERE events.tenant_id = $1 GROUP BY events.user_id; `; const result = await client.query<AnalyticRow>(query, [tenantId]); await client.query('COMMIT'); return result.rows; } catch (error: any) { await client.query('ROLLBACK'); // SQLSTATE 53400 indicates temp_file_limit exceeded if (error?.code === '53400') { throw new Error(`Analytical query aborted: Spilled temp files exceeded 1GB safety limit for tenant ${tenantId}`); } throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Streaming Hash Join Memory & Spill Limit Enforcement

Postgres 17 Streaming Hash Join Memory & Spill Limit Enforcement defines an industrial-grade behavioral standard for backend engineering. Enforces strict per-query memory limits and temporary disk spill bounds for Postgres 17 streaming and parallel hash joins. Prevents container cgroup OOM kills and disk exhaustion during multi-batch inner relation builds by dynamically scoping work_mem, hash_mem_multiplier, and temp_file_limit within transactional 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: Executing Large Parallel Hash Joins Under Global Pool Defaults Leading to Container cgroup OOM Kills 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	Executing Large Parallel Hash Joins Under Global Pool Defaults Leading to Container cgroup OOM Kills	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 aggregation queries within explicit transactions using dedicated single-client checkouts when adjusting memory settings.

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.