PostgreSQL 17 Failover Slot Sync, Streaming Decoding, and Buffer Invariants

--- description: PostgreSQL 17 Failover Slot Sync, Streaming Decoding, and Buffer Invariants - Enforces PostgreSQL 17 logical replication and memory architecture guardrails across database orchestrators and migration scripts. Mandates failover-enabled replication slots synchronized to physical standbys, enforces controlled streaming modes to eliminate disk spillage during logical decoding, and guarantees strict bounds on shared_buffers, logical_decoding_work_mem, and max_slot_wal_keep_size to prevent primary cluster exhaustion. globs: src/database/**/*.{ts,js,sql}, src/infra/**/*.{ts,js,tf}, scripts/db/**/*.{ts,sh,sql} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-buffer-invariants" --- # PostgreSQL 17 Failover Slot Sync, Streaming Decoding, and Buffer Invariants > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Unbounded Logical Replication Slots Causing WAL Exhaustion and Failover Data Loss ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 Logical Replication and Memory Buffer invariants. You must reject any configuration, migration, or programmatic replication provisioning that creates logical replication slots without PostgreSQL 17 failover synchronization (failover = true), fails to pin synchronized_standby_slots, allows unbounded WAL retention via unset max_slot_wal_keep_size, or omits streaming mode configurations. Ensure shared_buffers (25% system RAM ceiling), logical_decoding_work_mem, and maintenance_work_mem are strictly budgeted against peak concurrency to prevent out-of-memory kernel panics. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover => true` in PostgreSQL 17 to permit slot retention across physical standby failover. - Engineers MUST ALWAYS configure `synchronized_standby_slots` on the primary instance to include physical replication standbys before promoting or switching over downstream logical subscribers. - Engineers MUST ALWAYS configure `max_slot_wal_keep_size` (e.g., 32GB to 64GB) to avoid catastrophic disk exhaustion on the primary when logical subscribers lag or disconnect. - Services MUST enable subscription streaming (`streaming = 'parallel'` or `streaming = 'on'`) in PostgreSQL 17 to bypass serial transaction decoding and prevent massive uncommitted transactions from spooling to disk. - Engineers MUST explicitly bound `logical_decoding_work_mem` (e.g., 64MB - 256MB) per decoding session rather than relying on default unbounded spill thresholds. - Database configurations MUST NOT set `shared_buffers` greater than 25% to 40% of total host RAM to avoid double-buffering cache penalties with the Linux page cache and maintain sufficient memory for decoding pipelines. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Replication Provisioning & Failover Slot Safety #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creates a legacy logical slot without `failover => true`, leaving the slot vulnerable to total loss upon primary standby promotion. Omits `max_slot_wal_keep_size` bounds, creating an unmonitored risk of primary disk saturation. Subscribes without PostgreSQL 17 streaming or failover sync attributes. ```typescript import { Client } from 'pg'; export async function setupLogicalReplication(client: Client, slotName: string, pubName: string): Promise<void> { // Anti-pattern: Pre-PG17 style slot creation without failover support // Missing max_slot_wal_keep_size protections, causing unbounded WAL growth if subscriber dies await client.query(`CREATE PUBLICATION ${pubName} FOR ALL TABLES;`); await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); // Subscribing without streaming enables huge disk spillage for large transactions // and fails over to primary without slot synchronization const subQuery = ` CREATE SUBSCRIPTION sub_${slotName} CONNECTION 'host=db-primary dbname=app user=rep_user' PUBLICATION ${pubName} WITH (copy_data = true); `; await client.query(subQuery); } ``` #### Production Standard (Correct) > **Standard**: Implements PostgreSQL 17 native failover slot semantics (`failover => true`), bounds WAL accumulation using `max_slot_wal_keep_size`, and enables `streaming = 'parallel'` to maximize subscriber throughput while protecting host memory buffers. ```typescript import { Client } from 'pg'; export interface ReplicationSlotConfig { slotName: string; pubName: string; primaryConnString: string; maxWalKeepMb: number; } export async function setupLogicalReplication( client: Client, config: ReplicationSlotConfig ): Promise<void> { const { slotName, pubName, primaryConnString, maxWalKeepMb } = config; // Enforce WAL retention ceiling to safeguard primary storage await client.query(`ALTER SYSTEM SET max_slot_wal_keep_size = '${maxWalKeepMb}MB';`); await client.query(`SELECT pg_reload_conf();`); // Create publication for isolated workloads await client.query(`CREATE PUBLICATION ${pubName} FOR ALL TABLES;`); // PG17 Invariant: Create logical replication slot with failover = true // Ensures slot is synchronized to physical standby slots via pg_sync_replication_slots() await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, two_phase => true, failover => true );`, [slotName] ); // PG17 Invariant: Enable parallel streaming & failover synchronization on subscription const subQuery = ` CREATE SUBSCRIPTION sub_${slotName} CONNECTION '${primaryConnString}' PUBLICATION ${pubName} WITH ( copy_data = true, streaming = 'parallel', failover = true, create_slot = false, slot_name = '${slotName}' ); `; await client.query(subQuery); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Slot Sync, Streaming Decoding, and Buffer Invariants

PostgreSQL 17 Failover Slot Sync, Streaming Decoding, and Buffer Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 logical replication and memory architecture guardrails across database orchestrators and migration scripts. Mandates failover-enabled replication slots synchronized to physical standbys, enforces controlled streaming modes to eliminate disk spillage during logical decoding, and guarantees strict bounds on shared_buffers, logical_decoding_work_mem, and max_slot_wal_keep_size to prevent primary cluster exhaustion. 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: Unsynchronized Unbounded Logical Replication Slots Causing WAL Exhaustion and Failover Data Loss 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	Unsynchronized Unbounded Logical Replication Slots Causing WAL Exhaustion and Failover Data Loss	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 provision logical replication slots with `failover => true` in PostgreSQL 17 to permit slot retention across physical standby failover.

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.