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 happenAt-least-once
: Retry request untilacknowledged
, may repeat updateExactly-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
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
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