Pinning Reads to the Primary After a Write in a Web Session

Problem statement: A user submits a form, the write commits, and the very next request in that session reads from a replica that has not yet applied it — so you want a short, targeted window during which that session’s reads are pinned to the primary and then automatically released back to the replicas.

Symptom Identification

Primary-read pinning is the operational counterpart to a read-after-write bug: you reach for it once you have already seen the staleness surface in production. The tells:

  • A “post/redirect/get” flow where the GET after a successful POST renders the pre-write state, then corrects itself on reload.
  • Users who edit a record and land on a list or detail page that still shows the old value for a second or two.
  • Wildly intermittent failures that only reproduce when the app is fanned out across multiple nodes hitting a replica pool, never on a developer’s single-database laptop.
  • A background job kicked off by a write that reads the row back too fast and processes stale input.
  • Session-affinity logic that already sticks users to an application server, but does nothing about which database their reads hit.

If the fix is “just retry the read a moment later,” the correct engineering answer is usually not a retry — it is a short primary-read window that removes the race entirely for the session that just wrote.

Root Cause Analysis

The underlying cause is the same asynchronous propagation delay that every read replica introduces, but the fix here is deliberately coarse and cheap. Instead of comparing exact replication positions on every read — the precise approach covered in read-your-writes consistency with read replicas — you flip a per-session switch when a write happens and route all of that session’s reads to the primary until the switch expires.

This trades a small amount of precision for a large amount of simplicity. You do not need to capture an LSN, carry it through every layer, or query replica replay positions. You need one boolean-with-expiry, keyed to the session or user, checked once per request in routing middleware. The cost is that the primary briefly serves reads it could have offloaded — but only for the sessions that just wrote, and only for a few seconds.

The design decision that determines everything downstream is where the window lives and what keys it. A cookie keyed by session is trivial but browser-local. A shared store keyed by user ID survives multi-device and background-job access. And a time-based TTL is a probabilistic guarantee, while an LSN-gated window is a hard one. The rest of this guide walks the time-based implementation and then shows where the LSN token slots in for flows that cannot tolerate the residual risk. This pattern sits inside the broader practice of managing sticky sessions in distributed database reads, where affinity is applied to the data tier rather than only the web tier.

Architecture: The Window State Machine

Each session moves through three states. A write opens the window; every read while it is open goes to the primary; the window closes on expiry and reads return to replicas.

Primary-read window state machine A session in the replica-reads state performs a write, which transitions it to the primary-pinned state for the length of the window TTL. While pinned, all reads go to the primary. When the TTL expires the session returns to reading from replicas. Replica reads steady state reads → replica pool Primary-pinned window active (TTL) reads → primary WRITE commits set marker + expiry TTL expires → resume replicas read within window read, no window TTL ≈ worst-case replication lag + margin (typically 2–10 s)

Step-by-Step Resolution

Step 1: Open the window on every write

When a request performs any write, stamp the session with an expiry timestamp. Keep the marker cheap — a single timestamp is enough for the time-based design.

python
# Called from your write path (or a post-commit hook)
import time

PRIMARY_WINDOW_SECONDS = 5

def open_primary_window(session_store, key: str) -> None:
    expires_at = time.time() + PRIMARY_WINDOW_SECONDS
    session_store.set(f"pin_primary:{key}", expires_at, ttl=PRIMARY_WINDOW_SECONDS)

The key is the session ID for browser-local guarantees or the user ID for account-wide guarantees. Setting the store’s own TTL equal to the window length means expired markers clean themselves up.

Inline verification: Immediately after a write, session_store.get("pin_primary:<key>") returns a timestamp a few seconds in the future. It disappears on its own once the TTL lapses.

Step 2: Size the TTL to worst-case lag

The window must outlast the replica’s catch-up time, or a stale read slips through at expiry. Derive the TTL from measured lag, not a guess.

sql
-- PostgreSQL: sample recent worst-case replica lag to size the window
SELECT max(EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())))
       AS worst_lag_seconds
FROM pg_stat_replication;

Set PRIMARY_WINDOW_SECONDS to this worst-case figure plus a safety margin (for example, p99_lag * 1.5, floored at 2 seconds). If your replication lag has a heavy tail, prefer the LSN-gated variant in the edge cases section over a very long fixed TTL that wastes primary capacity.

Inline verification: Compare your chosen TTL against a week of the worst_lag_seconds metric. The TTL should exceed the p99 comfortably; if lag routinely approaches the TTL, the window is too short.

Step 3: Force primary reads in middleware

Check the marker once per request and, while the window is active, route every read to the primary. Doing this in middleware keeps individual queries and views ignorant of routing.

python
# Django-style middleware; sets a request-scoped routing flag
import time
from myapp.routers import force_primary_reads   # thread-local context manager

class PrimaryWindowMiddleware:
    def __init__(self, get_response):
        self.get_response = get_response

    def __call__(self, request):
        key = request.session.session_key or request.user_id
        marker = session_store.get(f"pin_primary:{key}")
        if marker and float(marker) > time.time():
            with force_primary_reads():          # all reads this request hit primary
                return self.get_response(request)
        return self.get_response(request)

This reuses the same force_primary_reads mechanism described in ORM middleware for automatic query routing, so the window integrates with routing you may already have.

Inline verification: During the window, SELECT pg_is_in_recovery() on the served read connection returns f (primary). After the window, the same query returns t (replica).

Step 4: Share the marker across application nodes

In a fanned-out deployment, the write and the follow-up read may hit different servers. The marker must be visible to all of them — via a shared store or a signed client cookie.

python
# Signed cookie carrying the expiry (browser-local guarantee, no shared store)
import hmac, hashlib, time

def set_primary_cookie(response, secret: bytes, seconds: int = 5) -> None:
    expires_at = str(int(time.time()) + seconds)
    sig = hmac.new(secret, expires_at.encode(), hashlib.sha256).hexdigest()
    response.set_cookie("pin_primary", f"{expires_at}.{sig}",
                        max_age=seconds, httponly=True, samesite="Lax")

Choose the cookie when the guarantee is per-browser and you want no shared-state dependency; choose the Redis-style store (Step 1) when background jobs or other devices must honour the same window.

Inline verification: Perform the write on one node and the read on another. With the shared marker in place, the second node routes to the primary; without it, the read leaks to a replica.

Step 5: Let the window expire and resume replica reads

No explicit close step is needed — the TTL does the work. When the marker lapses, the middleware check fails and reads flow back to the replica pool automatically, restoring full read-scaling.

python
# No action required; the marker's TTL removes it. Optionally observe it:
def window_active(key: str) -> bool:
    marker = session_store.get(f"pin_primary:{key}")
    return bool(marker and float(marker) > time.time())

Inline verification: After PRIMARY_WINDOW_SECONDS, window_active(key) returns False and a fresh read lands on a replica (pg_is_in_recovery() returns t).

Configuration Snippet

A compact, framework-agnostic configuration for the primary-read window:

yaml
primary_read_window:
  # Where the marker lives: "cookie" (browser-local) or "shared_store" (cross-node)
  backend: shared_store
  store_url: "redis://session-store.internal:6379/3"

  # What the window is keyed on: "session" or "user"
  key_scope: user

  # Window length — size to worst-case replication lag + margin
  ttl_seconds: 5
  min_ttl_seconds: 2

  # Optional hard guarantee: also gate on the write's LSN/GTID
  lsn_gating:
    enabled: false                # true for flows that cannot tolerate any staleness
    max_wait_ms: 800              # bounded wait before falling back to primary anyway

  # Which HTTP methods open the window
  open_on_methods: ["POST", "PUT", "PATCH", "DELETE"]

Verification and Rollback

Confirm the window works end to end

python
# Integration test: write, then assert the immediate read is served by primary
open_primary_window(store, key="user:42")
assert window_active("user:42") is True
# The read within the window must hit the primary:
conn = router.get_read_connection(key="user:42")
assert conn.execute("SELECT pg_is_in_recovery()").scalar() is False

Run this across multiple application nodes if you use a shared store, to confirm the marker is genuinely visible fleet-wide. Then let the TTL lapse and assert a subsequent read returns to a replica. For high-stakes flows, layer the explicit primary-read path from how to force primary reads for critical user transactions on top of the window.

Roll back safely

If the window causes a primary-read surge — for example the TTL was set too long or key_scope: user fanned it across too many requests — reduce blast radius without a redeploy:

  1. Shorten ttl_seconds (or set it to min_ttl_seconds) in config and reload; existing markers expire quickly.
  2. If needed, disable the window entirely by clearing the marker prefix: redis-cli --scan --pattern 'pin_primary:*' | xargs redis-cli del. Reads immediately revert to replicas.
  3. Correctness during rollback is unaffected — you only lose the read-after-write protection, and the primary sheds the extra load instantly.

Because the primary is always current, over-pinning is a performance regression, never a correctness one — which makes this a low-risk pattern to dial up or down in production.

Edge Cases and Gotchas

A fixed TTL is a bet, not a guarantee

A time-based window assumes lag stays below the TTL. During a lag spike — a bulk import, a long DDL, a saturated replica — the replica can still be behind when the window expires, and a stale read slips through at the boundary. If a flow cannot tolerate even that rare miss, gate the window on the write’s LSN (PostgreSQL pg_current_wal_lsn()) or GTID set and only end it when a replica’s replay position passes the mark. This is the precise mechanism detailed in read-your-writes consistency with read replicas; the time window and the LSN gate compose cleanly, with the timer as a cheap default and the LSN check reserved for critical paths.

Keying on session misses multi-device and background work

If you key the window on the browser session, a write from a phone will not pin reads for the same user’s laptop, and a background job triggered by the write reads with no window at all. When account-wide correctness matters, key on user ID and store the marker in a shared store so every request path — web, mobile, worker — consults the same window. The cookie approach is fundamentally single-browser and cannot cover these cases.

Idempotent retries and double-open

Clients retry POSTs, and load balancers replay requests. If opening the window has side effects beyond setting a timestamp, a retry can double them. Keep open_primary_window idempotent: writing the same expiry key twice is harmless, and taking max(existing_expiry, new_expiry) ensures a retry never shortens a live window. This mirrors the monotonic-watermark discipline used in LSN token tracking and keeps the window from flickering closed under retry storms.

FAQ

Should I store the primary-read window in a cookie or a server-side store?

Use a cookie when the guarantee only needs to hold for the same browser and you want zero shared-state dependencies; the client carries a signed expiry timestamp. Use a server-side store such as Redis when the window must hold across devices, background jobs, or requests that do not carry the cookie. The server-side store also lets you key the window on user ID rather than session, which covers multi-tab and multi-device access to the same account.

How long should the primary-read window last?

Set the window to your worst-case observed replication lag plus a margin, commonly 2 to 10 seconds for healthy fleets. Too short and the replica may still be behind when the window expires, reintroducing the stale read. Too long and you needlessly push read load onto the primary. A fixed TTL is simple; an LSN token is more precise because it ends the window exactly when the replica catches up rather than on a guessed timer.

Does a time-based window guarantee the replica has caught up?

No. A fixed TTL is a probabilistic bet that lag stays below the window length; if a lag spike outlasts the TTL, a stale read slips through at expiry. For a hard guarantee, gate the window on the write’s LSN or GTID and only end it once a replica’s replay position has passed that mark. Many teams use a time window for its simplicity and layer LSN gating on the few flows that cannot tolerate any staleness.

← Back to Managing Sticky Sessions in Distributed Database Reads