PostgreSQL 17 Logical Decoding Spill-to-Disk Memory Ceiling Invariant

--- description: PostgreSQL 17 Logical Decoding Spill-to-Disk Memory Ceiling Invariant - Enforces strict memory ceilings on PostgreSQL 17 logical decoding walsender processes via logical_decoding_work_mem and streaming pgoutput protocol parameters. Prevents Linux OOM-killer invocations and cascading failovers by forcing large in-flight transactions to spill to disk or stream in chunks to downstream CDC consumers. Mandates downstream stream-frame handling and storage quota guardrails on replication slots. globs: src/cdc/**/*.ts, src/database/**/*.ts, src/infra/**/*.ts, src/replication/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-logical-decoding-spill-ceiling" --- # PostgreSQL 17 Logical Decoding Spill-to-Disk Memory Ceiling Invariant > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unbounded WalSender Shared Memory Buffering During Large Bulk DML Mutations ## System Constraints You are a Principal Engineer enforcing PostgreSQL 17 Logical Decoding Spill-to-Disk Memory Ceiling Invariants. Mandate strict memory limits on walsender decoding processes, enable streaming replication in pgoutput to prevent memory spikes during high-volume DML, handle stream transaction framing protocol tokens, and continuously assert pg_stat_replication_slots spill telemetry. ## Strict Guidelines - Engineers MUST ALWAYS configure `logical_decoding_work_mem` to an explicit ceiling (minimum 64MB, maximum 256MB) per walsender session, never leaving it unconfigured or inflating it to absorb large transactions. - Engineers MUST ALWAYS enable `streaming = 'on'` or `streaming = 'parallel'` in pgoutput plugin options for PostgreSQL 17 logical replication streams to enable chunked decoding for in-flight transactions. - Downstream CDC ingestion consumers MUST implement protocol handlers for logical replication streaming frames (`STREAM_START`, `STREAM_STOP`, `STREAM_ABORT`, `STREAM_COMMIT`) instead of assuming atomic whole-transaction frames. - Services MUST monitor `pg_stat_replication_slots` for `spill_txns`, `spill_count`, and `spill_bytes`, alerting when spill rates indicate unbatched write anti-patterns in upstream application code. - Components NEVER issue long-running multi-gigabyte monolithic batch transactions on logical-replication-enabled tables; bulk operations MUST be chunked upstream into deterministic batch windows. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Replication Connection and Stream Framing #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting explicit logical_decoding_work_mem and failing to enable pgoutput streaming causes the PostgreSQL walsender process to buffer all changes for massive bulk transactions in RAM. When transactions exceed available OS memory, the kernel OOM killer terminates the primary PostgreSQL postmaster process. ```typescript import { Client } from 'pg'; // Flawed configuration omitting logical_decoding_work_mem and stream chunking export async function initCdcStream(client: Client, slotName: string) { // Default session memory allows uncontrolled walsender heap growth // Omitting 'streaming' option buffers full multi-GB transactions in walsender memory await client.query(` START_REPLICATION SLOT ${slotName} LOGICAL 0/0 ( proto_version '1', publication_names 'cdc_publication' ); `); client.on('copyData', (chunk: Buffer) => { // Assumes whole-transaction framing; fails completely or OOMs during bulk DML const messageType = String.fromCharCode(chunk[0]); if (messageType === 'B') { console.log('BEGIN transaction'); } else if (messageType === 'C') { console.log('COMMIT transaction'); } }); } ``` #### Production Standard (Correct) > **Standard**: Enforces logical_decoding_work_mem within safe thresholds (64MB-256MB) and specifies proto_version 4 with streaming 'on'. In PostgreSQL 17, transactions exceeding this memory boundary stream changes directly or spill deterministically to disk, protecting the primary cluster from OOM worker terminations. ```typescript import { Client } from 'pg'; interface LogicalReplicationConfig { slotName: string; publicationName: string; decodingWorkMemKb: number; // Max ceiling 64MB-256MB (e.g., 65536) } export async function initCdcStream(client: Client, config: LogicalReplicationConfig): Promise<void> { const memoryCeilingKb = Math.min(Math.max(config.decodingWorkMemKb, 65536), 262144); // Invariant 1: Enforce session-level decoding memory ceiling before starting replication await client.query(`SET logical_decoding_work_mem = '${memoryCeilingKb}kB';`); // Invariant 2: Enable pgoutput protocol v4 with streaming enabled to spill/stream chunks const startReplSql = ` START_REPLICATION SLOT ${config.slotName} LOGICAL 0/0 ( proto_version '4', publication_names '${config.publicationName}', streaming 'on' ); `; await client.query(startReplSql); client.on('copyData', (chunk: Buffer) => { const messageType = String.fromCharCode(chunk[0]); // Invariant 3: Handle stream-chunk frames for transactions exceeding memory ceiling switch (messageType) { case 'B': // Standard Begin case 'C': // Standard Commit break; case 'S': // STREAM START (PG14+ pgoutput) // Transaction payload streamed in chunks without inflating walsender RSS break; case 'E': // STREAM STOP break; case 'T': // STREAM COMMIT break; case 'A': // STREAM ABORT break; default: break; } }); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Logical Decoding Spill-to-Disk Memory Ceiling Invariant

PostgreSQL 17 Logical Decoding Spill-to-Disk Memory Ceiling Invariant defines an industrial-grade behavioral standard for backend engineering. Enforces strict memory ceilings on PostgreSQL 17 logical decoding walsender processes via logical_decoding_work_mem and streaming pgoutput protocol parameters. Prevents Linux OOM-killer invocations and cascading failovers by forcing large in-flight transactions to spill to disk or stream in chunks to downstream CDC consumers. Mandates downstream stream-frame handling and storage quota guardrails on replication slots. 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 WalSender Shared Memory Buffering During Large Bulk DML Mutations 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	Unbounded WalSender Shared Memory Buffering During Large Bulk DML Mutations	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 configure `logical_decoding_work_mem` to an explicit ceiling (minimum 64MB, maximum 256MB) per walsender session, never leaving it unconfigured or inflating it to absorb large transactions.

Engineers MUST ALWAYS configure `logical_decoding_work_mem` to an explicit ceiling (minimum 64MB, maximum 256MB) per walsender session, never leaving it unconfigured or inflating it to absorb large transactions.

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.