Replica Reads and the Stale Data Problem
Shopify has relied on database replication for redundancy and failure recovery for a long time, but only recently began treating replicas as a viable read-only data source for applications. The appeal is straightforward: offloading reads to replicas relieves pressure on primary servers and can improve performance for read-heavy workloads. But replication lag complicates the picture. Data read from a replica can be seconds or even minutes stale, which is acceptable for many atomic queries but problematic when results are assembled from multiple related reads.
If a series of queries is routed to different replicas with varying lag, and the underlying data changes mid-sequence, the assembled result can be internally inconsistent. A row returned by one query might be missing from a related table when the next query lands on a more lagging replica. That kind of mangled dataset not only degrades the user experience; if it informs subsequent writes, it can corrupt data at the source.
Two Consistency Models That Didn't Fit
Tight consistency guarantees every replica is fully synchronized with the primary before any operation proceeds. It eliminates variable lag but is expensive and cancels out most of the performance benefit of using replicas. The primary is still offloaded, but reads from replicas are delayed waiting for synchronization.
Causal consistency built on global transaction identifiers (GTIDs) was another candidate. Under this model, every transaction on the primary carries a GTID that survives replication. Requests can be conditioned on the presence of a specific GTID in a replica, ensuring the replica is at least as current as some known prior state from a read or write the application has already performed. It falls short of tight consistency but is practically equivalent for many use cases.
The cost is operational complexity: each replica needs software to report its current GTID back to the proxy so server selection can honor a minimum GTID requirement. Shopify's Database Connection Management team ultimately decided its use cases didn't justify that level of guarantee.
Hash-Based Monotonic Reads
The team settled on a middle ground: monotonic read consistency. Successive reads follow a consistent timeline, but they may not reflect the absolute latest data in real time. The simplest way to guarantee this is to route related reads to the same server, so each read reflects the primary's state at the same moment or later than the previous read in the sequence.
The implementation is deliberately opt-in to avoid overhead for applications that don't need it, and it requires no application awareness of database topology or replica management. Applications reach Shopify's MySQL servers through a proxy layer based on ProxySQL, which organizes servers into hostgroups: pools of interchangeable servers that appear as a single data source to the application. Normally, the proxy routes each request to a randomly chosen server within the hostgroup, with selection weighted by latency and load balancing considerations.
Shopify modified this selection algorithm in its fork of ProxySQL. An application that needs consistency across a series of requests supplies a unique identifier in a query comment:
/* consistent_read_id:<some unique ID> */ SELECT <fields> FROM <table>
The identifier is a UUID representing a job or other sequence of related requests. Instead of random selection, the proxy hashes the consistent_read_id to an integer, then takes that value modulo the number of servers to derive a pseudorandom index into the server list. The same identifier always yields the same index, so every request in the series lands on the same replica. The actual implementation accounts for configurable server weighting, but the principle holds.
Two Edge Cases Fixed in Review and Testing
The first problem surfaced during code review and concerns server availability. If a server goes down between successive consistent-read requests, and that server was the one previously selected, inconsistency is unavoidable. But the team found that applying the selection algorithm directly to the list of currently available servers could cause unnecessary inconsistency even when the downed server wasn't the selected one: the server count changes, so the modulo result shifts for all identifiers, not just the one tied to the failed server. The fix indexes into the full list of configured servers in the host group first, then disqualifies the selected server and reselects if needed. The outcome is only affected when the selected server is actually down.
The second issue appeared as an intermittent bug causing inconsistent reads in a small percentage of cases. ProxySQL was performing an extra round of load balancing after the initial server selection. With a 1:1 target weighting, if the real distribution drifted to 3:1, ProxySQL would forcibly reroute requests to the underweighted server, overriding the hash-based selection. Disabling this additional rebalancing for consistent-read requests eliminated the stray inconsistencies.
Trade-Offs and Open Questions
Shopify is now exploring ways to incorporate replication lag measurements as a tunable factor in the read-consistency approach. The hash-based method's advantages are simplicity and low overhead. Its principal weakness: server outages, especially intermittent ones, can introduce read inconsistencies that are hard to detect. For applications that can tolerate occasional consistency failures, this approach to monotonic reads is a reasonable fit. Applications with stricter requirements would do better to investigate GTID-based causal consistency, which ProxySQL also supports.
