Postgres 17 Logical Decoding Spooling & Work Memory Quota Enforcement

Enforces strict memory boundary quotas (logical_decoding_work_mem) and batch chunking invariants to prevent catastrophic primary node Out-Of-Memory (OOM) cascades during logical replication. Uncommitted transactions in write-heavy workloads accumulate changes in the PostgreSQL ReorderBuffer; without bounded work memory and deterministic spill-to-disk policies, walsender processes exhaust host virtual memory. Applications and migrations must enforce bounded mutation transaction sizes and verify replication slot spill telemetry.

--- description: Postgres 17 Logical Decoding Spooling & Work Memory Quota Enforcement - Enforces strict memory boundary quotas (logical_decoding_work_mem) and batch chunking invariants to prevent catastrophic primary node Out-Of-Memory (OOM) cascades during logical replication. Uncommitted transactions in write-heavy workloads accumulate changes in the PostgreSQL ReorderBuffer; without bounded work memory and deterministic spill-to-disk policies, walsender processes exhaust host virtual memory. Applications and migrations must enforce bounded mutation transaction sizes and verify replication slot spill telemetry. globs: src/**/*.{ts,js,sql}, migrations/**/*.{ts,sql}, infra/**/*.{tf,conf,sql} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-decoding-memory-spooling-quota" --- # Postgres 17 Logical Decoding Spooling & Work Memory Quota Enforcement > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Executing unchunked bulk DML inside long-lived transactions under unbounded logical_decoding_work_mem, exhausting walsender memory and inducing primary OOM crashes ## System Constraints You are a Principal Software Architect enforcing Postgres 17 Logical Decoding Memory Spooling & Work_mem Quota Standards. You must prevent memory exhaustion on write-heavy primary databases running logical replication (CDC, Debezium, pglogical). Mandate explicit setting of `logical_decoding_work_mem`, strictly require chunked transactional execution for data migrations and bulk operations, and enforce automated telemetry validation over `pg_stat_replication_slots` to guarantee disk spooling without system thrashing. ## Strict Guidelines - Engineers MUST ALWAYS set an explicit `logical_decoding_work_mem` (typically 64MB to 256MB) globally or on replication connection roles, avoiding default over-allocation across concurrent walsenders. - Engineers MUST NOT execute multi-row bulk updates or deletes in an unbounded single transaction when logical replication slots are active. - Code MUST batch bulk mutations into discrete, bounded transaction chunks (e.g., 1,000–5,000 rows per transaction) to prevent ReorderBuffer bloat in walsender processes. - Services MUST monitor `spill_txns`, `spill_count`, and `spill_bytes` in `pg_stat_replication_slots` to detect abnormal spooling behavior and lag buildup. - Components NEVER hold open transactions during external RPC calls or uncommitted bulk data generation, as logical decoding cannot advance the oldest transaction LSN until commit. ## Bad vs Good Implementations ### Bulk Mutation Invariant: Unbounded Transaction vs. Bounded Chunked Migration #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Executing an unbounded multi-row DELETE or UPDATE in a single transaction fills the logical decoding ReorderBuffer with change events for millions of rows. Walsender processes attempt to maintain the assembly in RAM until hitting memory limits, causing memory spikes and leading to kernel OOM killing the Postgres primary. ```typescript import { Pool } from 'pg'; export async function archiveOldEvents(pool: Pool, cutoffDate: Date): Promise<void> { const client = await pool.connect(); try { // ANTI-PATTERN: Single massive transaction modifying millions of rows. // In PostgreSQL 17 with active logical slots (e.g., Debezium, Kafka CDC), // all uncommitted changes accumulate in walsender ReorderBuffer. // If logical_decoding_work_mem is breached or unconstrained, // it triggers severe OOM cascades or uncontrolled disk thrashing on the primary. await client.query('BEGIN;'); const res = await client.query( 'DELETE FROM audit_events WHERE created_at < $1 RETURNING id;', [cutoffDate] ); console.log(`Archived ${res.rowCount} records in a single transaction.`); await client.query('COMMIT;'); } catch (err) { await client.query('ROLLBACK;'); throw err; } finally { client.release(); } } ``` #### Production Standard (Correct) > **Standard**: The mutation is chunked into bounded iterations using explicit LIMITs and immediate commits. Walsender processes process discrete transactions without exhausting logical_decoding_work_mem, and replication slot spool telemetry is inspected to apply backpressure if spill thresholds are exceeded. ```typescript import { Pool } from 'pg'; interface BatchOptions { batchSize?: number; cutoffDate: Date; maxLagBytesThreshold?: number; } export async function archiveOldEventsSafely( pool: Pool, options: BatchOptions ): Promise<number> { const batchSize = options.batchSize ?? 2500; let totalDeleted = 0; let rowsProcessedInBatch = 0; do { const client = await pool.connect(); try { // ENFORCED INVARIANT: // 1. Bound every mutation to a small transaction slice. // 2. Commit immediately so walsenders can decode and release buffer memory without spool pressure. await client.query('BEGIN;'); const result = await client.query( `WITH target AS ( SELECT ctid FROM audit_events WHERE created_at < $1 LIMIT $2 FOR UPDATE ) DELETE FROM audit_events WHERE ctid IN (SELECT ctid FROM target);`, [options.cutoffDate, batchSize] ); await client.query('COMMIT;'); rowsProcessedInBatch = result.rowCount ?? 0; totalDeleted += rowsProcessedInBatch; // Check replication slot spooling metrics before next batch const slotCheck = await client.query( `SELECT slot_name, spill_txns, spill_bytes FROM pg_stat_replication_slots WHERE spill_bytes > $1;`, [100 * 1024 * 1024] // 100MB threshold ); if (slotCheck.rows.length > 0) { // Backoff to allow walsender memory release / spool draining await new Promise((resolve) => setTimeout(resolve, 200)); } } catch (err) { await client.query('ROLLBACK;'); throw err; } finally { client.release(); } } while (rowsProcessedInBatch === batchSize); return totalDeleted; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: Postgres 17 Logical Decoding Spooling & Work Memory Quota Enforcement

Postgres 17 Logical Decoding Spooling & Work Memory Quota Enforcement defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory boundary quotas (logical_decoding_work_mem) and batch chunking invariants to prevent catastrophic primary node Out-Of-Memory (OOM) cascades during logical replication. Uncommitted transactions in write-heavy workloads accumulate changes in the PostgreSQL ReorderBuffer; without bounded work memory and deterministic spill-to-disk policies, walsender processes exhaust host virtual memory. Applications and migrations must enforce bounded mutation transaction sizes and verify replication slot spill telemetry. 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 unchunked bulk DML inside long-lived transactions under unbounded logical_decoding_work_mem, exhausting walsender memory and inducing primary OOM crashes 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 unchunked bulk DML inside long-lived transactions under unbounded logical_decoding_work_mem, exhausting walsender memory and inducing primary OOM crashes	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 set an explicit `logical_decoding_work_mem` (typically 64MB to 256MB) globally or on replication connection roles, avoiding default over-allocation across concurrent walsenders.

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.