What types of table replication exist in databases?

Replication is the process of creating and maintaining multiple copies of data in different locations.

Main types of table replication:

1. Master-Replica (One-way, Asynchronous/Synchronous):

   • Master-Asynchronous Replica: Changes are made on the master server and asynchronously replicated to replicas. Fast write on master, but data loss is possible if the master fails before replication.
   • Master-Synchronous Replica: Changes on the master are considered complete only after they are confirmed on all replicas. Ensures data consistency but slower write on master.

2. Multi-Master (Two-way):

   • Changes can be made on any master server and then replicated to all others. Requires conflict resolution when the same data is changed simultaneously on different servers.

3. Peer-to-Peer:

   • Each replication participant can read and write data, and changes are propagated to all others. This is a generalization of Multi-Master, where there is no strict "master" concept.

4. Transactional Replication:

   • Individual transactions are replicated as they occur. Suitable for scenarios where low latency and high consistency are important.

5. Snapshot Replication:

   • A full copy of data (snapshot) is created at a specific point in time and replicated to subscribers. Used for initial synchronization or for tables that rarely change.

6. Merge Replication:

   • Participants work autonomously and then merge changes. Used in scenarios with mobile users or geographically distributed systems. Requires complex conflict resolution.

7. Logical Replication:

   • Not physical data representation is replicated, but logical changes (INSERT, UPDATE, DELETE operations). Allows data replication between databases with different schemas or even different DBMS.

Examples:

• MySQL Replication (Master-Replica)
• PostgreSQL Logical Replication
• MongoDB Replica Sets (Primary-Secondary)
• Galera Cluster (Multi-Master)