Database Replica Lag Solution

Solution

1. Use redis as middle man (Best performance)

Redis can be used as middle man between the server and the application.

Advantages

• High performance

Cons

• Need to change application code
• Need to add new infras (redis) which can be costly

2. Use Proxy database (No code change)

We can use a proxy database service if our application doesn't afford code change

How it works:

1. your application talks to the proxy instead of the actual application code.
2. Each proxy will track a GTIDs (Global Transaction ID) of the slave database
   1. This would be use to determine if the slave has reached a specific version number before allow reading
   2. All slave eventually sync to the same GTID

Advantages

• No application code change needed

Cons

• New infrastructure
• Not suit for high performance application
• Suffer from replication lag:
  • By default the distribution of PgBouncer is evenly to all node. So some would get the latest update, some would get stale data
  • We can manually setup to eject the replicas that have high lag out from the load balancing pool by using HAProxy
    • Which can run a script to detect if a database is behind a certain threshold, set it to maintenance mode

3. Apply critical section to read from Master DB (No infras change)

We can apply critical section to always read from master database.

How it works:

1. After the user write, we set a flag in the session to read from master database in the next 5-10 mins (whatever the duration that slave database need to sync up)
2. All their requests are then read from master database to show latest result
3. When the flag expired, request is then read from slave to off-load from master database

Advantage

• Simpler, cheap to implement

Cons

• Need to change application code
• Does not work when many people need to read the latest at the same time

Summary