PostgreSQL 17 Logical Failover Slot Sync & Causal Read Routing Standard

--- description: PostgreSQL 17 Logical Failover Slot Sync & Causal Read Routing Standard - Mandates failover-enabled logical replication slots (`failover = true`), primary physical synchronization gating via `standby_slot_names`, and causal LSN-tracked read routing in PostgreSQL 17. Eliminates WAL retention truncation, CDC stream starvation, and stale replica reads during physical primary failovers. globs: src/database/**/*.{ts,js,sql}, src/replication/**/*.{ts,js}, src/infra/postgres/**/*.{ts,tf,conf} alwaysApply: true verified_by: "agentrules.org" license: "CC-BY-4.0" source: "https://agentrules.org/rules/pg17-logical-failover-slot-sync-read-routing" --- # PostgreSQL 17 Logical Failover Slot Sync & Causal Read Routing Standard > **Severity**: CRITICAL | **Category**: backend > **Anti-Pattern Warning**: Unsynchronized Logical Slots and Uncoordinated Read Dispatch Causing Consumer Drop and Replication Desynchronization ## System Constraints You are a Principal Software Architect enforcing PostgreSQL 17 Logical Failover Slot Synchronization and Conflict-Free Causal Read Routing. Mandate that every logical replication slot provisioned for downstream consumers enables failover (`failover = true`), that primary clusters enforce physical standby retention via `standby_slot_names`, and that database access routing layers verify replica LSN convergence before servicing read-after-write operations. ## Strict Guidelines - Engineers MUST ALWAYS provision logical replication slots with `failover => true` and configure `standby_slot_names` on the physical primary to prevent the primary from purging WAL segments before physical standbys synchronize them. - Engineers MUST NOT route read operations to physical standbys without propagating and verifying the transactional commit LSN (`confirmed_flush_lsn` or `pg_last_wal_replay_lsn()`) to guarantee causal consistency and prevent stale reads. - Services MUST enable `sync_replication_slots = true` on all standby instances and periodically verify slot synchronization status via `pg_replication_slots` (`sync_state` reporting 'sync'). - Code SHOULD catch replication lag threshold breaches and automatically fallback to primary execution or fail fast rather than allowing non-deterministic eventual consistency breaches in critical workflows. - Components NEVER issue unbounded queries directly against standbys without setting `hot_standby_feedback = on` or tuning `max_standby_streaming_delay` to avoid query cancellation storms due to physical WAL replay conflicts. ## Bad vs Good Implementations ### PostgreSQL 17 Failover-Ready Slot Provisioning & Causal LSN Read Router #### Bad Antipattern (AI Common Hallucination) > **Why it fails**: Creating a logical slot without `failover => true` in PostgreSQL 17 prevents standby instances from tracking and syncing the slot via `sync_replication_slots`. In a failover event, the new primary loses the slot, causing downstream CDC/Kafka starvation. Additionally, querying the replica without LSN verification exposes consumers to dirty/stale reads. ```typescript import { Pool } from 'pg'; const primaryPool = new Pool({ connectionString: process.env.PRIMARY_DB_URL }); const replicaPool = new Pool({ connectionString: process.env.REPLICA_DB_URL }); // ANTI-PATTERN: Creates non-failover slot prone to drop upon failover; // executes read-after-write blindly against replica without checking replayed LSN. export async function setupConsumerAndRead(streamName: string, entityId: string) { await primaryPool.query( `SELECT pg_create_logical_replication_slot($1, 'pgoutput');`, [streamName] ); // Stale read anomaly: replica might be hundreds of MBs behind primary const result = await replicaPool.query( 'SELECT * FROM orders WHERE id = $1', [entityId] ); return result.rows[0]; } ``` #### Production Standard (Correct) > **Standard**: Ensures the logical slot is created with `failover => true`, enabling PostgreSQL 17's slot synchronization engine. Read queries track causality tokens (`pg_lsn`) to guarantee reads on the replica are conflict-free and causally consistent, falling back to primary when replication lag would produce stale reads. ```typescript import { Pool, PoolClient } from 'pg'; interface CausalToken { commitLsn: string; } export class ResilientDatabaseRouter { constructor( private readonly primary: Pool, private readonly replica: Pool, private readonly maxLagMs: number = 2000 ) {} // Invariant 1: PG17 failover-enabled logical slot creation with two_phase support async provisionFailoverSlot(slotName: string): Promise<void> { await this.primary.query( `SELECT pg_create_logical_replication_slot( slot_name => $1, plugin => 'pgoutput', temporary => false, two_phase => true, failover => true );`, [slotName] ); } // Invariant 2: Causal read routing checking standby replay advancement against write LSN async executeCausalRead<T>( query: string, params: unknown[], token?: CausalToken ): Promise<T[]> { if (!token) { const res = await this.replica.query(query, params); return res.rows; } const client: PoolClient = await this.replica.connect(); try { const lsnCheck = await client.query<{ is_caught_up: boolean }>( `SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn AS is_caught_up;`, [token.commitLsn] ); if (lsnCheck.rows[0]?.is_caught_up) { const res = await client.query(query, params); return res.rows; } } finally { client.release(); } // Fallback safely to primary if replica has not replayed the required LSN const fallbackRes = await this.primary.query(query, params); return fallbackRes.rows; } } ```

Architectural Specification & Invariant Rationale

Architectural Invariant Design: PostgreSQL 17 Logical Failover Slot Sync & Causal Read Routing Standard

PostgreSQL 17 Logical Failover Slot Sync & Causal Read Routing Standard defines an industrial-grade behavioral standard for backend engineering. Mandates failover-enabled logical replication slots (`failover = true`), primary physical synchronization gating via `standby_slot_names`, and causal LSN-tracked read routing in PostgreSQL 17. Eliminates WAL retention truncation, CDC stream starvation, and stale replica reads during physical primary failovers. 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 and Uncoordinated Read Dispatch Causing Consumer Drop and Replication Desynchronization 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 and Uncoordinated Read Dispatch Causing Consumer Drop and Replication Desynchronization	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` and configure `standby_slot_names` on the physical primary to prevent the primary from purging WAL segments before physical standbys synchronize them.

Engineers MUST ALWAYS provision logical replication slots with `failover => true` and configure `standby_slot_names` on the physical primary to prevent the primary from purging WAL segments before physical standbys synchronize them.

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.