PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Invariants

--- description: PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Invariants - Enforces PostgreSQL 17 logical replication failover slot synchronization and precise memory allocation across streaming pipelines. Prevents catastrophic primary disk exhaustion from unmanaged WAL accumulation, consumer desynchronization on physical replica promotion, and out-of-memory crashes from unconstrained logical decoding buffers. globs: src/**/*.{ts,tsx}, migrations/**/*.sql, deploy/**/*.tf, deploy/**/*.yaml alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-failover-buffers-standard" --- # PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Invariants > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Creating unmonitored logical replication slots without failover sync, max WAL retention limits, and bounded decoding memory ## System Constraints You are a Principal Database Reliability Engineer and Distributed Systems Architect specializing in PostgreSQL 17 high-availability and streaming architecture. Mandate PostgreSQL 17 failover slot management (synchronized_standby_slots, failover = true), max_slot_wal_keep_size boundaries, streaming parallel decoding options, and dedicated memory buffer sizing (shared_buffers, logical_decoding_work_mem) across all schemas, connection managers, and infrastructure automation. ## Strict Guidelines - Engineers MUST ALWAYS provision PostgreSQL 17 logical replication slots with `failover = true` and map physical standby replication slot names in `synchronized_standby_slots` on the primary node. - Engineers MUST ALWAYS configure explicit, non-infinite `max_slot_wal_keep_size` (e.g., between 64GB and 256GB depending on partition storage) to ensure lagging or dead logical subscribers do not exhaust disk storage and take down the primary node. - Engineers MUST NOT use unconstrained `logical_decoding_work_mem`; it MUST be bounded explicitly (recommended: 64MB to 256MB) per walsender process to prevent out-of-memory events during bulk transactions while avoiding excessive disk spills. - Services MUST enable parallel transaction streaming (`ALTER SUBSCRIPTION ... SET (streaming = 'parallel')`) in PostgreSQL 17 for high-throughput write workloads to bypass single-threaded head-of-line blocking on subscriber worker processes. - Engineers MUST verify that `shared_buffers` is configured between 25% and 40% of dedicated database host RAM, with `huge_pages = try` or `on`, and monitor replication buffer pool impact using `pg_buffercache`. - Infrastructure automation MUST continuously check slot health via `pg_replication_slots` monitoring `wal_status` ('lost', 'extended', 'reserved') and drop or fail over invalid slots before checkpoint retention fails. ## Bad vs Good Implementations ### Configuring PG17 Failover-Safe Subscription and Slot Parameters #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Omitting `failover = true` means the slot will not sync to physical standbys in PostgreSQL 17. If the primary crashes and a standby promotes, this subscription breaks permanently. Without setting `max_slot_wal_keep_size` on the publisher, a stalled consumer will exhaust disk space. Omitting `streaming = 'parallel'` degrades throughput by buffering giant transactions sequentially. ```typescript import { Client } from 'pg'; export async function setupLogicalReplication(client: Client, subName: string, pubConnStr: string): Promise<void> { // Anti-Pattern: Unmanaged slot without failover synchronization, // unbounded WAL retention risk, and default single-threaded synchronous streaming await client.query(` CREATE SUBSCRIPTION ${subName} CONNECTION '${pubConnStr}' PUBLICATION core_data_pub WITH (copy_data = true, create_slot = true); `); } ``` #### Production Standard (Correct) > **Standard**: Uses PostgreSQL 17 `failover = true` to allow logical slots to synchronize across physical standbys (`synchronized_standby_slots`). Sets `max_slot_wal_keep_size` to safeguard against disk exhaustion. Binds `logical_decoding_work_mem` to prevent OOM on the publisher and enables `streaming = 'parallel'` to parallelize applying large concurrent streaming changes. ```typescript import { Client } from 'pg'; interface ReplicationSubscriptionConfig { subscriptionName: string; publisherConnStr: string; publicationName: string; maxSlotWalKeepMb: number; decodingWorkMemMb: number; } export async function setupProductionLogicalReplication( subscriberClient: Client, publisherClient: Client, config: ReplicationSubscriptionConfig ): Promise<void> { // Invariant 1: Ensure Publisher protects WAL disk exhaustion and bounds decoding memory await publisherClient.query(` ALTER SYSTEM SET max_slot_wal_keep_size = '${config.maxSlotWalKeepMb}MB'; ALTER SYSTEM SET logical_decoding_work_mem = '${config.decodingWorkMemMb}MB'; SELECT pg_reload_conf(); `); // Invariant 2: Create Postgres 17 failover-synchronized subscription with parallel streaming const safeSubscriptionName = clientEscapeIdentifier(config.subscriptionName); const safePubName = clientEscapeIdentifier(config.publicationName); await subscriberClient.query(` CREATE SUBSCRIPTION ${safeSubscriptionName} CONNECTION '${config.publisherConnStr}' PUBLICATION ${safePubName} WITH ( copy_data = true, create_slot = true, failover = true, streaming = 'parallel' ); `); } function clientEscapeIdentifier(identifier: string): string { if (!/^[a-zA-Z0-9_]+$/.test(identifier)) { throw new Error(`Invalid identifier: ${identifier}`); } return `"${identifier}"`; } ```

Architectural Specification & Invariant Rationale

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

PostgreSQL 17 Failover Slot Synchronization and Memory Buffer Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces PostgreSQL 17 logical replication failover slot synchronization and precise memory allocation across streaming pipelines. Prevents catastrophic primary disk exhaustion from unmanaged WAL accumulation, consumer desynchronization on physical replica promotion, and out-of-memory crashes from unconstrained logical decoding buffers. 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 unmonitored logical replication slots without failover sync, max WAL retention limits, and bounded decoding 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 unmonitored logical replication slots without failover sync, max WAL retention limits, and bounded decoding 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 provision PostgreSQL 17 logical replication slots with `failover = true` and map physical standby replication slot names in `synchronized_standby_slots` on the primary node.

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.