SQL Replication: Distributing Data for Scale and Reliability
Replication copies data from one database to another. It improves read throughput and availability. Here is how I set up and reason about replication. Replication is the process of copying data from a primary database to one or more replicas. The primary handles writes, and replicas serve reads. I use replication for two reasons: read scaling, where I distribute query load across multiple servers, and high availability, where a replica can be promoted to primary if the original fails. Understanding the tradeoffs of each replication mode is essential for choosing the right setup. How Replication Works Replication is based on a log of changes. The primary records every data modification in a log, called the write-ahead log in PostgreSQL or the binlog in MySQL. Replicas read this log and apply the changes to their local copy. The replica is always slightly behind the primary because it must read the log after the primary writes it. This delay is called replication lag. . PostgreSQL: configure the primary wal_level = replica max_wal_senders = 5 . Create a replication user CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'strong_pass'; . On the replica: clone the primary pg_basebackup -h…