Efficient workload balancing in replicated databases based on result lag computation

A computer-implemented method in a computer system comprising a primary database system and a secondary database system, the secondary database system configured as a backup system for the primary database system that is capable of providing at least a minimum amount of essential functionality of the primary database system during a disruption to the primary database system, the method comprising:

• receiving by the primary database system a query request from a client application in advance of receiving a query,
• determining by the primary database system that a routing hint in the query request indicates that workload from the query may be shifted to the secondary database system;
• determining that execution of the query does not involve writing data;
• determining by the primary database system to instruct the client application to route the query to the secondary database system;
• selectively instructing, by the primary database system, the client application to route the query to the secondary database system based on a calculated per-table result lags;
• identifying all source tables implicated by the query having a corresponding replica table;
• and
• determining the calculated per-table result lag based on a last commit time for each source table and a last replayed transaction commit timestamp for the corresponding replica table.

A non-transitory computer readable storage medium embodying programming instruction for performing a method, the method comprising:

• receiving by a primary database system a query request from a client application in advance of receiving a query,
• determining by the primary database system that a routing hint in the query request indicates that workload from the query may be shifted to a secondary database system, the secondary database system configured as a backup system for the primary database system that is capable of providing at least a minimum amount of essential functionality of the primary database system during a disruption to the primary database system;
• determining that execution of the query does not involve writing data;
• determining by the primary database system to instruct the client application to route the query to the secondary database system;
• selectively instructing, by the primary database system, the client application to route the query to the secondary database system based on a calculated per-table result lag;
• identifying all source tables implicated by the query having a corresponding replica table;
• and
• determining the calculated per-table result lag based on a last commit time for each source table and a last replayed transaction commit timestamp for the corresponding replica table.