Scalable Human Blog

Logical replication consists of a process of replicating data objects and their changes. Key characteristics as followed:

• Based upon their replication identity (primary key)
• The term logical replication is used to contrast with physical replication
  • Logical replication uses the exact block addresses and byte by byte replication
  • PostgresSQL supports both mechanisms concurrently

“Logical replication sends row-by-row changes, physical replication sends disk block changes. Logical replication is better for some tasks, physical replication for others.”

https://stackoverflow.com/questions/33621906/difference-between-stream-replication-and-logical-replication

• Logical replication enables more control over both data replication and security

How does logical replication work?

Logical replication uses a publish and subscribe model.

• This can have one or more subscribers to one more publisher nodes
• Subscribers will pull data from the publishers and they may re-publish data
  • This is to allow cascading replication or more complex configurations

Logical replication of a table, traditionally begins with a snapshot of the data.

1. This is on the publisher database and then subsequently copying this to the subscriber(s)
2. Once completed, the changes on the publisher are sent to the subscriber(s) as they occur in realtime
3. The subscriber applies the data in the same order as the publisher
   • This provides guarantee of transactional consistency
   • This method of replication is also called transactional replication

Logical log for RDBMS

A logical log for RDBMS is usually a sequence of records, describing writes database to tables.

• This is with the granularity of a row
  • For an inserted row a log contains new values all columns
  • For a deleted row the log contains enough information to uniquely identify row that was deleted
    • Identification is typically determined by primary key as previously mentioned
    • But what if there is no primary key? 🤔
      • The old values of all the columns will need to be logged
  • For the updated row the log will contain enough information to uniquely identify the updated row and new values of columns
    • At least all the new values of the columns that change

A transaction that generates several rows?

• This creates several log records
  • Followed by a record that a transaction was committed
    • MySQL bin log when configured to use row based replication uses this approach

Final note

Since logical log is decoupled from the storage engine internals… It can be more easily be backwards compatible.

• This allows the leader and the follower to run different versions of the database software or different storage engines ✅
• A logical log format is also easier for external applications to parse ✅
• This is useful if you want to send contents of a database to external systems ✅
  • Such as data warehouse for offline analysis
  • Or building custom indexes and caches..
    • This technique is called change data capture!

📚 Further Reading & Related Topics

If you’re exploring logical log replication in distributed data-intensive systems, these related articles will provide deeper insights:

• Distributed Data-Intensive Systems: What Is a Conflict? – Learn how conflicts arise in distributed systems and how logical log replication can help resolve them.

• Distributed Data-Intensive Systems: Reading and Writing Quorums – Explore how quorum-based replication strategies, when combined with logical logs, enhance data consistency and system reliability.