What types of replication exist for tables in databases?
sobes.tech AI
Answer from AI
Data replication is the process of copying data from one database (master) to other databases (replicas). Main types:
-
Synchronous replication: A transaction is considered complete only after it is confirmed on both the master and the replica. Ensures high data consistency but may slow down transaction execution due to waiting for confirmation from the replica.
-
Asynchronous replication: A transaction is considered complete immediately after it is executed on the master. Data is copied to the replica with some delay. Provides higher write performance on the master but may lead to divergence between the master and the replica in case of master failure before changes are copied.
-
Semi-synchronous (or Quorum) replication: A compromise between synchronous and asynchronous. A transaction is considered complete after it is confirmed by the master and at least one (or a certain number of) replica. Reduces latency compared to synchronous replication and decreases the risk of data loss compared to asynchronous.
-
Logical replication: Only changes at the logical level (e.g., INSERT, UPDATE, DELETE) are replicated. Allows flexible selection of data and tables to replicate, as well as replication between different versions or even different DBMSs.
-
Physical replication: Changes at the physical level (e.g., data blocks, WAL/redo logs) are replicated. Usually faster than logical replication but less flexible and requires the same version of the DBMS and architecture.
From a topology perspective, replication can be:
- Master-Slave: One master handles writes, and one or more replicas are used for reads.
- Multi-Master: Multiple nodes can simultaneously act as masters. Requires conflict resolution when writing to the same data on different masters.
- Ring (or Circular): Each node can be a master for the next node in the ring and a replica for the previous one.
- Star: One central master replicates data to peripheral nodes, and peripheral nodes replicate back to the central one (less common).
When choosing the type of replication, considerations include:
- Data consistency (Consistency)
- Availability (Availability)
- Performance (Performance)
- Fault tolerance (Fault Tolerance)