---
title: "Read Consistency with Database Replicas"
description: "Database replicas can improve read-heavy application performance and reduce pressure on primary servers, but differing replication lag can make multi-query reads produce incoherent datasets. Shopify’s Database Connection Management team compared tight consistency and GTID-based causal consistency before choosing opt-in monotonic read consistency, in which related reads are routed to one replica and therefore follow a consistent timeline rather than necessarily seeing the latest data. Their ProxySQL fork accepts a UUID in the consistent_read_id query comment, hashes it into a pseudorandom index over a weighted hostgroup server list, and disables later rebalancing that could override the selection. The implementation indexes configured servers before handling outages, though a selected server failure can still break consistency. The approach offers low overhead but tolerates occasional inconsistencies."
---

# Read Consistency with Database Replicas

[Shopify](https://yomu.fyi/company/shopify) · 2023-10-18 · Feb 22, 2021

**Type:** Explainer

## Summary

Database replicas can improve read-heavy application performance and reduce pressure on primary servers, but differing replication lag can make multi-query reads produce incoherent datasets. Shopify’s Database Connection Management team compared tight consistency and GTID-based causal consistency before choosing opt-in monotonic read consistency, in which related reads are routed to one replica and therefore follow a consistent timeline rather than necessarily seeing the latest data. Their ProxySQL fork accepts a UUID in the consistent\_read\_id query comment, hashes it into a pseudorandom index over a weighted hostgroup server list, and disables later rebalancing that could override the selection. The implementation indexes configured servers before handling outages, though a selected server failure can still break consistency. The approach offers low overhead but tolerates occasional inconsistencies.

## Context

Using replicas for reads can improve performance and reduce pressure on primary database servers, but replication lag varies between replicas. When related data is assembled through multiple queries sent to different replicas, those differences can produce unpredictable or mangled results.

## Approach / What changed

Shopify modified its ProxySQL fork to support opt-in monotonic read consistency. Applications add a UUID-based consistent\_read\_id in query comments; ProxySQL hashes the identifier into a repeatable index over the weighted hostgroup server list, preserves the selection across requests, indexes configured servers before handling failures, and disables additional rebalancing for these requests.

## Takeaways

- Tight consistency keeps replicas current but is expensive and reduces the performance benefits of replica reads.
- GTID-based causal consistency can enforce a minimum known replica state, but Shopify rejected it because it required software on every replica and added unnecessary complexity for its use cases.
- Hash-based server selection provides low-overhead monotonic reads, but outages—particularly intermittent ones—can still introduce difficult-to-detect inconsistencies.

**Tags:** [MySQL](https://yomu.fyi/topic/mysql), [Performance](https://yomu.fyi/topic/performance), [ProxySQL](https://yomu.fyi/topic/proxysql)

- Source: [Shopify](https://shopify.engineering/read-consistency-database-replicas)
- Source URL: https://shopify.engineering/read-consistency-database-replicas
- Ingested by Yomu: 2026-08-31T01:10:16.680Z

[Read original post](https://shopify.engineering/read-consistency-database-replicas)