Balancing Database Read and Write Queries with Replication Lag Handling

Co-author’s:

1. Pankaj Pandey (Senior Technical Architect @ Tata 1mg)
2. Prashant Mishra (Technical Architect @ Tata 1mg)
3. Vimal Sharma (Technical Architect @ Tata 1mg)

In our pursuit of optimizing query performance, we’ve devised a robust strategy to manage read and write queries effectively, despite replication lag. By segregating read and write operations, we prevent undue strain on our database during high loads. To tackle potential replication lag challenges, we’ve implemented an intelligent mechanism that closely monitors lag for specific tables. When lag is detected, we seamlessly redirect read requests to the primary node for those tables, ensuring that users always access the most up-to-date data. This innovative approach not only boosts system responsiveness but also upholds data consistency, ensuring a smooth user experience, regardless of underlying conditions.

Our Approach to Query Management

• Smarter Reads: We divert read queries to replicas, lightening the load on the master and freeing it up for write tasks.
• Mastering Writes: Write queries are directed straight to the master, ensuring data integrity and efficient handling.
• Replication Lag? No Drag: Our system adeptly tackles replica lag. When detected, read queries are intelligently routed to primary, so you always get the freshest data.

How did we Implement?

Initially, we segregated read queries to replicas and write queries to the primary using regex by identifying SELECT statements. Yet, replica data synchronization could lead to replication lag. Our solution? Leveraging PostgreSQL’s Log Sequence Number (LSN) concept to manage this complexity.There are two types of LSNs:

1. Write Log Sequence Number (write LSN): This marks where a data modification was written in the Write-Ahead Log (WAL), recording database changes.
2. Replay Log Sequence Number (replay LSN): This indicates the point till which changes are applied during replication or recovery.

Our strategy revolved around storing both the write LSN for individual tables and the replay LSN for the database within Redis. This enabled us to make informed decisions when it came to recognizing replication lag in tables crucial to our queries. If a lag was detected, we seamlessly routed those queries to the primary database, ensuring the freshest data.Each write action, encompassing insert, delete, or update operations on models or tables, triggered a post-save method within the application. This method tapped into PostgreSQL to retrieve both the write LSN and the replay LSN. Subsequently, these LSNs were updated in Redis, affixed with keys such as “DB_NAME-TABLE_NAME-write_lsn” and “DB_NAME-replay_lsn”.For LSN retrieval, we employed a PostgreSQL query:select write_lsn, replay_lsn, client_addr from pg_stat_replication where usename='rdsrepladmin' and application_name='walreceiver' order by replay_lsn asc limit 1Dealing with multiple replicas, we determined the minimum replay LSN to guarantee comprehensive coverage for replication lag, even in the least updated database.Before executing a select query, Redis came into play. We checked for the existence of keys such as “DB_NAME-TABLE_NAME-write_lsn” and “DB_NAME-replay_lsn”. Based on the values of these keys, our decision matrix was clear:i. If the keys were not found, the select query was sent to a replica.ii. If the write LSN key was located, a comparison ensued against the replay LSN key. If the write LSN was less than or equal to the replay LSN, the query took the path to the replica; otherwise, it headed to the primary.In the event of any condition faltering, our default protocol was to reroute the select query to the primary.While this approach introduced some latency because of Redis operations, the expected impact remained in the sub-millisecond range. In worse cases, it correlated with the timeout set in the Redis operation during the select process.We have centralized the implementation of this logic within our ORM wrapper layer. This allowed all our services to access this functionality without the need to duplicate the code across multiple service boundaries.The above approach is effective for handling write queries executed through the application and will work in all scenarios.However, it may not provide the same level of control when write queries are executed directly on the database. To address this, we have developed a dedicated service called “DB Tracker”. This service is designed to monitor and record the Log Sequence Numbers (LSNs) associated with direct database queries.

DB Tracker Responsibilities:

• The DB Tracker Tool establishes connections with each Primary RDS (Relational Database Service).
• When a service with read/write split changes is deployed, it pushes the details of all master databases it connects to into the “database_names” Redis set, using the “sadd” command.
• These changes are implemented within the service’s Object-Relational Mapping (ORM), and the DB details are pushed to the Redis set during the service’s restart.
• Sample master DB details to be pushed include host, port, database name, user, and password.

{ "host": connection_params["host"], "port": connection_params["port"], "database": connection_params["database"], "user": connection_params["superuser_user"], "password": connection_params["superuser_password"]}

• The DB Tracker tool periodically reads this Redis key to maintain an updated list of databases and establishes a connection with each one.

The DB Tracker has two primary responsibilities:1. Replication Lag Management: It identifies the replay_lsn for all databases listed in Redis and then updates this information in a designated Redis key for each respective database.2. Table Dependency Management:

• For each database listed in above json code snippet, the DB Tracker Tool examines all tables and generates a list of table dependencies.
• A table “B” is considered dependent on table “A” if one of the database triggers on table “A” updates data in table “B” whenever data in table “A” is updated.
• To identify these triggers and trigger-procedures, the tool queries the “information_schema.triggers” table, checking for events related to a specific DB table.

SELECT * FROM information_schema.triggers WHERE event_object_table = '<DB table>';

• The “action_statement” column of the “information_schema.triggers” table contains the trigger-procedure name. Pattern matching is used to extract this name.
• Next, the “pg_proc” table is queried to retrieve the source code of the trigger-procedure, which is stored in the “prosrc” column.

SELECT prosrc FROM pg_proc WHERE proname = '<trigger-procedure name>';

• With the trigger-procedure source code in hand, pattern matching is again used to identify all the table names (dependent tables) mentioned in the source code.
• The core objective behind maintaining this dependency list is to facilitate the retrieval and updating of the write_lsn for all tables that are dependent on a specific table when a write query is executed on that particular table in application.

Essentially, the DB Tracker helps to manage non-application queries directly executed on the database, ensuring smooth operations.Trade Offs in Existing Open Source Solutions: We had explored numerous available tools. However, none of these solutions perfectly suited our needs.

• Many of the tools we evaluated relied on a simple yet less-than-ideal approach: routing all read queries to replica databases completely. While this approach was straightforward, but it introduced latency and replication lag into the system.
• Another approach we encountered was the use of static delays in query routing configurations. For instance, some tools suggested adding a fixed delay of, say, 30 seconds in application configuration. All read queries initiated within this time frame would be directed to the primary database before being allowed to reach the replicas. This approach proved less effective, especially in environments with frequent write operations, as it resulted in the majority of read queries being funneled to the primary database.
• While there were some of the paid solutions which incorporate caching mechanisms, but these came with a significant cost.Given the scale of our operations and the need for a highly efficient solution, we recognized the need for a more tailored and intelligent approach. We developed an in-house system that intelligently routes read queries, ensuring optimal performance and resource utilization for our specific use case.

In conclusion, our approach to query performance optimization, replication lag handling, intelligent routing, and effective resource utilization, along with the contribution of the DB Tracker tool, results in a robust, high-performance, and scalable system. This strategy ensures that our system consistently delivers exceptional user experiences, regardless of the underlying conditions. It also emphasizes optimal resource allocation, enabling our system to operate efficiently and at scale.

---

Balancing Database Read and Write Queries with Replication Lag Handling was originally published in Tata 1mg Technology on Medium, where people are continuing the conversation by highlighting and responding to this story.