Postgres 17 JSON_TABLE Projection Memory Budget and Allocation Bounding

--- description: Postgres 17 JSON_TABLE Projection Memory Budget and Allocation Bounding - Unbounded relational projections using Postgres 17 SQL/JSON JSON_TABLE across arbitrary JSONB payloads risk rapid backend worker memory exhaustion and Linux OOM killer termination. This standard enforces deterministic projection buffer bounding through JSON path array slicing, strict output column width limits, transaction-scoped work_mem ceilings, and keyset-paginated source batches. globs: src/**/*.ts, src/**/*.sql, src/repositories/**/*.ts, src/database/**/*.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-json-862" --- # Postgres 17 JSON_TABLE Projection Memory Budget and Allocation Bounding > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded Nested JSON_TABLE Projections Triggering Postgres Worker OOM Termination ## System Constraints You are a Principal Database Architect and Performance Engineer enforcing strict memory guardrails on Postgres 17 SQL/JSON operations. Your mandate is to prevent worker process OOM crashes by ensuring every JSON_TABLE projection is bounded in source row count, array slice depth, output column widths, and session-level working memory. ## Strict Guidelines - Engineers MUST ALWAYS enforce explicit array path slices (e.g., 'strict $.items[0 to 499]') or client-level batch size constraints instead of evaluating open-ended wildcards ('$.items[*]') on unverified JSONB payloads. - Engineers MUST NOT project nested object payloads into unbounded TEXT or unrestricted JSON/JSONB columns within JSON_TABLE definitions without explicit maximum length specifications (e.g., VARCHAR(255)). - Code MUST set a defensive transaction-local memory ceiling via `SET LOCAL work_mem = '64MB'` or statement timeouts when unpacking relational sets exceeding 1,000 JSON elements. - Services MUST unpack large JSON documents using keyset-paginated driver queries or streaming cursors rather than projecting an entire table's JSONB column via LATERAL JSON_TABLE in a single statement. - Components NEVER use `ERROR ON ERROR` clauses in JSON_TABLE columns when parsing untrusted external JSON structures; engineers MUST use `NULL ON ERROR` or `EMPTY ON ERROR` to avoid aborting long-running batch transactions. ## Bad vs Good Implementations ### Unbounded vs Bounded JSON_TABLE Relational Projection #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Querying all records with '$.records[*]' without limiting the source rows or JSON path indexes forces Postgres 17 to allocate indeterminate projection buffers in the worker executor. Furthermore, unbounded TEXT formats and ERROR ON ERROR will exhaust memory or abort queries on malformed sub-documents. ```typescript import { sql } from 'kysely'; import type { Kysely } from 'kysely'; import type { Database } from './types'; export async function extractEventLogEntries(db: Kysely<Database>, tenantId: string) { // ANTI-PATTERN: Wildcard path over unbounded JSONB payloads with unbounded TEXT projections // and no transaction-local memory bounds. High-volume rows can crash backend workers via OOM. return await sql` SELECT events.id AS source_event_id, jt.* FROM raw_event_logs events, JSON_TABLE( events.payload, '$.records[*]' COLUMNS ( item_id text PATH '$.id', action text PATH '$.action', metadata text FORMAT JSON PATH '$.details' ERROR ON ERROR ) ) AS jt WHERE events.tenant_id = ${tenantId} `.execute(db); } ``` #### Production Standard (Correct) > **Standard**: Sets strict session-level memory boundaries (32MB), keyset-paginates outer table rows, bounds the JSON path slice to a maximum of 100 entries ('lax $.records[0 to 99]'), assigns deterministic column widths, and handles JSON parse failures gracefully with NULL ON ERROR. ```typescript import { sql } from 'kysely'; import type { Kysely } from 'kysely'; import type { Database } from './types'; export async function extractEventLogEntries( db: Kysely<Database>, tenantId: string, lastSeenId: string, batchSize: number = 250 ) { // PRODUCTION STANDARD: Keyset-paginated input, array slice upper-bounding, // bounded VARCHAR column widths, safe error handling, and transaction-local work_mem. return await db.transaction().execute(async (trx) => { await sql`SET LOCAL work_mem = '32MB'`.execute(trx); await sql`SET LOCAL statement_timeout = '5000'`.execute(trx); return await sql` SELECT events.id AS source_event_id, jt.item_id, jt.action, jt.metadata FROM ( SELECT id, payload FROM raw_event_logs WHERE tenant_id = ${tenantId} AND id > ${lastSeenId} ORDER BY id ASC LIMIT ${batchSize} ) events, JSON_TABLE( events.payload, 'lax $.records[0 to 99]' COLUMNS ( item_id VARCHAR(64) PATH '$.id', action VARCHAR(32) PATH '$.action', metadata VARCHAR(2048) FORMAT JSON PATH '$.details' NULL ON ERROR ) ) AS jt `.execute(trx); }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Projection Memory Budget and Allocation Bounding

Postgres 17 JSON_TABLE Projection Memory Budget and Allocation Bounding defines an industrial-grade behavioral standard for backend engineering. Unbounded relational projections using Postgres 17 SQL/JSON JSON_TABLE across arbitrary JSONB payloads risk rapid backend worker memory exhaustion and Linux OOM killer termination. This standard enforces deterministic projection buffer bounding through JSON path array slicing, strict output column width limits, transaction-scoped work_mem ceilings, and keyset-paginated source batches. 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 Nested JSON_TABLE Projections Triggering Postgres Worker OOM Termination 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 Nested JSON_TABLE Projections Triggering Postgres Worker OOM Termination	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 array path slices (e.g., 'strict $.items[0 to 499]') or client-level batch size constraints instead of evaluating open-ended wildcards ('$.items[*]') on unverified JSONB payloads.

Engineers MUST ALWAYS enforce explicit array path slices (e.g., 'strict $.items[0 to 499]') or client-level batch size constraints instead of evaluating open-ended wildcards ('$.items[*]') on unverified JSONB payloads.

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.