Database Replication: Synchronous vs Asynchronous Explained

Imagine you're building a global e-commerce platform. Your database is the backbone, storing orders, inventory, and user data. If your primary database goes down, you lose everything. That's where replication comes in: you maintain copies of your data on multiple servers. But how do you keep those copies in sync? The answer lies in two strategies: synchronous and asynchronous replication. Synchronous replication waits for all copies to confirm a write before telling the client it's done, ensuring strong consistency but adding latency. Asynchronous replication lets the write complete immediately on the primary and propagates changes later, offering better performance but risking data loss. In this tutorial, we'll dive deep into both approaches, their trade-offs, real-world incidents, and how to debug replication issues in production.

What is Database Replication?

Database replication is the process of copying data from one database server (the primary) to one or more other servers (replicas). This ensures high availability, fault tolerance, and read scalability. Replication can be synchronous or asynchronous, affecting consistency and performance. In synchronous replication, the primary waits for all replicas to acknowledge a write before committing. In asynchronous replication, the primary commits immediately and sends changes to replicas later. The choice depends on your application's consistency requirements and latency tolerance.

-- Example: Setting up MySQL asynchronous replication
-- On primary server:
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS; -- note File and Position

-- On replica server:
CHANGE MASTER TO
  MASTER_HOST='primary_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=12345;
START SLAVE;
SHOW SLAVE STATUS\G

Synchronous Replication: Strong Consistency at a Cost

In synchronous replication, the primary waits for all replicas to confirm that a write has been applied before acknowledging the client. This guarantees that all replicas have the same data at all times, providing strong consistency. However, this increases write latency because the slowest replica determines the response time. If a replica is down, the primary may block or fail the write. Synchronous replication is ideal for applications where data integrity is critical, such as financial transactions or inventory management. Common implementations include MySQL Group Replication, PostgreSQL synchronous replication, and Google Spanner.

-- PostgreSQL synchronous replication setup
-- On primary: postgresql.conf
synchronous_standby_names = 'standby1, standby2'

-- On standby: recovery.conf
primary_conninfo = 'host=primary_host port=5432 user=repl password=password'

-- Check sync state on primary:
SELECT application_name, state, sync_state FROM pg_stat_replication;

Asynchronous Replication: Performance with Trade-offs

Asynchronous replication allows the primary to commit writes immediately without waiting for replicas. Changes are sent to replicas asynchronously, often via a binary log or write-ahead log. This results in low write latency and high throughput, but introduces a risk of data loss if the primary fails before changes are replicated. Replicas may lag behind, causing stale reads. Asynchronous replication is suitable for read-heavy applications, analytics, or geographical distribution where eventual consistency is acceptable. Examples include MySQL asynchronous replication, MongoDB replica sets, and Cassandra.

-- MySQL asynchronous replication status check
SHOW SLAVE STATUS\G
-- Key fields:
-- Slave_IO_Running: Yes
-- Slave_SQL_Running: Yes
-- Seconds_Behind_Master: 0 (ideal)

-- Simulate a write on primary:
INSERT INTO orders (id, product) VALUES (1, 'Laptop');
-- On replica, the insert appears after a short delay.

Semi-Synchronous Replication: A Middle Ground

Semi-synchronous replication is a compromise: the primary waits for at least one replica to acknowledge the write before committing. This reduces data loss risk while keeping latency lower than full synchronous replication. If no replica acknowledges within a timeout, the primary falls back to asynchronous mode. This is useful for applications that need better consistency than async but can tolerate some latency. MySQL and PostgreSQL support semi-synchronous replication.

-- MySQL semi-synchronous replication setup
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- 1 second timeout

-- On replica:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;

Choosing Between Synchronous and Asynchronous

The choice depends on your application's consistency requirements, latency tolerance, and network conditions. Use synchronous replication for critical data that cannot be lost (e.g., financial transactions). Use asynchronous replication for read-heavy workloads or when low write latency is paramount. Consider semi-synchronous as a balanced option. Also factor in geographical distance: synchronous replication over long distances can be very slow. In multi-region deployments, asynchronous replication is common, often with conflict resolution strategies.

-- Hypothetical decision query
SELECT CASE
  WHEN consistency_required = 'strong' AND latency_tolerance = 'low' THEN 'Synchronous'
  WHEN consistency_required = 'eventual' AND latency_tolerance = 'high' THEN 'Asynchronous'
  ELSE 'Semi-synchronous'
END AS replication_type;

Monitoring and Troubleshooting Replication

Effective monitoring is crucial for replication health. Key metrics include replication lag, IO and SQL thread status, and error logs. Tools like MySQL's SHOW SLAVE STATUS, PostgreSQL's pg_stat_replication, and third-party solutions (e.g., Percona Monitoring and Management) help. Common issues: network latency, disk I/O bottlenecks, and data conflicts. For async replication, lag can spike during heavy writes. For sync, a slow replica can block writes. Always have a rollback plan.

-- MySQL replication monitoring queries
-- Check lag
SELECT TIMESTAMPDIFF(SECOND, last_seen, NOW()) AS lag_seconds
FROM information_schema.global_status
WHERE variable_name = 'Seconds_Behind_Master';

-- Check threads
SHOW PROCESSLIST;
-- Look for 'Binlog Dump' on primary and 'Slave_IO'/'Slave_SQL' on replica

Advanced Topics: Multi-Master and Conflict Resolution

Multi-master replication allows multiple nodes to accept writes, increasing write scalability but introducing conflict resolution challenges. Synchronous multi-master (e.g., Galera Cluster) uses certification-based replication to avoid conflicts. Asynchronous multi-master (e.g., MySQL master-master) requires application-level conflict resolution or last-write-wins strategies. Conflict resolution can be based on timestamps, vector clocks, or custom logic. This is common in distributed databases like Cassandra and DynamoDB.

-- Example: last-write-wins conflict resolution
-- Assume two masters insert same key
-- Master A: INSERT INTO users (id, name, updated_at) VALUES (1, 'Alice', '2025-01-01 10:00:00');
-- Master B: INSERT INTO users (id, name, updated_at) VALUES (1, 'Bob', '2025-01-01 10:00:01');
-- After replication, the later timestamp wins:
SELECT * FROM users WHERE id = 1; -- returns Bob

Logical vs Physical Replication: PostgreSQL Comparison

In PostgreSQL, replication can be implemented at two levels: physical and logical. Physical replication operates at the block level, copying entire data files from primary to standby. It is simple, efficient, and supports all DDL changes automatically, but requires identical hardware and PostgreSQL versions. Logical replication, introduced in PostgreSQL 10, works at the row level using a publish-subscribe model. It allows selective replication of tables, cross-version replication, and even partial data filtering. However, logical replication does not replicate DDL changes automatically and may have higher overhead due to row-level decoding.

Consider a scenario where you need to replicate only a subset of tables to a reporting server. With logical replication, you can create a publication on the primary:

``sql CREATE PUBLICATION sales_pub FOR TABLE orders, customers; ``

Then on the subscriber:

``sql CREATE SUBSCRIPTION sales_sub CONNECTION 'host=primary dbname=mydb' PUBLICATION sales_pub; ``

Physical replication is ideal for full database failover with minimal latency, while logical replication excels in data distribution, upgrades, and multi-master setups. Choose based on your consistency and flexibility needs.

-- On primary server
CREATE PUBLICATION sales_pub FOR TABLE orders, customers;

-- On subscriber server
CREATE SUBSCRIPTION sales_sub CONNECTION 'host=primary dbname=mydb' PUBLICATION sales_pub;

Cascading Replication and Bi-Directional Replication

Cascading replication allows a standby server to act as a source for other standbys, reducing load on the primary. In PostgreSQL, physical cascading replication is straightforward: configure a standby to stream from another standby using the primary_conninfo parameter. For example, set up a cascading standby by pointing it to an intermediate standby:

``sql -- On cascading standby's postgresql.conf primary_conninfo = 'host=intermediate_standby port=5432 user=replicator password=secret' ``

Bi-directional replication (BDR) enables multiple nodes to accept writes and replicate changes to each other. This is more complex and often handled by extensions like BDR or using logical replication in a multi-master setup. For instance, with logical replication, you can create publications and subscriptions in both directions, but conflict resolution must be handled manually or via application logic.

Cascading replication is useful for geographically distributed architectures where you want to minimize direct connections to the primary. BDR is critical for active-active configurations but requires careful design to avoid data conflicts. Always test conflict resolution strategies in a staging environment.

-- On cascading standby's postgresql.conf
primary_conninfo = 'host=intermediate_standby port=5432 user=replicator password=secret'

-- For bi-directional logical replication, create publications on both nodes
-- Node A
CREATE PUBLICATION pub_a FOR TABLE t1;
CREATE SUBSCRIPTION sub_b CONNECTION 'host=node_b dbname=mydb' PUBLICATION pub_b;

-- Node B
CREATE PUBLICATION pub_b FOR TABLE t1;
CREATE SUBSCRIPTION sub_a CONNECTION 'host=node_a dbname=mydb' PUBLICATION pub_a;

Replication Monitoring: Lag, Conflicts, and Healing

Monitoring replication health is critical to ensure data consistency and availability. Key metrics include replication lag (time delay between primary and standby), conflicts (in logical replication), and automatic healing mechanisms.

In PostgreSQL, you can query replication lag using system views:

```sql -- Check physical replication lag SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_bytes, now() - pg_last_xact_replay_timestamp() AS lag_time FROM pg_stat_replication;

-- For logical replication, check subscription status SELECT subname, pg_stat_subscription.last_msg_receipt_time, pg_stat_subscription.latest_end_lsn FROM pg_stat_subscription; ```

Conflicts in logical replication occur when a replicated row violates a constraint (e.g., unique key) on the subscriber. PostgreSQL logs these errors and may skip the conflicting transaction. To detect conflicts, monitor the subscriber logs or use extensions like pglogical. Automatic healing involves retrying failed transactions or re-syncing tables via ALTER SUBSCRIPTION ... REFRESH PUBLICATION.

Set up alerting for lag exceeding thresholds (e.g., > 10 seconds) and implement automated failover with tools like Patroni. Regularly test failover procedures to ensure healing works as expected.

-- Physical replication lag
SELECT pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS lag_bytes,
       now() - pg_last_xact_replay_timestamp() AS lag_time
FROM pg_stat_replication;

-- Logical replication subscription status
SELECT subname, last_msg_receipt_time, latest_end_lsn
FROM pg_stat_subscription;

-- Check for replication conflicts in logs (example)
SELECT * FROM pg_stat_subscription WHERE last_msg_receipt_time < now() - interval '5 minutes';