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) --- USE THIS FIRST

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) — read-your-writes consistency
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