What is database replication, and what is the difference between synchronous and asynchronous replication?

What the interviewer wants to hear

• Replication copies changes from a primary to one or more replicas
• It is used for high availability, read scaling and disaster recovery
• Synchronous: commit waits for a replica to confirm, so no data loss but higher latency
• Asynchronous: commit returns at once, replicas can lag and recent data can be lost on failover
• Replication is not a backup, because mistakes replicate too

Model answer

Replication means keeping copies of a database on other servers by sending them every change from the primary, usually by streaming the write-ahead log or binlog. We use it for high availability, so a replica can take over if the primary fails, for read scaling and for copies in another region. With synchronous replication the primary waits until at least one replica confirms it has the change before telling the client the commit succeeded. That gives zero data loss on failover but adds network latency to every write. With asynchronous replication the commit returns immediately, so it is faster, but replicas can lag, and if the primary dies the last few transactions may be lost. Either way replication is not a backup, because a bad DELETE replicates in seconds.

More easy Database Administrator questions

• What is a database index, and what are the costs of adding one?
• Explain the ACID properties of a database transaction with a simple example.
• How would you design a basic backup strategy for a production database?
• What is normalization, and when would you choose to denormalize?
• How do you find the slowest queries on a database server?
• What checks would you do at the start of a normal day as a DBA?
• When would you use a logical backup like pg_dump or mysqldump, and when a physical backup?
• What basic steps would you take to secure a new production database?
• Explain the main SQL join types and give an example of when you would use each.