PostgreSQL 17 Failover Replication Slots, Streaming Decoding, and Memory Tuning Invariants

--- description: PostgreSQL 17 Failover Replication Slots, Streaming Decoding, and Memory Tuning Invariants - Enforces strict configuration and migration invariants for PostgreSQL 17 logical replication and shared buffer allocation. Requires failover slot synchronization across physical standby instances, streaming logical transaction decoding thresholds, and mathematically bounded buffer and work memory provisioning to prevent WAL disk exhaustion and out-of-memory cascading failures during subscriber failover. globs: src/**/*.sql, migrations/**/*.sql, deploy/postgres/**/*.conf, infra/**/*.tf, src/database/**/*.ts alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/postgres-17-logical-replication-and-buffers-guardrail" --- # PostgreSQL 17 Failover Replication Slots, Streaming Decoding, and Memory Tuning Invariants > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Slots, Unbounded In-Memory Decoding, and Overcommitted Shared Buffers Leading to WAL Starvation ## System Constraints You are a Principal Database Architect and Infrastructure Engineer enforcing PostgreSQL 17 logical replication, failover slot synchronization, and memory buffer safety. You must enforce PG17-specific logical replication controls (such as pg_create_logical_replication_slot with failover => true, synchronized_standby_slots, and streaming = parallel/on) while guaranteeing that shared_buffers, work_mem, and logical_decoding_work_mem are strictly bounded against total node RAM. ## Strict Guidelines - Engineers MUST ALWAYS enable 'failover => true' when creating logical replication slots in PostgreSQL 17 via pg_create_logical_replication_slot() or CREATE_REPLICATION_SLOT. - Engineers MUST ALWAYS configure 'synchronized_standby_slots' on the primary cluster to reference physical standbys before allowing subscribers to stream from failover-enabled logical slots. - Engineers MUST set 'streaming' to 'on' or 'parallel' when defining PostgreSQL 17 subscriptions to prevent large transactions from exhausting subscriber spool memory. - Engineers MUST NOT allow total memory allocation (shared_buffers + (max_connections * work_mem) + (max_worker_processes * logical_decoding_work_mem) + maintenance_work_mem) to exceed 80% of total host RAM. - Engineers MUST configure 'max_slot_wal_keep_size' with a positive integer ceiling (e.g., 64GB) to avoid indefinite primary WAL retention and disk partition exhaustion in the event of subscriber lag or failure. - Engineers MUST verify that standby servers specify 'sync_replication_slots = true' in postgresql.conf to activate the PG17 slot synchronization worker process. ## Bad vs Good Implementations ### PostgreSQL 17 Failover Slot Creation & Safe Subscription Invariants #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical slot without failover => true in PG17 prevents standby promotion from preserving subscriber state, causing subscriber data desynchronization. Omitting max_slot_wal_keep_size exposes the primary node to disk starvation if the subscriber drops offline, and creating subscriptions without streaming decodes massive multi-gigabyte transactions entirely in subscriber RAM. ```sql -- Anti-pattern: Missing PG17 failover slot flag, missing WAL retention ceiling, unbuffered subscription streaming SELECT pg_create_logical_replication_slot('customer_cdc_slot', 'pgoutput'); -- Primary postgresql.conf omitted synchronized_standby_slots and max_slot_wal_keep_size is -1 (unlimited) -- ALTER SYSTEM SET max_slot_wal_keep_size = -1; -- Subscriber creates subscription without parallel streaming CREATE SUBSCRIPTION sub_customer_events CONNECTION 'host=primary.internal dbname=app user=rep_user password=secret' PUBLICATION pub_customers WITH (copy_data = true, create_slot = false, slot_name = 'customer_cdc_slot'); ``` #### Production Standard (Correct) > **Standard**: Explicitly passes failover => true to pg_create_logical_replication_slot, pairs it with synchronized_standby_slots on the primary and sync_replication_slots on the standby, bounds WAL drift via max_slot_wal_keep_size, and uses streaming = 'parallel' with bounded logical_decoding_work_mem to prevent memory exhaustion during bulk transactions. ```sql -- PostgreSQL 17 Invariant Compliant Configuration -- 1. Primary engine parameters: -- ALTER SYSTEM SET synchronized_standby_slots = 'physical_standby_1,physical_standby_2'; -- ALTER SYSTEM SET max_slot_wal_keep_size = '64GB'; -- ALTER SYSTEM SET logical_decoding_work_mem = '128MB'; -- 2. Create logical replication slot with failover persistence guaranteed SELECT pg_create_logical_replication_slot( slot_name => 'customer_cdc_slot', plugin => 'pgoutput', temporary => false, twophase => true, failover => true ); -- 3. Standby engine configuration: -- ALTER SYSTEM SET sync_replication_slots = true; -- ALTER SYSTEM SET hot_standby_feedback = on; -- 4. Create subscription configured for parallel streaming logical decoding CREATE SUBSCRIPTION sub_customer_events CONNECTION 'host=primary.internal port=5432 dbname=app user=rep_user password=secret' PUBLICATION pub_customers WITH ( copy_data = true, create_slot = false, slot_name = 'customer_cdc_slot', streaming = 'parallel', failover = true ); ``` ### TypeScript Database Pool and Buffer Mathematical Allocation Guard #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Overcommits memory. 50% shared_buffers leaves only 50% for the OS, page cache, work_mem, and logical decoding workers. max_connections (500) * work_mem (128MB) exceeds total host capacity and triggers OOM Killer during complex joins or multiple parallel decoding streams. ```typescript // Anti-pattern: Calculates connection limits without accounting for logical decoding workers or shared_buffers boundary interface NodeSpecs { totalRamMb: number; } export function calculatePostgresLimits(specs: NodeSpecs) { // Blindly sets shared buffers to 50% RAM and allows dynamic unbounded work_mem const sharedBuffersMb = Math.floor(specs.totalRamMb * 0.5); const maxConnections = 500; const workMemMb = 128; // 500 * 128MB = 64GB potential allocation alone! return { shared_buffers: `${sharedBuffersMb}MB`, max_connections: maxConnections, work_mem: `${workMemMb}MB`, logical_decoding_work_mem: '1024MB' }; } ``` #### Production Standard (Correct) > **Standard**: Ensures total memory strictly respects the 80% maximum utilization ceiling by reserving adequate overhead for the OS, caps shared_buffers at 25%, accounts for PG17 worker processes (including logical decoding and slot synchronization workers), and safely computes work_mem based on concurrency limits. ```typescript // Invariant Fix: Validated PostgreSQL 17 memory envelope with strict headroom bounds interface NodeSpecs { totalRamMb: number; maxWorkerProcesses: number; } export interface PostgresTuningConfig { sharedBuffersMb: number; maxConnections: number; workMemMb: number; logicalDecodingWorkMemMb: number; maintenanceWorkMemMb: number; } export function calculatePostgres17MemoryBounds(specs: NodeSpecs): PostgresTuningConfig { // Invariant 1: shared_buffers must remain between 25% and 40% of dedicated host RAM const sharedBuffersMb = Math.floor(specs.totalRamMb * 0.25); // Invariant 2: Reserve 20% absolute headroom for OS kernel, FS cache, and network buffers const maxDynamicMemoryMb = Math.floor(specs.totalRamMb * 0.55); const maxConnections = 100; const maintenanceWorkMemMb = Math.min(2048, Math.floor(specs.totalRamMb * 0.05)); const logicalDecodingWorkMemMb = 128; // Bounded for PG17 parallel logical stream workers const workerBudgetMb = specs.maxWorkerProcesses * logicalDecodingWorkMemMb; const remainingForWorkMem = maxDynamicMemoryMb - workerBudgetMb - maintenanceWorkMemMb; if (remainingForWorkMem <= 0) { throw new Error('Insufficient RAM for configured worker processes and logical decoding budget.'); } // Invariant 3: work_mem bounded per backend connection to prevent OOM const workMemMb = Math.max(4, Math.floor(remainingForWorkMem / (maxConnections * 2))); return { sharedBuffersMb, maxConnections, workMemMb, logicalDecodingWorkMemMb, maintenanceWorkMemMb }; } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Failover Replication Slots, Streaming Decoding, and Memory Tuning Invariants

PostgreSQL 17 Failover Replication Slots, Streaming Decoding, and Memory Tuning Invariants defines an industrial-grade behavioral standard for backend engineering. Enforces strict configuration and migration invariants for PostgreSQL 17 logical replication and shared buffer allocation. Requires failover slot synchronization across physical standby instances, streaming logical transaction decoding thresholds, and mathematically bounded buffer and work memory provisioning to prevent WAL disk exhaustion and out-of-memory cascading failures during subscriber failover. 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 Logical Slots, Unbounded In-Memory Decoding, and Overcommitted Shared Buffers Leading to WAL Starvation 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 Logical Slots, Unbounded In-Memory Decoding, and Overcommitted Shared Buffers Leading to WAL Starvation	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 enable 'failover => true' when creating logical replication slots in PostgreSQL 17 via pg_create_logical_replication_slot() or CREATE_REPLICATION_SLOT.

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.