Postgres 17 JSON_TABLE Memory Projection & Bounded Expansion Standard

--- description: Postgres 17 JSON_TABLE Memory Projection & Bounded Expansion Standard - Mandates strict memory guardrails, column dimension bounds, and traversal depth limits when using Postgres 17 JSON_TABLE row projection functions. Prohibits unbounded NESTED PATH expansions over high-volume hierarchical JSON payloads that cause catastrophic backend work_mem exhaustion and OOM kills. globs: src/**/*.ts, src/**/*.sql, src/repositories/**/*.ts, src/services/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-guardrail" --- # Postgres 17 JSON_TABLE Memory Projection & Bounded Expansion Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Hierarchical JSON_TABLE Expansion Inducing Postgres Backend Out-Of-Memory Crashes ## System Constraints You are a Principal Backend and Database Reliability Engineer enforcing the Postgres 17 JSON_TABLE Memory Projection Standard. Mandate explicit column sizing, cardinality limits on NESTED PATH expressions, payload size assertions, and strict ON ERROR/EMPTY clauses whenever querying hierarchical JSON payloads with SQL/JSON functions. ## Strict Guidelines - Engineers MUST ALWAYS enforce explicit size bounds on projected columns (e.g., VARCHAR(255), INT) rather than unbounded TEXT within JSON_TABLE COLUMNS clauses. - Engineers MUST ALWAYS configure explicit ON ERROR and ON EMPTY handling clauses (e.g., EMPTY ON ERROR, NULL ON EMPTY) to avoid uncontrolled fallback memory accumulation during deserialization failures. - Engineers MUST NOT execute multi-level NESTED PATH expansions on untrusted or unbounded JSON arrays without strict JSONPath array slicing or defensive payload size limits prior to DB ingestion. - Services MUST batch or chunk JSON payloads exceeding 2MB at the application layer instead of streaming monolithic multi-megabyte JSON blobs directly into a single JSON_TABLE projection invocation. - Queries MUST NOT cross-join JSON_TABLE results with parent tables without selective, indexed predicate pushdowns that constrain the Cartesian projection cardinality. - Code SHOULD enforce transaction-level statement_timeout and constrained work_mem boundaries prior to executing complex multi-path JSON_TABLE projections. ## Bad vs Good Implementations ### Postgres 17 JSON_TABLE Dynamic Relational Row Projection #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Parsing unbounded hierarchical JSON with multiple NESTED PATH levels using raw TEXT column projections forces the Postgres backend query engine to allocate unbounded per-tuple memory contexts. A single large payload will exceed work_mem and trigger an unrecoverable server-process OOM kill. ```typescript import { sql } from 'kysely'; import { db } from '../database'; interface IngestPayload { ordersJson: string; } export async function projectOrderRecordsUnbounded({ ordersJson }: IngestPayload) { // Anti-pattern: Unbounded TEXT types, arbitrary array size, no error fallbacks, and multi-tier NESTED PATH return await sql` SELECT jt.* FROM JSON_TABLE( ${ordersJson}::jsonb, '$.orders[*]' COLUMNS ( order_id TEXT PATH '$.id', customer_name TEXT PATH '$.customer.name', NESTED PATH '$.items[*]' COLUMNS ( item_id TEXT PATH '$.id', quantity TEXT PATH '$.qty', price TEXT PATH '$.price', NESTED PATH '$.modifiers[*]' COLUMNS ( modifier_name TEXT PATH '$.name' ) ) ) ) AS jt; `.execute(db); } ``` #### Production Standard (Correct) > **Standard**: Imposes an application-tier size threshold (1MB), sets a local statement timeout, bounds JSONPath array traversal using slicing ('[0 to 500]', '[0 to 50]'), specifies explicit sized column domains (UUID, VARCHAR(100), INTEGER), replaces recursive nesting with array projection, and guards deserialization with explicit EMPTY ON ERROR semantics. ```typescript import { sql } from 'kysely'; import { db } from '../database'; interface IngestPayload { ordersJson: string; } const MAX_PAYLOAD_BYTES = 1024 * 1024; // 1MB safety envelope export async function projectOrderRecordsBounded({ ordersJson }: IngestPayload) { if (Buffer.byteLength(ordersJson, 'utf8') > MAX_PAYLOAD_BYTES) { throw new Error('Payload size exceeds 1MB threshold; must be ingested via chunked stream'); } // Guardrail: Enforce bounded types, JSONPath slice constraints, and explicit error clauses return await db.transaction().execute(async (trx) => { await sql`SET LOCAL statement_timeout = '3000ms'`.execute(trx); return await sql` SELECT jt.* FROM JSON_TABLE( ${ordersJson}::jsonb, '$.orders[0 to 500]' EMPTY ON ERROR ON EMPTY COLUMNS ( order_id UUID PATH '$.id' ERROR ON ERROR, customer_name VARCHAR(100) PATH '$.customer.name' NULL ON EMPTY, NESTED PATH '$.items[0 to 50]' COLUMNS ( item_id UUID PATH '$.id', quantity INTEGER PATH '$.qty' DEFAULT '0' ON EMPTY, price_cents BIGINT PATH '$.price_cents' DEFAULT '0' ON EMPTY, modifier_names VARCHAR(50)[] PATH '$.modifiers[*].name' ) ) ) AS jt; `.execute(trx); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Memory Projection & Bounded Expansion Standard

Postgres 17 JSON_TABLE Memory Projection & Bounded Expansion Standard defines an industrial-grade behavioral standard for backend engineering. Mandates strict memory guardrails, column dimension bounds, and traversal depth limits when using Postgres 17 JSON_TABLE row projection functions. Prohibits unbounded NESTED PATH expansions over high-volume hierarchical JSON payloads that cause catastrophic backend work_mem exhaustion and OOM kills. 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 Hierarchical JSON_TABLE Expansion Inducing Postgres Backend Out-Of-Memory Crashes 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 Hierarchical JSON_TABLE Expansion Inducing Postgres Backend Out-Of-Memory Crashes	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 size bounds on projected columns (e.g., VARCHAR(255), INT) rather than unbounded TEXT within JSON_TABLE COLUMNS clauses.

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.