AgentRules.org
RulesStacksClientsAuditorPrunerSelf-HealRFCsBenchmarkCLICI/CDAuthorsSubmit
This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →
backendcritical PriorityID: postgres17-json-table-memory-spill-quota

PostgreSQL 17 JSON_TABLE Memory Spill and Nested Path Materialization Quota

Enforces deterministic memory quotas and relational materialization boundaries on PostgreSQL 17 SQL/JSON JSON_TABLE projections. Prevents query engine backend OOM kills by restricting unbounded NESTED PATH expansions on dynamic JSONB payloads and enforcing transaction-local work_mem, temp_file_limit, and typed column width boundaries.

Prohibited Anti-Pattern: Unbounded Nested JSON_TABLE Projections Without Local Memory and Spill Controls Triggering Backend OOM

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 JSON_TABLE Memory Spill and Nested Path Materialization Quota - Enforces deterministic memory quotas and relational materialization boundaries on PostgreSQL 17 SQL/JSON JSON_TABLE projections. Prevents query engine backend OOM kills by restricting unbounded NESTED PATH expansions on dynamic JSONB payloads and enforcing transaction-local work_mem, temp_file_limit, and typed column width boundaries.
globs: src/**/*.sql, src/**/*.ts, migrations/**/*.sql, src/repositories/**/*.ts, src/db/**/*.ts
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres17-json-table-memory-spill-quota"
---

# PostgreSQL 17 JSON_TABLE Memory Spill and Nested Path Materialization Quota

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded Nested JSON_TABLE Projections Without Local Memory and Spill Controls Triggering Backend OOM

## System Constraints
You are a Principal Database Architect and Query Engine Specialist enforcing memory boundaries for PostgreSQL 17 SQL/JSON features. You mandate bounded work_mem, strict column projection sizes, and nested path guardrails on all JSON_TABLE queries to protect database instances from Out-Of-Memory (OOM) termination and disk-fill denial of service.

## Strict Guidelines
- Engineers MUST ALWAYS enforce transaction-local memory ceilings (`SET LOCAL work_mem` and `SET LOCAL temp_file_limit`) when projecting nested arrays via `JSON_TABLE` to prevent memory exhaustion.
- Engineers MUST specify concrete, bounded column widths (e.g., `VARCHAR(n)`, `INTEGER`, `NUMERIC(p,s)`) inside the `JSON_TABLE(...) COLUMNS` clause and MUST NOT use unbounded `TEXT` for dynamic nested fields.
- Engineers MUST NOT execute multi-level `NESTED PATH` projections directly over unbounded table scans; queries MUST filter rows via indexed keys or bounded cursor/keyset pagination prior to `JSON_TABLE` materialization.
- Services MUST configure explicit `ERROR ON ERROR` or structured fallback clauses (`EMPTY ON ERROR`) in SQL/JSON path evaluations to prevent silent buffer accumulation and catastrophic backtracking during parsing.
- Components NEVER execute dynamic or schema-less `JSON_TABLE` queries against large untrusted JSON payloads without an enforcing `statement_timeout` within the local transaction scope.

## Bad vs Good Implementations
### PostgreSQL 17 JSON_TABLE Projection with Memory and Temp Disk Quotas

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Executes multi-level NESTED PATH projections with unbounded TEXT columns over an unmetered batch. When handling deeply nested arrays or multi-megabyte payloads, PostgreSQL backend processes allocate memory rapidly without ceiling checks, risking temp file exhaustion or kernel OOM termination.
```typescript
import { Pool } from 'pg';

export async function extractCustomerOrderItems(pool: Pool, batchId: string) {
  // Flaw: Unbounded NESTED PATH projection across unindexed rows without work_mem or temp quotas.
  // Materializes open-ended TEXT buffers, causing memory amplification and potential backend OOM kills.
  const query = `
    SELECT 
      c.id AS customer_id,
      jt.order_id,
      jt.item_name,
      jt.item_quantity
    FROM customer_events c,
    JSON_TABLE(
      c.payload, '$.orders[*]'
      COLUMNS (
        order_id TEXT PATH '$.id',
        NESTED PATH '$.items[*]'
        COLUMNS (
          item_name TEXT PATH '$.name',
          item_quantity TEXT PATH '$.quantity'
        )
      )
    ) AS jt
    WHERE c.batch_id = $1;
  `;
  const { rows } = await pool.query(query, [batchId]);
  return rows;
}
```

#### Production Standard (Correct)
> **Standard**: Wraps the query in a transaction setting explicit local ceilings (`work_mem = '32MB'`, `temp_file_limit = '256MB'`, and `statement_timeout = '15000'`). Columns enforce explicit length limits (`VARCHAR(64)`, `VARCHAR(255)`, `INTEGER`) and strict error behaviors (`ERROR ON ERROR`), while keyset pagination limits row exposure to avoid unbounded Cartesian memory growth.
```typescript
import { PoolClient } from 'pg';

interface OrderItemRow {
  customer_id: string;
  order_id: string;
  item_name: string;
  item_quantity: number;
}

export async function extractCustomerOrderItemsBounded(
  client: PoolClient,
  batchId: string,
  cursorId: string,
  pageSize: number
): Promise<OrderItemRow[]> {
  // Invariant: Isolate memory allocation using transaction-scoped quotas and bounded types.
  await client.query('BEGIN TRANSACTION READ ONLY');
  try {
    // Cap memory and spill footprint strictly for this projection
    await client.query("SET LOCAL work_mem = '32MB'");
    await client.query("SET LOCAL temp_file_limit = '256MB'");
    await client.query("SET LOCAL statement_timeout = '15000'");

    const query = `
      SELECT 
        c.id AS customer_id,
        jt.order_id,
        jt.item_name,
        jt.item_quantity
      FROM customer_events c,
      JSON_TABLE(
        c.payload, '$.orders[*]'
        COLUMNS (
          order_id VARCHAR(64) PATH '$.id' ERROR ON ERROR,
          NESTED PATH '$.items[*]'
          COLUMNS (
            item_name VARCHAR(255) PATH '$.name' DEFAULT 'UNKNOWN' ON EMPTY,
            item_quantity INTEGER PATH '$.quantity' DEFAULT 0 ON ERROR
          )
        )
      ) AS jt
      WHERE c.batch_id = $1
        AND c.id > $2
      ORDER BY c.id ASC
      LIMIT $3;
    `;

    const { rows } = await client.query<OrderItemRow>(query, [batchId, cursorId, pageSize]);
    await client.query('COMMIT');
    return rows;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  }
}
```
Recommended MCP Servers (mcp-serverlist.com)

Equip agent with system tools to verify code for this stack.

PostgreSQL MCP Inspector
Inspects running PostgreSQL backend metrics, connection pool memory utilization, and explain plans for temporary spill files.
Recommended Agent Skills (askills.dev)

Ready-to-use executable skill functions tailored to this stack.

Postgres EXPLAIN ANALYZE Buffer Profiler
Automated CLI skill to profile query execution plans with EXPLAIN (ANALYZE, BUFFERS), validating that work_mem spills do not exceed acceptable disk limits.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 JSON_TABLE Memory Spill and Nested Path Materialization Quota

PostgreSQL 17 JSON_TABLE Memory Spill and Nested Path Materialization Quota defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic memory quotas and relational materialization boundaries on PostgreSQL 17 SQL/JSON JSON_TABLE projections. Prevents query engine backend OOM kills by restricting unbounded NESTED PATH expansions on dynamic JSONB payloads and enforcing transaction-local work_mem, temp_file_limit, and typed column width boundaries. 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 Without Local Memory and Spill Controls Triggering Backend OOM 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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftUnbounded Nested JSON_TABLE Projections Without Local Memory and Spill Controls Triggering Backend OOMCritical
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 InconsistencyAI 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:

1

Engineers MUST ALWAYS enforce transaction-local memory ceilings (`SET LOCAL work_mem` and `SET LOCAL temp_file_limit`) when projecting nested arrays via `JSON_TABLE` to prevent memory exhaustion.

Engineers MUST ALWAYS enforce transaction-local memory ceilings (`SET LOCAL work_mem` and `SET LOCAL temp_file_limit`) when projecting nested arrays via `JSON_TABLE` to prevent memory exhaustion.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
2

Engineers MUST specify concrete, bounded column widths (e.g., `VARCHAR(n)`, `INTEGER`, `NUMERIC(p,s)`) inside the `JSON_TABLE(...) COLUMNS` clause and MUST NOT use unbounded `TEXT` for dynamic nested fields.

Engineers MUST specify concrete, bounded column widths (e.g., `VARCHAR(n)`, `INTEGER`, `NUMERIC(p,s)`) inside the `JSON_TABLE(...) COLUMNS` clause and MUST NOT use unbounded `TEXT` for dynamic nested fields.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
3

Engineers MUST NOT execute multi-level `NESTED PATH` projections directly over unbounded table scans

Engineers MUST NOT execute multi-level `NESTED PATH` projections directly over unbounded table scans; queries MUST filter rows via indexed keys or bounded cursor/keyset pagination prior to `JSON_TABLE` materialization.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
4

Services MUST configure explicit `ERROR ON ERROR` or structured fallback clauses (`EMPTY ON ERROR`) in SQL/JSON path evaluations to prevent silent buffer accumulation and catastrophic backtracking during parsing.

Services MUST configure explicit `ERROR ON ERROR` or structured fallback clauses (`EMPTY ON ERROR`) in SQL/JSON path evaluations to prevent silent buffer accumulation and catastrophic backtracking during parsing.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
5

Components NEVER execute dynamic or schema-less `JSON_TABLE` queries against large untrusted JSON payloads without an enforcing `statement_timeout` within the local transaction scope.

Components NEVER execute dynamic or schema-less `JSON_TABLE` queries against large untrusted JSON payloads without an enforcing `statement_timeout` within the local transaction scope.

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.
Enforcement:Integrated into the Agent Pack rule file. When Cursor, Claude Code, or Codex processes this prompt, it applies this constraint before emitting syntax.
Technical Questions & Answers

Frequently Asked Technical Questions

Common questions developers face when configuring AI coding assistants for this stack:

Q:Why is PostgreSQL 17 JSON_TABLE Memory Spill and Nested Path Materialization Quota critical for AI coding assistants?

Enforces deterministic memory quotas and relational materialization boundaries on PostgreSQL 17 SQL/JSON JSON_TABLE projections. Prevents query engine backend OOM kills by restricting unbounded NESTED PATH expansions on dynamic JSONB payloads and enforcing transaction-local work_mem, temp_file_limit, and typed column width boundaries. Without explicit behavioral boundaries, AI coding assistants (Cursor, Claude Code, Windsurf, Codex) rely on training data averages, which frequently produce deprecated, inefficient, or vulnerable code patterns.

Q:How does adopting this rule prevent AI hallucinations?

This rule explicitly declares what the AI must avoid (Unbounded Nested JSON_TABLE Projections Without Local Memory and Spill Controls Triggering Backend OOM) 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.

Open Specification · CC BY 4.0·Free for Personal, Team & Commercial In-Repo Use

Citation & Open Source Attribution Guidelines

This specification is licensed under Creative Commons Attribution 4.0 International (CC BY 4.0). You are completely free to adapt and share this rule in your workspace. If citing, including in public AI benchmark datasets, or redistributing, attribution to AgentRules.org is required.

Related BACKEND Rules

View all rules →
backend
42

Fastify 5 Schema Compilers & Encapsulation Production Standard

Fastify relies on lexical encapsulation contexts to sandbox decorators, hooks, and compiled validation/serialization schemas without memory leaks or cross-tenant bleed. In Fastify 5, misuse of global compilers or failing to encapsulate plugin boundaries causes compiled `fast-json-stringify` serializers and Ajv validators to either crash on duplicate IDs, leak private data across sibling scopes, or bypass JIT compilation optimizations. Production services must enforce strict encapsulation hierarchies and type-safe schema compilation across every routing subtree.

backend
68

Bun Embedded SQLite WAL Mode & Transaction Synchronization Standard

Production database standard for bun:sqlite. Mandates enabling Write-Ahead Logging (PRAGMA journal_mode = WAL), busy timeouts, and using db.transaction() for atomic operations without lock starvation.

backend
68

Drizzle ORM Batch API & Interactive Transaction Isolation Standard

Production database standard for Drizzle ORM (PostgreSQL & MySQL). Mandates using db.batch() over HTTP drivers to prevent round-trip waterfalls, explicit db.transaction() with serializable/read-committed isolation, and strictly avoids unawaited query promises.

1 Rule in Stack

PostgreSQL