PostgreSQL 17 Logical Failover Slots and Shared Buffer Invariants

--- description: PostgreSQL 17 Logical Failover Slots and Shared Buffer Invariants - Enforces architectural guardrails for PostgreSQL 17 streaming logical replication and memory buffer allocation. Mandates the use of PG17 native failover-enabled logical slots with standby synchronization, bounded WAL retention limits, and strict memory budgeting between shared_buffers and logical_decoding_work_mem. globs: src/database/**/*.ts, src/infra/**/*.ts, migrations/**/*.sql, src/services/replication/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-logical-replication-failover-memory-invariants" --- # PostgreSQL 17 Logical Failover Slots and Shared Buffer Invariants > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating Unsynchronized Logical Replication Slots Without Failover Flags or Bounded WAL Retention ## System Constraints You are a Principal Database Infrastructure Engineer and AI Rules Author enforcing PostgreSQL 17 Logical Replication and Memory Buffer invariants. You must prevent data loss during high-availability primary failovers, avoid catastrophic primary disk exhaustion caused by abandoned replication slots, and prevent operating system OOM panics caused by misaligned shared_buffers and logical_decoding_work_mem configurations. Enforce strict adherence to PostgreSQL 17's native failover slot management and connection-level safeguards. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with 'failover = true' on PostgreSQL 17 primary instances intended for HA topologies to ensure slot state syncs to physical standbys. - Engineers MUST ALWAYS define 'max_slot_wal_keep_size' globally and monitor 'wal_status' in 'pg_replication_slots' to invalidate laggy slots rather than letting WAL exhaust primary disk storage. - Engineers MUST NOT allow logical replication subscribers to connect directly without verifying that the primary's 'synchronized_standby_slots' includes the physical standby forwarding the replication stream. - Services MUST explicitly configure 'logical_decoding_work_mem' per slot/session to cap memory utilization when streaming large transactions, ensuring cumulative decoding memory plus 'shared_buffers' (capped at 25-40% total RAM) never exceeds OS physical RAM boundaries. - Components NEVER issue raw 'pg_create_logical_replication_slot' calls with default parameters; they must explicitly pass parameters for (slot_name, plugin, temporary, two_phase, failover). ## Bad vs Good Implementations ### Provisioning Logical Replication Slots with PG17 Failover and WAL Safety #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Legacy creation omission: In PostgreSQL 17, omitting the failover parameter leaves the slot unmanaged by synchronized standbys. If a physical failover occurs, the new primary will not have this slot, forcing a complete and costly table re-sync. It also fails to verify WAL retention constraints. ```typescript import { Client } from 'pg'; export async function setupReplicationSlot(client: Client, slotName: string): Promise<void> { // ANTI-PATTERN: Creates a legacy slot without PG17 failover awareness. // If the primary crashes, standby failover loses the replication cursor, // causing subscriber desync. Unbounded WAL consumption risks disk exhaustion. await client.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName] ); } ``` #### Production Standard (Correct) > **Standard**: The robust implementation verifies the PostgreSQL 17 version boundary, ensures `max_slot_wal_keep_size` is defensively bounded against unbounded WAL accumulation, and passes `failover => true` and `two_phase => true` to guarantee HA standby synchronization. ```typescript import { Client } from 'pg'; export interface ReplicationSlotOptions { slotName: string; plugin?: string; twoPhase?: boolean; failover?: boolean; } export async function setupReplicationSlot( client: Client, options: ReplicationSlotOptions ): Promise<void> { const { slotName, plugin = 'pgoutput', twoPhase = true, failover = true } = options; // INVARIANT: Assert PostgreSQL server version >= 17 for native failover support const versionRes = await client.query('SHOW server_version_num;'); const versionNum = parseInt(versionRes.rows[0].server_version_num, 10); if (versionNum < 170000) { throw new Error(`PostgreSQL 17+ required for native failover slots. Detected: ${versionNum}`); } // INVARIANT: Verify global max_slot_wal_keep_size is finite to prevent primary disk full const walKeepRes = await client.query('SHOW max_slot_wal_keep_size;'); const walKeepSetting = walKeepRes.rows[0].max_slot_wal_keep_size; if (walKeepSetting === '-1') { throw new Error('Unsafe configuration: max_slot_wal_keep_size must be bounded to prevent disk exhaustion'); } // PostgreSQL 17 signature: pg_create_logical_replication_slot(slot_name, plugin, temporary, two_phase, failover) await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => $2, temporary => false, two_phase => $3, failover => $4 );`, [slotName, plugin, twoPhase, failover] ); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Logical Failover Slots and Shared Buffer Invariants

PostgreSQL 17 Logical Failover Slots and Shared Buffer Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces architectural guardrails for PostgreSQL 17 streaming logical replication and memory buffer allocation. Mandates the use of PG17 native failover-enabled logical slots with standby synchronization, bounded WAL retention limits, and strict memory budgeting between shared_buffers and logical_decoding_work_mem. 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 Unsynchronized Logical Replication Slots Without Failover Flags or Bounded WAL Retention 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 Unsynchronized Logical Replication Slots Without Failover Flags or Bounded WAL Retention	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' on PostgreSQL 17 primary instances intended for HA topologies to ensure slot state syncs to physical standbys.

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.