Postgres 17 Logical Decoding Memory Quota and CDC Throttling Guardrail

--- description: Postgres 17 Logical Decoding Memory Quota and CDC Throttling Guardrail - Protects Change Data Capture (CDC) replication slots from walsender out-of-memory crashes and disk spill saturation caused by unbounded bulk transactions. Enforces micro-batched transactional writes and backpressure validation against logical_decoding_work_mem limits and pg_stat_replication_slots spill metrics. globs: src/repositories/**/*.ts, src/services/**/*.ts, src/jobs/**/*.ts, src/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-decoding-memory-guardrail" --- # Postgres 17 Logical Decoding Memory Quota and CDC Throttling Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing unbounded multi-row DML in single transactions that breach logical_decoding_work_mem and exhaust replication worker memory ## System Constraints You are a Principal Database Reliability Engineer enforcing strict CDC safety standards for PostgreSQL 17. Massive, uncommitted bulk mutations cause logical decoding workers (walsender) to accumulate changes in memory up to logical_decoding_work_mem before spilling uncommitted transactions to disk, leading to replication lag, severe I/O degradation, and walsender OOM termination. You must reject any code performing unbounded multi-row mutations inside a single transaction and mandate explicit transactional batching, pacing intervals, and replication slot health checks. ## Strict Guidelines - Engineers MUST ALWAYS partition bulk insert, update, and delete operations into discrete transactional chunks of at most 500 to 2,000 records. - Engineers MUST NOT wrap large iterative mutation loops within an overarching ambient transaction. - Services performing bulk data modifications MUST explicitly query pg_stat_replication_slots to verify spill_bytes and spill_txns rate before continuing bulk processing. - Code SHOULD introduce non-blocking micro-delays (e.g., 50-100ms) between transaction chunk commits to allow walsender processes to drain their logical decoding queue. - Bulk migration and backfill scripts MUST verify that target tables with logical replication publications do not generate unindexed TOAST-heavy payload surges that amplify decoding memory overhead. ## Bad vs Good Implementations ### Unbounded Bulk Mutation vs. Paced Chunked Commit with Backpressure #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: All records are committed in a single transaction. For 100,000+ rows, the logical decoding process must track all in-flight changes in memory until COMMIT. This breaches logical_decoding_work_mem, forcing PostgreSQL to spill serialized changes to disk or crash walsender with an out-of-memory error. ```typescript import { PoolClient } from 'pg'; interface BulkPayload { id: string; tenantId: string; metadata: Record<string, unknown>; } export async function ingestEventBatch(client: PoolClient, records: BulkPayload[]): Promise<void> { // ANTI-PATTERN: Wrapping 100,000+ operations in a single atomic transaction. // This saturates logical_decoding_work_mem on Postgres 17 walsender processes, // causing catastrophic disk spills or walsender OOM during replication decoding. await client.query('BEGIN'); try { for (const record of records) { await client.query( 'INSERT INTO event_audit_log (id, tenant_id, payload) VALUES ($1, $2, $3)', [record.id, record.tenantId, JSON.stringify(record.metadata)] ); } await client.query('COMMIT'); } catch (error) { await client.query('ROLLBACK'); throw error; } } ``` #### Production Standard (Correct) > **Standard**: The operation divides the payload into discrete 1,000-row transactions, committing each chunk independently and releasing decoding memory immediately. It queries pg_stat_replication_slots to introduce backpressure when spill thresholds are approached and inserts pacing delays between batches. ```typescript import { Pool, PoolClient } from 'pg'; interface BulkPayload { id: string; tenantId: string; metadata: Record<string, unknown>; } const BATCH_SIZE = 1000; const THROTTLE_DELAY_MS = 50; async function checkReplicationLag(client: PoolClient): Promise<void> { const result = await client.query(` SELECT slot_name, spill_bytes, spill_txns FROM pg_stat_replication_slots WHERE active = true AND spill_bytes > 67108864; -- 64MB alert threshold `); if (result.rows.length > 0) { // Back off when replication slots are spilling excessively await new Promise((resolve) => setTimeout(resolve, 1000)); } } export async function ingestEventBatch(pool: Pool, records: BulkPayload[]): Promise<void> { for (let i = 0; i < records.length; i += BATCH_SIZE) { const chunk = records.slice(i, i + BATCH_SIZE); const client = await pool.connect(); try { await checkReplicationLag(client); await client.query('BEGIN'); const values: unknown[] = []; const placeholders = chunk.map((record, index) => { const offset = index * 3; values.push(record.id, record.tenantId, JSON.stringify(record.metadata)); return `($${offset + 1}, $${offset + 2}, $${offset + 3})`; }).join(', '); await client.query( `INSERT INTO event_audit_log (id, tenant_id, payload) VALUES ${placeholders}`, values ); await client.query('COMMIT'); } catch (error) { await client.query('ROLLBACK'); throw error; } finally { client.release(); } if (i + BATCH_SIZE < records.length) { await new Promise((resolve) => setTimeout(resolve, THROTTLE_DELAY_MS)); } } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Decoding Memory Quota and CDC Throttling Guardrail

Postgres 17 Logical Decoding Memory Quota and CDC Throttling Guardrail defines an industrial-grade behavioral standard for backend engineering. Protects Change Data Capture (CDC) replication slots from walsender out-of-memory crashes and disk spill saturation caused by unbounded bulk transactions. Enforces micro-batched transactional writes and backpressure validation against logical_decoding_work_mem limits and pg_stat_replication_slots spill metrics. 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: Executing unbounded multi-row DML in single transactions that breach logical_decoding_work_mem and exhaust replication worker 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 Mode	AI Symptom (The Anti-Pattern)	Severity	Production Impact & Remedy
AI Hallucination & Deprecation Drift	Executing unbounded multi-row DML in single transactions that breach logical_decoding_work_mem and exhaust replication worker 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 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 partition bulk insert, update, and delete operations into discrete transactional chunks of at most 500 to 2,000 records.

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.