Fix Cross-Region Replication Lag in AWS RDS

1. Primary Region Write Volume Exceeds Replica Capacity

This is the #1 cause. When your primary database handles more writes than the replica can apply, lag builds up. I've seen this happen on db.r5.large instances with heavy batch jobs or sudden traffic spikes. The replica's apply rate is limited by its instance size and network throughput.

What's actually happening here: The primary writes to its transaction log (binlog for MySQL, WAL for PostgreSQL). The replica streams those logs and applies them. If the primary writes 1000 transactions per second but the replica can only apply 800, you get 200 transactions of lag per second. After 10 seconds, that's 2000 transactions behind.

Fix: Scale the replica or throttle the primary

1. Check the ReplicaLag metric in CloudWatch. If it's climbing steadily instead of spiking and recovering, this is your problem.
2. Upgrade the replica to a larger instance class. For MySQL, at least one size bigger than the primary (e.g., primary is db.r5.large, replica becomes db.r5.xlarge). PostgreSQL replicas benefit more from better I/O than raw compute.
3. If upgrading isn't possible, throttle write-heavy jobs on the primary. A batch job inserting 10 million rows? Break it into chunks of 100k with a 5-second pause between chunks.

# Check replication lag in MySQL
SHOW REPLICA STATUS\G
# Look for Seconds_Behind_Master (MySQL 5.7) or Seconds_Behind_Source (8.0)

The value you want is under 60 seconds for cross-region. Above 300 seconds means trouble.

2. Network Latency or Packet Loss Between Regions

Cross-region replication relies on the network between AWS regions. If you're replicating from us-east-1 to eu-west-2, that's a transatlantic hop. Typical RTT (round-trip time) is 80-100ms. But if there's packet loss or congestion on that path, the replication stream stalls while it retransmits.

The reason step 3 works is that enabling TCP keepalive forces the connection to detect hangs faster. Without it, the replication thread might wait up to 5 minutes before timing out.

Fix: Check network and adjust replication parameters

1. Run mtr or traceroute from the replica's region to the primary region. High latency or packet loss over 1% means the AWS backbone is having issues.
2. Open an AWS Support case if you see consistent packet loss. It's their network, they need to fix it.
3. Adjust the replication timeout settings:

   -- For MySQL
   SET GLOBAL slave_net_timeout = 30;  -- default is 60 seconds
   
   -- For PostgreSQL
   ALTER SYSTEM SET wal_receiver_timeout = '30s';  -- default is 60 seconds
   

   This makes the replica detect a broken connection faster and reconnect, reducing lag from dead connections.

I've seen this fix drop lag from 600 seconds to 30 seconds in us-east-1 to sa-east-1 setups. The shorter timeout catches hung connections before they accumulate backlog.

3. Large Transactions or Long-Running Queries Blocking Replication

Here's one people miss: a single DELETE that removes 2 million rows on the primary. That transaction is sent as one unit to the replica. The replica must apply the whole thing before it can continue. During that time, lag spikes because nothing else from the primary is getting applied.

The reason this happens: MySQL and PostgreSQL replicate transactions atomically. A 10-minute transaction on the primary becomes a 10-minute blocking operation on the replica. The replica can't apply any other writes until that transaction finishes.

Fix: Break up large transactions and tune the replica

1. Rewrite batch operations on the primary to use smaller batches. Instead of one DELETE with no LIMIT, do:
   

   -- Instead of:
   DELETE FROM logs WHERE created_at < '2023-01-01';
   
   -- Do:
   DELETE FROM logs WHERE created_at < '2023-01-01' LIMIT 10000;
   -- Then loop until no rows affected
   

   This breaks the work into small, fast transactions that the replica can apply quickly.
2. For PostgreSQL, increase max_standby_streaming_delay on the replica to avoid conflicts with read queries on the replica itself.
3. Check the replica's query activity during high lag:
   

   -- MySQL
   SHOW PROCESSLIST;
   -- Look for 'System lock' or 'waiting for handler commit' on the replica
   
   -- PostgreSQL
   SELECT * FROM pg_stat_activity WHERE state = 'active';
   -- Look for 'recovery' or 'walreceiver' processes
   

   If you see long-running queries on the replica that conflict with apply, those queries need tuning or scheduling during off-peak hours.

Quick-Reference Summary Table

Start with the first cause. It covers about 70% of cases I've debugged. If that doesn't fix it, move to network issues. The large transaction cause is rarer but can be identified by looking at your application's write patterns.