Statement-Based vs Row-Based Replication
You'll learn: How databases replicate data by either copying the SQL commands or the actual changed rows, and why the choice matters for consistency.
When a primary database replicates changes to replicas, it has two fundamental approaches: send the SQL statements that caused the change, or send the actual data rows that changed.
Statement-Based Replication
The primary logs each modifying SQL statement (INSERT, UPDATE, DELETE) and replicas re-execute those exact statements.
Think of it like: Sharing a recipe instead of sharing the finished meal. Each replica follows the same cooking instructions.
Benefits:
- Compact: a single
UPDATE users SET status = 'active'might affect thousands of rows but only logs one statement - Network-efficient for bulk operations
Risks:
- Non-deterministic functions cause divergence (e.g.,
NOW(),RAND(),UUID()produce different values on each replica) - Statement order matters critically
- Triggers and stored procedures may behave differently across replicas
Row-Based Replication
The primary logs the actual before/after state of every row that changed. Replicas apply these exact data modifications.
Think of it like: Sending the finished meal itself. No interpretation needed—every replica gets identical food.
Benefits:
- Deterministic: replicas guaranteed to match primary exactly
- Works safely with any function or trigger
- Can replicate from any database engine
Risks:
- Larger log size: updating 10,000 rows logs 10,000 row changes
- More network bandwidth
- Slower for bulk operations
Modern Practice
Most databases (MySQL, PostgreSQL) default to row-based or offer hybrid modes that choose intelligently based on the statement type.
Key Takeaway: Statement-based replication is compact but risks consistency with non-deterministic operations; row-based replication guarantees identical replicas at the cost of bandwidth and storage.