How do the updates happening on the master reach the replica

How do the updates happening on the master reach the replica? ⚡

In a classic master-replica setup, any write operation happening on the master is logged in the replication log file (write-ahead log) as an event. The format in which the event is logged can be one of the following two

1. statement-based
2. row-based

Statement-based Format

The master records the operation as an event in its log, and when the replica reads this log, it executes the same operation on its copy of data. This way, the operation on the Master is executed on the Replica, which keeps it in sync with the Master.

So a simple statement like

UPDATE tasks SET is_done = true WHERE user_id = 53;

is logged as

UPDATE tasks SET is_done = true WHERE user_id = 53;

This keeps the event log files smaller and replication is much quicker. The issue happens when there are non-deterministic operations like rand and uuid, which when executed on a replica will result in a different value.

Row-based Format

In the row-based format, the master logs the updates on the individual data record instead of the operation.

The entry made in the log file would indicate how the data has changed on the master. When the Replica reads this log, it updates its copy of the data by applying the changes on its data items (not having to re-execute the operation).

So a simple statement like

UPDATE tasks SET is_done = true WHERE user_id = 53;

is logged as

tasks:121 is_done=true
tasks:142 is_done=true
tasks:643 is_done=true
tasks:713 is_done=true
tasks:862 is_done=true

The biggest advantage of this approach is that changes can be safely and predictably applied to the replica even when the command is non-deterministic.

This approach suffers from a huge log fan-out. For example, if an operation affects 5000 rows, the master would create 5000 entries in the log file which bloats the log file and slows down the replication.

⚡ I keep writing and sharing these engineering nuggets, so if you are keen on learning them, follow along.

subscribe: youtube.com/c/ArpitBhayani