Database Replication — Slot Bloat That Kills Your Primary

At scale, a single database server cannot handle millions of concurrent reads. When it dies, the whole product goes dark. Database replication solves both problems by spreading reads across servers and keeping a hot standby ready to take over.

But replication introduces its own failure modes. Stale data after a write, replication slots silently filling the primary's disk, and split-brain scenarios during failover are real. I've debugged each of these in production, and the pattern is always the same: the monitoring wasn't there.

This article covers how replication works under the hood — WAL streaming, lag measurement, topology design — and the failure patterns that catch most teams off guard. By the end, you'll know how to design a topology that survives production, not just the happy path.

Database Replication — The Illusion of Free Read Scaling

Database replication is the process of copying data from a primary database to one or more replica databases to distribute read load and provide failover capacity. The core mechanic is a continuous stream of write-ahead log (WAL) records shipped from primary to replicas, which replay them to maintain an eventually consistent copy. This is not a cache — replicas hold full table data, not a subset.

In practice, replication introduces a lag window: replicas are always slightly behind the primary. PostgreSQL uses physical replication slots that pin WAL segments until all replicas confirm receipt. If a replica falls behind or disconnects, WAL accumulates on the primary, eventually filling disk and crashing the primary. This is slot bloat — a silent killer that takes down production databases with no query spike.

Use replication when you need read-heavy workloads to scale beyond a single node's capacity, or when you require a hot standby for rapid failover. It is not a substitute for proper indexing or connection pooling — those are cheaper and simpler. Replication buys you read throughput at the cost of operational complexity and a new failure mode: the primary can die because a replica is slow.

Core Architectures: Primary-Replica vs. Multi-Primary

In a Primary-Replica setup — the architecture that powers the vast majority of production databases you'll encounter — all writes go to a single Primary node. That node records every change in a Write-Ahead Log (WAL) or Binary Log and ships those events to one or more Replica nodes. Replicas are read-only; they apply the log entries to their own copy of the data to stay in sync. This is the industry default for a reason: there is exactly one source of truth, conflict resolution is trivially simple (there are no conflicts), and the operational model is easy to reason about.

Multi-Primary replication allows writes on any node, which sounds like the availability holy grail until you actually implement it. If two clients update the same row on different primaries within the same time window, the system must resolve the conflict using strategies like Last Write Wins (LWW), Vector Clocks, or custom application logic. Last Write Wins sounds simple but silently discards legitimate writes — whichever timestamp wins, the other write vanishes with no error and no log entry. Vector Clocks are more correct but require your application layer to understand and handle conflict signals. Neither is free.

I've seen teams reach for Multi-Primary when their real problem was read scaling, which Primary-Replica solves without any of that complexity. The rule is straightforward: don't adopt multi-primary until you've genuinely exhausted vertical scaling and read-replica offloading, and your team understands distributed consensus deeply enough to operate it at 3 AM.

-- Run this on the Primary to see connected standbys and their lag
SELECT 
    client_addr, 
    state, 
    sent_lsn, 
    write_lsn, 
    flush_lsn, 
    replay_lsn,
    (sent_lsn - replay_lsn) AS replication_lag_bytes
FROM pg_stat_replication;

-- Run this on a Replica to confirm it is in recovery and check replay timestamp
SELECT 
    pg_is_in_recovery()                  AS is_replica,
    pg_last_xact_replay_timestamp()       AS last_replayed_at,
    now() - pg_last_xact_replay_timestamp() AS current_lag;

Conflict Resolution Patterns

When multiple nodes accept writes — as in multi-primary or active-active topologies — conflicts are inevitable. Two clients update the same row on different primaries before replication propagates either change. The system must decide which version wins and how to reconcile the loser. This section covers the three dominant approaches: Last Writer Wins, Vector Clocks, and CRDTs.

Last Writer Wins (LWW) is the simplest and most common. Each write carries a timestamp. The operation with the most recent timestamp wins; the losing write is silently discarded. LWW is easy to implement and fast — no coordination needed during writes. The cost is silent data loss: if two writes happen within the same clock tick, or if one node's clock is skewed, legitimate updates vanish without notification. LWW is acceptable for low-criticality data like session preferences or user-agent strings. It is dangerous for financial or inventory data where every update matters.

Vector Clocks track causal relationships by maintaining a mapping of node -> logical counter. When a write arrives, the vector clock is compared to the current version. If one clock dominates (all counters >= the other's and at least one >), there is no conflict and the later version wins. If neither dominates, a genuine conflict exists and must be resolved, typically by returning both versions to the application layer. Vector clocks never silently lose data, but they require application-level conflict handlers, add metadata overhead per row, and make the read path more complex.

CRDTs (Conflict-free Replicated Data Types) are mathematical structures designed so that concurrent updates always converge without coordination. For example, a counter that only increments can use a G-Counter where each node stores its own increment count and the total is the sum. CRDTs eliminate conflicts entirely but only support a limited set of data types (counters, sets, registers). They are not a general-purpose solution for relational tables with arbitrary schemas. CRDTs are most common in real-time collaboration tools (shared document editing) and eventually-consistent key-value stores.

For most database replication scenarios, LWW with careful clock synchronization is the pragmatic default, and multi-primary should be avoided unless the write throughput requirement genuinely mandates it. If you must use multi-primary, plan for conflict resolution in your application layer from day one — retrofitting it later is painful and error-prone.

# io.thecodeforge: Example conflict resolver using Vector Clocks
# This is a simplified simulation — real systems use persistent storage for clock data

class VectorClock:
    def __init__(self, clocks=None):
        self.clocks = clocks or {}  # node_id -> counter

    def increment(self, node_id):
        self.clocks[node_id] = self.clocks.get(node_id, 0) + 1

    def dominates(self, other):
        # Returns True if self > other (self is strictly newer)
        has_greater = False
        for node, counter in self.clocks.items():
            other_counter = other.clocks.get(node, 0)
            if counter < other_counter:
                return False
            if counter > other_counter:
                has_greater = True
        for node, counter in other.clocks.items():
            if node not in self.clocks and counter > 0:
                return False
        return has_greater

    def concurrent(self, other):
        return not self.dominates(other) and not other.dominates(self)

# Example usage
a = VectorClock({'A': 3, 'B': 1})
b = VectorClock({'A': 2, 'B': 2})
print(f"a dominates b: {a.dominates(b)}")  # False (A:3>2 but B:1<2)
print(f"concurrent: {a.concurrent(b)}")    # True -> conflict!
print(f"b dominates a: {b.dominates(a)}")  # False

Synchronous vs. Asynchronous Replication

This is the most consequential trade-off in replication design, and it's worth spending time with rather than defaulting to whatever the tutorial used.

In asynchronous replication, the primary confirms the write to the client as soon as it's committed locally. The WAL is shipped to replicas in the background, after the acknowledgment. This is fast — write latency is bounded only by local disk speed — but it creates a window where committed transactions exist only on the primary. If the primary crashes before shipping that WAL, those transactions are gone. The replica becomes the new primary with a gap in its history that nobody can fill.

In synchronous replication, the primary waits until at least one replica confirms it has received and durably written the WAL before acknowledging the commit to the client. This closes the data loss window entirely — at least one copy of every committed transaction exists before the client gets a response. The cost is that write latency now includes the network round-trip to the replica. If your replica is in a different availability zone, you're paying 2–10ms per write, every write, forever.

Neither is obviously correct. The right choice depends on what your data is worth and what your users will tolerate. A social media feed can absorb async lag without most users noticing. A financial transaction system where a committed payment might vanish on primary failure is a different conversation entirely.

# io.thecodeforge: Quick-start Postgres Primary-Replica cluster for local development
# Do not use this config verbatim in production — add TLS, auth, and resource limits
services:
  db-primary:
    image: postgres:16-alpine
    environment:
      POSTGRES_PASSWORD: forge_secret
      POSTGRES_USER: forge_admin
    command: |
      postgres
        -c wal_level=replica
        -c max_wal_senders=10
        -c max_replication_slots=10
        -c wal_keep_size=256MB

  db-replica:
    image: postgres:16-alpine
    depends_on:
      - db-primary
    environment:
      PGPASSWORD: forge_secret
    command: |
      /bin/sh -c "
      until pg_basebackup -h db-primary -D /var/lib/postgresql/data \
        -U forge_admin -vP -W --wal-method=stream -R; do
        echo 'Primary not ready, retrying in 2s...'; sleep 2
      done
      echo 'Basebackup complete. Starting replica...'
      postgres"

Sync vs Async Decision Matrix

To help you choose between synchronous and asynchronous replication, here is a visual decision matrix summarizing key trade-offs across common dimensions. Use this alongside the other content in this section to evaluate your specific use case.

Replication Lag: Causes, Measurement, and Consequences

Replication lag is the time gap between a transaction committing on the primary and that same transaction becoming visible on a replica. It's measured two ways: time-based (how many seconds is the replica behind?) and log-based (how many bytes of WAL has the replica not yet applied?). Both metrics matter and they tell you different things. Time tells you the user-visible impact. Bytes tell you how much backlog the replica is carrying and whether it's catching up or falling further behind.

Lag is not a bug in your replication setup. It is a fundamental, expected property of asynchronous replication, and the engineering question is never 'how do I eliminate lag?' but 'is this lag within acceptable bounds for my use case?' A 200ms lag on a social media activity feed is invisible to users. A 200ms lag on a bank account balance immediately after a wire transfer is a customer complaint and potentially a compliance issue. The same 200ms, completely different problem severity.

The causes of lag that I see most often in production: long-running transactions on the primary that generate a large WAL burst when they commit, replica hardware that is weaker than the primary (slower IOPS means slower WAL apply), network congestion between primary and replica — especially on shared cloud network links during peak hours, and replicas running heavy read queries that compete with the WAL apply process for disk I/O. That last one is subtle and frequently overlooked: your read traffic is physically competing with replication for the same disk, and replication will lose if the disk is saturated.

-- io.thecodeforge: Replication lag monitoring — run both methods, not just one

-- Method 1: Time-based lag (run on the REPLICA)
-- Tells you user-visible staleness in wall-clock time
SELECT 
    now() - pg_last_xact_replay_timestamp() AS time_lag,
    CASE
        WHEN now() - pg_last_xact_replay_timestamp() > INTERVAL '5 seconds' THEN 'PAGE: lag exceeds 5s'
        WHEN now() - pg_last_xact_replay_timestamp() > INTERVAL '1 second'  THEN 'WARN: lag exceeds 1s'
        ELSE 'OK'
    END AS lag_status;

-- Method 2: Byte-based lag (run on the PRIMARY)
-- Tells you WAL backlog depth — a replica at 0s time lag can still carry 500MB of queued WAL
SELECT 
    client_addr,
    state,
    sent_lsn,
    replay_lsn,
    pg_size_pretty(pg_wal_lsn_diff(sent_lsn, replay_lsn)) AS lag_pretty,
    pg_wal_lsn_diff(sent_lsn, replay_lsn)                  AS lag_bytes,
    CASE
        WHEN pg_wal_lsn_diff(sent_lsn, replay_lsn) > 104857600 THEN 'PAGE: >100MB lag'
        WHEN pg_wal_lsn_diff(sent_lsn, replay_lsn) > 10485760  THEN 'WARN: >10MB lag'
        ELSE 'OK'
    END AS lag_status
FROM pg_stat_replication;

Write-Ahead Logs and Binary Logs: The Replication Plumbing

Every mainstream relational database uses a write-ahead log as the physical foundation of replication. In PostgreSQL it's the WAL. In MySQL it's the Binary Log (binlog). The concept is the same in both: before any change is applied to the actual data files, it's written sequentially to the log first. The log is the authoritative record of every change the primary made, in the exact order it was made. Replicas consume this log to reconstruct the primary's state on their own storage.

WAL serves two distinct purposes that are easy to conflate. First, crash recovery: if the primary dies mid-write, the WAL records enough information to replay or roll back the incomplete transaction on restart. The data files are always recoverable from a consistent checkpoint plus the WAL that follows it. Second, replication: the primary streams WAL segments to connected replicas over a network socket, and replicas apply them in sequence. These two purposes share the same physical log, which is why WAL configuration affects both durability and replication behavior.

The wal_level setting in PostgreSQL determines how much detail the WAL contains. The replica level is the minimum for physical streaming replication — it records enough to reconstruct data changes. The logical level adds row-level detail needed for logical replication and change data capture pipelines like Debezium. Logical WAL generates more volume per write — roughly 2–4x in high-UPDATE workloads — so don't set it unless you actually need it. And changing wal_level requires a server restart, so plan this before you have replicas depending on it.

-- io.thecodeforge: PostgreSQL WAL configuration audit and replication slot monitoring

-- Check current WAL level — requires server restart to change
SHOW wal_level;
SHOW max_wal_senders;
SHOW max_replication_slots;

-- Recommended postgresql.conf settings for a replication primary:
-- wal_level = replica            -- minimum for physical streaming replication
-- max_wal_senders = 10          -- max concurrent replication connections (replicas + tools)
-- max_replication_slots = 10    -- max slots; each slot pins WAL until the replica consumes it
-- wal_keep_size = '1GB'         -- WAL to retain without slots (safety net, not a substitute for slots)
-- max_slot_wal_keep_size = '10GB' -- hard cap on WAL any single slot can pin (CRITICAL — set this)

-- Monitor active WAL position
SELECT 
    pg_current_wal_lsn()                          AS current_lsn,
    pg_walfile_name(pg_current_wal_lsn())          AS current_wal_file;

-- Audit replication slots — pay attention to inactive slots retaining large WAL
SELECT 
    slot_name,
    slot_type,
    active,
    pg_size_pretty(
        pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    ) AS retained_wal,
    CASE
        WHEN active = false 
         AND pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) > 1073741824
        THEN 'ALERT: inactive slot retaining >1GB — check immediately'
        ELSE 'OK'
    END AS slot_status
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC;

Automated Failover: When the Primary Dies

Manual failover — SSH into a replica, run pg_ctl promote, update your connection strings, restart the application, redirect traffic — works in a controlled staging exercise during business hours. In production at 3 AM with pager alerts firing, adrenaline running, and half the team half-asleep, manual failover is how data gets lost and how the wrong replica gets promoted. I've seen both happen.

Automated failover tools handle the entire sequence: detect the primary failure, select the most up-to-date eligible replica, promote it, reconfigure remaining replicas to follow the new primary, and update the application's connection endpoint. All of that in under 30 seconds, without human judgment calls under pressure.

The two dominant tools for PostgreSQL are Patroni (built on etcd or Consul for distributed consensus and leader election) and Repmgr (simpler setup, less operational overhead for smaller clusters). For MySQL, Orchestrator is the standard. All three solve the same core problem: ensuring that exactly one node holds the write role at any moment. The 'exactly one' constraint is the hard part — distributed systems make this genuinely difficult because a network partition can make a live primary look dead to the failover system.

Failover tooling alone is not enough. The application must know where to send writes after failover completes. This requires either a connection pooler (PgBouncer, ProxySQL) or DNS-based service discovery (Route53, Consul) that can be updated programmatically as part of the failover sequence. Hard-coding the primary's IP address in your application configuration file is a guarantee that your automated failover will still require manual application intervention — which defeats most of the point.

# io.thecodeforge: Patroni configuration for a 3-node PostgreSQL HA cluster
# Patroni uses a DCS (etcd/consul) for distributed leader election and lock management
# Run one instance of this per PostgreSQL node — change 'name' and 'connect_address' per node

scope: forge-cluster
name: node-1   # change to node-2, node-3 on other nodes
namespace: /db/

restapi:
  listen: 0.0.0.0:8008
  connect_address: node-1.internal:8008

etcd3:
  hosts: etcd-1:2379, etcd-2:2379, etcd-3:2379

dcs:
  ttl: 30                           # leader lock TTL in seconds
  loop_wait: 10                     # how often Patroni checks its status
  retry_timeout: 10
  maximum_lag_on_failover: 1048576  # 1MB — replica must be within 1MB of primary to be promotion-eligible
  synchronous_mode: true            # require at least one synchronous replica before committing
  synchronous_mode_strict: false    # do not block writes if no sync replica is available (degrades to async)
pg_hba:
  - host replication replicator 10.0.0.0/8 md5
  - host all all 0.0.0.0/0 md5

postgresql:
  listen: 0.0.0.0:5432
  connect_address: node-1.internal:5432
  data_dir: /var/lib/postgresql/16/main
  bin_dir: /usr/lib/postgresql/16/bin
  parameters:
    max_connections: 200
    shared_buffers: 2GB
    wal_level: replica
    max_wal_senders: 10
    max_replication_slots: 10
    max_slot_wal_keep_size: 10GB     # safety valve — prevents slot bloat from filling disk
  authentication:
    replication:
      username: replicator
      password: forge_repl_secret
    superuser:
      username: postgres
      password: forge_admin_secret

Designing Your Replication Topology for Production

A replication topology is not just 'one primary and a couple of replicas.' It's a deliberate design that accounts for how reads are routed, how WAL shipping load is distributed across the primary's network interface, what the failover blast radius looks like, and whether your replicas will actually help when the primary fails — or whether they'll fail alongside it because they share the same infrastructure failure domain.

The most common production topology is a star: one primary with N replicas, all directly connected to the primary via streaming replication. Read traffic is distributed across replicas via a load balancer or connection pooler. HAProxy and PgBouncer in pool mode are the standard choices here. This works well for single-region deployments up to roughly 10 replicas, at which point the primary's WAL shipping starts to become a bottleneck — it's shipping the same WAL stream N times over the same network interface.

Beyond 10 replicas, or whenever you can measure WAL shipping as a CPU or network ceiling on the primary, cascading (hierarchical) replication is worth evaluating. Tier-1 replicas connect directly to the primary. Tier-2 replicas connect to tier-1 replicas and inherit their lag in addition to their own. The primary's WAL shipping burden drops proportionally, but you add a lag tier at each level — tier-2 replicas are always at least as far behind as tier-1. Monitor each tier's lag separately.

For multi-region deployments, place at least one replica in each region. Route reads to the nearest replica for latency. But be clear-eyed about what cross-region replication lag means: a primary in us-east-1 and a replica in eu-west-1 will carry 80–150ms of lag under normal conditions, and more during network events. Writes always go to the single primary — and if that primary is in a different region than your users, those writes pay the cross-region latency too. Geographic distribution of replicas improves read latency; it does not improve write latency unless you go multi-primary, with all the complexity that brings.

-- io.thecodeforge: Common Production Replication Topologies

-- STAR TOPOLOGY (recommended default, up to ~10 replicas)
-- All replicas connect directly to the primary
-- Primary ships WAL to every replica — network bandwidth scales linearly with replica count
--
--                    [Primary]
--                 /      |      \n--           [Rep1]    [Rep2]    [Rep3]
--         (reads)    (reads)   (hot standby)

-- CASCADING TOPOLOGY (for 10+ replicas, or when WAL shipping is a primary bottleneck)
-- Tier-1 replicas connect to primary; tier-2 replicas connect to tier-1
-- Reduces primary WAL shipping load — each tier-1 ships to its tier-2 children
-- Lag at tier-2 = tier-1 lag + tier-2 lag — monitor each tier separately
--
--                    [Primary]
--                 /             \n--           [Rep1-A]           [Rep1-B]
--           /     \            /      \n--     [Rep2-A] [Rep2-B]  [Rep2-C]  [Rep2-D]

-- MULTI-REGION TOPOLOGY (for geographic read distribution)
-- One or more replicas per region — route reads to nearest replica
-- Cross-region lag is 80-150ms under normal conditions — set SLAs accordingly
-- Writes still go to the single primary — cross-region writes pay the RTT
--
--   [Primary: us-east-1] ----WAL----> [Replica: eu-west-1]
--          |                                  |
--   [Local Read Replicas]          [Local Read Replicas]
--   (us-east-1 reads)              (eu-west-1 reads)

Architectural Topology Diagram

The following Mermaid diagram visualizes the three major replication topologies: star, cascading, and multi-region. Use it as a reference when designing your deployment.

Health Check Dashboard Metrics

A replication health dashboard should surface the metrics that let you detect problems before they become outages. Below is a recommended set of metrics with alert thresholds.

Replication Security: Encryption and Authentication

Replication streams are sensitive — they carry every data change your database makes. If an attacker intercepts the WAL stream, they have a near-complete copy of your data. Secure replication connections with SSL/TLS encryption between primary and replicas. In PostgreSQL, set ssl = on in postgresql.conf and require sslmode=verify-full in pg_hba.conf for the replication user. For MySQL, enable --ssl-mode=VERIFY_IDENTITY for the replication connection.

Authentication matters just as much. Create a dedicated replication user with minimal privileges — it should only have REPLICATION or REPLICATION SLAVE privileges, not superuser. Use strong passwords or certificate-based authentication. Rotate credentials regularly.

Network segmentation is your last line of defense. Place replication traffic on a private subnet or dedicated VLAN. Use firewall rules to allow replication only from known replica IPs. Never expose replication ports (5432 for PostgreSQL, 3306 for MySQL) to the public internet.

I've seen security audits catch replication streams flowing over unencrypted connections between cloud VPCs. It's an easy fix that many teams overlook because 'it's internal traffic.' Internal traffic still crosses shared infrastructure.

Replication in Cloud Environments (AWS RDS, Aurora, Cloud SQL)

Managed cloud databases simplify replication setup but constrain your control. Understanding those constraints prevents surprises during failover or scaling.

AWS RDS for PostgreSQL uses a 'Multi-AZ' deployment for high availability — it's automatic synchronous replication between AZs. The read replica feature uses asynchronous replication and can be promoted manually. RDS does not expose replication slots natively, so you cannot use logical replication with custom slots. If you need logical replication, use RDS Custom or self-managed on EC2.

Aurora has a completely different architecture — it's not standard replication. Aurora uses a shared-storage volume (6 copies across 3 AZs) rather than WAL shipping. 'Replicas' up to 15 can be added with virtually no lag (typically single-digit milliseconds). Failover is automatic within 30 seconds. Aurora shines for read-heavy workloads but has limitations: it does not support all PostgreSQL extensions and has a higher per-row overhead.

Google Cloud SQL for PostgreSQL offers 'grooming' for high availability via synchronous replication within the same region. Read replicas are asynchronous and can be promoted. Cloud SQL supports pglogical for logical replication if you enable the flag. Understand that failover in Cloud SQL typically takes 1-2 minutes due to DNS propagation and instance restart.

When using any managed service, test failover during low-traffic periods. Know the failover time, understand how connections are redirected, and ensure your application retries on connection loss. The cloud handles the infrastructure, but you own the operational readiness.

Disaster Recovery Planning with Replication

Replication is part of a disaster recovery plan, but it's not the whole plan. A DR plan based solely on replication fails when someone accidentally drops a table (the DROP propagates to replicas), when a widespread outage takes down both primary and replicas (if they're in the same region), or when a bug in application code corrupts data (the corruption spreads to all copies).

Use replication for availability and read scaling, not for backup. Always maintain independent point-in-time backups using pg_dump, WAL archiving (pgBackRest, Barman), or managed backup services. Test restores on a schedule — a backup you haven't tested is not a backup.

Define recovery objectives. RPO (Recovery Point Objective) is how much data you can afford to lose. RTO (Recovery Time Objective) is how fast you need to recover. Synchronous replication gives you RPO=0 (within a region). Asynchronous replication has a small RPO (seconds of WAL in transit). WAL archiving provides point-in-time recovery to any transaction. Map these to your DR scenarios.

Plan for region failures. If your database is in a single region, a regional outage takes everything down. Use cross-region replicas or backups in another region. Cross-region replication has higher lag and cost but reduces recovery time. Test a regional failover process at least once per quarter.

Document and practice the runbook. The best plan is worthless if nobody knows how to execute it under pressure. Run a DR drill during off-peak hours. Simulate a primary failure, a replica failure, a region failure, and a DROP TABLE incident. Measure actual RPO and RTO. Adjust based on results.

Replication Topologies: Beyond the Binary Choice

Most guides stop at master-slave vs multi-master. That's the difference between owning a hammer and knowing there are screwdrivers. In production, you will build hybrids because read patterns are never uniform. A star topology works when your primary lives in us-east-1 and replicas fan out to other regions. Chain replication (replica receiving from another replica) reduces load on the primary but introduces cascading lag. Tree topologies solve geographic distribution without overwhelming a single node. Pick topology based on latency budgets, not dogma. If your replica in Singapore lags by 200ms because it syncs directly from Virginia, you should have used a regional intermediate. Measure round-trip times between nodes before wiring them together. Grafana dashboards that show per-replica lag tiers are not optional—they are your early warning system.

-- io.thecodeforge.replication
-- Detect chain depth by querying replication metadata
SELECT
    replica_host,
    source_host,
    CASE 
        WHEN source_host = primary_host THEN 1
        ELSE 2
    END AS replication_hop,
    seconds_behind_master
FROM replication_status
ORDER BY seconds_behind_master DESC;

Read-Your-Writes Consistency: The Bear Trap

Async replication is fantastic for throughput. Then you write a record, redirect the user to a screen that reads from a replica, and the data is gone. The user reloads. Still gone. They file a ticket and your on-call finds zero rows because replication lag swallowed the write. This is the read-your-writes consistency gap and it burns every junior who thinks "eventually consistent" means "fast enough." The fix is session stickiness. Route reads for a session back to the replica that confirmed the write if and only if that replica has caught up past the write's binlog position. Use an in-memory cache keyed on session_id that stores the last write LSN. Every read from a replica must check that LSN against the replica's current executed_gtid_set. If the replica is behind, stall the read or route to the primary. This adds 1ms per request—cheap insurance against a midnight pager.

# io.thecodeforge.replication
import time
from threading import Lock

class ReadYourWritesGuard:
    def __init__(self, db_cluster):
        self._write_lsn = {}
        self._lock = Lock()
        self._cluster = db_cluster

    def record_write(self, session_id: str, lsn: int):
        with self._lock:
            self._write_lsn[session_id] = (lsn, time.monotonic())

    def get_safe_replica(self, session_id: str, timeout_ms=100):
        write_lsn, timestamp = self._write_lsn.get(session_id, (0, 0))
        if time.monotonic() - timestamp > 0.5:
            return self._cluster.any_replica()  # expired
        start = time.monotonic_ns()
        while (time.monotonic_ns() - start) < timeout_ms * 1_000_000:
            for replica in self._cluster.replicas_by_lag():
                if replica.executed_gtid >= write_lsn:
                    return replica
            time.sleep(0.001)
        # timeout: fall back to primary
        return self._cluster.primary