Postgres 17 JSON_TABLE Bounded Memory Allocation and Relational Ingestion Standard

--- description: Postgres 17 JSON_TABLE Bounded Memory Allocation and Relational Ingestion Standard - Enforces strict payload chunking, typed column projection boundaries, and session-level memory constraints when utilizing PostgreSQL 17's SQL/JSON JSON_TABLE feature. Prevents unindexed lateral expansions of monolithic JSON documents from exceeding backend work_mem or triggering operating system OOM killer process termination. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, src/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-bounded-memory-allocation-standard" --- # Postgres 17 JSON_TABLE Bounded Memory Allocation and Relational Ingestion Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded In-Memory Relational Explosion via Monolithic JSON_TABLE Document Parsing ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 JSON_TABLE bounded memory allocation standards. PostgreSQL 17 introduces the SQL/JSON standard JSON_TABLE() expression, which parses JSON/JSONB documents into relational rowsets. You must ensure that arbitrary client payloads, nested path explosions, and unbounded text columns never cause memory leaks, temp-file disk thrashing, or backend worker SIGKILL crashes. Mandate payload chunking, explicit schema sizing, path depth limits, and work_mem boundaries. ## Strict Guidelines - Engineers MUST ALWAYS chunk input JSON payloads at the application boundary (max 5,000 items or 5MB per batch) before passing them to a JSON_TABLE query. - Engineers MUST NOT project unbounded TEXT columns inside JSON_TABLE; all string columns MUST specify a fixed length (e.g., VARCHAR(255)) with TRUNCATE ON EMPTY or explicit ERROR handlers. - Code MUST explicitly set transaction-local guardrails (SET LOCAL statement_timeout and SET LOCAL work_mem) before executing complex JSON_TABLE queries with NESTED PATH expressions. - Services MUST validate JSON depth and payload byte sizes prior to SQL parameter binding to eliminate deeply recursive jsonpath parsing overhead in the database process. - Queries MUST NOT chain multiple NESTED PATH clauses in a single JSON_TABLE without explicit child row cardinality limits, preventing Cartesian product memory spikes. ## Bad vs Good Implementations ### Unbounded Monolithic JSON_TABLE vs. Bounded Chunked Ingestion #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Feeding an unconstrained multi-megabyte JSON array directly into Postgres 17 JSON_TABLE bypasses node-level heap limits and forces the Postgres worker process to deserialize the entire structure at once. Using unbounded TEXT columns and missing transaction-level execution boundaries exposes the primary database node to memory exhaustion and kernel termination. ```typescript import { Pool } from 'pg'; export async function ingestNestedEvents(pool: Pool, payload: unknown[]): Promise<void> { // ANTI-PATTERN: Passing an unbounded in-memory array directly into JSON_TABLE. // If payload contains 500k records or deep nested trees, Postgres jsonpath evaluation // will allocate memory per path node, easily exceeding work_mem and triggering an OOM. const query = ` INSERT INTO events (event_id, user_id, event_type, payload_data) SELECT jt.event_id, jt.user_id, jt.event_type, jt.payload_data FROM JSON_TABLE( $1::jsonb, '$[*]' COLUMNS ( event_id UUID PATH '$.id', user_id UUID PATH '$.userId', event_type TEXT PATH '$.type', payload_data TEXT FORMAT JSON PATH '$.data' ) ) AS jt; `; await pool.query(query, [JSON.stringify(payload)]); } ``` #### Production Standard (Correct) > **Standard**: Batches records client-side, bounds payload byte size, limits statement timeout and session work_mem, enforces rigid VARCHAR length bounds, and uses strict 'ERROR ON ERROR' parsing directives to guarantee deterministic memory consumption within Postgres 17 workers. ```typescript import { Pool, PoolClient } from 'pg'; const BATCH_SIZE = 2_000; const MAX_PAYLOAD_BYTES = 5 * 1024 * 1024; // 5MB limit per chunk export async function ingestNestedEvents(pool: Pool, rawPayloads: unknown[]): Promise<void> { if (rawPayloads.length === 0) return; for (let i = 0; i < rawPayloads.length; i += BATCH_SIZE) { const chunk = rawPayloads.slice(i, i + BATCH_SIZE); const serialized = JSON.stringify(chunk); if (Buffer.byteLength(serialized, 'utf-8') > MAX_PAYLOAD_BYTES) { throw new Error(`Batch starting at index ${i} exceeds maximum allowed size of 5MB`); } const client: PoolClient = await pool.connect(); try { await client.query('BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED'); // Constrain session work_mem and abort rogue evaluations promptly await client.query("SET LOCAL statement_timeout = '10s'"); await client.query("SET LOCAL work_mem = '64MB'"); const boundedQuery = ` INSERT INTO events (event_id, user_id, event_type, payload_data) SELECT jt.event_id, jt.user_id, jt.event_type, jt.payload_data FROM JSON_TABLE( $1::jsonb, '$[*]' COLUMNS ( event_id UUID PATH '$.id' ERROR ON ERROR, user_id UUID PATH '$.userId' ERROR ON ERROR, event_type VARCHAR(64) PATH '$.type' ERROR ON ERROR, payload_data JSONB FORMAT JSON PATH '$.data' ) ) AS jt ON CONFLICT (event_id) DO NOTHING; `; await client.query(boundedQuery, [serialized]); await client.query('COMMIT'); } catch (err) { await client.query('ROLLBACK'); throw err; } finally { client.release(); } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Bounded Memory Allocation and Relational Ingestion Standard

Postgres 17 JSON_TABLE Bounded Memory Allocation and Relational Ingestion Standard defines an industrial-grade behavioral standard for backend engineering. Enforces strict payload chunking, typed column projection boundaries, and session-level memory constraints when utilizing PostgreSQL 17's SQL/JSON JSON_TABLE feature. Prevents unindexed lateral expansions of monolithic JSON documents from exceeding backend work_mem or triggering operating system OOM killer process termination. 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 In-Memory Relational Explosion via Monolithic JSON_TABLE Document Parsing 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 In-Memory Relational Explosion via Monolithic JSON_TABLE Document Parsing	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 chunk input JSON payloads at the application boundary (max 5,000 items or 5MB per batch) before passing them to a JSON_TABLE query.

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.