Postgres 17 JSON_TABLE Projection Pushdown and SIMD Scan Optimization

--- description: Postgres 17 JSON_TABLE Projection Pushdown and SIMD Scan Optimization - Postgres 17 introduces SQL:2023 JSON_TABLE capabilities which allow tabular projection of jsonb data. When combined with functional indexes or targeted expressions, queries must be structured to ensure projection predicates push down to the storage engine (heap or index scan) rather than forcing full document retrieval and CPU-intensive serial JSON deserialization across thousands of rows. globs: src/db/**/*.ts, src/repositories/**/*.ts, src/queries/**/*.sql, migrations/**/*.sql alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-json-table-vectorized-pushdown-guardrail" --- # Postgres 17 JSON_TABLE Projection Pushdown and SIMD Scan Optimization > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unindexed JSON_TABLE Path Projections Triggering Per-Row CPU Deserialization and Memory Spills ## System Constraints You are a Principal Database Architect and Backend Systems Engineer enforcing Postgres 17 JSON_TABLE performance invariants. You must prevent patterns that materialize the full jsonb document or force serial CPU deserialization during query evaluation. Ensure queries using JSON_TABLE either leverage matched functional/expression indexes (like jsonb_path_ops GIN or specific B-Tree expression indexes) or permit the planner to push filter predicates down before JSON projection. Reject any PR or query construction that joins, filters, or scans large jsonb collections without verifying explain plans for heap/index pushdown. ## Strict Guidelines - Engineers MUST ALWAYS match JSON_TABLE column path expressions to corresponding B-Tree expression indexes or GIN jsonb_path_ops indexes when filtering high-cardinality fields. - Engineers MUST NOT apply WHERE clause predicates on derived JSON_TABLE columns that prevent the planner from pushing the condition down into the underlying base table index scan. - Code SHOULD enforce strict type-safe projection within JSON_TABLE using explicit column types and omit default 'EMPTY ON ERROR' handling unless required, preventing silent fallback to runtime evaluation. - Services MUST verify via EXPLAIN (ANALYZE, BUFFERS) that execution plans reflect 'Index Scan' or 'Bitmap Index Scan' rather than 'Seq Scan' followed by 'Table Function Scan: JSON_TABLE'. - Components NEVER construct dynamic, unparameterized JSON path expressions inside JSON_TABLE that invalidate prepared statement plan caching and vectorization optimizations. ## Bad vs Good Implementations ### Postgres 17 JSON_TABLE Filter Pushdown via Expression Indexing #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Filtering on `jt.tenant_id` after JSON_TABLE forces Postgres to deserialize the `payload` jsonb column for every row in `audit_logs` before discarding non-matching rows. This wastes CPU cycles on JSON parsing and blows out memory buffers. ```typescript import { sql } from 'kysely'; import { db } from './database'; // ANTI-PATTERN: Scans entire table, projects every row through JSON_TABLE, // and filters post-projection on the derived column 'tenant_id'. // Results in massive CPU deserialization overhead. export async function getAuditLogsByTenant(tenantId: string) { return await sql` SELECT jt.id, jt.event_type, jt.created_at FROM audit_logs a, JSON_TABLE( a.payload, '$' COLUMNS ( id text PATH '$.id', tenant_id text PATH '$.tenant_id', event_type text PATH '$.event_type', created_at timestamptz PATH '$.timestamp' ) ) AS jt WHERE jt.tenant_id = ${tenantId}; `.execute(db); } ``` #### Production Standard (Correct) > **Standard**: By expressing the predicate on the base relation column `a.payload->>'tenant_id'`, Postgres 17 can evaluate the index scan first, pushing the predicate down to the heap scan and executing JSON_TABLE projection only on candidate tuples. ```typescript import { sql } from 'kysely'; import { db } from './database'; // PRODUCTION FIX: Applies pushdown predicate directly to base table using indexed jsonb operator, // ensuring only matching rows are passed to JSON_TABLE for projection. // Requires index: CREATE INDEX idx_audit_logs_tenant ON audit_logs ((payload->>'tenant_id')); export async function getAuditLogsByTenant(tenantId: string) { return await sql` SELECT jt.id, jt.event_type, jt.created_at FROM audit_logs a, JSON_TABLE( a.payload, '$' COLUMNS ( id text PATH '$.id', event_type text PATH '$.event_type', created_at timestamptz PATH '$.timestamp' ) ) AS jt WHERE a.payload->>'tenant_id' = ${tenantId}; `.execute(db); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 JSON_TABLE Projection Pushdown and SIMD Scan Optimization

Postgres 17 JSON_TABLE Projection Pushdown and SIMD Scan Optimization defines an industrial-grade behavioral standard for backend engineering. Postgres 17 introduces SQL:2023 JSON_TABLE capabilities which allow tabular projection of jsonb data. When combined with functional indexes or targeted expressions, queries must be structured to ensure projection predicates push down to the storage engine (heap or index scan) rather than forcing full document retrieval and CPU-intensive serial JSON deserialization across thousands of rows. 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: Unindexed JSON_TABLE Path Projections Triggering Per-Row CPU Deserialization and Memory Spills 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	Unindexed JSON_TABLE Path Projections Triggering Per-Row CPU Deserialization and Memory Spills	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:

Engineers MUST ALWAYS match JSON_TABLE column path expressions to corresponding B-Tree expression indexes or GIN jsonb_path_ops indexes when filtering high-cardinality fields.

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.