Postgres 17 JSON_TABLE Memory Bounds and WorkMem Guardrail

--- description: Postgres 17 JSON_TABLE Memory Bounds and WorkMem Guardrail - Enforces strict transaction-local memory ceilings (`work_mem`), streaming projection semantics, and chunked cursor evaluation when extracting relational rows from massive JSON payloads using Postgres 17's SQL/JSON `JSON_TABLE`. Prevents query backend termination and cluster-wide Linux Out-Of-Memory (OOM) killer cascades caused by unconstrained in-memory JSON path tree evaluation during concurrent high-throughput unnesting. globs: src/db/**/*.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-json-table-work-mem-allocation-standard" --- # Postgres 17 JSON_TABLE Memory Bounds and WorkMem Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE evaluation over large payloads without transaction-local memory fencing and streaming limits ## System Constraints You are a Principal Database Architect and Infrastructure Reliability Engineer enforcing Postgres 17 JSON_TABLE bounded memory allocation invariants. Uncontrolled invocation of JSON_TABLE over non-trivial JSON documents causes exponential memory overhead due to JSON document object model (DOM) materialization in server process memory. You must mandate transaction-local work_mem ceilings, enforce batching/cursor ingestion for large payloads, forbid PASSING expressions with oversized inline literals, and require explicit PATH data type mappings with strict ERROR ON ERROR handling to prevent silent truncation or runaway memory allocation. ## Strict Guidelines - Engineers MUST ALWAYS enforce a transaction-scoped `SET LOCAL work_mem = '...MB'` ceiling prior to executing `JSON_TABLE` operations on dynamic or user-supplied JSONB documents. - Engineers MUST NOT execute `JSON_TABLE` directly on raw unbounded client request payloads without pre-validating max payload byte-length in the application tier. - Services MUST process payloads exceeding 10MB through chunked batch processing or streaming cursor pagination instead of invoking `JSON_TABLE` over an aggregate monolithic document. - Queries MUST ALWAYS specify explicit `COLUMNS` definitions with bounded types (e.g., `VARCHAR(n)` instead of unbounded `TEXT`, explicit `ERROR ON ERROR` clauses) to prevent silent fallback allocations or unbounded string growth. - Components NEVER execute concurrent parallel workers on queries combining `JSON_TABLE` with heavy sorting (`ORDER BY`) or hash joins without calculating the `max_parallel_workers_per_gather * work_mem` cumulative memory footprint. ## Bad vs Good Implementations ### Unbounded JSON_TABLE Extraction vs. Transaction-Fenced Bounded Ingestion #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Passing unbounded JSONB into JSON_TABLE with default server-wide work_mem and unbounded text types causes Postgres to allocate large in-memory parse trees. Concurrent executions multiply this footprint, leading to PostgreSQL backend crashes or Linux OOM-killer invocations. ```typescript import { sql } from 'kysely'; import { db } from '../database'; export async function ingestAuditLogs(rawJsonPayload: string) { // ANTI-PATTERN: Passes massive JSON payload directly into JSON_TABLE without // transaction-local memory guards, using unbounded text types and empty error handling. // This can consume unbounded backend memory and trigger an OS OOM kill under concurrency. return await db.transaction().execute(async (trx) => { return await sql` SELECT jt.event_id, jt.event_name, jt.payload FROM JSON_TABLE( ${rawJsonPayload}::jsonb, '$[*]' COLUMNS ( event_id text PATH '$.id', event_name text PATH '$.name', payload text FORMAT JSON PATH '$.details' EMPTY ON ERROR ) ) AS jt `.execute(trx); }); } ``` #### Production Standard (Correct) > **Standard**: Validates payload size at runtime before reaching the database, sets transaction-local `work_mem` to prevent runaway allocation, applies a tight `statement_timeout`, uses strictly bounded types (`uuid`, `varchar(128)`), and guarantees strict parsing failures via `ERROR ON ERROR`. ```typescript import { sql } from 'kysely'; import { db } from '../database'; const MAX_PAYLOAD_BYTES = 5 * 1024 * 1024; // 5 MB threshold limit export async function ingestAuditLogs(rawJsonPayload: string) { const byteLength = Buffer.byteLength(rawJsonPayload, 'utf8'); if (byteLength > MAX_PAYLOAD_BYTES) { throw new Error(`Payload size ${byteLength} exceeds maximum bounded allocation limit of ${MAX_PAYLOAD_BYTES}`); } // PRODUCTION INVARIANT: Set transaction-local work_mem and statement_timeout, // enforce bounded string column lengths, and mandate ERROR ON ERROR. return await db.transaction().execute(async (trx) => { await sql`SET LOCAL work_mem = '32MB'`.execute(trx); await sql`SET LOCAL statement_timeout = '15s'`.execute(trx); return await sql` SELECT jt.event_id, jt.event_name, jt.payload FROM JSON_TABLE( ${rawJsonPayload}::jsonb, '$[*]' COLUMNS ( event_id uuid PATH '$.id' ERROR ON ERROR, event_name varchar(128) PATH '$.name' ERROR ON ERROR, payload jsonb FORMAT JSON PATH '$.details' ERROR ON ERROR ) ) AS jt `.execute(trx); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Memory Bounds and WorkMem Guardrail

Postgres 17 JSON_TABLE Memory Bounds and WorkMem Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict transaction-local memory ceilings (`work_mem`), streaming projection semantics, and chunked cursor evaluation when extracting relational rows from massive JSON payloads using Postgres 17's SQL/JSON `JSON_TABLE`. Prevents query backend termination and cluster-wide Linux Out-Of-Memory (OOM) killer cascades caused by unconstrained in-memory JSON path tree evaluation during concurrent high-throughput unnesting. 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 JSON_TABLE evaluation over large payloads without transaction-local memory fencing and streaming limits 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 JSON_TABLE evaluation over large payloads without transaction-local memory fencing and streaming limits	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 enforce a transaction-scoped `SET LOCAL work_mem = '...MB'` ceiling prior to executing `JSON_TABLE` operations on dynamic or user-supplied JSONB documents.

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.