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: postgres-17-jsontable-streaming-memory-ceiling

Postgres 17 JSON_TABLE Virtual Tuple Streaming Memory Ceiling

Enforces strict memory ceilings and pagination bounds when executing SQL/JSON JSON_TABLE queries with nested path expansions in PostgreSQL 17. Unconstrained nested path evaluations generate unbounded in-memory virtual tuple projections that trigger Linux kernel OOM kills (SIGKILL) on containerized database backends.

Prohibited Anti-Pattern: Unbounded Cartesian NESTED PATH expansions in JSON_TABLE buffering millions of virtual tuples into memory.

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 JSON_TABLE Virtual Tuple Streaming Memory Ceiling - Enforces strict memory ceilings and pagination bounds when executing SQL/JSON JSON_TABLE queries with nested path expansions in PostgreSQL 17. Unconstrained nested path evaluations generate unbounded in-memory virtual tuple projections that trigger Linux kernel OOM kills (SIGKILL) on containerized database backends.
globs: src/**/*.ts, src/repositories/**/*.ts, src/database/**/*.ts, migrations/**/*.sql
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-jsontable-streaming-memory-ceiling"
---

# Postgres 17 JSON_TABLE Virtual Tuple Streaming Memory Ceiling

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded Cartesian NESTED PATH expansions in JSON_TABLE buffering millions of virtual tuples into memory.

## System Constraints
You are a Principal Database Architect and Infrastructure Guardrail enforcing strict memory bounds on Postgres 17 JSON_TABLE queries. You must prevent uncatchable OOM kills caused by virtual tuple streaming during multi-tier NESTED PATH evaluations. Mandate strict row batching, local work_mem governance, bounded JSON path filters, and cursor-based node streaming.

## Strict Guidelines
- Engineers MUST ALWAYS enforce keyset or limit/offset batch boundaries on the source JSONB relation before projecting rows through JSON_TABLE with NESTED PATH clauses.
- Engineers MUST NOT execute JSON_TABLE queries containing multiple sibling or deep NESTED PATH branches over large result sets without setting a transaction-scoped 'SET LOCAL statement_timeout' and 'SET LOCAL work_mem'.
- Code SHOULD filter JSON arrays upstream using jsonb_path_query or size checks (e.g., jsonb_array_length) prior to multi-level relational unnesting to prevent exponential Cartesian tuple multiplication.
- Services MUST consume expanded JSON_TABLE query results using streaming cursors (such as pg-query-stream) instead of buffering full virtual tuple result sets into Node.js client memory.
- Components NEVER execute JSON_TABLE with 'EMPTY ON ERROR' without strict input validation, as silent fallbacks mask runaway Cartesian fan-outs during schema evolution.

## Bad vs Good Implementations
### Unbounded JSON_TABLE Expansion vs. Bounded Streaming Transaction

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Executing multiple NESTED PATH projections over unbounded rows causes a Cartesian product of virtual tuples inside PostgreSQL's executor. Because this runs via pool.query() without session-level work_mem containment or streaming cursors, it risks immediate OOM killer termination (SIGKILL) by cgroups in both the database and the backend service.
```typescript
import { Pool } from 'pg';

export async function extractNestedTelemetry(pool: Pool, customerId: string) {
  // CRITICAL FLAW: Unbounded scan with deeply nested sibling paths
  // Multiplies virtual tuples across thousands of rows, exhausting PostgreSQL backend memory
  // and loading gigabytes of materialized tuples into Node.js heap memory simultaneously.
  const query = `
    SELECT d.id, jt.sensor_id, jt.metric_timestamp, jt.metric_value
    FROM customer_devices d,
    JSON_TABLE(
      d.payload_json,
      '$.sensors[*]' COLUMNS (
        sensor_id text PATH '$.id',
        NESTED PATH '$.readings[*]'
          COLUMNS (
            metric_timestamp timestamptz PATH '$.ts',
            metric_value double precision PATH '$.val'
          ),
        NESTED PATH '$.diagnostics[*]'
          COLUMNS (
            diagnostic_code text PATH '$.code'
          )
      )
    ) AS jt
    WHERE d.customer_id = $1;
  `;

  const result = await pool.query(query, [customerId]);
  return result.rows;
}
```

#### Production Standard (Correct)
> **Standard**: The query isolates JSON_TABLE execution behind bounded source subqueries with jsonb array guards, removes unneeded sibling Cartesian projections, establishes a dedicated read-only transaction with a 15-second statement timeout and a 64MB work_mem ceiling, and delivers rows to Node.js via an asynchronous QueryStream to keep RAM usage constant regardless of data volume.
```typescript
import { Pool, PoolClient } from 'pg';
import QueryStream from 'pg-query-stream';

export async function* extractNestedTelemetryStream(
  pool: Pool,
  customerId: string,
  batchSize = 500
): AsyncGenerator<any> {
  const client: PoolClient = await pool.connect();
  
  try {
    await client.query('BEGIN READ ONLY');
    // Constrain executor memory ceiling and execution duration to avoid runaway OOM
    await client.query('SET LOCAL statement_timeout = \'15s\'');
    await client.query('SET LOCAL work_mem = \'64MB\'');

    // Enforce row-bounding and separate unnesting to eliminate Cartesian fan-out
    const queryText = `
      SELECT d.id, jt.sensor_id, jt.metric_timestamp, jt.metric_value
      FROM (
        SELECT id, payload_json
        FROM customer_devices
        WHERE customer_id = $1
          AND jsonb_array_length(payload_json->'sensors') > 0
        LIMIT $2
      ) d,
      JSON_TABLE(
        d.payload_json,
        '$.sensors[*]' COLUMNS (
          sensor_id text PATH '$.id',
          NESTED PATH '$.readings[*]'
            COLUMNS (
              metric_timestamp timestamptz PATH '$.ts',
              metric_value double precision PATH '$.val'
            )
        ) PLAN (d.payload_json DEFAULT)
      ) AS jt;
    `;

    const stream = client.query(new QueryStream(queryText, [customerId, batchSize]));
    
    for await (const row of stream) {
      yield row;
    }

    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.

Postgres MCP Server
Inspect Postgres query execution plans and profile EXPLAIN ANALYZE virtual tuple memory allocation.
Recommended Agent Skills (askills.dev)

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

PostgreSQL Query Plan Diagnostic Tool
CLI diagnostic utility for inspecting tuplestore overflows and JSON_TABLE executor memory consumption.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Virtual Tuple Streaming Memory Ceiling

Postgres 17 JSON_TABLE Virtual Tuple Streaming Memory Ceiling defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory ceilings and pagination bounds when executing SQL/JSON JSON_TABLE queries with nested path expansions in PostgreSQL 17. Unconstrained nested path evaluations generate unbounded in-memory virtual tuple projections that trigger Linux kernel OOM kills (SIGKILL) on containerized database backends. 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 Cartesian NESTED PATH expansions in JSON_TABLE buffering millions of virtual tuples into memory. 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 Cartesian NESTED PATH expansions in JSON_TABLE buffering millions of virtual tuples into memory.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 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 keyset or limit/offset batch boundaries on the source JSONB relation before projecting rows through JSON_TABLE with NESTED PATH clauses.

Engineers MUST ALWAYS enforce keyset or limit/offset batch boundaries on the source JSONB relation before projecting rows through JSON_TABLE with NESTED PATH clauses.

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 execute JSON_TABLE queries containing multiple sibling or deep NESTED PATH branches over large result sets without setting a transaction-scoped 'SET LOCAL statement_timeout' and 'SET LOCAL work_mem'.

Engineers MUST NOT execute JSON_TABLE queries containing multiple sibling or deep NESTED PATH branches over large result sets without setting a transaction-scoped 'SET LOCAL statement_timeout' and 'SET LOCAL work_mem'.

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 filter JSON arrays upstream using jsonb_path_query or size checks (e.g., jsonb_array_length) prior to multi-level relational unnesting to prevent exponential Cartesian tuple multiplication.

Code SHOULD filter JSON arrays upstream using jsonb_path_query or size checks (e.g., jsonb_array_length) prior to multi-level relational unnesting to prevent exponential Cartesian tuple multiplication.

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 consume expanded JSON_TABLE query results using streaming cursors (such as pg-query-stream) instead of buffering full virtual tuple result sets into Node.js client memory.

Services MUST consume expanded JSON_TABLE query results using streaming cursors (such as pg-query-stream) instead of buffering full virtual tuple result sets into Node.js client memory.

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 JSON_TABLE with 'EMPTY ON ERROR' without strict input validation, as silent fallbacks mask runaway Cartesian fan-outs during schema evolution.

Components NEVER execute JSON_TABLE with 'EMPTY ON ERROR' without strict input validation, as silent fallbacks mask runaway Cartesian fan-outs during schema evolution.

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 Postgres 17 JSON_TABLE Virtual Tuple Streaming Memory Ceiling critical for AI coding assistants?

Enforces strict memory ceilings and pagination bounds when executing SQL/JSON JSON_TABLE queries with nested path expansions in PostgreSQL 17. Unconstrained nested path evaluations generate unbounded in-memory virtual tuple projections that trigger Linux kernel OOM kills (SIGKILL) on containerized database backends. 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 Cartesian NESTED PATH expansions in JSON_TABLE buffering millions of virtual tuples into memory.) 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

Postgres