Data Replication

tags : Scaling Databases, Data Engineering, Database, Distributed Systems, Concurrency Consistency Models

FAQ §

What does replication give? §

• Availability
• Fault tolerance

Retries and Idempotency §

• At-most-once: Send request, don’t retry, update may not happen
• At-least-once: Retry request until acknowledged, may repeat update
• Exactly-once: (retry + idempotence) or deduplication

Issues with idempotency §

• If the functions are different eg. add & subtract on the same data, we don’t have the same gurantees
• $f(f(x))=f(x)\text{but}f(g(f(x)))\ne g(f(x))$

What’s a tombstone? §

• When we actually don’t delete something
• But instead just mark it as deleted
• Can be GC’ed later

Replication Types §

Based on “How” §

• Sync
• Async
• Consensus Protocols

Failure Modes §

Node failures §

• Followers/Secondary: catchup recovery from log
• Leader
  • Detecting leader failure: timeout based detection/Heartbeat message
  • Leader Election: Node learns it’s a leader, communicated w followers
  • Failover

Adding and removing data §

• We can solve this by using logical Clocks and timestamps!
• Every record has a logical timestamp of last write. This tells which values are newer/older.
• Without timestamp even if replicas talked among themselves, they’ll not know which one was the last write. With timestamp that’s solved with a reconcile state / anti-entropy mechanism.

Architecture Types §

Leader-Follower §

Single Leader §

• Distinct Leader: Master or Primary
• Followers: Read replicas, slaves, secondaries, participants
• Flow
  • Read through followers
  • Write through leaders
• Replication
  • Sync: Blocking
  • Async: Non-blocing
  • Semi-Sync : Sometime sync/sometimes async
• Issues
  • With async, the new leader might not have the latest writes

Leaderless §

• See Consensus Protocols

Handling writes in Replicated Systems §

LWW (Last Writer Wins) §

• We just keep the last value and discard others
• Uses Lamport Clocks (total order)

MVR (Multi Value Register) §

• We want to keep all the values from all clients here
• This will keep multiple values and cause a conflict, the application code then can decide how to deal with the conflict.
• Uses Vector Clocks (partial order)

TODO Types §

NOTE: Many of these could be different names for same thing

WAL file archiving §

Full table replication §

Full table replication copies all existing, new, and updated data from the primary database to the target, or even to every site in your distributed system.

Key based (incremental) replication §

Key-based incremental replication identifies updated and new data using a replication key column in the primary database and only updates data in the replica databases which has changed since the last update. This key is typically a timestamp, datestamp, or an integer.

Log based (incremental) replication §

Log-based incremental replication copies data based on the database binary log file, which provides information on changes to the primary database such as inserts, updates, and deletes.

Logical replication §

Trigger based replication §

Real time replication §

Streaming replication §

Multi master replication §

• More often than not, when people think they “need” multi-master they actually don’t.
• the application needs to be written for such a setup

Master-Master replication §

active-active replication §

TO Read §

• Chain replication: https://www.cs.princeton.edu/courses/archive/fall16/cos418/docs/L13-strong-cap.pdf
• I believe MongoDB has moved to a RAFT based algorithm in their new replication protocol, CockroachDB uses a variation on it. It and PAXOS are the two of the most common distributed consensus approaches I believe.
• DDIA : Chapter 5 & 7
• Replication, Clustering, and Connection Pooling - PostgreSQL wiki
• PostgreSQL: Documentation: 16: Chapter 27. High Availability, Load Balancing,…
• https://en.wikipedia.org/wiki/Replication_(computing)
• https://arpitbhayani.me/blogs/multi-master-replication/
• https://arpitbhayani.me/blogs/master-replica-replication/
• https://www.reddit.com/r/SQLServer/comments/brqhfc/is_transactional_replication_the_right_approach/
• https://www.reddit.com/r/dataengineering/comments/130ls2m/how_does_your_company_handle_replication_and_cdc/
• https://debezium.io/
• https://docs.google.com/spreadsheets/d/1DwobnPHZCCAYCcgR3u62-XFRwWQbbTXeurYo2-2rR0A/edit#gid=0
• https://www.reddit.com/r/PostgreSQL/comments/9fjeoe/postgresql_tutorial_getting_started_with/
• https://redis.com/blog/what-is-data-replication/
• https://www.keboola.com/blog/database-replication-techniques
• https://learn.microsoft.com/en-us/sql/relational-databases/replication/types-of-replication?view=sql-server-ver16
• https://github.com/neuroforgede/pg_auto_failover_ansible
• https://github.com/hapostgres/pg_auto_failover
• https://www.2ndquadrant.com/en/blog/logical-replication-postgresql-10/
• https://www.citusdata.com/blog/2018/02/21/three-approaches-to-postgresql-replication/
• https://www.reddit.com/r/PostgreSQL/comments/10qn58p/logical_replication_in_postgresql/
• https://medium.com/@piyushbhangale1995/logical-replication-in-postgresql-c448e4b3eb95
• https://www.reddit.com/r/PostgreSQL/comments/14g0vls/framework_for_achieving_postgresql_multimaster/
• https://medium.com/@atakanserbes/understanding-database-replication-a-practical-overview-dbe23344acc7
• https://www.enterprisedb.com/
• Follower Reads