PostgreSQL 17 Logical Replication Failover Slot Synchronization & Buffer Isolation Guardrail

--- description: PostgreSQL 17 Logical Replication Failover Slot Synchronization & Buffer Isolation Guardrail - Mandates PostgreSQL 17 failover-enabled logical replication slots (`failover => true`) synchronized with physical standbys to prevent CDC divergence after HA failovers. Enforces explicit `logical_decoding_work_mem` boundaries to prevent logical decoding workers from exhausting `shared_buffers` and spilling unbounded change streams to disk, combined with strict `max_slot_wal_keep_size` limits to protect WAL partitions. globs: src/db/**/*.ts, src/infrastructure/**/*.ts, src/replication/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-failover-buffers-guardrail" --- # PostgreSQL 17 Logical Replication Failover Slot Synchronization & Buffer Isolation Guardrail > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Ephemeral Failover Slots with Unbounded WAL Decoders Spilling to Disk and Thrashing Shared Buffers ## System Constraints You are a Principal Database Infrastructure Architect enforcing PostgreSQL 17 Logical Replication invariants. In Postgres 17, logical replication failover slots MUST be configured with failover enabled (`failover => true`) and synchronized to physical standbys (`sync_replication_slots = on`) to prevent pipeline disruption upon primary election. Large change stream pipelines MUST utilize `streaming = 'parallel'` or `'on'` on subscriptions, backed by a tuned `logical_decoding_work_mem` (independent of `shared_buffers` and `work_mem`) to halt disk serialization loops and prevent buffer cache thrashing. Unbounded replication slots without `max_slot_wal_keep_size` are forbidden as they risk crashing the primary cluster via WAL volume saturation. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots in PostgreSQL 17 using `pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, failover => true)` when replicating across HA topologies. - Engineers MUST NOT allow logical replication slots to exist without setting `max_slot_wal_keep_size` on the primary instance to avoid unchecked WAL accumulation. - Services MUST configure PostgreSQL 17 subscriptions with `streaming = 'parallel'` (or `'on'`) for write-heavy tables to stream in-flight transactions and prevent synchronous subscriber bottlenecks. - Code SHOULD enforce runtime inspection of `pg_replication_slots.sync_state` and ensure physical standbys maintain `sync_replication_slots = on` prior to promoting any replica. - Components NEVER permit logical decoding allocations to default to unconstrained server memory; `logical_decoding_work_mem` MUST be explicitly configured (e.g., between 64MB and 512MB per worker) to prevent dirtying pages inside `shared_buffers` and thrashing the OS page cache. ## Bad vs Good Implementations ### PostgreSQL 17 Logical Slot & Subscription Provisioning Invariants #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical replication slot in PostgreSQL 17 without `failover => true` means the slot is not mirrored to physical standbys. If primary failover occurs, the slot is lost, forcing full resynchronization of the consumer. Omitting `streaming = 'parallel'` causes large write transactions to be held in decoder memory or spilled to disk temp files before apply, causing latency spikes and buffer thrashing. ```typescript import { Client } from 'pg'; export async function setupLogicalReplication(client: Client, slotName: string, subName: string, connStr: string) { // Anti-pattern: PG16 legacy syntax omitting failover capability in PG17 // Omits failover => true; replica promotion will orphan downstream consumers await client.query(`SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [slotName]); // Omits streaming mode; defaults to buffering entire large transactions in memory/disk await client.query(` CREATE SUBSCRIPTION ${subName} CONNECTION '${connStr}' PUBLICATION app_cdc_pub WITH (create_slot = false, slot_name = '${slotName}'); `); } ``` #### Production Standard (Correct) > **Standard**: This pattern validates PostgreSQL 17 version compatibility, verifies `max_slot_wal_keep_size` to protect WAL storage, explicitly activates `failover => true` on both the logical replication slot and subscription, and enforces `streaming = 'parallel'` to parallelize logical decoding workers without saturating the primary's `shared_buffers`. ```typescript import { Client } from 'pg'; interface ReplicationSlotOptions { slotName: string; subscriptionName: string; connectionString: string; publicationName: string; } export async function setupPg17LogicalReplication( client: Client, options: ReplicationSlotOptions ): Promise<void> { const { slotName, subscriptionName, connectionString, publicationName } = options; // 1. Verify PG17+ runtime and WAL retention boundaries const { rows } = await client.query<{ server_version_num: string; max_slot_wal_keep_size: string }>( `SELECT current_setting('server_version_num') AS server_version_num, current_setting('max_slot_wal_keep_size') AS max_slot_wal_keep_size;` ); const version = parseInt(rows[0].server_version_num, 10); if (version < 170000) { throw new Error(`PostgreSQL 17+ required for failover slots. Detected: ${version}`); } if (rows[0].max_slot_wal_keep_size === '-1') { throw new Error('Unbounded max_slot_wal_keep_size detected. Configure WAL retention guard before creating slots.'); } // 2. Create logical replication slot with PG17 failover enabled // failover => true allows standby nodes to synchronize the slot state via sync_replication_slots await client.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, twophase => false, failover => true );`, [slotName] ); // 3. Create subscription with parallel transaction streaming and failover enabled await client.query( `CREATE SUBSCRIPTION ${subscriptionName} CONNECTION '${connectionString}' PUBLICATION ${publicationName} WITH ( create_slot = false, slot_name = '${slotName}', streaming = 'parallel', failover = true );` ); } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Logical Replication Failover Slot Synchronization & Buffer Isolation Guardrail

PostgreSQL 17 Logical Replication Failover Slot Synchronization & Buffer Isolation Guardrail defines an industrial-grade behavioral standard for backend engineering. Mandates PostgreSQL 17 failover-enabled logical replication slots (`failover => true`) synchronized with physical standbys to prevent CDC divergence after HA failovers. Enforces explicit `logical_decoding_work_mem` boundaries to prevent logical decoding workers from exhausting `shared_buffers` and spilling unbounded change streams to disk, combined with strict `max_slot_wal_keep_size` limits to protect WAL partitions. 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: Ephemeral Failover Slots with Unbounded WAL Decoders Spilling to Disk and Thrashing Shared Buffers 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	Ephemeral Failover Slots with Unbounded WAL Decoders Spilling to Disk and Thrashing Shared Buffers	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 in PostgreSQL 17 using `pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, failover => true)` when replicating across HA topologies.

Engineers MUST ALWAYS provision logical replication slots in PostgreSQL 17 using `pg_create_logical_replication_slot('slot_name', 'pgoutput', false, false, failover => true)` when replicating across HA topologies.

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.