Postgres 17 JSON_TABLE Parallel Worker Memory Fencing

--- description: Postgres 17 JSON_TABLE Parallel Worker Memory Fencing - Enforces transaction-scoped dynamic work_mem calculation and parallel worker fences for SQL/JSON transformations using PostgreSQL 17 JSON_TABLE. Unbounded parallel query plans executing memory-intensive JSON unpacking lead to multiplicative work_mem allocation across worker nodes, triggering Linux OOM-killer termination of Postgres backends. globs: src/database/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/queries/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-parallel-worker-memory-fencing" --- # Postgres 17 JSON_TABLE Parallel Worker Memory Fencing > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Global Uncapped work_mem with Unconstrained max_parallel_workers Under JSON_TABLE Scans ## System Constraints You are a Principal Database Reliability Engineer enforcing Postgres 17 JSON_TABLE Parallel Worker Memory Fencing. Mandate explicit, transaction-level work_mem throttling and parallel execution boundaries on all queries utilizing SQL/JSON JSON_TABLE transformations to eliminate backend OOM crashes. ## Strict Guidelines - Engineers MUST ALWAYS execute JSON_TABLE batch transformations inside explicit transactions configured with dynamically throttled 'SET LOCAL work_mem' and 'SET LOCAL max_parallel_workers_per_gather'. - Engineers MUST NOT execute JSON_TABLE across large JSONB documents under default session work_mem or unbounded pool-level parallel workers. - Code SHOULD calculate transaction work_mem using the formula: floor(SafeBatchBudgetBytes / (ActiveWorkers + 1)) to prevent aggregate worker memory overshoot. - Services MUST enforce strict PASSING clause typing in JSON_TABLE columns (e.g., PATH '$' RETURNING ... ERROR ON ERROR) to prevent silent fallback plan materialization and hidden worker allocations. - Components NEVER execute raw or ORM-generated dynamic queries using JSON_TABLE without an attached execution timeout and worker fence configuration. ## Bad vs Good Implementations ### Unfenced JSON_TABLE Query vs. Dynamic Memory Fenced Transaction #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Default pool execution allows the query planner to spawn parallel workers where each worker node allocates up to work_mem independently during JSON parsing and sorting phases. Under high payload sizes, aggregate memory consumption exceeds system memory, triggering an out-of-memory kernel panic or backend termination. ```typescript import { Pool } from 'pg'; export async function ingestJsonEventsBad(pool: Pool, payload: Buffer): Promise<void> { // Anti-pattern: Inherits ambient work_mem and uncontrolled parallel worker count. // If max_parallel_workers_per_gather is 4, total work_mem can spike by (4 + 1) * nodes * work_mem, // causing Linux OOM killer termination on massive nested JSONB arrays. await pool.query(` INSERT INTO parsed_events (event_id, event_type, payload_data) SELECT jt.id, jt.type, jt.data FROM raw_ingest r, JSON_TABLE( r.raw_json, '$.events[*]' COLUMNS ( id BIGINT PATH '$.id', type TEXT PATH '$.type', data JSONB PATH '$.data' ) ) AS jt WHERE r.batch_id = $1; `, [123]); } ``` #### Production Standard (Correct) > **Standard**: The transaction strictly isolates the execution using SET LOCAL to enforce max_parallel_workers_per_gather and computes a safe, partitioned work_mem. Adding ERROR ON ERROR avoids silent plan materialization fallbacks, guaranteeing bounded memory overhead. ```typescript import { Pool, PoolClient } from 'pg'; interface FencedBatchOptions { maxParallelWorkers: number; totalBudgetMb: number; batchId: number; } export async function ingestJsonEventsGood( pool: Pool, options: FencedBatchOptions ): Promise<void> { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN;'); // Invariant: Derive per-worker work_mem to fence total memory consumption // Formula: Budget / (workers + 1 leader) const perWorkerMemMb = Math.max( 4, Math.floor(options.totalBudgetMb / (options.maxParallelWorkers + 1)) ); // Fencing execution parameters locally within the transaction await client.query(`SET LOCAL statement_timeout = '30s';`); await client.query(`SET LOCAL max_parallel_workers_per_gather = ${options.maxParallelWorkers};`); await client.query(`SET LOCAL work_mem = '${perWorkerMemMb}MB';`); await client.query(` INSERT INTO parsed_events (event_id, event_type, payload_data) SELECT jt.id, jt.type, jt.data FROM raw_ingest r, JSON_TABLE( r.raw_json, '$.events[*]' COLUMNS ( id BIGINT PATH '$.id' ERROR ON ERROR, type VARCHAR(64) PATH '$.type' ERROR ON ERROR, data JSONB PATH '$.data' ) ) AS jt WHERE r.batch_id = $1; `, [options.batchId]); await client.query('COMMIT;'); } catch (error) { await client.query('ROLLBACK;'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Parallel Worker Memory Fencing

Postgres 17 JSON_TABLE Parallel Worker Memory Fencing defines an industrial-grade behavioral standard for backend engineering. Enforces transaction-scoped dynamic work_mem calculation and parallel worker fences for SQL/JSON transformations using PostgreSQL 17 JSON_TABLE. Unbounded parallel query plans executing memory-intensive JSON unpacking lead to multiplicative work_mem allocation across worker nodes, triggering Linux OOM-killer termination of Postgres backends. 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 with Unconstrained max_parallel_workers Under JSON_TABLE Scans 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 with Unconstrained max_parallel_workers Under JSON_TABLE Scans	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 execute JSON_TABLE batch transformations inside explicit transactions configured with dynamically throttled 'SET LOCAL work_mem' and 'SET LOCAL max_parallel_workers_per_gather'.

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.