Postgres 17 SQL:2023 JSON_TABLE Streaming Projection & Memory Bounds

--- description: Postgres 17 SQL:2023 JSON_TABLE Streaming Projection & Memory Bounds - Enforces bounded memory projection and selective path materialization for Postgres 17 SQL:2023 JSON_TABLE queries. Prevents backend out-of-memory (OOM) failures and work_mem thrashing by mandating pre-filtered indexed scans, narrow column typing, and bounded sibling nested paths over large JSONB payloads. globs: src/**/*.sql, src/**/*.ts, src/**/*.pg.ts, src/db/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-guardrail-postgres-17-sql--916" --- # Postgres 17 SQL:2023 JSON_TABLE Streaming Projection & Memory Bounds > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE Lateral Joins Triggering Memory Exhaustion via Deep Nested Path Materialization ## System Constraints You are a Principal Database Architect enforcing Postgres 17 SQL:2023 JSON_TABLE execution safety. Mandate strict memory projection boundaries, indexed pre-filtering, and explicit column typing to avoid work_mem exhaustion and executor OOM on massive JSONB payloads. ## Strict Guidelines - Engineers MUST ALWAYS filter rows via GIN indexes or indexed scalar relational columns BEFORE passing JSONB documents into JSON_TABLE. - Engineers MUST NOT project unbounded TEXT or raw JSONB columns in JSON_TABLE definitions when narrow data types (e.g., INT, BIGINT, TIMESTAMPTZ, VARCHAR(n)) are sufficient. - Code MUST explicitly specify error behavior using 'ERROR ON ERROR' or 'EMPTY ON ERROR' on JSON_TABLE column clauses to prevent hidden runtime memory thrashing or silent truncation. - Queries MUST NOT define cross-product sibling NESTED PATH clauses on high-cardinality arrays without explicit parent-child key joins or independent subquery isolation. - Services MUST process large batches of JSONB rows using keyset pagination or server-side cursors rather than materializing millions of projected JSON_TABLE rows in a single execution plan. ## Bad vs Good Implementations ### Postgres 17 JSON_TABLE Transformation in TypeScript Data Layer #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The query lacks a WHERE clause to filter out unindexed rows, processes full unconstrained JSONB payloads, uses unbounded TEXT columns, and evaluates cross-product sibling NESTED PATH clauses, materializing massive Cartesian products in Postgres work_mem. ```typescript import { sql } from 'slonik'; // ANTI-PATTERN: Scans entire table, detoasts huge JSONB blobs, performs // unconstrained sibling NESTED PATH expansion with unbounded TEXT columns, // causing plan node memory explosion and query backend termination. export async function getAuditEvents(pool: any, orgId: string) { return pool.query(sql.unsafe` SELECT jt.* FROM audit_logs a, JSON_TABLE( a.payload, '$.events[*]' COLUMNS ( event_id TEXT PATH '$.id', event_type TEXT PATH '$.type', raw_data TEXT PATH '$.details', NESTED PATH '$.tags[*]' COLUMNS (tag_name TEXT PATH '$'), NESTED PATH '$.mutations[*]' COLUMNS (delta_val TEXT PATH '$.value') ) ) AS jt; `); } ``` #### Production Standard (Correct) > **Standard**: The query pre-filters rows using indexed columns (`org_id`, `id`) and a JSON path containment check (`@?`), limits row streaming with keyset pagination, enforces narrow data types (`BIGINT`, `VARCHAR(64)`), and eliminates cartesian sibling paths. ```typescript import { sql } from 'slonik'; // PRODUCTION INVARIANT: Pre-filters via indexed tenant and cursor columns, // projects strictly typed scalar columns, handles errors explicitly, and // avoids cartesian explosion across nested paths. export async function getAuditEvents( pool: any, orgId: string, lastLogId: number, limit: number ) { return pool.query(sql.unsafe` SELECT a.id AS log_id, jt.event_id, jt.event_type, jt.created_at FROM audit_logs a CROSS JOIN JSON_TABLE( a.payload, '$.events[*]' ERROR ON ERROR COLUMNS ( event_id BIGINT PATH '$.id', event_type VARCHAR(64) PATH '$.type', created_at TIMESTAMPTZ PATH '$.timestamp' ) ) AS jt WHERE a.org_id = ${orgId} AND a.id > ${lastLogId} AND a.payload @? '$.events[*].id' ORDER BY a.id ASC LIMIT ${limit}; `); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 SQL:2023 JSON_TABLE Streaming Projection & Memory Bounds

Postgres 17 SQL:2023 JSON_TABLE Streaming Projection & Memory Bounds defines an industrial-grade behavioral standard for backend engineering. Enforces bounded memory projection and selective path materialization for Postgres 17 SQL:2023 JSON_TABLE queries. Prevents backend out-of-memory (OOM) failures and work_mem thrashing by mandating pre-filtered indexed scans, narrow column typing, and bounded sibling nested paths over large JSONB payloads. 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 Lateral Joins Triggering Memory Exhaustion via Deep Nested Path Materialization 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 Lateral Joins Triggering Memory Exhaustion via Deep Nested Path Materialization	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 filter rows via GIN indexes or indexed scalar relational columns BEFORE passing JSONB documents into JSON_TABLE.

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.