PostgreSQL 17 Memory-Adaptive Parallel Hash Join Guardrail

--- description: PostgreSQL 17 Memory-Adaptive Parallel Hash Join Guardrail - Enforces bounded memory allocations and worker throttling for parallel hash join queries on PostgreSQL 17 read replicas. Uncontrolled session-level work_mem overrides multiply across parallel gather workers, triggering Linux Out-Of-Memory (OOM) killer terminations under high concurrency. globs: src/**/*.ts, src/database/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-memory-adaptive-parallel-hash-join" --- # PostgreSQL 17 Memory-Adaptive Parallel Hash Join Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Session-Level work_mem Inflation with Parallel Query Execution ## System Constraints You are a Principal Database Reliability Engineer enforcing strict memory safety invariants for PostgreSQL 17 parallel query execution. Unbounded session-level work_mem and uncontrolled parallel worker spawn count scale multiplicatively, risking OOM-killer SIGKILL on replica nodes. Mandate explicit worker-bounded work_mem calculations, transaction isolation via SET LOCAL, and enforce query timeouts. ## Strict Guidelines - Engineers MUST ALWAYS compute dynamic work_mem as a fraction of remaining instance headroom divided by (max_parallel_workers_per_gather + 1) * expected_concurrent_joins. - Engineers MUST NEVER execute unbounded 'SET work_mem' at the session level; always scope modifications strictly to transactions via 'SET LOCAL work_mem'. - Code SHOULD verify and configure 'hash_mem_multiplier' when targeting PostgreSQL 17 to ensure hash table sizes do not inflate beyond physical RAM ceilings. - Services MUST enforce strict 'SET LOCAL statement_timeout' alongside any dynamic memory elevation to avoid worker thread starvation. - Components NEVER execute raw parallel analytical queries directly against primary OLTP connections; they must route to dedicated analytical read-pools with enforced parallel caps. ## Bad vs Good Implementations ### Unsafe Dynamic work_mem Inflation vs Memory-Adaptive Transactional Guardrail #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing 'SET work_mem' at the session level corrupts the pooled connection state and scales multiplicatively with worker threads. In PostgreSQL 17, a parallel hash join allocates work_mem per participant worker, resulting in rapid memory exhaustion and OOM-killer termination under high concurrency. ```typescript import { PoolClient } from 'pg'; export async function runAnalyticalJoin(client: PoolClient, tenantId: string): Promise<unknown[]> { // ANTI-PATTERN: Session-level work_mem override without considering parallel workers. // If max_parallel_workers_per_gather = 4, 1GB work_mem can consume up to 5GB per query. // Under 20 concurrent replica queries, memory demands reach 100GB+, triggering kernel OOM. await client.query("SET work_mem = '1GB'"); const result = await client.query( `SELECT c.id, SUM(o.total_amount) FROM customers c JOIN orders o ON c.id = o.customer_id WHERE c.tenant_id = $1 GROUP BY c.id`, [tenantId] ); // Leaks elevated work_mem to subsequent pooled connections return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Wraps execution in a transaction using 'SET LOCAL', bounds parallel workers, sets the PostgreSQL 17 'hash_mem_multiplier' safely, clamps statement timeouts to 30s, and computes work_mem based on replica concurrency headroom. ```typescript import { PoolClient } from 'pg'; interface MemoryBudgetConfig { maxAvailableMemBytes: number; maxParallelWorkers: number; activeConcurrentQueries: number; } function calculateSafeWorkMemKb(config: MemoryBudgetConfig): number { const workerMultiplier = config.maxParallelWorkers + 1; const safeConcurrency = Math.max(config.activeConcurrentQueries, 1); const perSlotBytes = config.maxAvailableMemBytes / (workerMultiplier * safeConcurrency); const minWorkMemKb = 4096; // 4MB floor const calculatedKb = Math.floor(perSlotBytes / 1024); return Math.max(minWorkMemKb, calculatedKb); } export async function runAnalyticalJoinSafe( client: PoolClient, tenantId: string, budget: MemoryBudgetConfig ): Promise<unknown[]> { const safeWorkMemKb = calculateSafeWorkMemKb(budget); await client.query('BEGIN'); try { // SAFE: Strictly scoped to the transaction with worker bounds and statement timeout await client.query(`SET LOCAL work_mem = '${safeWorkMemKb}kB'`); await client.query(`SET LOCAL max_parallel_workers_per_gather = '${budget.maxParallelWorkers}'`); await client.query("SET LOCAL hash_mem_multiplier = '1.0'"); await client.query("SET LOCAL statement_timeout = '30000'"); // 30s limit const result = await client.query( `SELECT c.id, SUM(o.total_amount) FROM customers c JOIN orders o ON c.id = o.customer_id WHERE c.tenant_id = $1 GROUP BY c.id`, [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 Memory-Adaptive Parallel Hash Join Guardrail

PostgreSQL 17 Memory-Adaptive Parallel Hash Join Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces bounded memory allocations and worker throttling for parallel hash join queries on PostgreSQL 17 read replicas. Uncontrolled session-level work_mem overrides multiply across parallel gather workers, triggering Linux Out-Of-Memory (OOM) killer terminations under high concurrency. 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 Inflation with Parallel Query Execution 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 Inflation with Parallel Query Execution	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 compute dynamic work_mem as a fraction of remaining instance headroom divided by (max_parallel_workers_per_gather + 1) * expected_concurrent_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.