Postgres 17 JSON_TABLE Dynamic Execution Memory Bound Standard

--- description: Postgres 17 JSON_TABLE Dynamic Execution Memory Bound Standard - Enforces deterministic memory bounds and query timeouts on Postgres 17 SQL/JSON JSON_TABLE projections across streaming ingest pipelines. Prevents adversarial, deep-nested, or oversized JSON payloads from triggering backend Out-Of-Memory (OOM) terminations and cascading worker evictions. globs: src/**/*.ts, src/**/*.dao.ts, src/**/*.repository.ts, src/**/*.service.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-bound-standard" --- # Postgres 17 JSON_TABLE Dynamic Execution Memory Bound Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE Projections on Untrusted Ingest Payloads Inducing Backend Worker OOM Kills ## System Constraints You are a Principal Backend Database Architect enforcing memory bounding standards for Postgres 17 SQL/JSON features. Mandate strict pre-execution byte limits, bounded jsonpath queries without recursive descent, localized session memory guardrails (statement_timeout, work_mem), and parameterization via the PASSING clause. ## Strict Guidelines - Engineers MUST ALWAYS enforce pre-flight byte-size validation at the ingress layer and verify octet_length before passing JSON payloads to JSON_TABLE. - Engineers MUST NOT utilize recursive wildcard descent paths (e.g., '$.**') or unconstrained nested paths inside JSON_TABLE row pattern expressions. - Code SHOULD execute JSON_TABLE projections within an isolated transaction enforcing 'SET LOCAL statement_timeout' and bounded 'SET LOCAL work_mem'. - Services MUST explicitly specify column scalar types with deterministic width bounds (e.g., VARCHAR(255) instead of unbounded TEXT) and configure explicit error clauses (e.g., ERROR ON ERROR or EMPTY ON ERROR). - Components NEVER interpolate raw JSON strings into SQL statements; all projections MUST bind inputs as typed parameters using the SQL/JSON PASSING clause. ## Bad vs Good Implementations ### Untrusted Streaming Ingest via JSON_TABLE #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Direct string interpolation exposes SQL injection risks. Using recursive descent '$.**' forces the Postgres JSON path parser to traverse arbitrary depths in memory, causing rapid memory spikes. Unbounded TEXT columns and absence of 'statement_timeout' allow maliciously crafted payloads to exhaust backend memory and trigger OS OOM terminations of postgres worker processes. ```typescript import { PoolClient } from 'pg'; export async function ingestStreamBatch(client: PoolClient, rawPayload: string): Promise<void> { // ANTI-PATTERN: Direct interpolation, no byte bounds, recursive descent '$.**', // unbounded TEXT columns, and no statement-level timeout or memory controls. const query = ` INSERT INTO event_store (event_id, event_type, payload) SELECT jt.id, jt.event_type, jt.data FROM JSON_TABLE( '${rawPayload}', '$.**' COLUMNS ( id INT PATH '$.id', event_type TEXT PATH '$.type', data JSONB PATH '$.data' ) ) AS jt; `; await client.query(query); } ``` #### Production Standard (Correct) > **Standard**: Validates payload size prior to database dispatch. Enforces explicit per-transaction statement timeouts and restricted work_mem. Uses parameterized input via the SQL/JSON PASSING clause, eliminates recursive wildcard traversal with explicit array iteration ('$.events[*]'), bounds output scalar types to VARCHAR(64)/BIGINT, and attaches strict 'ERROR ON ERROR'/'EMPTY ON ERROR' handling to prevent unbounded buffer allocation. ```typescript import { PoolClient } from 'pg'; const MAX_JSON_BYTES = 2 * 1024 * 1024; // 2MB hard bound const INGEST_TIMEOUT_MS = 3000; export async function ingestStreamBatch(client: PoolClient, rawPayload: string): Promise<void> { const byteLength = Buffer.byteLength(rawPayload, 'utf8'); if (byteLength > MAX_JSON_BYTES) { throw new Error(`Payload exceeds maximum allowed memory budget: ${byteLength} bytes`); } // Execute within a bounded transaction context await client.query('BEGIN'); try { await client.query(`SET LOCAL statement_timeout = '${INGEST_TIMEOUT_MS}ms'`); await client.query(`SET LOCAL work_mem = '16MB'`); const query = ` INSERT INTO event_store (event_id, event_type, payload) SELECT jt.id, jt.event_type, jt.data FROM JSON_TABLE( $1::jsonb, '$.events[*]' PASSING $1::jsonb AS doc COLUMNS ( id BIGINT PATH '$.id' ERROR ON ERROR, event_type VARCHAR(64) PATH '$.type' ERROR ON ERROR, data JSONB PATH '$.data' EMPTY ON ERROR ) ) AS jt; `; await client.query(query, [rawPayload]); await client.query('COMMIT'); } catch (err) { await client.query('ROLLBACK'); throw err; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Dynamic Execution Memory Bound Standard

Postgres 17 JSON_TABLE Dynamic Execution Memory Bound Standard defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic memory bounds and query timeouts on Postgres 17 SQL/JSON JSON_TABLE projections across streaming ingest pipelines. Prevents adversarial, deep-nested, or oversized JSON payloads from triggering backend Out-Of-Memory (OOM) terminations and cascading worker evictions. 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 Projections on Untrusted Ingest Payloads Inducing Backend Worker 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	Unbounded JSON_TABLE Projections on Untrusted Ingest Payloads Inducing Backend Worker 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 enforce pre-flight byte-size validation at the ingress layer and verify octet_length before passing JSON payloads to JSON_TABLE.

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.