PostgreSQL 17 Subtransaction Cache Overflow Defense Standard

--- description: PostgreSQL 17 Subtransaction Cache Overflow Defense Standard - Prevents catastrophic ProcArrayLock and SubtransSLRU latch contention across all concurrent connections caused by exceeding PGPROC_MAX_CACHED_SUBXIDS (64). Mandates strict avoidance of iterative savepoints, nested ORM rollback scopes in loops, and requires atomic batching or partitioned transactional boundaries. globs: src/**/*.{ts,js,py,go,sql} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-subtransaction-cache-overflow-defense-standard" --- # PostgreSQL 17 Subtransaction Cache Overflow Defense Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Loop-Scoped Savepoints and Deeply Nested ORM Subtransactions Inducing Subtrans SLRU Lock Saturation ## System Constraints You are a Principal Backend and Database Reliability Engineer enforcing the PostgreSQL 17 Subtransaction Cache Overflow Defense Standard. In PostgreSQL, each backend process maintains an in-memory cache of up to 64 active subtransaction IDs (PGPROC.subxids). When subtransactions exceed 64, PGPROC.subxid_overflowed becomes true, forcing every concurrent backend doing snapshot generation or visibility checks (TransactionIdIsInProgress/HeapTupleSatisfiesMVCC) to serialize on the SubtransSLRU and heavily contend for ProcArrayLock. You must identify and eliminate all patterns that generate dynamic, nested, or loop-based savepoints, enforcing atomic multi-row operations, deterministic idempotent UPSERTs, or chunked top-level transactions instead. ## Strict Guidelines - Engineers MUST NEVER execute SAVEPOINT, begin_nested(), or nested ORM subtransactions inside iterative loops, batch processors, or high-concurrency ingestion pipelines. - Engineers MUST NEVER exceed a strict maximum nesting depth of 1 subtransaction (e.g., a single isolated savepoint for expected benign conflict recovery) within any active transaction. - Engineers MUST replace iterative catch-and-continue savepoints with atomic native set-based operations (e.g., INSERT ... ON CONFLICT DO NOTHING / UPDATE) or pre-flight validation. - Code executing batch mutations MUST chunk input into isolated, top-level atomic transactions committed per chunk rather than wrapping thousands of loop iterations with savepoints inside a single transaction. - Services MUST monitor pg_stat_slru where name = 'subtrans' for spikes in blocks_read, blocks_written, and lock waits indicative of subtransaction cache overflow. ## Bad vs Good Implementations ### Batch Ingestion with Loop-Level Savepoints vs. Atomic Vectorized Upsert #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a savepoint per item in a loop exceeds PostgreSQL's 64-item subtransaction cache whenever records.length > 64. When overflowed, every subsequent visibility check by any backend must walk the pg_subtrans SLRU via shared memory locks, collapsing database-wide throughput. ```typescript import { PoolClient } from 'pg'; interface RecordPayload { id: string; tenantId: string; data: string; } export async function ingestRecordsBad( client: PoolClient, records: RecordPayload[] ): Promise<{ succeeded: string[]; failed: string[] }> { const succeeded: string[] = []; const failed: string[] = []; // CRITICAL ANTI-PATTERN: Wrapping >64 iterations with savepoints inside a single outer transaction. // This causes PGPROC_MAX_CACHED_SUBXIDS overflow, triggering SubtransSLRU thrashing and // global ProcArrayLock contention for all concurrent PostgreSQL backends. await client.query('BEGIN'); try { for (let i = 0; i < records.length; i++) { const record = records[i]; const spName = `sp_${i}`; await client.query(`SAVEPOINT ${spName}`); try { await client.query( 'INSERT INTO tenant_records (id, tenant_id, payload) VALUES ($1, $2, $3)', [record.id, record.tenantId, record.data] ); await client.query(`RELEASE SAVEPOINT ${spName}`); succeeded.push(record.id); } catch (err) { await client.query(`ROLLBACK TO SAVEPOINT ${spName}`); failed.push(record.id); } } await client.query('COMMIT'); } catch (err) { await client.query('ROLLBACK'); throw err; } return { succeeded, failed }; } ``` #### Production Standard (Correct) > **Standard**: Replaces iterative subtransactions with atomic, chunked set-based queries using UNNEST() and ON CONFLICT. This generates zero subtransactions, guarantees PGPROC.subxid_overflowed remains false, and keeps visibility checks bounded strictly to the local ProcArray snapshot. ```typescript import { PoolClient } from 'pg'; interface RecordPayload { id: string; tenantId: string; data: string; } export async function ingestRecordsGood( client: PoolClient, records: RecordPayload[], chunkSize = 500 ): Promise<{ totalProcessed: number }> { // Invariant Guardrail: Zero dynamic savepoints. Eliminate subxid cache allocation completely. // Batch records using Postgres native unnest() with ON CONFLICT resolution. let totalProcessed = 0; for (let i = 0; i < records.length; i += chunkSize) { const chunk = records.slice(i, i + chunkSize); const ids = chunk.map((r) => r.id); const tenantIds = chunk.map((r) => r.tenantId); const payloads = chunk.map((r) => r.data); await client.query('BEGIN'); try { const query = ` INSERT INTO tenant_records (id, tenant_id, payload) SELECT * FROM UNNEST($1::uuid[], $2::uuid[], $3::jsonb[]) ON CONFLICT (id) DO UPDATE SET payload = EXCLUDED.payload, updated_at = NOW(); `; const result = await client.query(query, [ids, tenantIds, payloads]); await client.query('COMMIT'); totalProcessed += result.rowCount ?? 0; } catch (err) { await client.query('ROLLBACK'); throw err; } } return { totalProcessed }; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Subtransaction Cache Overflow Defense Standard

PostgreSQL 17 Subtransaction Cache Overflow Defense Standard defines an industrial-grade behavioral standard for backend engineering. Prevents catastrophic ProcArrayLock and SubtransSLRU latch contention across all concurrent connections caused by exceeding PGPROC_MAX_CACHED_SUBXIDS (64). Mandates strict avoidance of iterative savepoints, nested ORM rollback scopes in loops, and requires atomic batching or partitioned transactional boundaries. 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: Loop-Scoped Savepoints and Deeply Nested ORM Subtransactions Inducing Subtrans SLRU Lock Saturation 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	Loop-Scoped Savepoints and Deeply Nested ORM Subtransactions Inducing Subtrans SLRU Lock Saturation	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 NEVER execute SAVEPOINT, begin_nested(), or nested ORM subtransactions inside iterative loops, batch processors, or high-concurrency ingestion pipelines.

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.