Postgres 17 Hash Join Adaptive Spill Memory-Cap Standard

--- description: Postgres 17 Hash Join Adaptive Spill Memory-Cap Standard - Enforces deterministic per-query memory limits and spill-to-disk safeguards for multi-tenant analytical workloads in PostgreSQL 17. Guarantees that hash join allocations adaptively respect transaction-scoped work_mem and hash_mem_multiplier boundaries, preventing concurrent multi-tenant aggregations from triggering Linux kernel OOM (Out-Of-Memory) killer termination. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/analytics/**/*.ts, src/infra/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-hashjoin-adaptive-spill-memory-cap-standard" --- # Postgres 17 Hash Join Adaptive Spill Memory-Cap Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Hash Table Expansion Triggering Host-Level OOM Killer During Multi-Tenant Analytical Aggregations ## System Constraints You are a Principal Database Architect and Infrastructure Guardrail Enforcer. You must ensure all multi-tenant analytical queries and aggregations targeting PostgreSQL 17 strictly isolate and constrain memory allocation. Prevent out-of-memory kernel panics by enforcing deterministic, transaction-scoped memory budgeting via SET LOCAL work_mem, tuning hash_mem_multiplier, and mandating fail-safe spill instrumentation. ## Strict Guidelines - Engineers MUST ALWAYS isolate analytical and ad-hoc aggregations into dedicated transactional contexts that explicitly execute SET LOCAL work_mem and SET LOCAL hash_mem_multiplier within predetermined tenant tier budgets. - Engineers MUST NOT execute cross-tenant analytical joins or aggregations using default pool connection session states without explicit memory clamps. - Code MUST calculate aggregate memory ceilings such that (active_analytical_workers * work_mem * hash_mem_multiplier) strictly reserves at least 35% free physical RAM for kernel caches, shared buffers, and backend daemon overhead. - Services MUST monitor and log PostgreSQL temp file creation (log_temp_files) and EXPLAIN (ANALYZE, BUFFERS, MEMORY) spill metrics to identify excessive disk thrashing resulting from under-allocated hash batches. - Components NEVER permit untrusted or tenant-provided parameters to dynamically set session-level work_mem above the hard-coded maximum tenant tier limit. - Analytical queries MUST explicitly configure statement_timeout and idle_in_transaction_session_timeout locally to prevent orphan hash tables from pinning memory. ## Bad vs Good Implementations ### Analytical Multi-Tenant Aggregator Memory Clamping #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executes high-cardinality multi-table hash joins against standard pool connections without transaction isolation, statement timeouts, or work_mem clamping. If data surges, PostgreSQL 17 hash joins will consume multi-gigabyte heap buffers per backend, exhausting host memory and crashing the database engine. ```typescript import { Pool } from 'pg'; const dbPool = new Pool({ connectionString: process.env.DATABASE_URL }); export async function runTenantAggregation(tenantId: string, startDate: Date, endDate: Date) { // Anti-pattern: Relies on global session work_mem across wide hash joins. // A sudden spike in tenant row volume expands hash tables indefinitely, // multiplying across concurrent queries and inducing Linux OOM killer termination. const query = ` SELECT o.tenant_id, o.customer_id, COUNT(o.id) AS total_orders, SUM(i.quantity * i.unit_price) AS gross_revenue FROM orders o JOIN order_items i ON o.id = i.order_id AND o.tenant_id = i.tenant_id WHERE o.tenant_id = $1 AND o.created_at BETWEEN $2 AND $3 GROUP BY o.tenant_id, o.customer_id; `; const result = await dbPool.query(query, [tenantId, startDate, endDate]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: Acquires a dedicated client in a read-only transaction, establishes a deterministic statement timeout, and sets local work_mem and hash_mem_multiplier limits. This forces PostgreSQL 17 to adaptively partition and spill intermediate hash batches to disk rather than continuously expanding heap memory beyond safe operating boundaries. ```typescript import { Pool, PoolClient } from 'pg'; const dbPool = new Pool({ connectionString: process.env.DATABASE_URL, max: 20, }); const MAX_ALLOWED_WORK_MEM_MB = 128; const DEFAULT_ANALYTICAL_WORK_MEM_MB = 32; interface AggregationBudgetOptions { tenantTier: 'standard' | 'enterprise'; timeoutMs?: number; } export async function runTenantAggregation( tenantId: string, startDate: Date, endDate: Date, options: AggregationBudgetOptions ) { const client: PoolClient = await dbPool.connect(); try { await client.query('BEGIN READ ONLY'); // Clamp work_mem and Postgres 17 hash_mem_multiplier to deterministic boundaries const allocatedWorkMem = options.tenantTier === 'enterprise' ? MAX_ALLOWED_WORK_MEM_MB : DEFAULT_ANALYTICAL_WORK_MEM_MB; const timeout = options.timeoutMs ?? 15000; // Guard against OOM: enforce deterministic batch spill ceiling await client.query(`SET LOCAL statement_timeout = ${Number(timeout)}`); await client.query(`SET LOCAL work_mem = '${allocatedWorkMem}MB'`); await client.query(`SET LOCAL hash_mem_multiplier = 1.0`); // Deterministic PG17 hash cap const query = ` SELECT o.tenant_id, o.customer_id, COUNT(o.id) AS total_orders, SUM(i.quantity * i.unit_price) AS gross_revenue FROM orders o JOIN order_items i ON o.id = i.order_id AND o.tenant_id = i.tenant_id WHERE o.tenant_id = $1 AND o.created_at BETWEEN $2 AND $3 GROUP BY o.tenant_id, o.customer_id; `; const result = await client.query(query, [tenantId, startDate, endDate]); await client.query('COMMIT'); return result.rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Hash Join Adaptive Spill Memory-Cap Standard

Postgres 17 Hash Join Adaptive Spill Memory-Cap Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic per-query memory limits and spill-to-disk safeguards for multi-tenant analytical workloads in PostgreSQL 17. Guarantees that hash join allocations adaptively respect transaction-scoped work_mem and hash_mem_multiplier boundaries, preventing concurrent multi-tenant aggregations from triggering Linux kernel OOM (Out-Of-Memory) killer termination. 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 Hash Table Expansion Triggering Host-Level OOM Killer During Multi-Tenant Analytical Aggregations 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 Hash Table Expansion Triggering Host-Level OOM Killer During Multi-Tenant Analytical Aggregations	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 ad-hoc aggregations into dedicated transactional contexts that explicitly execute SET LOCAL work_mem and SET LOCAL hash_mem_multiplier within predetermined tenant tier budgets.

Engineers MUST ALWAYS isolate analytical and ad-hoc aggregations into dedicated transactional contexts that explicitly execute SET LOCAL work_mem and SET LOCAL hash_mem_multiplier within predetermined tenant tier budgets.

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.