PostgreSQL High Availability — Streaming Replication

BACKEND SERIES

Day 33: PostgreSQL High Availability — Streaming Replication & Read-Write Splitting in Python

14 min read

Series: Logic & Legacy

Day 33 / 50

Level: Senior / System Architect

⏳ Context: As web applications grow, 80% to 90% of database traffic is typically read-heavy (fetching feeds, user profiles, and analytics). Funneling all read and write traffic through a single PostgreSQL node creates a bottleneck. To scale horizontally, production architectures deploy a **Primary Database** for state mutations (`INSERT`/`UPDATE`/`DELETE`) and stream changes to multiple **Read Replicas** for query workloads. Today, we build a production-grade Read-Write routing engine in Python using SQLAlchemy 2.0 and asyncpg.

1. Physical Streaming Replication: WAL Record Transmission

PostgreSQL replication relies on the **Write-Ahead Log (WAL)**. Every transaction is appended to WAL buffers before disk commit. In **Physical Streaming Replication**, the Primary node streams raw WAL byte blocks to Read Replicas over a TCP connection. Replicas constantly apply these WAL records in read-only recovery mode.

The Catch: Replication Lag & Consistency

In asynchronous streaming replication, there is always a tiny delay (replication lag) between Primary commit and Replica replay. If a user updates their profile and immediately reloads the page via a Read Replica, they might see stale data—a bug known as Read-Your-Own-Writes inconsistency.

The Mental Model: The Master Ledger and Photocopies

Think of the Primary DB as the master accountant holding the main ledger book. Every transaction entry is written to a ticker tape (WAL). Assistants at replica desks continuously copy the ticker tape into duplicate reference books. If an assistant falls 2 seconds behind reading the tape, they report yesterday's balance until caught up.

Checking Replication Lag in PostgreSQL

-- Run on Primary to monitor connected replicas and byte lag
SELECT 
    client_addr, 
    state, 
    sent_lsn, 
    write_lsn, 
    flush_lsn, 
    replay_lsn,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS byte_lag
FROM pg_stat_replication;

2. Dynamic Read-Write Engine Routing in Python

To implement Read-Write splitting seamlessly in Python backends, we configure SQLAlchemy 2.0 with primary and replica engine pools:

SQLAlchemy Async Read-Write Session Routing

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession
from sqlalchemy.orm import sessionmaker
import random

# Primary Engine (All Mutations)
primary_engine = create_async_engine("postgresql+asyncpg://user:pass@primary-db:5432/main")

# Read Replica Engines (Read Queries)
replica_engines = [
    create_async_engine("postgresql+asyncpg://user:pass@replica-1:5432/main"),
    create_async_engine("postgresql+asyncpg://user:pass@replica-2:5432/main")
]

class RoutingAsyncSession(AsyncSession):
    def get_bind(self, mapper=None, clause=None, **kw):
        if self._flushing or (clause is not None and not clause.is_select):
            return primary_engine
        # Round-robin or random selection across read replicas
        return random.choice(replica_engines)

3. Enforcing Read-Your-Own-Writes Consistency

To prevent users from seeing stale data immediately after updating records, backends use a **Sticky Primary Window** strategy: when a user issues a state mutation, all read queries for that specific user session are pinned to the Primary database for 5 seconds before returning to replica load balancing.

The Shareable Quote: "Scaling reads across replicas is easy; preserving user-perceived consistency during replication lag requires intentional session routing."

🛠️ Day 33 Project: Build a Dual-Engine Routing FastAPI Service

Build a FastAPI microservice featuring a custom SQLAlchemy AsyncSession router that splits writes to primary and reads across replica pools.

• Set up primary and read-replica SQLAlchemy async engines.
• Override `AsyncSession.get_bind()` to dynamically route SQL statements based on clause type.
• Implement a Redis-backed sticky session middleware to pin mutated user sessions to the primary engine for 3 seconds.

🔥 PRO UPGRADE / TEASER

Tomorrow, we explore database caching and invalidation strategies: Day 34: Caching Architecture — Redis Cache-Aside, Write-Through, and Cache Stampede Prevention (Singleflight Pattern).

Architectural Consulting

If you are building high-concurrency Python backends or microservices and need senior architectural guidance, I am available for direct contracting.

Explore Enterprise Engagements →

← Previous

Day 32: Indexing & Query Execution Plans

Next →

Day 34: Caching & Cache Stampede Prevention