PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Guardrails

--- description: PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Guardrails - Enforces failover-enabled logical replication slots and synchronized standby slot coordination introduced in PostgreSQL 17 to prevent subscriber dropouts during physical failover. Mandates explicit allocation boundaries for logical_decoding_work_mem, max_slot_wal_keep_size, and shared_buffers to prevent buffer cache pollution and catastrophic WAL disk exhaustion. globs: src/database/**/*.ts, src/infra/**/*.ts, src/replication/**/*.ts, migrations/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-failover-slot-buffer-guardrail" --- # PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Guardrails > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating Legacy Non-Failover Logical Slots with Unbounded WAL Retention and Unpinned Buffer Memory ## System Constraints You are a Principal Database Architect enforcing PostgreSQL 17 Logical Replication and Memory Buffer invariants. Ensure all replication slot configurations, DDL operations, and cluster orchestration logic explicitly manage failover slot properties (failover = true, standby_slot_names), constrain logical_decoding_work_mem to prevent shared_buffers eviction or OOM, and clamp max_slot_wal_keep_size to prevent catastrophic disk exhaustion. ## Strict Guidelines - Engineers MUST ALWAYS pass 'failover => true' when invoking 'pg_create_logical_replication_slot' on PostgreSQL 17+ clusters to ensure slot persistence across physical failovers. - Engineers MUST configure 'standby_slot_names' on the primary to enforce synchronized standby confirmation before logical decoders advance LSNs. - Services MUST explicitly enforce 'max_slot_wal_keep_size' to ensure inactive or lagging logical subscribers do not cause catastrophic disk exhaustion. - Code MUST set 'logical_decoding_work_mem' explicitly (typically 64MB to 256MB) per decoding session to prevent large transactions from exhausting OS memory or evicting pages from shared_buffers. - Standby sync workers MUST have 'sync_replication_slots = on' enabled on physical replicas to pull logical slot state continuously from the primary. - Components NEVER drop or recreate logical replication slots without verifying the consumer catalog timeline and WAL retention metrics in 'pg_replication_slots'. ## Bad vs Good Implementations ### Postgres 17 Logical Replication Slot Provisioning & Buffer Boundary Setup #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Legacy logical replication slots in PostgreSQL do not synchronize to physical standbys, causing subscriber breaks when the primary fails over. Omitting explicit limits on logical_decoding_work_mem and max_slot_wal_keep_size exposes the primary to out-of-memory errors and storage starvation if the subscriber lags. ```typescript import { Client } from 'pg'; export async function provisionReplicationPipeline(client: Client, slotName: string, publicationName: string): Promise<void> { // Flaw 1: Legacy slot creation without PG17 failover support - orphaned on primary failover await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); // Flaw 2: Publication created without slot synchronization safeguards await client.query(`CREATE PUBLICATION ${publicationName} FOR ALL TABLES;`); // Flaw 3: Memory left unbounded; default logical_decoding_work_mem can blow past heap // Flaw 4: No max_slot_wal_keep_size protection, leading to WAL accumulation and disk filling } ``` #### Production Standard (Correct) > **Standard**: Leverages PostgreSQL 17 failover-enabled logical slots ('failover => true'), pins logical_decoding_work_mem to prevent unbounded backend memory allocations, enforces max_slot_wal_keep_size against disk overflow, and asserts configuration integrity via pg_replication_slots. ```typescript import { Client } from 'pg'; export interface ReplicationSlotConfig { slotName: string; publicationName: string; decodingWorkMemMb: number; maxSlotWalKeepSizeMb: number; } export async function provisionReplicationPipeline( client: Client, config: ReplicationSlotConfig ): Promise<void> { const { slotName, publicationName, decodingWorkMemMb, maxSlotWalKeepSizeMb } = config; // 1. Bound WAL retention globally/session-level to protect storage if subscriber disconnects await client.query(`SET max_slot_wal_keep_size = '${maxSlotWalKeepSizeMb}MB';`); // 2. Bound decoding work memory to protect shared_buffers from spill pressure await client.query(`SET logical_decoding_work_mem = '${decodingWorkMemMb}MB';`); // 3. Create publication explicitly scoped await client.query(`CREATE PUBLICATION ${publicationName} FOR ALL TABLES;`); // 4. PostgreSQL 17 native failover slot creation: failover => true, two_phase => true await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, two_phase => true, failover => true );`, [slotName] ); // 5. Verify the slot is configured with failover enabled const res = await client.query( `SELECT slot_name, failover, synced, wal_status FROM pg_replication_slots WHERE slot_name = $1;`, [slotName] ); if (!res.rows[0]?.failover) { throw new Error(`Invariant violation: Slot ${slotName} was created without failover capability.`); } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Guardrails

PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Guardrails defines an industrial-grade behavioral standard for backend engineering. Enforces failover-enabled logical replication slots and synchronized standby slot coordination introduced in PostgreSQL 17 to prevent subscriber dropouts during physical failover. Mandates explicit allocation boundaries for logical_decoding_work_mem, max_slot_wal_keep_size, and shared_buffers to prevent buffer cache pollution and catastrophic WAL disk 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: Creating Legacy Non-Failover Logical Slots with Unbounded WAL Retention and Unpinned Buffer 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	Creating Legacy Non-Failover Logical Slots with Unbounded WAL Retention and Unpinned Buffer 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 pass 'failover => true' when invoking 'pg_create_logical_replication_slot' on PostgreSQL 17+ clusters to ensure slot persistence across physical failovers.

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.