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-work-mem-spill-prevention

Postgres 17 SQL/JSON JSON_TABLE Memory Boundary and Work-Mem Spill Prevention Guardrail

Enforces deterministic batch chunking, explicit projection boundaries, and work_mem allocation guards for Postgres 17 JSON_TABLE transformations in CDC ingest paths. Eliminates silent temp-file disk thrashing by preventing cardinality misestimates and unbounded hash joins from exceeding memory limits.

Prohibited Anti-Pattern: Unbounded Dynamic JSON_TABLE Projection Spilling to Temp Disk Files During CDC Ingestion

Export Ready-to-Drop Configuration

Select format below to copy code or download file directly.

---
description: Postgres 17 SQL/JSON JSON_TABLE Memory Boundary and Work-Mem Spill Prevention Guardrail - Enforces deterministic batch chunking, explicit projection boundaries, and work_mem allocation guards for Postgres 17 JSON_TABLE transformations in CDC ingest paths. Eliminates silent temp-file disk thrashing by preventing cardinality misestimates and unbounded hash joins from exceeding memory limits.
globs: src/db/**/*.ts, src/ingest/**/*.ts, src/cdc/**/*.ts, src/repositories/**/*.ts
alwaysApply: true
verified_by: "agentrules.org"
license: "CC-BY-4.0"
source: "https://agentrules.org/rules/postgres-17-json-table-work-mem-spill-prevention"
---

# Postgres 17 SQL/JSON JSON_TABLE Memory Boundary and Work-Mem Spill Prevention Guardrail

> **Severity**: CRITICAL | **Category**: backend
> **Anti-Pattern Warning**: Unbounded Dynamic JSON_TABLE Projection Spilling to Temp Disk Files During CDC Ingestion

## System Constraints
You are a Principal Software Architect enforcing Postgres 17 SQL/JSON JSON_TABLE Work-Mem Spilling Prevention Guardrail. Mandate bounded batch sizes, localized transaction work_mem configurations, strictly typed column definitions with explicit length constraints, and temp-file monitoring assertions across all CDC ingestion paths.

## Strict Guidelines
- Engineers MUST ALWAYS chunk dynamic JSON payloads into bounded micro-batches (maximum 1,000 items per array) prior to executing JSON_TABLE queries to prevent the query planner from underestimating row counts.
- Engineers MUST ALWAYS declare explicit column lengths (e.g., VARCHAR(64) instead of unbounded TEXT) and explicit NULL ON ERROR / EMPTY ON ERROR clauses inside the JSON_TABLE COLUMNS clause to avoid internal string re-allocations.
- Engineers MUST NOT execute JSON_TABLE over deep nested paths with CROSS JOINs against large relational tables without a materialization barrier or indexed key constraints.
- Services MUST scope SET LOCAL work_mem dynamically within the CDC transaction boundary when large transformations are unavoidable, ensuring memory allocations revert immediately after commit.
- Components NEVER allow unbounded batch processing without querying pg_stat_database.temp_bytes or verifying EXPLAIN (ANALYZE, BUFFERS) produces zero temporary file writes (Disk: 0 kB) in integration suites.

## Bad vs Good Implementations
### Ingesting CDC Batch Payloads via Postgres 17 JSON_TABLE

#### Bad Antipattern (AI Common Hallucination)
> **Why it fails**: Feeding an unbounded JSON array into JSON_TABLE without batching causes severe planner cardinality misestimates. Default work_mem (typically 4MB) is exceeded during the hash join with tenant_configs, triggering silent spill-to-disk (temp_files writes), saturating I/O and stalling CDC consumer lag.
```typescript
import { Pool } from 'pg';

interface CdcEvent {
  payload: string; // Serialized JSON array of 50,000 items
}

export async function processCdcBatch(pool: Pool, event: CdcEvent): Promise<void> {
  // ANTI-PATTERN: Ingesting raw unbounded payload using default work_mem.
  // Postgres planner defaults JSON_TABLE row estimate to 100 or 1,000 rows,
  // causing Hash Join / Sort to spill gigabytes to disk temp files.
  await pool.query(`
    INSERT INTO target_events (event_id, tenant_id, payload_data, ingested_at)
    SELECT 
      jt.id,
      jt.tenant_id,
      jt.data,
      NOW()
    FROM JSON_TABLE(
      $1::jsonb, '$[*]'
      COLUMNS (
        id text PATH '$.id',
        tenant_id text PATH '$.tenant_id',
        data jsonb PATH '$.data'
      )
    ) AS jt
    LEFT JOIN tenant_configs tc ON tc.tenant_id = jt.tenant_id;
  `, [event.payload]);
}
```

#### Production Standard (Correct)
> **Standard**: The payload is sliced into deterministic chunks of 1,000 records. A transaction-local `SET LOCAL work_mem` provides sufficient headroom for hash joins without persisting across connections. Using `WITH ... AS MATERIALIZED` and explicit column types (`VARCHAR(36)`, `VARCHAR(64)`) enforces correct memory footprint estimation and eliminates temp-file spills.
```typescript
import { Pool, PoolClient } from 'pg';

interface CdcRecord {
  id: string;
  tenant_id: string;
  data: Record<string, unknown>;
}

const BATCH_CHUNK_SIZE = 1_000;

export async function processCdcBatch(
  client: PoolClient,
  records: CdcRecord[]
): Promise<void> {
  // Invariant 1: Micro-batch payloads to keep working memory strictly deterministic
  for (let i = 0; i < records.length; i += BATCH_CHUNK_SIZE) {
    const chunk = records.slice(i, i + BATCH_CHUNK_SIZE);
    
    await client.query('BEGIN');
    try {
      // Invariant 2: Localized transaction-scoped work_mem prevents global memory exhaustion
      await client.query("SET LOCAL work_mem = '32MB'");

      // Invariant 3: Explicit column types with constraints prevent uncontrolled memory projection
      // Invariant 4: EMPTY ON ERROR ensures parsing failures don't leak unhandled exceptions
      await client.query(`
        WITH parsed_batch AS MATERIALIZED (
          SELECT 
            jt.id,
            jt.tenant_id,
            jt.data
          FROM JSON_TABLE(
            $1::jsonb, '$[*]'
            COLUMNS (
              id VARCHAR(36) PATH '$.id' ERROR ON ERROR,
              tenant_id VARCHAR(64) PATH '$.tenant_id' ERROR ON ERROR,
              data JSONB PATH '$.data' EMPTY ON ERROR
            )
          ) AS jt
        )
        INSERT INTO target_events (event_id, tenant_id, payload_data, ingested_at)
        SELECT 
          pb.id,
          pb.tenant_id,
          pb.data,
          NOW()
        FROM parsed_batch pb
        INNER JOIN tenant_configs tc ON tc.tenant_id = pb.tenant_id;
      `, [JSON.stringify(chunk)]);

      await client.query('COMMIT');
    } 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
Enables inspection of pg_stat_database temp_bytes, active locks, and query explain plans within developer agents.
Recommended Agent Skills (askills.dev)

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

Postgres Query Plan & Buffer Checker
CLI skill that parses EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) outputs to assert temp_written_blocks is 0.
Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 SQL/JSON JSON_TABLE Memory Boundary and Work-Mem Spill Prevention Guardrail

Postgres 17 SQL/JSON JSON_TABLE Memory Boundary and Work-Mem Spill Prevention Guardrail defines an industrial-grade behavioral standard for backend engineering. Enforces deterministic batch chunking, explicit projection boundaries, and work_mem allocation guards for Postgres 17 JSON_TABLE transformations in CDC ingest paths. Eliminates silent temp-file disk thrashing by preventing cardinality misestimates and unbounded hash joins from exceeding memory limits. 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 Dynamic JSON_TABLE Projection Spilling to Temp Disk Files During CDC Ingestion 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 Dynamic JSON_TABLE Projection Spilling to Temp Disk Files During CDC IngestionCritical
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 chunk dynamic JSON payloads into bounded micro-batches (maximum 1,000 items per array) prior to executing JSON_TABLE queries to prevent the query planner from underestimating row counts.

Engineers MUST ALWAYS chunk dynamic JSON payloads into bounded micro-batches (maximum 1,000 items per array) prior to executing JSON_TABLE queries to prevent the query planner from underestimating row counts.

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 ALWAYS declare explicit column lengths (e.g., VARCHAR(64) instead of unbounded TEXT) and explicit NULL ON ERROR / EMPTY ON ERROR clauses inside the JSON_TABLE COLUMNS clause to avoid internal string re-allocations.

Engineers MUST ALWAYS declare explicit column lengths (e.g., VARCHAR(64) instead of unbounded TEXT) and explicit NULL ON ERROR / EMPTY ON ERROR clauses inside the JSON_TABLE COLUMNS clause to avoid internal string re-allocations.

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 JSON_TABLE over deep nested paths with CROSS JOINs against large relational tables without a materialization barrier or indexed key constraints.

Engineers MUST NOT execute JSON_TABLE over deep nested paths with CROSS JOINs against large relational tables without a materialization barrier or indexed key constraints.

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 scope SET LOCAL work_mem dynamically within the CDC transaction boundary when large transformations are unavoidable, ensuring memory allocations revert immediately after commit.

Services MUST scope SET LOCAL work_mem dynamically within the CDC transaction boundary when large transformations are unavoidable, ensuring memory allocations revert immediately after commit.

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 allow unbounded batch processing without querying pg_stat_database.temp_bytes or verifying EXPLAIN (ANALYZE, BUFFERS) produces zero temporary file writes (Disk

Components NEVER allow unbounded batch processing without querying pg_stat_database.temp_bytes or verifying EXPLAIN (ANALYZE, BUFFERS) produces zero temporary file writes (Disk: 0 kB) in integration suites.

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 SQL/JSON JSON_TABLE Memory Boundary and Work-Mem Spill Prevention Guardrail critical for AI coding assistants?

Enforces deterministic batch chunking, explicit projection boundaries, and work_mem allocation guards for Postgres 17 JSON_TABLE transformations in CDC ingest paths. Eliminates silent temp-file disk thrashing by preventing cardinality misestimates and unbounded hash joins from exceeding memory limits. 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 Dynamic JSON_TABLE Projection Spilling to Temp Disk Files During CDC Ingestion) 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