Deterministic Memory Quotas and Disk-Spill Prevention for Postgres 17 JSON_TABLE

Q: How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Monolithic In-Memory JSON Deserialization and Unbounded Projections Inducing Temp Disk Spilling) and prescribes verified positive examples. Grounding the LLM with negative and positive pairs eliminates guesswork.

Q: Which AI coding environments are supported?

This specification is multi-platform compatible: Cursor (.cursor/rules/*.mdc & .cursorrules), Anthropic Claude Code (CLAUDE.md), Windsurf (.windsurfrules), Codex / Cline (AGENTS.md), and GitHub Copilot (.github/copilot-instructions.md).

Q: Can I combine this rule with other technology stacks?

Yes. Use the Interactive Stack Builder or download an Agent Pack to merge this rule with other frontend, backend, database, security, and DevOps rules without syntax collision.

--- description: Deterministic Memory Quotas and Disk-Spill Prevention for Postgres 17 JSON_TABLE - Enforces deterministic work_mem allocation and chunked streaming execution for Postgres 17 JSON_TABLE projections. Prevents uncontrolled temp file disk spilling and worker process OOM terminations when transforming high-throughput semi-structured JSON payloads into relational tuples. globs: src/**/*.ts, src/**/*.pg.ts, src/repositories/**/*.ts, src/db/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-memory-bounds" --- # Deterministic Memory Quotas and Disk-Spill Prevention for Postgres 17 JSON_TABLE > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Monolithic In-Memory JSON Deserialization and Unbounded Projections Inducing Temp Disk Spilling ## System Constraints You are a Principal Software Architect enforcing deterministic resource usage in Postgres 17 SQL/JSON pipelines. You must mandate bounded memory ingestion, strict type sizing in JSON_TABLE COLUMNS clauses, transactional SET LOCAL work_mem configurations, and chunked cursor-based ingestion to prevent temporary table disk spilling and backend connection OOM crashes. ## Strict Guidelines - Engineers MUST ALWAYS bound work_mem locally within an explicit transaction block using SET LOCAL work_mem calibrated to batch throughput (e.g., 32MB-64MB) before running JSON_TABLE queries. - Engineers MUST NOT pass unbounded multi-megabyte JSON/JSONB payloads into a single monolithic JSON_TABLE call; ingestion payloads MUST be chunked client-side to <= 4MB or <= 5,000 documents per batch. - Code SHOULD specify explicit scalar types with strict length boundaries (e.g., VARCHAR(64), INT, NUMERIC(12,2)) in the JSON_TABLE COLUMNS clause instead of defaulting to generic TEXT or unconstrained JSONB. - Services MUST verify zero temporary file generation by asserting that pg_stat_database.temp_files or EXPLAIN (ANALYZE, BUFFERS) shows 0 bytes written to disk during transformations. - Components NEVER execute Cartesian product expansions across multiple nested arrays inside a single JSON_TABLE path without explicit parent-child join constraints. ## Bad vs Good Implementations ### Postgres 17 JSON_TABLE Batch Ingestion #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Passing an unbounded JSON document to JSON_TABLE under default work_mem leads to memory bloat (JSON AST representation is 4-10x larger than raw string). Unconstrained text column definitions prevent planner optimization, forcing temp disk spills and causing latency spikes across shared buffer pools. ```typescript import { Pool } from 'pg'; export async function ingestTelemetryBad(pool: Pool, payloads: unknown[]): Promise<void> { // Anti-Pattern: Unbounded array serialized into a single monolithic JSON parameter, // default work_mem (typically 4MB), unbounded text types, and no temp disk spill monitoring. const query = ` INSERT INTO metric_records (device_id, reading, recorded_at) SELECT jt.device_id, jt.reading, jt.recorded_at FROM JSON_TABLE( $1::jsonb, '$[*]' COLUMNS ( device_id text PATH '$.device_id', reading text PATH '$.reading', recorded_at text PATH '$.timestamp' ) ) AS jt; `; // Pushing hundreds of thousands of events causes massive memory spikes // and forces Postgres to spill sort/hash operations into temporary disk files. await pool.query(query, [JSON.stringify(payloads)]); } ``` #### Production Standard (Correct) > **Standard**: Payloads are chunked deterministically client-side into 2,500 items, keeping the parsed JSON structure well within predictable boundaries. SET LOCAL work_mem provides adequate working memory for relational projection without affecting global sessions. Explicit data types (VARCHAR(64), NUMERIC(10,4), TIMESTAMPTZ) and error handlers (ERROR ON ERROR, DEFAULT ON EMPTY) prevent Cartesian expansion and temp file disk spills. ```typescript import { Pool, PoolClient } from 'pg'; const CHUNK_SIZE = 2_500; const BOUNDED_WORK_MEM = '32MB'; export async function ingestTelemetryGood(pool: Pool, payloads: Array<Record<string, unknown>>): Promise<void> { const client: PoolClient = await pool.connect(); try { for (let i = 0; i < payloads.length; i += CHUNK_SIZE) { const chunk = payloads.slice(i, i + CHUNK_SIZE); const jsonPayload = JSON.stringify(chunk); await client.query('BEGIN'); // Enforce deterministic execution context for the specific batch size await client.query(`SET LOCAL work_mem = '${BOUNDED_WORK_MEM}'`); const query = ` INSERT INTO metric_records (device_id, reading, recorded_at) SELECT jt.device_id, jt.reading, jt.recorded_at FROM JSON_TABLE( $1::jsonb, '$[*]' COLUMNS ( device_id VARCHAR(64) PATH '$.device_id' ERROR ON ERROR, reading NUMERIC(10, 4) PATH '$.reading' DEFAULT 0 ON EMPTY, recorded_at TIMESTAMPTZ PATH '$.timestamp' ERROR ON ERROR ) ) AS jt; `; await client.query(query, [jsonPayload]); await client.query('COMMIT'); } } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Deterministic Memory Quotas and Disk-Spill Prevention for Postgres 17 JSON_TABLE

Deterministic Memory Quotas and Disk-Spill Prevention for Postgres 17 JSON_TABLE defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic work_mem allocation and chunked streaming execution for Postgres 17 JSON_TABLE projections. Prevents uncontrolled temp file disk spilling and worker process OOM terminations when transforming high-throughput semi-structured JSON payloads into relational tuples. 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: Monolithic In-Memory JSON Deserialization and Unbounded Projections Inducing Temp Disk Spilling 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	Monolithic In-Memory JSON Deserialization and Unbounded Projections Inducing Temp Disk Spilling	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 bound work_mem locally within an explicit transaction block using SET LOCAL work_mem calibrated to batch throughput (e.g., 32MB-64MB) before running JSON_TABLE queries.

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.