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-json-table-workmem-streaming-projection

Postgres 17 JSON_TABLE WorkMem Bounded Streaming Projection

Enforces deterministic memory bounds and streaming execution paths when projecting massive JSON/JSONB payloads using PostgreSQL 17's SQL/JSON JSON_TABLE standard. Unbounded in-memory materialization of nested JSON arrays during concurrent OLTP operations bypasses work_mem limits, precipitating backend worker Out-Of-Memory (OOM) termination and connection drops. Queries must enforce pre-chunked streaming projections, typed scalar extraction without intermediate subquery buffering, and explicit memory limits via session parameters.

Prohibited Anti-Pattern: Unbounded jsonb_to_recordset or Materializing JSON_TABLE Evaluation on Multi-Megabyte Payloads

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 JSON_TABLE WorkMem Bounded Streaming Projection - Enforces deterministic memory bounds and streaming execution paths when projecting massive JSON/JSONB payloads using PostgreSQL 17's SQL/JSON JSON_TABLE standard. Unbounded in-memory materialization of nested JSON arrays during concurrent OLTP operations bypasses work_mem limits, precipitating backend worker Out-Of-Memory (OOM) termination and connection drops. Queries must enforce pre-chunked streaming projections, typed scalar extraction without intermediate subquery buffering, and explicit memory limits via session parameters.
globs: src/db/**/*.ts, src/repositories/**/*.ts, src/services/**/*.ts, migrations/**/*.sql
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-json-table-workmem-streaming-projection"
---

# Postgres 17 JSON_TABLE WorkMem Bounded Streaming Projection

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded jsonb_to_recordset or Materializing JSON_TABLE Evaluation on Multi-Megabyte Payloads

## System Constraints
You are a Principal Database Architect enforcing Postgres 17 JSON_TABLE WorkMem Bounded Streaming Projection. Mandate strict memory envelope controls when expanding high-cardinality JSON/JSONB structures into relational representations. Prevent whole-document in-memory AST expansion, prevent Cartesian subpath explosion, mandate typed column mappings over raw JSONB passthrough, and ensure streaming projection with strict statement-level memory controls.

## Strict Guidelines
- Engineers MUST ALWAYS use PostgreSQL 17 standard JSON_TABLE with explicit column types, avoiding legacy 'jsonb_to_recordset' or intermediate 'jsonb_array_elements' joins for documents exceeding 1MB.
- Engineers MUST NEVER project intermediate nested structures as 'FORMAT JSON' or unparsed 'jsonb' inside JSON_TABLE column clauses, as this forces backend buffer retention instead of stream projection.
- Queries processing large external payloads MUST wrap the transaction in a deterministic statement-level memory envelope (e.g., SET LOCAL work_mem = '64MB') to avoid backend worker OOM kills under connection pool contention.
- Engineers MUST configure error-handling fallback clauses (e.g., 'EMPTY ON ERROR' or 'DEFAULT ... ON ERROR') to avoid full query cancellation and rolling rollback allocations during malformed stream ingestion.
- Queries MUST NOT perform nested Cartesian projections with multiple unbounded 'NESTED PATH' expressions in a single JSON_TABLE call without enforcing a LIMIT or paging via keyset cursors.
- Engineers MUST pass raw inputs directly as typed parameters without intermediate string concatenations or client-side JSON parsing that duplicates memory footprints across both Node.js/Go runtimes and the Postgres backend.

## Bad vs Good Implementations
### Unbounded JSON Expansion vs. Postgres 17 Memory-Bounded JSON_TABLE Projection

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: jsonb_array_elements parses the entire 50MB JSON tree in memory at once. If 30 concurrent transactions execute this, the Postgres worker processes exhaust available RAM and trigger the Linux OOM killer, killing the Postgres backend process. Furthermore, extracting values via ->> after full deserialization causes repetitive object scanning.
```typescript
import { sql } from 'kysely';
import { db } from '../database';

// ANTI-PATTERN: Ingests 50MB payload, expanding via jsonb_array_elements_text
// Forces Postgres to construct a massive AST and duplicate records in worker RAM
export async function ingestAuditBatchBad(batchId: string, rawPayload: string): Promise<void> {
  await db.transaction().execute(async (trx) => {
    await sql`
      INSERT INTO audit_events (batch_id, event_id, event_type, payload)
      SELECT 
        ${batchId},
        (item->>'id')::uuid,
        item->>'type',
        item->'details'
      FROM jsonb_array_elements(${rawPayload}::jsonb) AS item;
    `.execute(trx);
  });
}
```

#### Production Standard (Correct)
> **Standard**: PostgreSQL 17's JSON_TABLE evaluates path expressions as a streaming projection, directly extracting typed scalar fields into row slots without buffering an intermediate JSONB array. The explicit work_mem setting guarantees predictable operational limits, while 'DEFAULT ... ON EMPTY' guards prevent query crashes on incomplete records.
```typescript
import { sql } from 'kysely';
import { db } from '../database';

// PRODUCTION PATTERN: Uses PG17 JSON_TABLE with streaming bounded evaluation
// Explicitly controls session work_mem and avoids JSON intermediate tree retention
export async function ingestAuditBatchGood(batchId: string, rawPayload: string): Promise<void> {
  await db.transaction().execute(async (trx) => {
    // Bound the local memory footprint for this batch projection
    await sql`SET LOCAL work_mem = '32MB';`.execute(trx);
    
    await sql`
      INSERT INTO audit_events (batch_id, event_id, event_type, details_code, severity)
      SELECT 
        ${batchId}::uuid,
        jt.event_id,
        jt.event_type,
        jt.details_code,
        jt.severity
      FROM JSON_TABLE(
        ${rawPayload}::jsonb,
        '$[*]'
        COLUMNS (
          event_id UUID PATH '$.id' ERROR ON ERROR,
          event_type VARCHAR(64) PATH '$.type' DEFAULT 'UNKNOWN' ON EMPTY,
          details_code INT PATH '$.details.code' DEFAULT 0 ON EMPTY,
          severity VARCHAR(16) PATH '$.details.severity' DEFAULT 'info' ON EMPTY
        )
      ) AS jt;
    `.execute(trx);
  });
}
```
Recommended MCP Servers (mcp-serverlist.com)

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

Postgres EXPLAIN & Schema MCP
Inspects live execution plans, buffers, and memory allocations for complex SQL/JSON JSON_TABLE queries.
Recommended Agent Skills (askills.dev)

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

PostgreSQL EXPLAIN ANALYZE CLI
CLI utility to verify BUFFERS and MEMORY output on JSON_TABLE queries to validate streaming execution paths.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE WorkMem Bounded Streaming Projection

Postgres 17 JSON_TABLE WorkMem Bounded Streaming Projection defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic memory bounds and streaming execution paths when projecting massive JSON/JSONB payloads using PostgreSQL 17's SQL/JSON JSON_TABLE standard. Unbounded in-memory materialization of nested JSON arrays during concurrent OLTP operations bypasses work_mem limits, precipitating backend worker Out-Of-Memory (OOM) termination and connection drops. Queries must enforce pre-chunked streaming projections, typed scalar extraction without intermediate subquery buffering, and explicit memory limits via session parameters. 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 jsonb_to_recordset or Materializing JSON_TABLE Evaluation on Multi-Megabyte Payloads 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 jsonb_to_recordset or Materializing JSON_TABLE Evaluation on Multi-Megabyte PayloadsCritical
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 use PostgreSQL 17 standard JSON_TABLE with explicit column types, avoiding legacy 'jsonb_to_recordset' or intermediate 'jsonb_array_elements' joins for documents exceeding 1MB.

Engineers MUST ALWAYS use PostgreSQL 17 standard JSON_TABLE with explicit column types, avoiding legacy 'jsonb_to_recordset' or intermediate 'jsonb_array_elements' joins for documents 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

Engineers MUST NEVER project intermediate nested structures as 'FORMAT JSON' or unparsed 'jsonb' inside JSON_TABLE column clauses, as this forces backend buffer retention instead of stream projection.

Engineers MUST NEVER project intermediate nested structures as 'FORMAT JSON' or unparsed 'jsonb' inside JSON_TABLE column clauses, as this forces backend buffer retention instead of stream projection.

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

Queries processing large external payloads MUST wrap the transaction in a deterministic statement-level memory envelope (e.g., SET LOCAL work_mem = '64MB') to avoid backend worker OOM kills under connection pool contention.

Queries processing large external payloads MUST wrap the transaction in a deterministic statement-level memory envelope (e.g., SET LOCAL work_mem = '64MB') to avoid backend worker OOM kills under connection pool contention.

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 configure error-handling fallback clauses (e.g., 'EMPTY ON ERROR' or 'DEFAULT ... ON ERROR') to avoid full query cancellation and rolling rollback allocations during malformed stream ingestion.

Engineers MUST configure error-handling fallback clauses (e.g., 'EMPTY ON ERROR' or 'DEFAULT ... ON ERROR') to avoid full query cancellation and rolling rollback allocations during malformed stream ingestion.

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

Queries MUST NOT perform nested Cartesian projections with multiple unbounded 'NESTED PATH' expressions in a single JSON_TABLE call without enforcing a LIMIT or paging via keyset cursors.

Queries MUST NOT perform nested Cartesian projections with multiple unbounded 'NESTED PATH' expressions in a single JSON_TABLE call without enforcing a LIMIT or paging via keyset cursors.

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 pass raw inputs directly as typed parameters without intermediate string concatenations or client-side JSON parsing that duplicates memory footprints across both Node.js/Go runtimes and the Postgres backend.

Engineers MUST pass raw inputs directly as typed parameters without intermediate string concatenations or client-side JSON parsing that duplicates memory footprints across both Node.js/Go runtimes and the Postgres backend.

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 WorkMem Bounded Streaming Projection critical for AI coding assistants?

Enforces deterministic memory bounds and streaming execution paths when projecting massive JSON/JSONB payloads using PostgreSQL 17's SQL/JSON JSON_TABLE standard. Unbounded in-memory materialization of nested JSON arrays during concurrent OLTP operations bypasses work_mem limits, precipitating backend worker Out-Of-Memory (OOM) termination and connection drops. Queries must enforce pre-chunked streaming projections, typed scalar extraction without intermediate subquery buffering, and explicit memory limits via session parameters. 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 jsonb_to_recordset or Materializing JSON_TABLE Evaluation on Multi-Megabyte Payloads) 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