Database Replication
Database replication is the process of copying and synchronizing data from a primary database to one or more secondary databases, used to improve read scalability, availability, and disaster recovery.
Definition
Database replication is the process of copying and synchronizing data from a primary database to one or more secondary databases, used to improve read scalability, availability, and disaster recovery.
Overview
In the most common configuration, a primary (or "leader") node accepts all writes and streams a log of those changes to one or more replica ("follower" or "secondary") nodes, which apply the same changes to stay in sync. Applications can then route read-heavy traffic to replicas, freeing the primary to handle writes, and can fail over to a replica if the primary becomes unavailable — a core availability pattern for production databases like PostgreSQL and MySQL. Replication can be synchronous, where the primary waits for a replica to confirm it has received a write before acknowledging it to the client (stronger consistency, higher latency), or asynchronous, where the primary acknowledges writes immediately and replicas catch up shortly after (lower latency, but a window where a replica can be slightly behind — "replication lag"). Some systems support multi-primary (multi-master) replication, allowing writes to be accepted on more than one node, at the cost of needing conflict-resolution logic when the same data is modified concurrently on different nodes. Replication is closely related to, but distinct from, sharding: replication duplicates the same data for redundancy and read scaling, while sharding partitions different data across nodes for write and storage scaling; large systems frequently combine both, sharding data across clusters and then replicating each shard for durability. Replication topology and lag behavior are directly shaped by the CAP theorem trade-off a system has chosen — synchronous replication tends toward stronger consistency, while asynchronous replication favors availability and lower write latency. Managed cloud databases like Amazon Redshift and Azure SQL Database automate much of this replication topology, but understanding the underlying trade-offs remains essential for anyone designing a production data tier, a topic covered in PostgreSQL Mastery.
Key Concepts
- Primary node accepts writes; replica nodes apply a synced stream of changes
- Synchronous replication trades latency for stronger write consistency
- Asynchronous replication favors lower latency at the cost of replication lag
- Multi-primary (multi-master) replication requires conflict resolution logic
- Enables read scaling by routing read traffic to replica nodes
- Supports failover and disaster recovery when the primary becomes unavailable
- Complements, but is distinct from, sharding for horizontal scale
Use Cases
Frequently Asked Questions
From the Blog
How to Connect Python to a SQL Database
Learn how to connect Python to a SQL database, run queries safely, load results into pandas, and automate reports — a core skill for every data analyst.
Read More Data ScienceWhat Is a Database? A Plain-English Guide
A database is an organized collection of data stored so it can be easily accessed, managed, and updated by software. This guide explains the core types, how databases work, and why nearly every application depends on one.
Read More Data ScienceWhat Does a Database Analyst Do? Role, Skills, and Path
A database analyst designs, maintains, and optimizes the databases that store an organization's data, ensuring it stays accurate, secure, and fast to query. This guide covers the role's daily work, required skills, and how to break into it.
Read More AI & Technology7 Signs You Don't Need a Dedicated Vector Database
You probably do not need a dedicated vector database when your corpus fits comfortably in memory, query volume is low, filtering dominates ranking, or your existing database already offers vector search. This names seven concrete conditions, the failure modes of choosing wrongly, and the signals that should make you reconsider later.
Read More