PostgreSQL 17 Memory Budgeting & Dynamic Worker Guardrail

--- description: PostgreSQL 17 Memory Budgeting & Dynamic Worker Guardrail - Enforces deterministic work_mem allocation, dynamic shared memory (DSM) ceiling bounds, and capped parallel query worker execution paths in PostgreSQL 17. Eliminates container OOM kills triggered by unconstrained concurrent sorts, hash tables, and parallel partitioned index scans across multi-tenant workloads. globs: src/database/**/*.{ts,js,py,go,sql}, src/repositories/**/*.{ts,js,py,go}, infra/postgres/**/*.conf, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-memory-budgeting-worker-scaling-guardrail" --- # PostgreSQL 17 Memory Budgeting & Dynamic Worker Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Uncapped work_mem and Unbounded Parallel Worker Fan-Out Inducing Linux OOM Kills ## System Constraints You are a Principal Database Infrastructure Engineer enforcing PostgreSQL 17 Memory Budgeting & Parallel Worker Scaling Guardrails. You must mandate strict session-level memory budgeting for complex aggregate queries, eliminate global work_mem inflation, enforce SET LOCAL overrides within scoped transactions, and bound parallel worker fan-out across multi-partition table hierarchies to prevent OS-level OOM container termination. ## Strict Guidelines - Engineers MUST ALWAYS calculate maximum aggregate memory exposure via the invariant: Max RAM = shared_buffers + (max_connections * (work_mem * max_parallel_workers_per_gather + maintenance_work_mem)). - Engineers MUST NOT configure global work_mem above baseline operational needs (defaulting to 4MB-16MB) in postgresql.conf to satisfy isolated analytical queries. - Engineers MUST ALWAYS enforce SET LOCAL work_mem and SET LOCAL max_parallel_workers_per_gather within an explicit transaction boundary for memory-intensive multi-tenant partition aggregations. - Services MUST execute EXPLAIN (ANALYZE, MEMORY) in pre-production regression suites to verify sort, hash, and parallel partition scans execute entirely within allocated work_mem without spilling to disk or exceeding the tenant budget. - Components NEVER execute raw unbounded parallel aggregates across multi-tenant partitioned indices without enforcing plan-level worker limits, statement timeouts, and max_parallel_maintenance_workers constraints. ## Bad vs Good Implementations ### Unbounded Parallel Aggregation vs. Scoped Session-Bounded Transaction #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing analytical aggregations across partitioned tables with default connection pools risks concurrent spikes where every parallel worker allocates multiple work_mem allocations for sorts and hash joins, triggering an OS OOM kill. ```typescript import { Pool } from 'pg'; export async function runTenantAggregation(pool: Pool, tenantId: string): Promise<any[]> { // ANTI-PATTERN: Direct execution against global config with unbounded workers // Running against a multi-tenant partitioned index easily spawns max workers // where each worker consumes work_mem per node, causing cgroup OOM killer termination. const query = ` SELECT metric_name, percentile_cont(0.95) WITHIN GROUP (ORDER BY metric_value) AS p95 FROM tenant_partitioned_telemetry WHERE tenant_id = $1 GROUP BY metric_name; `; const result = await pool.query(query, [tenantId]); return result.rows; } ``` #### Production Standard (Correct) > **Standard**: SET LOCAL guarantees that work_mem, statement_timeout, and max_parallel_workers_per_gather are scoped strictly to the current transaction, preventing global memory bloat and deterministic per-query ceiling controls. ```typescript import { Pool, PoolClient } from 'pg'; export async function runTenantAggregation(pool: Pool, tenantId: string): Promise<any[]> { const client: PoolClient = await pool.connect(); try { // GUARDRAIL: Wrap in explicit transaction and constrain work_mem and workers locally await client.query('BEGIN READ ONLY;'); // Bound memory strictly to 64MB for this session only; reset automatically on COMMIT/ROLLBACK await client.query("SET LOCAL work_mem = '64MB';"); // Cap parallel workers to avoid worker starvation and multiplicative memory explosion await client.query('SET LOCAL max_parallel_workers_per_gather = 2;'); // Apply a fail-safe execution timeout to prevent resource exhaustion await client.query("SET LOCAL statement_timeout = '5000ms';"); const query = ` SELECT metric_name, percentile_cont(0.95) WITHIN GROUP (ORDER BY metric_value) AS p95 FROM tenant_partitioned_telemetry WHERE tenant_id = $1 GROUP BY metric_name; `; const result = await client.query(query, [tenantId]); 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: PostgreSQL 17 Memory Budgeting & Dynamic Worker Guardrail

PostgreSQL 17 Memory Budgeting & Dynamic Worker Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic work_mem allocation, dynamic shared memory (DSM) ceiling bounds, and capped parallel query worker execution paths in PostgreSQL 17. Eliminates container OOM kills triggered by unconstrained concurrent sorts, hash tables, and parallel partitioned index scans across multi-tenant workloads. 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 Uncapped work_mem and Unbounded Parallel Worker Fan-Out Inducing Linux 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	Global Uncapped work_mem and Unbounded Parallel Worker Fan-Out Inducing Linux 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 calculate maximum aggregate memory exposure via the invariant

Engineers MUST ALWAYS calculate maximum aggregate memory exposure via the invariant: Max RAM = shared_buffers + (max_connections * (work_mem * max_parallel_workers_per_gather + maintenance_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.