Owing to a bit of free time, I've been digging into my personal notebook from over the years, and putting some of my prior experiences on to paper. And today's one one is again straight outta the archives!
When dealing with read-your-own-writes (RYOW) issues in a read-heavy app, the first suggestion is almost always to just turn on synchronous replication.
There is a (wide-spread?) misconception that If the primary db waits to ack the write until all replicas confirm they have saved the data, the RYOW problem gets solved. BUT, it is a massive trap, and exposes a fundamental misunderstanding of what "synchronous" actually means to the db engine.
In standard sync replication, the primary only waits for the replica to confirm it has written the transaction to its Write-Ahead Log (WAL) on disk.
It guarantees durability, not visibility.
The replica has safely saved the data, but it has not necessarily applied it to the actual tables in memory yet.
If a read query hits that replica 1ms after the ack returns, the user will still see stale data because the replica's local apply process hasn't caught up.
To guarantee true visibility, you have to force the primary to wait until the replica has fully applied the transaction to memory (like using remote_apply in Postgres).
Configuring this extreme level of strict sync repl. not just adds latency penalties to the WRITE OP., it also ties write availability directly to the slowest read replica. If a single replica goes down, or the node crashes, the primary db literally stops accepting writes.
A minor read staleness issue suddenly transforms into a complete system outage.
Solving this at the db replication layer is the wrong approach. The routing layer is where this needs to be handled. There are two primary architectural patterns to fix this without tanking write performance.
1. Sticky routing (simpler)
When a user makes a write, a timestamp is dropped in their session cache. For a short window (say, 5 seconds), the app routes all of that specific user's reads directly to the primary db. Everyone else keeps hitting the replicas.
# Write operation
def update_profile(user_id, user_data):
primary_db.write(user_data)
# Drop a flag in cache that expires in 5 seconds
cache.set(f"ryow_flag_{user_id}", True, ttl=5)
# Read operation
def get_profile(user_id):
# Check if the user recently wrote data
if cache.get(f"ryow_flag_{user_id}"):
return primary_db.read(user_id) # Safe fallback
return replica_db.read(user_id) # Standard path
Pros:
Incredibly easy to implement.
Most frameworks support it out of the box or with simple middleware,
requiring zero changes to db engine internals.
Cons:
It relies on arbitrary time windows, which is essentially just hoping for the best, and If repl. lag spikes past the defined window, the issue returns.
It also puts unnecessary read load on the primary db for those 5 seconds, even if the replicas caught up in 10ms.
gets worse with the number of WRITE ops scaling.
caching SPOF
2. LSN tracking (complicated, but precise way)
Every write generates a log sequence number (LSN). The app stores that LSN in a cache, and queries it first in case of a read operation, essentially baking in the logic not to answer the query from a RR until it has processed the WAL up to this specific LSN. If the replica is too far behind, the app falls back to the primary db.
# Write operation
def update_profile(user_id, user_data):
# Db returns the exact log position of the write
lsn = primary_db.write_and_get_lsn(user_data)
cache.set(f"user_lsn_{user_id}", lsn)
# Read operation
def get_profile(user_id):
required_lsn = cache.get(f"user_lsn_{user_id}")
replica_lsn = replica_db.get_current_lsn()
# Mathematically guarantee the replica has applied the WAL
if replica_lsn >= required_lsn:
return replica_db.read(user_id)
return primary_db.read(user_id) # Fallback if replica is lagging
Pros:
Mathematically perfect consistency.
Stale data is never served, and replica usage is maximized because routing to the primary db only happens if absolutely necessary.
Cons:
Massive engineering effort.
It tightly couples app code to specific db engine internals,
and almost no standard ORMs support this natively.
the queries to the RR doubles.
caching SPOF
NOTE: There might be other (efficient) approaches too in addition but these 2 are the ones that I personally have come across.
The takeaway
Sticky routing often wins simply because the engineering overhead of building custom LSN trackers is rarely worth it for majority of RYOW apps.
To me accepting a slight bump in primary db load to keep app code simple is usually the right trade-off while keeping the primary db highly available.
Next time the db layer scales out, remember the golden rule of infra: never let read capacity compromise write availability.
