Postgres 17 JSON_TABLE Memory Allocation and Streaming Guardrails

--- description: Postgres 17 JSON_TABLE Memory Allocation and Streaming Guardrails - Enforces strict memory boundaries and deterministic path evaluations for Postgres 17 SQL/JSON JSON_TABLE projections. Unbounded wildcard path traversals and unconstrained joins against high-cardinality JSON collections can exhaust backend private work_mem and trigger OOM terminations. All queries utilizing JSON_TABLE must enforce strict path specifications, deterministic error-handling clauses, and localized work_mem ceilings. globs: src/**/*.ts, src/db/**/*.ts, src/repositories/**/*.ts, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-guardrails" --- # Postgres 17 JSON_TABLE Memory Allocation and Streaming Guardrails > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE Path Projections Inducing Backend OOM via Unconstrained Tuple Materialization ## System Constraints You are a Principal Database Architect enforcing Postgres 17 SQL/JSON streaming memory constraints. Mandate strict limits on work_mem allocation, eliminate recursive or unbounded path expressions in JSON_TABLE projections, and require defensive column projection typing and local memory boundaries. ## Strict Guidelines - Engineers MUST ALWAYS enforce bounded, explicit JSONPath expressions (e.g., 'lax $.items[*]') and NEVER use deep wildcard recursion (e.g., '$.**') inside JSON_TABLE path arguments. - Engineers MUST NOT execute JSON_TABLE projections across unbounded batch sizes without setting an explicit statement-level or transaction-level work_mem cap using 'SET LOCAL work_mem'. - Code SHOULD define explicit 'ON ERROR' and 'ON EMPTY' clauses (such as 'EMPTY ON ERROR' or 'ERROR ON ERROR') for all JSON_TABLE columns to avoid silent parsing state fallback allocations. - Services MUST ensure that outer queries scanning tables with large JSONB payloads apply filtering via GIN or expression indexes prior to invoking JSON_TABLE expansion. - Components NEVER expose unvalidated user-defined JSONPath inputs directly to JSON_TABLE expressions, preventing path-injection denial-of-service. ## Bad vs Good Implementations ### Postgres 17 JSON_TABLE Query Execution with Streamed Memory Guardrails #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Recursive path queries ('$.**') force Postgres to parse and retain every sub-object in memory. For large JSON documents or large result sets, this causes exponential tuple creation, exhausts backend work_mem, forces heavy temp-disk spills, and can trigger the OS OOM killer on the database instance. ```typescript import { Pool } from 'pg'; export async function extractEventPayloads(pool: Pool, tenantId: string) { // BAD: Wildcard recursive path $.** traverses deeply nested JSON structures, // materializing unbounded intermediate tuples into backend work_mem. // Lacks local work_mem limits and table filters, risking worker process OOM. const query = ` SELECT jt.* FROM raw_events e, JSON_TABLE( e.payload, 'lax $.**' COLUMNS ( item_id text PATH '$.id', metric numeric PATH '$.value' ) ) AS jt WHERE e.tenant_id = $1; `; const { rows } = await pool.query(query, [tenantId]); return rows; } ``` #### Production Standard (Correct) > **Standard**: The revised implementation constrains work_mem to 32MB locally, uses statement timeouts, restricts driving rows via index-backed pagination, uses a concrete array iteration path ('$.metrics[*]'), and specifies explicit error/empty fallback behaviors to guarantee predictable memory consumption. ```typescript import { Pool } from 'pg'; export async function extractEventPayloads(pool: Pool, tenantId: string, limit: number = 500) { const client = await pool.connect(); try { await client.query('BEGIN'); // Constrain private work_mem allocation for the current transaction await client.query("SET LOCAL work_mem = '32MB'"); await client.query("SET LOCAL statement_timeout = '5000ms'"); // GOOD: Target a deterministic, bounded array path; apply indexed column filter; // declare error fallback modes; enforce strict pagination on the driving relation. const query = ` SELECT e.id AS event_id, jt.item_id, jt.metric FROM ( SELECT id, payload FROM raw_events WHERE tenant_id = $1 AND processed_at IS NULL ORDER BY id ASC LIMIT $2 ) e, JSON_TABLE( e.payload, 'lax $.metrics[*]' COLUMNS ( item_id text PATH '$.id' ERROR ON ERROR, metric numeric PATH '$.value' DEFAULT '0' ON EMPTY ) ) AS jt; `; const { rows } = await client.query(query, [tenantId, limit]); await client.query('COMMIT'); return rows; } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Memory Allocation and Streaming Guardrails

Postgres 17 JSON_TABLE Memory Allocation and Streaming Guardrails defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory boundaries and deterministic path evaluations for Postgres 17 SQL/JSON JSON_TABLE projections. Unbounded wildcard path traversals and unconstrained joins against high-cardinality JSON collections can exhaust backend private work_mem and trigger OOM terminations. All queries utilizing JSON_TABLE must enforce strict path specifications, deterministic error-handling clauses, and localized work_mem ceilings. 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 Path Projections Inducing Backend OOM via Unconstrained Tuple 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 Path Projections Inducing Backend OOM via Unconstrained Tuple 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 enforce bounded, explicit JSONPath expressions (e.g., 'lax $.items[*]') and NEVER use deep wildcard recursion (e.g., '$.**') inside JSON_TABLE path arguments.

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.