Postgres 17 Backend Memory-Context Clamping & Preemptive Resource Guardrails

--- description: Postgres 17 Backend Memory-Context Clamping & Preemptive Resource Guardrails - Enforces strict query-level memory quotas and leverages PostgreSQL 17 enhanced memory context introspection (pg_backend_memory_contexts) to prevent Linux OOM-killer invocations. Requires programmatic session-level clamping of work_mem and statement_timeout alongside proactive termination of runaway memory-consuming analytical queries. Prevents uncontrolled backend memory growth across complex hash joins, materializations, and sort operations. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/infra/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-memory-context-clamping-guardrail" --- # Postgres 17 Backend Memory-Context Clamping & Preemptive Resource Guardrails > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Unclamped work_mem Allocation Leading to Backend Process Linux OOM Evictions ## System Constraints You are a Principal Database Architect and Backend Platform Engineer enforcing Postgres 17 Memory-Context Query Clamping & Resource Quotas. Mandate invariant controls against unconstrained backend memory allocation. Every complex query execution, batch operation, or analytical report must execute within isolated, pre-clamped session parameters. Proactively clamp work_mem, statement_timeout, and inspect pg_backend_memory_contexts to abort allocations before they trigger SIGKILL from the OS OOM killer. Never permit raw connection pooling checkouts that inherit unbounded global work_mem for complex joins, aggregations, or bulk inserts. ## Strict Guidelines - Engineers MUST ALWAYS isolate high-memory or ad-hoc batch queries within localized database transactions and apply scoped 'SET LOCAL work_mem' limits tailored to that specific operational profile. - Engineers MUST NOT rely on server-wide work_mem settings for complex sorting, hashing, or window functions that scale linearly with connection concurrency. - Services MUST monitor query execution against Postgres 17 'pg_backend_memory_contexts' when performing asynchronous long-running batch ingestion or reporting jobs. - Engineers MUST ALWAYS enforce strict 'SET LOCAL statement_timeout' alongside memory clamps to prevent runaway backend state locks. - Connection pooling frameworks MUST reset or wrap operations inside explicit transaction boundaries to prevent leaky 'SET' directives from contaminating pooled connections (prefer 'SET LOCAL'). - Services NEVER execute dynamic unbounded set-returning subqueries or cartesian joins without explicit memory budget assertions. ## Bad vs Good Implementations ### Unclamped Pooled Execution vs Scoped Clamped Session with Context Guard #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executes high-complexity joins and sorting directly on a raw pooled connection using default global memory settings. When high concurrency hits this path, the backend processes allocate separate work_mem buffers per node, multiplying memory consumption beyond system RAM and causing kernel OOM kills. ```typescript import { Pool } from 'pg'; const pool = new Pool(); export async function runAdHocAggregation(tenantId: string, payloadIds: string[]): Promise<any[]> { // Anti-Pattern: Inherits global work_mem on shared pool connection. // A complex hash join across large arrays can cause the backend memory context // to balloon uncontrollably, triggering the host Linux OOM killer. const client = await pool.connect(); try { const query = ` SELECT a.id, a.name, b.metric, sum(b.value) as total FROM tenant_metrics b JOIN tenant_entities a ON a.id = b.entity_id WHERE a.tenant_id = $1 AND b.id = ANY($2::uuid[]) GROUP BY a.id, a.name, b.metric ORDER BY total DESC; `; const res = await client.query(query, [tenantId, payloadIds]); return res.rows; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: Wraps query execution inside an atomic transaction using SET LOCAL to safely clamp work_mem and statement_timeout. When the transaction concludes, memory configurations revert instantly, guaranteeing that pool re-use remains deterministic and isolated from OOM-killer hazards. ```typescript import { Pool, PoolClient } from 'pg'; const pool = new Pool(); interface MemoryClampedOptions { workMemKb?: number; statementTimeoutMs?: number; } export async function runAdHocAggregation( tenantId: string, payloadIds: string[], options: MemoryClampedOptions = {} ): Promise<any[]> { const workMem = Math.min(options.workMemKb ?? 65536, 131072); // Max 128MB per backend node const timeout = options.statementTimeoutMs ?? 15000; // Strict 15s circuit breaker const client: PoolClient = await pool.connect(); try { // Invariant: Wrap in transaction and clamp memory locally to session transaction context await client.query('BEGIN;'); await client.query(`SET LOCAL work_mem = '${workMem}kB';`); await client.query(`SET LOCAL statement_timeout = ${timeout};`); const query = ` SELECT a.id, a.name, b.metric, sum(b.value) as total FROM tenant_metrics b JOIN tenant_entities a ON a.id = b.entity_id WHERE a.tenant_id = $1 AND b.id = ANY($2::uuid[]) GROUP BY a.id, a.name, b.metric ORDER BY total DESC; `; const res = await client.query(query, [tenantId, payloadIds]); await client.query('COMMIT;'); return res.rows; } catch (error) { await client.query('ROLLBACK;').catch(() => {}); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Backend Memory-Context Clamping & Preemptive Resource Guardrails

Postgres 17 Backend Memory-Context Clamping & Preemptive Resource Guardrails defines an industrial-grade behavioral standard for backend engineering. Enforces strict query-level memory quotas and leverages PostgreSQL 17 enhanced memory context introspection (pg_backend_memory_contexts) to prevent Linux OOM-killer invocations. Requires programmatic session-level clamping of work_mem and statement_timeout alongside proactive termination of runaway memory-consuming analytical queries. Prevents uncontrolled backend memory growth across complex hash joins, materializations, and sort operations. 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 Unclamped work_mem Allocation Leading to Backend Process Linux OOM Evictions 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 Unclamped work_mem Allocation Leading to Backend Process Linux OOM Evictions	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-memory or ad-hoc batch queries within localized database transactions and apply scoped 'SET LOCAL work_mem' limits tailored to that specific operational profile.

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.