PostgreSQL 17 Memory-Bounded Hash Aggregation & Streaming I/O Execution Guardrails

--- description: PostgreSQL 17 Memory-Bounded Hash Aggregation & Streaming I/O Execution Guardrails - Enforces strict session-level memory budgeting and streaming read engine primitives in PostgreSQL 17 to prevent disk-spill buffer degradation during concurrent aggregations. Mandates scoped work_mem and hash_mem_multiplier governance alongside EXPLAIN (ANALYZE, BUFFERS, MEMORY) verification to eliminate buffer contention and query latency spikes. Prohibits global work_mem inflation that induces OOM kills under concurrent heavy analytic load. globs: src/database/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-memory-bounded-hashagg-streaming-io" --- # PostgreSQL 17 Memory-Bounded Hash Aggregation & Streaming I/O Execution Guardrails > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global work_mem Inflation and Unbounded Hash Aggregation Disk Spills Causing Severe I/O Wait Cascades ## System Constraints You are a Principal Database Architect and Systems Engineer enforcing strict PostgreSQL 17 execution invariants. Your mandate is to prevent unbounded hash aggregation disk spills and optimize streaming I/O execution. You must ensure all analytical queries execute within bounded memory per execution node, use scoped transaction-level SET LOCAL directives for work_mem and hash_mem_multiplier, and inspect plan nodes with EXPLAIN (ANALYZE, BUFFERS, MEMORY). Reject any code that inflates global cluster memory parameters or executes ad-hoc groupings without partition boundaries. ## Strict Guidelines - Engineers MUST ALWAYS isolate high-cardinality analytical aggregations into dedicated transactions using `SET LOCAL work_mem` and `SET LOCAL hash_mem_multiplier` instead of altering global or user-level database configurations. - Engineers MUST NOT rely on uncalibrated default memory budgets for multi-million row groupings, which trigger recursive disk-spill partitions in HashAgg and devastate shared buffer hit ratios. - Services MUST execute analytical query plans under `EXPLAIN (ANALYZE, BUFFERS, MEMORY)` in staging to guarantee that HashAgg execution states remain in 'Disk: 0kB' (Memory bounded) and leverage PostgreSQL 17's streaming read engine. - Code SHOULD pre-filter large datasets with indexed sargable predicates or leverage partitioning to reduce HashAgg bucket sizing before performing GROUP BY operations across concurrent analytic requests. - Components NEVER execute unbounded analytical queries directly on write-primary connections; analytical aggregations must target designated read replicas configured with optimized vector streaming I/O parameters. ## Bad vs Good Implementations ### Scoped Transaction Memory & Streaming Execution vs Unbounded Global Execution #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default work_mem causes HashAgg to spill partitions to temp files on disk when tracking high-cardinality groups. This leads to intensive disk I/O, cache invalidation, and severe latency degradation under concurrent analytics workloads. ```typescript import { Pool } from 'pg'; export async function getAggregatedAnalytics(pool: Pool, tenantId: string) { // Anti-pattern: Relies on default work_mem (typically 4MB), causing PostgreSQL 17 // HashAgg to spill multiple partitions to disk during high-cardinality groupings. // Causes massive temp file write I/O and query latency degradation under concurrency. const query = ` SELECT metric_name, DATE_TRUNC('hour', recorded_at) AS window_hour, AVG(value) as avg_val, COUNT(DISTINCT user_id) as unique_users FROM telemetry_events WHERE tenant_id = $1 GROUP BY metric_name, DATE_TRUNC('hour', recorded_at); `; const client = await pool.connect(); try { const res = await client.query(query, [tenantId]); return res.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Uses a read-only transaction on a read replica, scopes work_mem and hash_mem_multiplier locally to avoid cluster OOM, bounds temporal ranges to prevent memory explosion, and configures PostgreSQL 17 streaming I/O execution. ```typescript import { Pool, PoolClient } from 'pg'; interface AnalyticsRow { metric_name: string; window_hour: Date; avg_val: number; unique_users: string; } export async function getAggregatedAnalytics( replicaPool: Pool, tenantId: string, startTime: Date, endTime: Date ): Promise<AnalyticsRow[]> { const client: PoolClient = await replicaPool.connect(); try { await client.query('BEGIN READ ONLY;'); // PostgreSQL 17 memory boundary: allocate deterministic memory per-node // hash_mem_multiplier scales work_mem specifically for HashAgg nodes before spilling await client.query('SET LOCAL work_mem = \'64MB\';'); await client.query('SET LOCAL hash_mem_multiplier = 2.0;'); // Enable PostgreSQL 17 streaming read engine optimization for sequential prefetching await client.query('SET LOCAL io_combine_limit = 16;'); const query = ` SELECT metric_name, DATE_TRUNC('hour', recorded_at) AS window_hour, AVG(value) AS avg_val, COUNT(DISTINCT user_id) AS unique_users FROM telemetry_events WHERE tenant_id = $1 AND recorded_at >= $2 AND recorded_at < $3 GROUP BY metric_name, DATE_TRUNC('hour', recorded_at); `; const result = await client.query<AnalyticsRow>(query, [tenantId, startTime, endTime]); await client.query('COMMIT;'); return result.rows; } catch (err) { await client.query('ROLLBACK;').catch(() => {}); throw err; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Memory-Bounded Hash Aggregation & Streaming I/O Execution Guardrails

PostgreSQL 17 Memory-Bounded Hash Aggregation & Streaming I/O Execution Guardrails defines an industrial-grade behavioral standard for backend engineering. Enforces strict session-level memory budgeting and streaming read engine primitives in PostgreSQL 17 to prevent disk-spill buffer degradation during concurrent aggregations. Mandates scoped work_mem and hash_mem_multiplier governance alongside EXPLAIN (ANALYZE, BUFFERS, MEMORY) verification to eliminate buffer contention and query latency spikes. Prohibits global work_mem inflation that induces OOM kills under concurrent heavy analytic load. 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: Global work_mem Inflation and Unbounded Hash Aggregation Disk Spills Causing Severe I/O Wait Cascades 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	Global work_mem Inflation and Unbounded Hash Aggregation Disk Spills Causing Severe I/O Wait Cascades	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 high-cardinality analytical aggregations into dedicated transactions using `SET LOCAL work_mem` and `SET LOCAL hash_mem_multiplier` instead of altering global or user-level database configurations.

Engineers MUST ALWAYS isolate high-cardinality analytical aggregations into dedicated transactions using `SET LOCAL work_mem` and `SET LOCAL hash_mem_multiplier` instead of altering global or user-level database configurations.

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.