What is Database Replication?
Learn what database replication is, how primary-replica sync works, and why it improves availability and read performance.
Expected Interview Answer
Database replication is the process of continuously copying data from one database server (the primary) to one or more other servers (replicas), so multiple copies of the same data stay in sync.
Replication improves availability, since a replica can take over if the primary fails, and it improves read scalability, since read queries can be spread across replicas instead of hitting one server. Changes are typically streamed from the primary’s transaction log to each replica, either synchronously (waiting for replicas to confirm) or asynchronously (replicas catch up shortly after). The trade-off is complexity: replication lag, conflict handling in multi-primary setups, and failover coordination.
- Improves availability through failover
- Scales read throughput across replicas
- Enables geographic distribution closer to users
- Provides a live backup for disaster recovery
AI Mentor Explanation
Think of a cricket board that keeps an official master scorebook at the stadium, but also has a runner relay every update to a second scorebook kept at a nearby broadcast van, in case the stadium book is damaged or delayed. Fans checking the broadcast van’s copy see nearly the same data, just moments behind. Database replication works the same way: a primary keeps the master record, and replicas continuously receive the same updates in case the primary becomes unavailable.
Step-by-Step Explanation
Step 1
Designate a primary server
All writes are directed to the primary, which records changes in its transaction log.
Step 2
Stream changes to replicas
The primary sends its log of changes to one or more replica servers.
Step 3
Apply changes on replicas
Each replica applies the received changes, keeping its data set synchronized with the primary.
Step 4
Serve reads or fail over
Replicas can handle read traffic, and a replica can be promoted to primary if the original fails.
What Interviewer Expects
- Understanding of primary-replica architecture
- Awareness of synchronous vs asynchronous replication
- Knowledge of replication lag and its implications
- Ability to explain benefits: availability, read scaling, disaster recovery
Common Mistakes
- Confusing replication with sharding or backups
- Not mentioning replication lag in asynchronous setups
- Assuming all replicas can always accept writes without conflict handling
- Failing to distinguish primary-replica from multi-primary replication
Best Answer (HR Friendly)
“Database replication means continuously copying data from a primary database to one or more replica servers, so they stay in sync. This improves availability, since a replica can take over if the primary fails, and improves performance, since read traffic can be spread across the replicas.”
Code Example
-- Writes always go to the primary
INSERT INTO Orders (customer_id, total) VALUES (101, 250.00);
-- Reads can be routed to a replica to reduce load on the primary
-- (application-level routing, not a single SQL statement)
SELECT * FROM Orders WHERE customer_id = 101;
-- executed against a read replica, which may be milliseconds behind
-- the primary due to replication lagFollow-up Questions
- What is the difference between synchronous and asynchronous replication?
- What is replication lag and why does it matter?
- How does replication differ from sharding?
- What happens during failover when a primary server goes down?
MCQ Practice
1. What is the main purpose of database replication?
Replication continuously copies data from a primary to one or more replicas so they stay synchronized.
2. In asynchronous replication, what can occur between the primary and a replica?
Asynchronous replication does not wait for replica confirmation, so a small delay (replication lag) can occur.
3. What is a key benefit of read replicas?
Read replicas let an application spread read queries across multiple servers, reducing load on the primary.
Flash Cards
What is database replication? — Continuously copying data from a primary database to one or more replica servers.
Synchronous vs asynchronous replication? — Synchronous waits for replica confirmation before committing; asynchronous commits and replicates shortly after.
What is replication lag? — The delay between a write on the primary and that write appearing on a replica.
Why use replication? — For high availability via failover and for scaling read traffic across replicas.