PostgreSQL 17 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection | AgentRules.org

Rules Stacks Clients Auditor Pruner Self-Heal RFCs Benchmark CLI CI/CD Authors Submit

This specification is maintained by the AgentRules Working Group. Have enhancements or want to claim maintainership?Submit Specification →

backendcritical PriorityID: pg17-json-table-memory-bounded-streaming-projection

PostgreSQL 17 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection

Enforces deterministic memory bounds and prevents query engine OOMs when projecting relational rows from massive semi-structured payloads using PostgreSQL 17 SQL/JSON JSON_TABLE. Mandates strict path projection, explicit column typing with fail-safe error handling, and transaction-local work_mem caps to guarantee streaming execution without intermediate AST materialization.

Prohibited Anti-Pattern: Unbounded JSON Parsing via Full-Document LATERAL recordset Deserialization

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: PostgreSQL 17 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection - Enforces deterministic memory bounds and prevents query engine OOMs when projecting relational rows from massive semi-structured payloads using PostgreSQL 17 SQL/JSON JSON_TABLE. Mandates strict path projection, explicit column typing with fail-safe error handling, and transaction-local work_mem caps to guarantee streaming execution without intermediate AST materialization.
globs: src/repositories/**/*.ts, src/database/**/*.ts, src/migrations/**/*.sql, src/**/*.dao.ts
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/pg17-json-table-memory-bounded-streaming-projection"
---

# PostgreSQL 17 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded JSON Parsing via Full-Document LATERAL recordset Deserialization

## System Constraints
You are a Principal Software Architect enforcing PostgreSQL 17 SQL/JSON JSON_TABLE architectural and memory invariants. You must prevent memory exhaustion from massive semi-structured payloads by mandating strict SQL/JSON syntax, transaction-scoped memory bounds, and zero-copy streaming projections over legacy deserialization functions.

## Strict Guidelines
- Engineers MUST use PostgreSQL 17 ANSI SQL/JSON 'JSON_TABLE' over legacy 'jsonb_to_recordset' or nested 'jsonb_array_elements' when projecting semi-structured payloads exceeding 1MB.
- Queries processing arbitrary or external JSON payloads MUST execute within a dedicated transaction encapsulating a constrained 'SET LOCAL work_mem' parameter to prevent backend worker OOM kills.
- Engineers MUST specify explicit column definitions with strict SQL data types and error clauses ('EMPTY ON ERROR' or 'ERROR ON ERROR') in 'JSON_TABLE' column definitions to prevent silent planner de-optimizations.
- Engineers MUST NOT extract raw intermediate JSON strings ('FORMAT JSON' without downstream scalar paths) inside high-volume projection loops.
- Path expressions passed to 'JSON_TABLE' MUST be anchored and specific (e.g., 'lax $.events[*]' instead of open-ended wildcards like '$..*') to enable path index pushdown and single-pass stream evaluation.
- Engineers MUST NEVER load multi-megabyte JSON payloads into the Node.js/TypeScript application layer for client-side destructuring if relational filtering or aggregation can be streamed directly inside PostgreSQL 17.

## Bad vs Good Implementations
### PostgreSQL 17 JSON_TABLE Projection vs Legacy Memory-Exhausting LATERAL Deserialization

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Using jsonb_to_recordset with an untyped JSON cast forces the PostgreSQL query planner to parse and fully instantiate the entire JSONB payload in memory. When payloads scale past work_mem limits, this causes disk-spilling temp files or backend crash failures.
```typescript
import { Pool } from 'pg';

export async function ingestAuditLogs(pool: Pool, tenantId: string, payload: unknown): Promise<void> {
  // ANTI-PATTERN: Serializes massive JSON payload into client memory and uses jsonb_to_recordset.
  // This forces Postgres to parse the entire JSON document into an unindexed in-memory AST,
  // leading to work_mem blowouts and potential OOM termination for multi-megabyte payloads.
  await pool.query(
    `
    INSERT INTO audit_events (tenant_id, event_id, event_type, created_at)
    SELECT 
      $1,
      elem.id::uuid,
      elem.type::text,
      elem.timestamp::timestamptz
    FROM jsonb_to_recordset($2::jsonb) AS elem(
      id text,
      type text,
      timestamp text
    )
    `,
    [tenantId, JSON.stringify(payload)]
  );
}
```

#### Production Standard (Correct)
> **Standard**: PG 17 JSON_TABLE streams through the JSONB structure along the targeted JSON path without full intermediate recordset AST materialization. Enforcing 'SET LOCAL work_mem' bounds planner memory, while strict column paths and error handling prevent malformed rows from destabilizing the database worker.
```typescript
import { Pool, PoolClient } from 'pg';

export async function ingestAuditLogs(pool: Pool, tenantId: string, rawJsonBuffer: Buffer): Promise<void> {
  const client: PoolClient = await pool.connect();
  try {
    await client.query('BEGIN');
    // INVARIANT: Cap transaction-local memory to contain projection footprint.
    await client.query("SET LOCAL work_mem = '32MB'");

    // INVARIANT: Use PG17 SQL/JSON JSON_TABLE for streaming zero-copy projection.
    // Paths are strictly anchored ('lax $[*]') with explicit types and empty-on-error guards.
    await client.query(
      `
      INSERT INTO audit_events (tenant_id, event_id, event_type, created_at)
      SELECT 
        $1,
        jt.event_id,
        jt.event_type,
        jt.created_at
      FROM JSON_TABLE(
        $2::jsonb,
        'lax $[*]' COLUMNS (
          event_id    uuid        PATH '$.id'        ERROR ON ERROR,
          event_type  text        PATH '$.type'      ERROR ON ERROR,
          created_at  timestamptz PATH '$.timestamp' EMPTY ON ERROR
        )
      ) AS jt
      WHERE jt.created_at IS NOT NULL
      `,
      [tenantId, rawJsonBuffer.toString('utf8')]
    );

    await client.query('COMMIT');
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}
```

Recommended MCP Servers (mcp-serverlist.com)

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

PostgreSQL Schema & Execution Inspector MCP

Inspects query execution plans (EXPLAIN ANALYZE BUFFERS) and verifies SQL/JSON memory utilization and index pushdowns.

Recommended Agent Skills (askills.dev)

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

Postgres Plan Memory Verifier CLI

Analyzes execution plans for Disk Spills, HashBatch overflows, and JSON_TABLE execution cost invariants.

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection

PostgreSQL 17 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic memory bounds and prevents query engine OOMs when projecting relational rows from massive semi-structured payloads using PostgreSQL 17 SQL/JSON JSON_TABLE. Mandates strict path projection, explicit column typing with fail-safe error handling, and transaction-local work_mem caps to guarantee streaming execution without intermediate AST materialization. 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 Parsing via Full-Document LATERAL recordset Deserialization 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 Parsing via Full-Document LATERAL recordset Deserialization	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:

1

Engineers MUST use PostgreSQL 17 ANSI SQL/JSON 'JSON_TABLE' over legacy 'jsonb_to_recordset' or nested 'jsonb_array_elements' when projecting semi-structured payloads exceeding 1MB.

Engineers MUST use PostgreSQL 17 ANSI SQL/JSON 'JSON_TABLE' over legacy 'jsonb_to_recordset' or nested 'jsonb_array_elements' when projecting semi-structured payloads exceeding 1MB.

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

Queries processing arbitrary or external JSON payloads MUST execute within a dedicated transaction encapsulating a constrained 'SET LOCAL work_mem' parameter to prevent backend worker OOM kills.

Queries processing arbitrary or external JSON payloads MUST execute within a dedicated transaction encapsulating a constrained 'SET LOCAL work_mem' parameter to prevent backend worker OOM kills.

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 specify explicit column definitions with strict SQL data types and error clauses ('EMPTY ON ERROR' or 'ERROR ON ERROR') in 'JSON_TABLE' column definitions to prevent silent planner de-optimizations.

Engineers MUST specify explicit column definitions with strict SQL data types and error clauses ('EMPTY ON ERROR' or 'ERROR ON ERROR') in 'JSON_TABLE' column definitions to prevent silent planner de-optimizations.

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

Engineers MUST NOT extract raw intermediate JSON strings ('FORMAT JSON' without downstream scalar paths) inside high-volume projection loops.

Engineers MUST NOT extract raw intermediate JSON strings ('FORMAT JSON' without downstream scalar paths) inside high-volume projection loops.

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

Path expressions passed to 'JSON_TABLE' MUST be anchored and specific (e.g., 'lax $.events[]' instead of open-ended wildcards like '$..') to enable path index pushdown and single-pass stream evaluation.

Path expressions passed to 'JSON_TABLE' MUST be anchored and specific (e.g., 'lax $.events[*]' instead of open-ended wildcards like '$..*') to enable path index pushdown and single-pass stream evaluation.

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.

6

Engineers MUST NEVER load multi-megabyte JSON payloads into the Node.js/TypeScript application layer for client-side destructuring if relational filtering or aggregation can be streamed directly inside PostgreSQL 17.

Engineers MUST NEVER load multi-megabyte JSON payloads into the Node.js/TypeScript application layer for client-side destructuring if relational filtering or aggregation can be streamed directly inside PostgreSQL 17.

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 SQL/JSON JSON_TABLE Memory-Bounded Streaming Projection critical for AI coding assistants?

Enforces deterministic memory bounds and prevents query engine OOMs when projecting relational rows from massive semi-structured payloads using PostgreSQL 17 SQL/JSON JSON_TABLE. Mandates strict path projection, explicit column typing with fail-safe error handling, and transaction-local work_mem caps to guarantee streaming execution without intermediate AST materialization. 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 Parsing via Full-Document LATERAL recordset Deserialization) 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 →

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.

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.

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