PostgreSQL 17 JSON_TABLE Memory Projection and Resource Guardrail

PostgreSQL 17 introduces the SQL:2023 standard JSON_TABLE function, which shreds JSON/JSONB payloads into relational row sets in-engine. When processing unbounded nested arrays or large batch payloads in OLTP hot paths, unconstrained path expressions and missing column-width constraints cause severe memory inflation and spill into temporary disk files, exhausting work_mem and crashing backend worker processes. This guardrail establishes bounded memory projections, explicit column sizing, error-handling clauses, and chunked ingestion invariants.

--- description: PostgreSQL 17 JSON_TABLE Memory Projection and Resource Guardrail - PostgreSQL 17 introduces the SQL:2023 standard JSON_TABLE function, which shreds JSON/JSONB payloads into relational row sets in-engine. When processing unbounded nested arrays or large batch payloads in OLTP hot paths, unconstrained path expressions and missing column-width constraints cause severe memory inflation and spill into temporary disk files, exhausting work_mem and crashing backend worker processes. This guardrail establishes bounded memory projections, explicit column sizing, error-handling clauses, and chunked ingestion invariants. globs: src/repositories/**/*.ts, src/db/**/*.ts, src/services/**/*.ts, migrations/**/*.sql, src/queries/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-projection-guardrail" --- # PostgreSQL 17 JSON_TABLE Memory Projection and Resource Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE Projections Without Size Limits or Streaming Chunking ## System Constraints You are a Principal Database Architect and Backend Systems Engineer enforcing memory projection invariants for PostgreSQL 17 JSON_TABLE operations. Mandate strict memory bounds on shredded arrays, eliminate unconstrained text/bytea columns within COLUMNS clauses, require explicit ON EMPTY and ON ERROR behaviors, and reject queries shredding large JSON collections without upfront payload size validation or batching. ## Strict Guidelines - Engineers MUST ALWAYS declare explicit length bounds (e.g., VARCHAR(n), NUMERIC(p,s)) in the COLUMNS definition of JSON_TABLE instead of unbounded TEXT or raw JSONB, preventing unbounded memory buffer allocations during row projection. - Engineers MUST ALWAYS define deterministic 'ON ERROR' and 'ON EMPTY' clauses (e.g., 'ERROR ON ERROR' or 'DEFAULT ... ON ERROR') in JSON_TABLE statements to prevent silent NULL propagations and unhandled memory leaks from malformed JSON parsing. - Engineers MUST NOT execute JSON_TABLE over non-chunked external JSON payloads exceeding 1MB within OLTP transaction contexts; payloads exceeding 1MB MUST be split into micro-batches prior to database ingestion. - Services MUST enforce strict JSON schema pre-validation at the application boundary (e.g., Zod, TypeBox, or JSON Schema) before passing raw payloads into PostgreSQL queries executing JSON_TABLE. - Code SHOULD enforce transaction-scoped 'SET LOCAL work_mem' or safety timeouts when executing analytical or batch shredding using JSON_TABLE to isolate memory consumption from noisy-neighbor connection pools. - Queries MUST NEVER use wildcard JSONPath traversals (e.g., '$.**') inside JSON_TABLE path arguments because non-deterministic node expansion bypasses plan memory cost estimations. ## Bad vs Good Implementations ### Unbounded JSON_TABLE Shredding vs. Bounded, Chunked Execution with Explicit Types #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Shredding an unbounded JSON payload using generic TEXT and unconstrained JSONB path projections inside an OLTP path bypasses memory limits. If the payload contains tens of thousands of nested items, PostgreSQL allocates excessive work_mem or spills temporary files to disk, degrading transaction throughput. ```typescript import { sql } from 'kysely'; import { db } from '../db/client'; interface InboundBatchPayload { rawJsonPayload: string; // Potentially 50MB of nested JSON } export async function ingestEventBatch(payload: InboundBatchPayload): Promise<void> { // CRITICAL ANTI-PATTERN: Wildcard JSON path, unbounded TEXT allocations, // and raw execution without work_mem or transaction memory boundaries. await sql` INSERT INTO audit_events (event_id, tenant_id, payload_data, created_at) SELECT j.event_id, j.tenant_id, j.payload_data, NOW() FROM JSON_TABLE( ${payload.rawJsonPayload}::jsonb, '$.events[*]' COLUMNS ( event_id TEXT PATH '$.id', tenant_id TEXT PATH '$.tenantId', payload_data JSONB PATH '$.data' ) ) AS j; `.execute(db); } ``` #### Production Standard (Correct) > **Standard**: The payload is pre-validated and bound to 512KB. The JSON_TABLE definition uses the standard SQL:2023 'strict $[*]' path mode, bounds all column widths (UUID, VARCHAR(64), VARCHAR(32)), sets 'ERROR ON ERROR' to stop memory leaks upon corrupted data, and restricts memory using 'SET LOCAL work_mem' within an isolated transaction. ```typescript import { sql } from 'kysely'; import { db } from '../db/client'; import { z } from 'zod'; const BATCH_SIZE_LIMIT_BYTES = 512 * 1024; // 512 KB per OLTP transaction const EventItemSchema = z.object({ id: z.string().uuid(), tenantId: z.string().max(64), code: z.string().max(32) }); export async function ingestEventBatchBounded( rawPayload: string, validatedItems: z.infer<typeof EventItemSchema>[] ): Promise<void> { const payloadBytes = Buffer.byteLength(rawPayload, 'utf8'); if (payloadBytes > BATCH_SIZE_LIMIT_BYTES) { throw new Error(`Payload size ${payloadBytes}B exceeds OLTP shredding limit of ${BATCH_SIZE_LIMIT_BYTES}B`); } // Enforce transaction isolation with local work_mem limits and explicit column typings await db.transaction().execute(async (trx) => { await sql`SET LOCAL work_mem = '16MB'`.execute(trx); await sql` INSERT INTO audit_events (event_id, tenant_id, event_code, created_at) SELECT j.event_id, j.tenant_id, j.event_code, CURRENT_TIMESTAMP FROM JSON_TABLE( ${JSON.stringify(validatedItems)}::jsonb, 'strict $[*]' COLUMNS ( event_id UUID PATH '$.id' ERROR ON ERROR, tenant_id VARCHAR(64) PATH '$.tenantId' ERROR ON ERROR, event_code VARCHAR(32) PATH '$.code' DEFAULT 'UNKNOWN' ON EMPTY ERROR ON ERROR ) ) AS j `.execute(trx); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 JSON_TABLE Memory Projection and Resource Guardrail

PostgreSQL 17 JSON_TABLE Memory Projection and Resource Guardrail defines an industrial-grade behavioral standard for backend engineering. PostgreSQL 17 introduces the SQL:2023 standard JSON_TABLE function, which shreds JSON/JSONB payloads into relational row sets in-engine. When processing unbounded nested arrays or large batch payloads in OLTP hot paths, unconstrained path expressions and missing column-width constraints cause severe memory inflation and spill into temporary disk files, exhausting work_mem and crashing backend worker processes. This guardrail establishes bounded memory projections, explicit column sizing, error-handling clauses, and chunked ingestion invariants. 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 Without Size Limits or Streaming Chunking 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 Without Size Limits or Streaming Chunking	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 declare explicit length bounds (e.g., VARCHAR(n), NUMERIC(p,s)) in the COLUMNS definition of JSON_TABLE instead of unbounded TEXT or raw JSONB, preventing unbounded memory buffer allocations during row projection.

Engineers MUST ALWAYS declare explicit length bounds (e.g., VARCHAR(n), NUMERIC(p,s)) in the COLUMNS definition of JSON_TABLE instead of unbounded TEXT or raw JSONB, preventing unbounded memory buffer allocations during row projection.

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.