Postgres 17 JSON_TABLE Nested Projection Memory Bounds Guardrail

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded Multi-Level NESTED PATH Projections Triggering Backend Worker OOM Crashes) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

Postgres 17 JSON_TABLE Nested Projection Memory Bounds Guardrail

--- description: Postgres 17 JSON_TABLE Nested Projection Memory Bounds Guardrail - Enforces strict batching, Cartesian isolation, and memory bounding on Postgres 17 JSON_TABLE queries during high-throughput nested JSON ingestion. Uncoordinated multi-level NESTED PATH clauses expand intermediate tuple slots inside executor memory contexts, causing backend memory exhaustion and worker process termination. Application code must enforce ingestion chunk sizes, bounded path projections, and transactional work_mem thresholds. 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/postgres17-json-table-nested-projection-memory-bounds" --- # Postgres 17 JSON_TABLE Nested Projection Memory Bounds Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Multi-Level NESTED PATH Projections Triggering Backend Worker OOM Crashes ## System Constraints You are a Principal Backend Architect enforcing strict Postgres 17 JSON_TABLE operational guardrails. You must prevent memory exhaustion from unbounded intermediate projection buffers during nested JSON unnesting. Mandate chunked ingest sizes, eliminate cross-product multi-nested path unnesting in single statements, enforce defensive JSON_TABLE column constraints, and protect executor work_mem. ## Strict Guidelines - Engineers MUST ALWAYS chunk incoming nested JSON payloads into bounded micro-batches (maximum 500-1,000 array elements per execute call) before executing JSON_TABLE projections. - Engineers MUST NOT execute sibling NESTED PATH clauses at the same projection level without parent foreign key joins, as sibling paths produce unbounded Cartesian products in Postgres executor memory contexts. - Code SHOULD explicitly specify column data types and error clauses (e.g., ERROR ON ERROR or EMPTY ON ERROR) to prevent silent memory-leaking string conversions during type coercion inside JSON_TABLE. - Services MUST configure a deterministic statement_timeout and evaluate work_mem constraints before running complex JSON_TABLE operations on untrusted external JSON payloads. - Components NEVER execute raw, unsanitized JSON documents greater than 1MB directly through deep multi-level NESTED PATH projections within an open interactive user transaction. ## Bad vs Good Implementations ### Ingesting Deeply Nested JSON via Postgres 17 JSON_TABLE #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Passing an arbitrary-size JSON payload with sibling NESTED PATH expressions (items and metadata.attributes) forces Postgres 17 to evaluate a Cartesian product across the nested arrays for every parent element inside the executor's memory context. In high-throughput ingest, this easily triggers Linux OOM killer or exceeds memory limits, crashing the Postgres worker process. ```typescript import { Pool } from 'pg'; interface IngestPayload { rawJsonData: string; } export async function ingestOrders(pool: Pool, payload: IngestPayload): Promise<void> { // Anti-Pattern: Executing unbounded multi-megabyte JSON payload with sibling/multi-nested // NESTED PATH clauses in a single statement. This causes Postgres 17 to allocate // exponential intermediate executor tuple buffers, risking OOM-kill of the backend worker. await pool.query(` INSERT INTO order_items (order_id, customer_id, item_sku, item_qty, attribute_name, attribute_val) SELECT jt.order_id, jt.customer_id, jt.item_sku, jt.item_qty, jt.attr_name, jt.attr_val FROM JSON_TABLE($1::jsonb, '$[*]' COLUMNS ( order_id BIGINT PATH '$.id', customer_id BIGINT PATH '$.customer_id', NESTED PATH '$.items[*]' COLUMNS ( item_sku TEXT PATH '$.sku', item_qty INT PATH '$.quantity' ), NESTED PATH '$.metadata.attributes[*]' COLUMNS ( attr_name TEXT PATH '$.name', attr_val TEXT PATH '$.value' ) ) ) AS jt; `, [payload.rawJsonData]); } ``` #### Production Standard (Correct) > **Standard**: The good pattern bounds ingestion size with MAX_BATCH_SIZE, eliminates the sibling Cartesian explosion by projecting a single linear hierarchy, applies explicit types with ERROR ON ERROR constraints to prevent intermediate projection corruption, and sets explicit work_mem and statement_timeout guards. ```typescript import { Pool, PoolClient } from 'pg'; interface OrderBatch { id: number; customerId: number; items: Array<{ sku: string; quantity: number }>; } const MAX_BATCH_SIZE = 500; export async function ingestOrdersSafe( pool: Pool, orders: OrderBatch[] ): Promise<void> { if (orders.length === 0) return; // Guardrail: Process in bounded micro-batches to restrict intermediate tuple buffer footprint for (let i = 0; i < orders.length; i += MAX_BATCH_SIZE) { const chunk = orders.slice(i, i + MAX_BATCH_SIZE); const client: PoolClient = await pool.connect(); try { await client.query('BEGIN'); // Constrain statement execution context await client.query('SET LOCAL statement_timeout = 5000'); await client.query('SET LOCAL work_mem = \'64MB\''); // Safe projection: Single linear NESTED PATH avoids Cartesian explosion. // Explicit error handling guarantees no silent memory retention on malformed JSON paths. await client.query(` INSERT INTO order_items (order_id, customer_id, item_sku, item_qty) SELECT jt.order_id, jt.customer_id, jt.item_sku, jt.item_qty FROM JSON_TABLE( $1::jsonb, '$[*]' COLUMNS ( order_id BIGINT PATH '$.id' ERROR ON ERROR, customer_id BIGINT PATH '$.customerId' ERROR ON ERROR, NESTED PATH '$.items[*]' COLUMNS ( item_sku VARCHAR(64) PATH '$.sku' ERROR ON ERROR, item_qty INTEGER PATH '$.quantity' ERROR ON ERROR ) ) ) AS jt; `, [JSON.stringify(chunk)]); 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 Nested Projection Memory Bounds Guardrail

Postgres 17 JSON_TABLE Nested Projection Memory Bounds Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces strict batching, Cartesian isolation, and memory bounding on Postgres 17 JSON_TABLE queries during high-throughput nested JSON ingestion. Uncoordinated multi-level NESTED PATH clauses expand intermediate tuple slots inside executor memory contexts, causing backend memory exhaustion and worker process termination. Application code must enforce ingestion chunk sizes, bounded path projections, and transactional work_mem thresholds. 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 Multi-Level NESTED PATH Projections Triggering Backend Worker OOM 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 Multi-Level NESTED PATH Projections Triggering Backend Worker OOM 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 chunk incoming nested JSON payloads into bounded micro-batches (maximum 500-1,000 array elements per execute call) before executing JSON_TABLE projections.

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.