Database Replication strategy in large systems

🚀 Context

Suppose you are building an e-commerce system that serves customers nationwide. When a customer places an order on the website, information about the order, products, payment, etc. needs to be stored in the database.

Some of the issues that can arise in this scenario are:

• Performance: As the number of customers and orders increases, the primary database will have to handle more and more requests. This can lead to slow performance and affect the customer experience.
• Failure and recovery: If something goes wrong with the primary database (e.g. hardware failure, power outage, etc.), customer data will be lost, severely impacting business operations.

To overcome these issues, we will need the help of Database Replication.

🌿 What is Replication?

Database replication is the process of copying and storing copies of a database across multiple database servers.

This process involves copying data from a master database to slave databases. Slave databases are typically used for read operations.

The main goals of database replication are:

• Ensure data is available and consistent across multiple locations.
• Improve performance by spreading access.
• Increase redundancy in case of a database failure

🏗️ Classification

1. Master-Slave Replication

Master-slave replication is one of the most common models in database replication. In this architecture, there is a primary database called the "master", and one or more secondary databases called "slaves".

How Master-Slave Replication works:

• Write data: All write operations (insert, update, delete) are performed on the master database. Slave databases cannot perform direct write operations.
• Replicate data: Any changes on the master database will be replicated and applied to the slave databases. This process is usually performed asynchronously to optimize performance.
• Read data: Data read requests (select) can be performed on both the master and slave databases. Distributing read requests improves performance.

Advantages of master-slave replication:

• High availability: If the master database fails, one of the slaves can be quickly promoted to become the new master, ensuring that the system continues to operate.
• Load balancing: Read requests can be distributed across multiple slaves, improving system performance.
• Easy to manage: The simple structure with a master database makes deployment, configuration, and management easy. Centralized management at the master also simplifies operations such as monitoring, backup, recovery, and system expansion by adding new slaves.

Disadvantages of master-slave replication:

• Single point of failure: The master database is a single point of failure. If it fails, the entire system will be disrupted until a new master is promoted.
• Data consistency: In asynchronous replication, there may be a delay in data synchronization between the master and slaves. This can lead to temporary data inconsistencies, especially in high-traffic systems.
• Performance bottleneck: All write operations are concentrated on the master database. As the number of slaves increases, the traffic to the master also increases, which can lead to performance bottlenecks.
• If the master database fails, the entire system will be disrupted.
• When the connection between the master and the slave is lost, the data consistency may be affected. Data on the slaves may not be synchronized in time.
• Concentrating write operations on a master database will reduce performance, as the number of slaves increases, the traffic to the master also increases. This can reduce the performance of the master database.

2. Multi-Master Replication

Unlike the master-slave replication model, multi-master replication is a database replication model with multiple databases acting as masters, capable of performing data write operations.

How Multi-Master Replication works:

• All database masters have write access.
• These changes are then replicated and applied to all other databases.
• This process is bidirectional, meaning that all database masters can write data and replicate each other.
• Data reading still occurs in all databases similar to master-slave replication.

Advantages of multi-master replication:

• High availability: When a database master fails, other masters can continue to operate.
• Good scalability: New database masters can be easily added to the system.
• Load distribution: Write operations can be distributed across multiple database masters.

Disadvantages of multi-master replication:

• Complex conflict handling: When there are multiple masters, conflicts when updating data will occur more frequently and more complicatedly. A mechanism for detecting and resolving conflicts is needed.
• Consistency: Depending on the replication mechanism, data consistency may be affected.
• Complexity in deployment and management: Multi-master systems are more complex to set up and operate than master-slave.

3. Asynchronous Replication

In Asynchronous Replication, the replication of data from master database to slave databases takes place in an asynchronous manner.

When there is a request from the client, the changes will be applied on the master database and the changes will be responded to the client, then these changes will be replicated and applied to the slave databases, but not immediately but at another time according to the system rules.

Advantages: Because the master database does not have to wait for the slaves to be updated, the implementation of changes on the master will be faster. This helps improve the overall performance of the system.

Disadvantages: Because the data is not updated continuously, it can lead to inconsistencies between the master and slave databases.

-> Asynchronous Replication is often used in cases where absolute consistency is not required, but performance and latency need to be improved.

4. Synchronous Replication

In Synchronous Replication, the replication of data from the master database to the slave databases takes place synchronously.

When there is a request from the client, the changes will be applied to the master and slave databases at the same time and then responded to the client.

Advantages: Ensure data consistency. When the master fails, it can immediately switch to using another slave, because the data is always synchronized.

Disadvantages: Due to the requirement to synchronize operations between the master and slave, the performance of the system may be affected, especially when there are many write operations.

-> Synchronous Replication is often used in applications that require very high data consistency, even if this affects performance partially. For example, financial, banking, medical systems, etc.

🌟 Conclusion

Database replication is a great solution and can solve many complex problems in system design. However, it is also a problem that engineers need to consider carefully before applying.