Postgres 17 Memory-Adaptive Hash Join Concurrency Governor Guardrail

--- description: Postgres 17 Memory-Adaptive Hash Join Concurrency Governor Guardrail - Enforces deterministic memory-bounded query execution for PostgreSQL 17 multi-node hash joins during analytical concurrency bursts. Prevents server-wide out-of-memory (OOM) events and cascade temp-file I/O thrashing by dynamically computing and setting transaction-local work_mem ceilings governed by active join concurrency tokens. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/infrastructure/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-memory-adaptive-hash-join-governor" --- # Postgres 17 Memory-Adaptive Hash Join Concurrency Governor Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded static work_mem allocation and uncoordinated parallel hash join execution under high analytical concurrency ## System Constraints You are a Principal Database Infrastructure Architect enforcing strict Postgres 17 analytical workload isolation. You must prohibit unchecked analytical queries with static high work_mem allocations. Mandate adaptive concurrency throttling, transactional scope isolation using SET LOCAL work_mem, and strict temp-file spill bounding for all parallel and multi-batch hash joins. ## Strict Guidelines - Engineers MUST ALWAYS set work_mem dynamically via `SET LOCAL work_mem = ...` within an explicit transaction block, never using session-wide `SET work_mem`. - Engineers MUST NOT execute heavy multi-table analytical joins without acquiring a concurrency governor lease to prevent aggregate memory oversubscription. - Services MUST calculate dynamic `work_mem` based on available system memory headroom divided by active analytical worker tokens (active_queries * max_parallel_workers_per_gather). - Code SHOULD enforce `SET LOCAL temp_file_limit` on analytical transactions to terminate runaway disk spills before saturating ephemeral I/O bandwidth. - Components NEVER execute unbounded analytical queries on primary transactional connection pools; dedicated analytical pools with query governors are strictly required. ## Bad vs Good Implementations ### Adaptive Memory Allocation with Dynamic work_mem Governor #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Allocates a fixed 1GB work_mem at session level. In Postgres 17, each hash join node across multiple parallel workers can allocate up to work_mem independently. Under concurrent bursts, total memory consumption escalates to (1GB * hash_nodes * workers * concurrent_queries), precipitating kernel OOM crashes or I/O starvation. ```typescript import { Pool } from 'pg'; export async function runAnalyticalReport(pool: Pool, tenantId: string) { const client = await pool.connect(); try { // Anti-pattern: Session-wide static work_mem modification under unbounded concurrency // High risk of node OOM when concurrent analytical queries fire simultaneously await client.query("SET work_mem = '1GB'"); const result = await client.query(` SELECT o.id, SUM(i.price * i.quantity) FROM orders o JOIN order_items i ON o.id = i.order_id JOIN customers c ON o.customer_id = c.id WHERE o.tenant_id = $1 GROUP BY o.id `, [tenantId]); return result.rows; } finally { // Session state leak if reset fails or connection drops unexpectedly await client.query("RESET work_mem"); client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Adheres to Postgres 17 memory safety standards: bounds memory allocation per-transaction using `SET LOCAL`, provisions dynamic `work_mem` proportional to live node saturation, applies a hard `temp_file_limit` safety valve, and returns connection tokens via a deterministic governor lease. ```typescript import { Pool, PoolClient } from 'pg'; import { AnalyticalConcurrencyGovernor } from './governor'; export async function runAnalyticalReport(pool: Pool, tenantId: string): Promise<any[]> { const governor = AnalyticalConcurrencyGovernor.getInstance(); // Acquire concurrency token and receive calibrated work_mem based on current fleet load const lease = await governor.acquireLease({ estimatedJoinNodes: 2, maxParallelWorkers: 2 }); const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // Invariant: Scoped dynamically to transaction block; cannot leak to connection pool await client.query('SET LOCAL work_mem = $1', [`${lease.computedWorkMemMb}MB`]); await client.query('SET LOCAL temp_file_limit = $1', [`${lease.maxSpillLimitMb}MB`]); await client.query('SET LOCAL statement_timeout = $1', [lease.statementTimeoutMs]); const result = await client.query(` SELECT o.id, SUM(i.price * i.quantity) FROM orders o JOIN /*+ HashJoin(i o) */ order_items i ON o.id = i.order_id JOIN customers c ON o.customer_id = c.id WHERE o.tenant_id = $1 GROUP BY o.id `, [tenantId]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK').catch(() => {}); throw error; } finally { client.release(); lease.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Memory-Adaptive Hash Join Concurrency Governor Guardrail

Postgres 17 Memory-Adaptive Hash Join Concurrency Governor Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic memory-bounded query execution for PostgreSQL 17 multi-node hash joins during analytical concurrency bursts. Prevents server-wide out-of-memory (OOM) events and cascade temp-file I/O thrashing by dynamically computing and setting transaction-local work_mem ceilings governed by active join concurrency tokens. 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 static work_mem allocation and uncoordinated parallel hash join execution under high analytical concurrency 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 static work_mem allocation and uncoordinated parallel hash join execution under high analytical concurrency	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 set work_mem dynamically via `SET LOCAL work_mem = ...` within an explicit transaction block, never using session-wide `SET work_mem`.

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.