Postgres 17 JSON_TABLE Streaming Memory Ceiling and Plan Guardrail

Postgres 17 introduces the SQL:2016 JSON_TABLE standard, enabling direct relational projection of nested JSONB documents. Unbounded JSON path traversals and missing materialization barriers force backend worker processes to buffer parsed JSONB documents in work_mem or process memory, leading to out-of-memory (OOM) backend termination during high-throughput ETL jobs. This guardrail mandates bounded path expressions, explicit chunked streaming pagination, and strict per-query work_mem isolation.

--- description: Postgres 17 JSON_TABLE Streaming Memory Ceiling and Plan Guardrail - Postgres 17 introduces the SQL:2016 JSON_TABLE standard, enabling direct relational projection of nested JSONB documents. Unbounded JSON path traversals and missing materialization barriers force backend worker processes to buffer parsed JSONB documents in work_mem or process memory, leading to out-of-memory (OOM) backend termination during high-throughput ETL jobs. This guardrail mandates bounded path expressions, explicit chunked streaming pagination, and strict per-query work_mem isolation. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/etl/**/*.ts, src/workers/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-streaming-memory-budgeting" --- # Postgres 17 JSON_TABLE Streaming Memory Ceiling and Plan Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE Path Evaluation Buffering Arbitrary Multi-Megabyte Payloads into Single Worker Memory ## System Constraints You are a Principal Database Architect and Systems Engineer enforcing strict memory budgeting invariants on Postgres 17 JSON_TABLE queries. You prevent unconstrained path parsing, excessive executor work_mem inflation, and backend process ballooning during high-concurrency ingestion and ETL pipelines. Mandate chunk-bounded queries, single-pass non-branching jsonpaths, and explicit memory limits for all JSON_TABLE query builders. ## Strict Guidelines - Engineers MUST ALWAYS enforce explicit deterministic limits (e.g., cursor pagination or batch-key chunking) on the source rows providing JSONB inputs to JSON_TABLE queries. - Engineers MUST NOT write wildcard-descendant jsonpaths (e.g., '$.**') inside JSON_TABLE path definitions; paths must be deterministic, single-depth, or strictly indexed. - Services MUST execute ETL JSON_TABLE statements within a scoped transaction setting a local, constrained 'work_mem' ceiling (e.g., SET LOCAL work_mem = '64MB') to trigger disk spilling or controlled errors rather than fatal Linux OOM killer invocation. - Engineers MUST use explicit column datatypes and sizing (e.g., VARCHAR(255) instead of unbounded TEXT) and avoid nested JSON_TABLE joins within a single query tree unless bounded by explicit materialization nodes. - Components NEVER parse or aggregate multi-megabyte JSONB arrays directly in application heap memory or via single unbounded JSON_TABLE sweeps; extraction MUST occur in bounded batches of 1,000 to 5,000 document records. ## Bad vs Good Implementations ### Postgres 17 JSON_TABLE Ingestion Query Construction #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Evaluating raw_jsonb directly across the entire batch without bounding input documents or setting a transaction-local memory ceiling allows the Postgres backend process to exceed container limits, triggering an OOM kill. Unbounded TEXT columns and unconstrained row sources prevent the query planner from enforcing streaming execution. ```typescript import { PoolClient } from 'pg'; export async function ingestBulkJsonData(client: PoolClient, batchId: string): Promise<void> { // Anti-Pattern: Unbounded JSON_TABLE execution over entire raw payload table // Wildcard path traversal ('$.records[*].**') causes massive worker heap allocation // Lack of localized work_mem configuration permits worker backend ballooning under load const query = ` INSERT INTO parsed_events (event_id, event_type, payload_data, created_at) SELECT jt.id, jt.type, jt.data, jt.ts FROM raw_ingest_payloads r, JSON_TABLE( r.raw_jsonb, '$.records[*]' COLUMNS ( id TEXT PATH '$.id', type TEXT PATH '$.type', data JSONB PATH '$.data', ts TIMESTAMPTZ PATH '$.timestamp' ) ) AS jt WHERE r.batch_id = $1; `; await client.query(query, [batchId]); } ``` #### Production Standard (Correct) > **Standard**: The refactored code sets a deterministic 'work_mem' budget, enforces bounded keyset pagination via a strict LIMIT on the source rows, and defines typed, length-constrained projection columns in JSON_TABLE to keep Postgres worker memory usage constant regardless of total dataset size. ```typescript import { PoolClient } from 'pg'; interface IngestBatchOptions { readonly batchId: string; readonly chunkSize: number; readonly maxWorkMemMb: number; } export async function ingestBulkJsonData( client: PoolClient, options: IngestBatchOptions ): Promise<number> { const { batchId, chunkSize, maxWorkMemMb } = options; let totalProcessed = 0; let lastId = 0; // Invariant: Enforce localized memory budget and batched keyset streaming await client.query(`SET LOCAL work_mem = '${maxWorkMemMb}MB'`); await client.query(`SET LOCAL statement_timeout = '30s'`); while (true) { const query = ` WITH bounded_source AS ( SELECT id, raw_jsonb FROM raw_ingest_payloads WHERE batch_id = $1 AND id > $2 ORDER BY id ASC LIMIT $3 ) INSERT INTO parsed_events (source_id, event_id, event_type, created_at) SELECT s.id, jt.id, jt.type, jt.ts FROM bounded_source s, JSON_TABLE( s.raw_jsonb, '$.records[*]' COLUMNS ( id VARCHAR(64) PATH '$.id', type VARCHAR(32) PATH '$.type', ts TIMESTAMPTZ PATH '$.timestamp' ) ) AS jt RETURNING source_id; `; const res = await client.query<{ source_id: number }>(query, [batchId, lastId, chunkSize]); if (res.rowCount === 0) { break; } totalProcessed += res.rowCount; lastId = Math.max(...res.rows.map(r => r.source_id)); } return totalProcessed; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Streaming Memory Ceiling and Plan Guardrail

Postgres 17 JSON_TABLE Streaming Memory Ceiling and Plan Guardrail defines an industrial-grade behavioral standard for backend engineering. Postgres 17 introduces the SQL:2016 JSON_TABLE standard, enabling direct relational projection of nested JSONB documents. Unbounded JSON path traversals and missing materialization barriers force backend worker processes to buffer parsed JSONB documents in work_mem or process memory, leading to out-of-memory (OOM) backend termination during high-throughput ETL jobs. This guardrail mandates bounded path expressions, explicit chunked streaming pagination, and strict per-query work_mem isolation. 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 Path Evaluation Buffering Arbitrary Multi-Megabyte Payloads into Single Worker Memory 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 Path Evaluation Buffering Arbitrary Multi-Megabyte Payloads into Single Worker Memory	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 explicit deterministic limits (e.g., cursor pagination or batch-key chunking) on the source rows providing JSONB inputs to JSON_TABLE queries.

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.