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: pg17-json-table-memory-bounds-streaming

PostgreSQL 17 JSON_TABLE Memory Bounds and Streaming Projection Invariants

Enforces strict memory ceilings, bounded path evaluations, and deterministic cardinality controls when projecting JSONB payloads using PostgreSQL 17 JSON_TABLE. Eliminates backend work_mem exhaustion, temp file disk spills, and query planner degradation caused by unbounded document materialization and lax nested array evaluations.

Prohibited Anti-Pattern: Unbounded JSON_TABLE Projections with Lax Path Expansions and Unconstrained Document Materialization

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 JSON_TABLE Memory Bounds and Streaming Projection Invariants - Enforces strict memory ceilings, bounded path evaluations, and deterministic cardinality controls when projecting JSONB payloads using PostgreSQL 17 JSON_TABLE. Eliminates backend work_mem exhaustion, temp file disk spills, and query planner degradation caused by unbounded document materialization and lax nested array evaluations.
globs: src/repositories/**/*.ts, src/services/**/*.ts, src/queries/**/*.ts, src/db/**/*.ts
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pg17-json-table-memory-bounds-streaming"
---

# PostgreSQL 17 JSON_TABLE Memory Bounds and Streaming Projection Invariants

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded JSON_TABLE Projections with Lax Path Expansions and Unconstrained Document Materialization

## System Constraints
You are a Principal Database Architect and Backend Systems Engineer enforcing PostgreSQL 17 SQL/JSON standards. Your objective is to prevent query planner misestimations and memory exhaustion (work_mem / RSS runaway) when using JSON_TABLE. You mandate deterministic JSON paths, explicit error handling semantics (ERROR ON ERROR vs. EMPTY ON ERROR), strict column sizing, batch limits, and proper lateral join safety to ensure non-blocking relational transformations.

## Strict Guidelines
- Engineers MUST ALWAYS define strict JSON paths (using 'strict $' instead of lax '$') within JSON_TABLE path specifications to eliminate unexpected cartesian nested traversals.
- Engineers MUST NOT project raw JSONB or arbitrary-length VARCHAR without an explicit length bound inside the COLUMNS clause of JSON_TABLE.
- Code SHOULD specify deterministic handling semantics (EMPTY ON ERROR or ERROR ON ERROR) for all extracted paths to prevent silent data corruption or backend thread panics.
- Services MUST batch high-throughput document ingest pipelines into bounded chunk sizes (<= 5,000 items or <= 4MB per statement) before passing payloads into JSON_TABLE.
- Components NEVER execute unconstrained LATERAL joins over JSON_TABLE without an explicit cardinality bound or WHERE-clause filter that the optimizer can index-scan.

## Bad vs Good Implementations
### PostgreSQL 17 JSON_TABLE Document Projection and Batch Ingestion

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Passing unbounded JSON payloads with lax path '$[*]', unrestricted 'text' datatypes, and nested raw 'jsonb' projections forces the PG17 execution engine to unpack the entire tree into transient backend memory without cardinality ceilings. This risks OOM killer invocation and severe work_mem exhaustion.
```typescript
import { Pool } from 'pg';

interface IngestionBatch {
  documentBatch: unknown[];
}

export async function ingestAuditPayloads(pool: Pool, batch: IngestionBatch): Promise<void> {
  // ANTI-PATTERN: Serializing unbounded payloads into JSON_TABLE with lax paths.
  // Causes catastrophic executor memory bloat, disk spills in work_mem, and unconstrained string allocations.
  const query = `
    INSERT INTO audit_events (event_id, event_type, user_id, payload)
    SELECT jt.id, jt.type, jt.user_id, jt.data
    FROM JSON_TABLE(
      $1::jsonb,
      '$[*]'
      COLUMNS (
        id text PATH '$.id',
        type text PATH '$.type',
        user_id text PATH '$.user_id',
        data jsonb PATH '$.data'
      )
    ) AS jt;
  `;

  await pool.query(query, [JSON.stringify(batch.documentBatch)]);
}
```

#### Production Standard (Correct)
> **Standard**: The invariant solution enforces a payload byte ceiling and record count limit before execution. The SQL uses 'strict' path evaluations, bounds every projected column type (varchar with explicit length), mandates 'ERROR ON ERROR' behavior to prevent silent corruption, and maps scalar fields instead of unparsed nested JSONB, keeping memory within predictable execution bounds.
```typescript
import { Pool, PoolClient } from 'pg';

interface AuditEventPayload {
  id: string;
  type: string;
  userId: string;
  traceId: string;
}

const BATCH_SIZE_LIMIT = 2000;
const MAX_JSON_PAYLOAD_BYTES = 4 * 1024 * 1024; // 4MB safety ceiling

export async function ingestAuditPayloads(
  client: PoolClient,
  events: AuditEventPayload[]
): Promise<number> {
  if (events.length === 0) return 0;
  if (events.length > BATCH_SIZE_LIMIT) {
    throw new RangeError(`Batch size ${events.length} exceeds limit of ${BATCH_SIZE_LIMIT}`);
  }

  const serialized = JSON.stringify(events);
  if (Buffer.byteLength(serialized, 'utf8') > MAX_JSON_PAYLOAD_BYTES) {
    throw new RangeError('Payload byte size exceeds 4MB memory boundary');
  }

  // INVARIANT: Strict JSON path mode, bounded types, deterministic ERROR ON ERROR,
  // and explicit column projections to maintain tight executor memory bounds.
  const query = `
    INSERT INTO audit_events (event_id, event_type, user_id, trace_id)
    SELECT 
      jt.id::uuid,
      jt.event_type,
      jt.user_id::uuid,
      jt.trace_id
    FROM JSON_TABLE(
      $1::jsonb,
      'strict $[*]' ERROR ON ERROR
      COLUMNS (
        id varchar(36) PATH 'strict $.id' ERROR ON ERROR,
        event_type varchar(64) PATH 'strict $.type' ERROR ON ERROR,
        user_id varchar(36) PATH 'strict $.userId' ERROR ON ERROR,
        trace_id varchar(64) PATH 'strict $.traceId' DEFAULT 'none' ON EMPTY
      )
    ) AS jt
    ON CONFLICT (event_id) DO NOTHING;
  `;

  const result = await client.query(query, [serialized]);
  return result.rowCount ?? 0;
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

PostgreSQL Context Server
Inspects live PostgreSQL execution plans (EXPLAIN ANALYZE) and identifies temp file disk spills and memory regressions.
Recommended Agent Skills (askills.dev)

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

PostgreSQL Query Plan Analyzer
CLI automation utility for evaluating EXPLAIN (ANALYZE, BUFFERS, SETTINGS) output against JSON_TABLE queries to assert memory allocations.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 JSON_TABLE Memory Bounds and Streaming Projection Invariants

PostgreSQL 17 JSON_TABLE Memory Bounds and Streaming Projection Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory ceilings, bounded path evaluations, and deterministic cardinality controls when projecting JSONB payloads using PostgreSQL 17 JSON_TABLE. Eliminates backend work_mem exhaustion, temp file disk spills, and query planner degradation caused by unbounded document materialization and lax nested array evaluations. 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 Projections with Lax Path Expansions and Unconstrained Document 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 ModeAI Symptom (The Anti-Pattern)SeverityProduction Impact & Remedy
AI Hallucination & Deprecation DriftUnbounded JSON_TABLE Projections with Lax Path Expansions and Unconstrained Document MaterializationCritical
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 define strict JSON paths (using 'strict $' instead of lax '$') within JSON_TABLE path specifications to eliminate unexpected cartesian nested traversals.

Engineers MUST ALWAYS define strict JSON paths (using 'strict $' instead of lax '$') within JSON_TABLE path specifications to eliminate unexpected cartesian nested traversals.

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 NOT project raw JSONB or arbitrary-length VARCHAR without an explicit length bound inside the COLUMNS clause of JSON_TABLE.

Engineers MUST NOT project raw JSONB or arbitrary-length VARCHAR without an explicit length bound inside the COLUMNS clause of JSON_TABLE.

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

Code SHOULD specify deterministic handling semantics (EMPTY ON ERROR or ERROR ON ERROR) for all extracted paths to prevent silent data corruption or backend thread panics.

Code SHOULD specify deterministic handling semantics (EMPTY ON ERROR or ERROR ON ERROR) for all extracted paths to prevent silent data corruption or backend thread panics.

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 batch high-throughput document ingest pipelines into bounded chunk sizes (<= 5,000 items or <= 4MB per statement) before passing payloads into JSON_TABLE.

Services MUST batch high-throughput document ingest pipelines into bounded chunk sizes (<= 5,000 items or <= 4MB per statement) before passing payloads into JSON_TABLE.

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 unconstrained LATERAL joins over JSON_TABLE without an explicit cardinality bound or WHERE-clause filter that the optimizer can index-scan.

Components NEVER execute unconstrained LATERAL joins over JSON_TABLE without an explicit cardinality bound or WHERE-clause filter that the optimizer can index-scan.

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 Bounds and Streaming Projection Invariants critical for AI coding assistants?

Enforces strict memory ceilings, bounded path evaluations, and deterministic cardinality controls when projecting JSONB payloads using PostgreSQL 17 JSON_TABLE. Eliminates backend work_mem exhaustion, temp file disk spills, and query planner degradation caused by unbounded document materialization and lax nested array evaluations. 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 JSON_TABLE Projections with Lax Path Expansions and Unconstrained Document Materialization) 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