How to deal with database master-slave replication delay in Laravel

Introduction

If you have a similar setup to us, you might have two databases in different regions to avoid read latency in each region.

Having that master-slave relation works perfectly, and will make your write operations fast in both regions, but writing in the slave from its own region is slow, giving it is actually writing in master (cross-region).
Reading in the slave region right after a write to the master region might return a miss, since there is a replication lag that you also have to be aware.

Setup

Let’s assume we have two Regions: A and B.

To have a bit of context, this setup is made by using Laravel’s database configuration.

Our default database connection has different read / write hosts. The magic occurs on the .env file for Region A, or for Region B.

'default' => [
    'read' => ['host' => env('DB_HOST_READ')],
    'write' => ['host' => env('DB_HOST_WRITE')],
    'database' => env('DB_MASTER'),
    ...

So for Region A .env we have:

# We read and write from the same database
DB_HOST_READ=db.region-A
DB_HOST_WRITE=db.region-A

And for Region B .env we have:

DB_HOST_READ=db.region-B  # We read in B
DB_HOST_WRITE=db.region-A # We write in A

Problem

We are now going to focus on Region B requests.

• The READ operation is on the same Region as the database, so it is FAST.
• The WRITE is a SLOW operation. The request goes to a different region, and has to wait until the operation is over to complete the request.
• The REPLICATION is also SLOW, same reason as above (although faster than cross-region write).

This setup is perfect for making READ operations faster. Ideally, a normal flow should:

• INSERT into MASTER
• REPLICATE into SLAVE
• READ from SLAVE the inserted record

But in reality, we introduced a delay problem.

If we for example register a new user in Region B, we:

• INSERT (WRITE) the user in the MASTER database.
• Wait until it is done inserting (since writing in master is slow).
• Redirect the user to their account.
• SELECT (READ) the user in the REPLICA database to show their new account.
• < User not found, since the REPLICATION didn’t complete yet >

Now you see the real problem.

Solution

Brainstorming

1. One possible solution could be to wait until the replication is done. The problem is that it is not really possible to measure the delay, and that it depends on the load of the database, so we discard that idea.
2. Another idea is to retry the read query until it is ready, but that can be tedious, and has the same problem we had before: we might wait a while for the replication to be done.
3. The next idea is to retry the read query but on the master connection instead. This sounds a bit better.

The question now is: when do we retry?

Retry rules

• If the method expects a model, and the model is null, we retry.
• If the method expects a collection, and the collection is empty, we retry.
• If the method expects to throw an exception, we catch it and retry.
• If the read query is already on the write connection, we don’t retry.

Also, we should only retry on some queries. Not every read query has to have this urgency. Sometimes the user can wait until it is visible, i.e.: a list of documents.
The best practice here will be to return the new document after writing it, and render it dynamically in the Front-end.

Approach

We are going to create "safe" macros; one per SELECT method that we want to replace on the Query Builder.

The most used query methods on our code-base are get, find, first, pluck and findOrFail.

The macros we will have to add to the Query Builder are:

• safeGet : Collection
• safeFind : ?Model
• safeFirst : ?Model
• safePluck : Collection
• safeFindOrFail : Model (but throws an ModelNotFound exception)

Logic Steps:

• Run the original query and grab the result.
• If it was already made using the writePdo, return the result.
• If the connection write is equal to the read connection (for example when in Region A), return the result.
• If we were expecting a collection, and it is not empty, return the result.
• If we were expecting a model, and it is not null, return the result.
• Else, run the same query but on the writePdo, and return the result.

Snippet

Make sure to add that service provider on your providers list in config/app.php

Usage

$user = User::where('email', $email)->safeFirst();

Bonus track: safe load relationships

Loading relationships is a bit trickier.
Basically we have to be aware of a couple things:
a) The relation is not loaded yet. If so, we don’t have to re-run it.
b) If we are dealing with a belongsTo (for example), we have to use ->safeFirst() ; if it is belongsToMany, it’s ->safeGet()
c) Don’t forget to set the relation so it can be accessed by using $user->profile

public function safeLoadProfile(User $user): User
{
    if ($user->relationLoaded('profile')) {
        // If the relation is already loaded, and it's
        // not null, means we don't have to load it again.
        return $user;
    }

    $profile = $user->profile()->safeFirst();
    return $user->setRelation('profile', $profile);
}

That’s it!

If you find it useful, let me know! You can find me on twitter @juampi_92