Database replication
Database replication is the process of copying and synchronizing data from one database (the primary) to one or more other databases (replicas), used to improve availability, read scalability, and disaster recovery.
Overview
Database replication maintains one or more copies of a database that stay synchronized with a primary (or "leader") database as changes occur. Replicas can serve read traffic, provide a failover target if the primary goes down, or support disaster recovery in a different data center or region. Unlike sharding, which splits data into distinct, non-overlapping subsets, replication keeps the same data duplicated across multiple servers.
Replication modes
- Synchronous replication: the primary waits for confirmation that a replica has received a write before acknowledging it to the client. This guarantees replicas are always up to date but adds latency to every write and can block writes if a replica is slow or unreachable.
- Asynchronous replication: the primary acknowledges writes immediately and propagates changes to replicas afterward. This is faster and more resilient to replica slowness, but replicas can lag behind, meaning a reader hitting a replica might see slightly stale data (replication lag).
- Semi-synchronous replication: a middle ground where the primary waits for acknowledgment from at least one replica before confirming the write, balancing durability and latency.
Topologies
- Primary-replica (leader-follower): one primary accepts all writes; replicas serve reads and can be promoted to primary on failure.
- Multi-primary: multiple nodes accept writes and replicate to each other, which improves write availability but requires conflict resolution when the same data is modified concurrently in different places.
Why it matters
Replication is foundational to high-availability systems: it lets applications keep serving reads (and, with failover, writes) even if a database node fails, and it lets read-heavy workloads scale by routing reads to replicas instead of overloading the primary. It's also the mechanism behind many change data capture (CDC) tools, which tap into a database's replication stream (e.g., a write-ahead log or binlog) to capture row-level changes for downstream pipelines, rather than querying the source database directly.
DuckDB, as an embedded single-node analytical engine, doesn't provide built-in primary-replica replication itself — analytical workloads using DuckDB typically read from files (Parquet, CSV) or replicated/CDC'd copies of operational data that have already been extracted from a transactional database, rather than replicating DuckDB instances against each other.
Related terms
Change Data Capture (CDC) is a technique for identifying and streaming row-level inserts, updates, and deletes from a source database as they happen, rather than repeatedly re-reading the whole table.
primary key →A primary key uniquely identifies each database row. Learn SQL definitions, examples, UUID vs auto-increment best practices, and how to fix constraint errors.
Database sharding →Database sharding is a horizontal partitioning technique that splits a large database into smaller, independent pieces called shards, each hosted on a separate server, to scale storage and throughput beyond a single machine.
analytical database →An analytical database, also known as an Online Analytical Processing (OLAP) database, is designed to efficiently handle complex queries and data analysis…
Debezium →Debezium is an open-source distributed platform for change data capture (CDC), which streams row-level insert, update, and delete events out of databases in real time.
relational database →A relational database is a structured collection of data organized into tables with rows and columns.
FAQS
Synchronous replication waits for a replica to confirm it received a write before acknowledging the write to the client, ensuring replicas are current but adding latency. Asynchronous replication acknowledges writes immediately and copies data to replicas afterward, which is faster but can leave replicas slightly behind (replication lag).
Many CDC tools work by reading a database's replication stream — such as a write-ahead log (PostgreSQL) or binary log (MySQL) — the same mechanism used to keep replicas in sync, to capture row-level inserts, updates, and deletes for downstream pipelines without querying the source tables directly.
