Postgres 17 JSON_TABLE Memory Projection and WorkMem Ceiling Standard

Establishes deterministic memory ceilings, plan cost controls, and strict column projection schemas on Postgres 17 SQL/JSON JSON_TABLE transformations. Unbounded array expansions and dynamic text extractions without localized work_mem boundaries or nested path limits trigger non-spillable executor buffer bloat, leading to connection termination and backend Out-Of-Memory (OOM) events. This standard enforces stream-friendly projections, localized session memory clamps, and explicit column sizing.

--- description: Postgres 17 JSON_TABLE Memory Projection and WorkMem Ceiling Standard - Establishes deterministic memory ceilings, plan cost controls, and strict column projection schemas on Postgres 17 SQL/JSON JSON_TABLE transformations. Unbounded array expansions and dynamic text extractions without localized work_mem boundaries or nested path limits trigger non-spillable executor buffer bloat, leading to connection termination and backend Out-Of-Memory (OOM) events. This standard enforces stream-friendly projections, localized session memory clamps, and explicit column sizing. globs: src/**/*.repository.ts, src/**/*.service.ts, src/**/*.dao.ts, src/database/**/*.sql, src/migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-projection-standard" --- # Postgres 17 JSON_TABLE Memory Projection and WorkMem Ceiling Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded JSON_TABLE Cartesian Expansions and Default WorkMem Starvation ## System Constraints You are a Principal Engineer enforcing the Postgres 17 JSON_TABLE Memory Projection and WorkMem Ceiling Standard. You must prevent runaway SQL/JSON queries from exhausting backend server memory. Mandate explicit SET LOCAL work_mem limits before executing high-volume JSON_TABLE operations, enforce explicit VARCHAR/NUMERIC column definitions with bounds rather than TEXT or untyped arrays, prohibit nested JSON_TABLE passes on unbounded root paths, and ensure batching mechanisms are present for large document unpacking. ## Strict Guidelines - Engineers MUST ALWAYS enforce localized transactional memory limits (`SET LOCAL work_mem = '64MB'`) prior to complex JSON_TABLE extraction pipelines to prevent OOM cascading across worker pools. - Engineers MUST ALWAYS declare explicit column lengths and concrete numeric types (e.g., `VARCHAR(255)`, `BIGINT`, `NUMERIC(12,2)`) in the `COLUMNS` clause instead of unconstrained `TEXT` or raw `JSONB` passes. - Engineers MUST NOT execute multi-level `NESTED PATH` expressions on arrays of unknown or dynamic cardinality without an outer `LIMIT` or keyset pagination cursor. - Engineers MUST use `ERROR ON ERROR` or explicit `EMPTY ON ERROR` semantics with structured fallbacks to prevent silent query plan degeneration under malformed JSON payloads. - Queries MUST filter records using GIN or btree indexes on the outer relational predicate *before* invoking `JSON_TABLE` to minimize executor memory expansion. - Services MUST NEVER stream entire unindexed table scans into `JSON_TABLE` expressions within real-time transactional read paths. ## Bad vs Good Implementations ### Unbounded JSON_TABLE Parsing vs. Bounded Transactional Memory Projection #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: The query relies on unconstrained TEXT allocations inside the JSON_TABLE definition and combines an unindexed extraction with a nested array path. Under large JSON payloads, Postgres 17 will materialize large internal tuples in memory without localized work_mem limits, quickly exhausting process memory and risking a backend crash. ```typescript import { Pool } from 'pg'; export async function extractEventPayloads(pool: Pool, batchId: string) { // ANTI-PATTERN: Default work_mem inherited, unconstrained TEXT columns, and unbounded multi-level NESTED PATH // This causes Postgres backend memory to spike proportionally to the JSON document array size, // potentially triggering an OOM kill under concurrent query load. const query = ` SELECT jt.* FROM raw_ingest_events r, JSON_TABLE( r.payload, '$.events[*]' COLUMNS ( event_id TEXT PATH '$.id', event_type TEXT PATH '$.type', payload TEXT FORMAT JSON PATH '$', NESTED PATH '$.attributes[*]' COLUMNS ( attr_key TEXT PATH '$.key', attr_val TEXT PATH '$.value' ) ) ) AS jt WHERE r.batch_id = $1; `; const { rows } = await pool.query(query, [batchId]); return rows; } ``` #### Production Standard (Correct) > **Standard**: The refactored code wraps the query in a transaction with localized memory guardrails (`SET LOCAL work_mem = '32MB'`), strictly constraints projected column widths (VARCHAR instead of unbound TEXT), filters input rows before applying `JSON_TABLE`, and uses precise error handling clauses to prevent uncontrolled memory spikes. ```typescript import { Pool, PoolClient } from 'pg'; export interface IngestedAttribute { eventId: string; eventType: string; attrKey: string; attrVal: string; } export async function extractEventPayloads(pool: Pool, batchId: string, limit: number = 5000): Promise<IngestedAttribute[]> { const client: PoolClient = await pool.connect(); try { await client.query('BEGIN READ ONLY'); // Enforce strict local work_mem ceiling for this session/transaction await client.query("SET LOCAL work_mem = '32MB'"); // Prevent runaway planning with statement timeout await client.query("SET LOCAL statement_timeout = '5000ms'"); // PRODUCTION FIX: // 1. Strict typed columns with bounded lengths // 2. Pre-filtering via bounded primary batch index and LIMIT // 3. Explicit error handling semantics (EMPTY ON ERROR) const query = ` SELECT jt.event_id AS "eventId", jt.event_type AS "eventType", jt.attr_key AS "attrKey", jt.attr_val AS "attrVal" FROM ( SELECT payload FROM raw_ingest_events WHERE batch_id = $1 LIMIT $2 ) r, JSON_TABLE( r.payload, '$.events[*]' COLUMNS ( event_id VARCHAR(64) PATH '$.id' ERROR ON ERROR, event_type VARCHAR(100) PATH '$.type' DEFAULT 'unknown' ON EMPTY, NESTED PATH '$.attributes[*]' COLUMNS ( attr_key VARCHAR(128) PATH '$.key', attr_val VARCHAR(1024) PATH '$.value' ) ) ) AS jt; `; const { rows } = await client.query<IngestedAttribute>(query, [batchId, limit]); await client.query('COMMIT'); return rows; } catch (err) { await client.query('ROLLBACK'); throw err; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Memory Projection and WorkMem Ceiling Standard

Postgres 17 JSON_TABLE Memory Projection and WorkMem Ceiling Standard defines an industrial-grade behavioral standard for backend engineering. Establishes deterministic memory ceilings, plan cost controls, and strict column projection schemas on Postgres 17 SQL/JSON JSON_TABLE transformations. Unbounded array expansions and dynamic text extractions without localized work_mem boundaries or nested path limits trigger non-spillable executor buffer bloat, leading to connection termination and backend Out-Of-Memory (OOM) events. This standard enforces stream-friendly projections, localized session memory clamps, and explicit column sizing. 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 Cartesian Expansions and Default WorkMem Starvation 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 Cartesian Expansions and Default WorkMem Starvation	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 localized transactional memory limits (`SET LOCAL work_mem = '64MB'`) prior to complex JSON_TABLE extraction pipelines to prevent OOM cascading across worker pools.

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.