Course contentsShow
← System Design
Lesson 1363 of 1,91934. Data Replication StrategiesFree lesson

Statement-Based vs Row-Based Replication

Comparing replicating SQL statements versus actual data changes, and their implications for consistency.

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.