Database develop. life cycle - Database Replication and Read Scaling

Introduction

Database replication is the process of maintaining copies of the same database on multiple servers. One database server generally acts as the primary server, while one or more additional servers maintain replicated copies of its data. Changes made to the primary database are transferred to the replica databases so that they remain synchronized. Replication is widely used in systems that need high availability, improved read performance, and better fault tolerance.

In a traditional database system, all application requests may be handled by a single database server. As the number of users increases, the server can become overloaded, particularly when many users are performing read operations such as searching, viewing records, or generating reports. Replication helps distribute these read requests among multiple database servers instead of forcing one server to handle all of them.

How Database Replication Works

In a common replication architecture, the primary database receives write operations such as INSERT, UPDATE, and DELETE. The changes are recorded and transmitted to replica servers. The replicas apply those changes to their own copies of the database.

For example, consider an online shopping application with one primary database and three replica databases. When a customer places an order, the order information is written to the primary database. The change is then replicated to the other database servers. When thousands of customers browse products, their read requests can be distributed among the replicas.

A simplified architecture can be represented as:

                 Application
                      |
              -----------------
              |               |
           Write            Read
              |               |
       Primary Database    Read Router
                              |
                    ---------------------
                    |         |         |
                 Replica 1 Replica 2 Replica 3

The exact replication mechanism depends on the database technology. Some systems use log-based replication, while others use different synchronization mechanisms to transfer changes between servers.

Primary and Replica Databases

The primary database, sometimes called the master database, is normally responsible for processing write operations. It contains the authoritative version of the data.

Replica databases, sometimes called secondary or read-replica databases, maintain copies of the primary database. They can be used to process read-only requests.

For example, suppose an application has the following operations:

INSERT customer
UPDATE customer
DELETE customer
SELECT customer
SELECT products
SELECT orders

The write operations can be directed to the primary database, while suitable SELECT operations can be directed to replicas.

This separation allows the primary server to concentrate more of its resources on operations that modify data.

Read Scaling

Read scaling refers to increasing an application's ability to handle a growing number of read requests by distributing those requests across multiple database servers.

Many applications have a much higher number of read operations than write operations. For example, an online news website may receive thousands of requests to read articles while only a small number of requests modify article information.

Without read scaling:

100,000 Read Requests
        |
        v
Single Database Server
        |
     Overload

With read scaling:

100,000 Read Requests
        |
        v
    Read Router
        |
   -----------------
   |       |       |
Replica  Replica  Replica
   1       2       3

The workload can therefore be distributed across several servers.

Read Replicas

A read replica is a database server specifically used to handle read operations. It receives replicated data from the primary database and makes that data available to applications.

For example, a video streaming platform may have:

Primary Database
       |
   Replication
       |
-------------------------
|          |            |
Replica A  Replica B    Replica C

User requests for video metadata, categories, recommendations, and other frequently accessed information can be distributed among the replicas.

This approach can improve the system's ability to handle a large number of simultaneous users.

Synchronous and Asynchronous Replication

Replication can broadly be implemented using synchronous or asynchronous approaches.

In synchronous replication, a write is not considered complete until the required replica or replicas have acknowledged the change. This can provide stronger consistency between database copies, but it may increase write latency.

In asynchronous replication, the primary database can complete the write without waiting for every replica to acknowledge the change. Replicas receive the changes afterward.

Asynchronous replication generally provides better write performance and lower latency, but there can be a short period during which the replica does not contain the latest data.

For example:

Primary:
Balance = 5000

Replica:
Balance = 5000

Primary updated:
Balance = 4000

Replica may temporarily show:
Balance = 5000

This temporary difference is commonly referred to as replication lag.

Replication Lag

Replication lag occurs when a replica takes some time to receive and apply changes made on the primary database.

Suppose a user changes their profile name. The updated information may immediately exist on the primary database, while a replica may still contain the previous value for a short period.

Replication lag can occur because of network delays, high database workload, slow replica hardware, or a large volume of changes waiting to be processed.

Applications that require the latest information immediately may therefore need to send particular reads to the primary database rather than relying on a replica.

Load Balancing for Read Requests

A read-scaling architecture often uses a load balancer or database-aware routing mechanism to distribute requests among replicas.

For example:

Application
     |
     v
Read Load Balancer
     |
---------------------
|         |         |
DB-1     DB-2      DB-3

The routing system can distribute requests using different strategies. A simple approach is to distribute requests approximately evenly among available replicas. More advanced systems can consider server capacity, current workload, response time, and replica health.

If one replica becomes unavailable, the routing mechanism can stop sending new read requests to that server and direct them to healthy replicas.

Benefits of Database Replication

Database replication provides several important advantages.

Improved read performance: Read requests can be distributed across multiple servers, reducing the workload on the primary database.

Higher availability: If a replica becomes unavailable, other replicas may continue serving read requests.

Fault tolerance: Multiple copies of data reduce dependence on a single database server, although replication should not automatically be treated as a complete backup strategy.

Geographical distribution: Replicas can sometimes be located in different regions so that users can access a database server closer to them.

Reduced primary workload: Moving appropriate read operations to replicas allows the primary database to focus on write operations.

Scalability: Additional replicas can be introduced as read traffic grows, subject to the database technology and application architecture.

Challenges of Database Replication

Replication also introduces several challenges.

The first challenge is replication lag. A replica may temporarily contain older information than the primary database.

The second challenge is increased infrastructure complexity. Instead of managing one database server, administrators must monitor multiple servers and the replication process between them.

The third challenge is failure management. If the primary database fails, an appropriate replica may need to be promoted to become the new primary. This process must be carefully designed to avoid data loss or inconsistent application behavior.

Another challenge is read-after-write consistency. A user may update information and then immediately request that information. If the subsequent read is directed to a replica that has not yet received the update, the user may temporarily see the old value.

Replication and Backup Are Different

Replication should not be confused with database backup.

Replication creates additional copies of database data for availability and workload distribution. If incorrect data is accidentally deleted from the primary database, that deletion may also be replicated to the replicas.

A backup provides a historical copy that can potentially be restored to an earlier state.

Therefore, a production system may use both:

Database Replication
        +
Regular Backups
        +
Recovery Procedures

Together, these mechanisms can address different types of failures.

Real-World Example

Consider an online banking application with millions of customers. Customers frequently perform read operations such as checking account balances, viewing transaction histories, and reviewing account information.

The system can use a primary database for updates and multiple replicas for appropriate read operations.

When a customer transfers money, the transaction is processed by the primary database. The resulting changes are then replicated to the secondary databases.

When customers request historical transaction information, these read requests can potentially be distributed among the replicas.

However, operations where the latest committed information is essential may need carefully designed consistency rules and may be directed to the primary database or handled using a consistency-aware routing strategy.

Conclusion

Database replication creates and maintains multiple copies of database data across different servers. When combined with read scaling, it allows applications to distribute read workloads instead of depending entirely on a single database server. Primary and replica databases, read routing, replication methods, replication lag, and consistency requirements are important concepts in designing such systems.

Replication is particularly useful for applications with large numbers of read requests and high availability requirements. However, it also introduces challenges such as synchronization delays, infrastructure complexity, consistency management, and failure handling. A well-designed replication architecture therefore needs appropriate monitoring, routing, recovery procedures, and backup strategies.