Handling Stale Replica Reads After a Schema Change

A user just saved a profile field that maps to a column you added an hour ago. The write committed on the primary, the API returned 200, and the very next screen — a read — comes back either missing the value or throwing column "loyalty_tier" does not exist. Refresh once and it works. The migration “succeeded,” the primary is correct, yet a slice of traffic sees a database that does not have the column. This is the read-your-writes failure that read/write splitting creates during a schema change: the ALTER TABLE ran on the primary and replicated down the stream, but a lagging replica is still serving the old schema, and your router sent the follow-up read to exactly that replica. The fix is not more DDL — it is teaching the read path to fence off any replica that has not yet caught up to the write.

Symptom / Error Signatures

Stale-replica-read problems after a schema change surface as an inconsistent, refresh-it-and-it-works class of bug. The exact signatures:

• PostgreSQL: a read that names the new column returns ERROR: column "loyalty_tier" does not exist from an application path that worked seconds earlier and works again on retry — the retry landed on a caught-up node.
• MySQL: ERROR 1054 (42S22): Unknown column 'loyalty_tier' in 'field list' on a SELECT routed to a replica while the same column resolves fine against the writer.
• Read-your-writes violation with no error at all: the write committed, but the immediate read returns the pre-write row (a NULL new column, a stale value) because it was served from a replica behind the commit LSN.
• Intermittency keyed to load balancing: the failure rate tracks the fraction of replicas still behind, and disappears entirely when you pin reads to the primary.
• Monitoring: pg_last_xact_replay_timestamp() lag or Seconds_Behind_Source is non-zero on at least one replica while application error logs show sporadic missing-column or empty-result reads.

The tell that separates this from a plain application bug is the correlation: errors appear only during the replication-lag window after a migration, only on the read path, and never against the writer endpoint.

Root Cause Analysis

Replication is asynchronous on both PostgreSQL streaming replication and MySQL. When the primary commits ALTER TABLE ... ADD COLUMN, that DDL is one more record in the WAL or binlog; replicas pull and replay it some milliseconds-to-seconds later. During that window the primary has the new column and the replica does not. Because your router classifies a stand-alone SELECT as a read and sends it to a replica, a read issued right after the write can be served by a node whose catalog still lacks the column — so it either errors (column does not exist) or, for a value change rather than a new column, returns the pre-write row. That is a read-your-writes violation: the session that made a durable write cannot see it because a different node answered the read.

The correct entry point for the schema change is never in doubt — DDL goes to the primary, a rule covered in Routing DDL vs DML Traffic During Migrations. What varies is how each engine lets you prove a replica has caught up so the router can fence it. PostgreSQL exposes replay positions as LSNs you can compare directly; MySQL exposes GTID sets and a wait primitive. The two diverge enough that the fencing code is engine-specific.

The unifying idea: a replica is safe for a given read only when its replay position is at or beyond the LSN (or GTID) at which the schema change — and the user’s own write — committed. Everything below is about capturing that position and gating reads on it.

Immediate Mitigation

When stale reads are firing in production right now, buy consistency back in this order. The first step stops the bleeding in seconds; the rest restore replica offload safely.

1. Pin reads to the primary to stop the errors immediately. Collapsing the split sends every read to the writer, which always has the new column. Safe as an emergency stop whenever the primary can absorb the read load.

-- ProxySQL admin (port 6032) · admin user · emergency stop, sends all reads to the primary
-- Removes the read-split rules; monitor primary load since it now serves reads too.
DELETE FROM mysql_query_rules WHERE rule_id IN (1, 2);
LOAD MYSQL QUERY RULES TO RUNTIME;

2. Capture the commit position of the schema change on the primary. This is the fence line: no replica may serve new-column reads until its replay reaches this point.

-- PostgreSQL · migration/monitoring role · run ON THE PRIMARY · read-only
-- Record this LSN right after the ALTER commits; it is the catch-up target for every replica.
SELECT pg_current_wal_lsn() AS ddl_commit_lsn;

3. Drain any replica whose replay position is behind that fence. A node past the lag budget leaves the read pool until it catches up, so no stale or missing-column read reaches it.

-- PostgreSQL · run ON EACH REPLICA · monitoring role · read-only
-- fully_caught_up = false means this replica must be drained from the new-column read pool.
SELECT pg_last_wal_replay_lsn() >= '0/1A2B3C4D'::pg_lsn AS fully_caught_up,
       now() - pg_last_xact_replay_timestamp()          AS replay_lag;

4. For MySQL, block the read until the replica has executed the write’s GTID. Run this on the replica connection just before the dependent read; it returns once the node has replayed up to that transaction, or times out so you can fall back to the primary.

-- MySQL 8.0 · run ON THE REPLICA connection · user with REPLICATION CLIENT · low-write safe
-- Returns 0 when caught up, -1 on timeout (1s); on -1, re-issue the read against the writer.
SELECT WAIT_FOR_EXECUTED_GTID_SET('3E11FA47-71CA-11E1-9E33-C80AA9429562:1-42', 1);

5. Re-enable the split only for reads that cannot name the new column. Backward-compatible reads (old columns only) are safe on the old schema, so return those to replicas first; keep new-column reads on the primary until every node clears the fence.

Permanent Fix / Long-Term Pattern

The durable fix is to make the read path lag-aware so it can never route a read that needs the new schema to a replica that lacks it. This is the read-your-writes complement to keeping DDL on the writer, and it builds on the routing discipline in the parent Read/Write Splitting Tactics guide.

Fence reads on replay position, not wall-clock lag. Wall-clock lag (“under two seconds”) is a heuristic; the correct gate is positional. Capture the commit LSN (PostgreSQL) or GTID (MySQL) of the write, and let a replica answer the dependent read only when its replay position is at or beyond that mark. Sessions that just wrote carry their commit position (a cookie, a header, or a request-scoped value) so their next read either waits for a replica to reach it or falls back to the writer.

-- PostgreSQL · run ON THE PRIMARY · read-only · which replicas are safe for new-column reads
-- Only rows where fully_caught_up is true may serve reads that name the migrated column.
SELECT application_name,
       replay_lsn,
       (replay_lsn >= pg_current_wal_lsn()) AS fully_caught_up,
       replay_lag
FROM   pg_stat_replication;

Sequence the deploy so new-schema reads never race replication. Add the column on the primary, wait until every replica confirms it present with near-zero lag, and only then roll out the application code that reads it from replicas. This is the routing-aware form of the additive ordering prescribed by the Expand and Contract Methodology: during the expand phase the new column is optional and nullable, so a replica still on the old schema answers every backward-compatible read correctly, and only the code that depends on the new column is gated on full propagation.

Throttle whatever drives lag so the fence window stays short. A long schema replay or an unthrottled backfill keeps replicas behind the fence and forces reads onto the primary. Keep the backfill under a lag budget so replicas clear the fence quickly, following Tuning Backfill Batch Size Against Replication Lag. The replica topology and warm-up mechanics that make this fencing reliable are detailed in Configuring Read Replicas for Seamless Schema Updates.

Verification Checklist

• The write path records its commit LSN/GTID and the read path can access it for read-your-writes fencing.
• Reads that name the new column route to a replica only when its replay position is at or beyond the DDL commit LSN/GTID, else to the primary.
• Every replica reports the new column present (information_schema.columns) and near-zero lag before new-column reads are routed to it.
• Backward-compatible reads (old columns only) stay on replicas throughout; only new-column reads are fenced.
• A replica past the lag budget is automatically drained from the new-column read pool and re-added on catch-up.
• Application logs show zero column does not exist / Unknown column errors on the read path after the fence is in place.

Up one level: Read/Write Splitting Tactics.

Related

• Routing DDL vs DML Traffic During Migrations
• Configuring Read Replicas for Seamless Schema Updates
• Read/Write Splitting Tactics

Frequently Asked Questions

Why does the same read succeed on retry? Because the retry landed on a different node. The first attempt hit a replica that had not yet replayed the schema change, so the column was missing or the row was stale; the retry either hit the primary or a replica that had since caught up. The intermittency — keyed to which node answers — is the signature of a stale replica read, not a bug in the query itself.

Is watching replication lag in seconds enough to prevent this? No. Wall-clock lag is a coarse heuristic and a replica can report “low lag” while still sitting just behind the exact transaction your user wrote. The correct gate is positional: compare the replica’s replay LSN (PostgreSQL) or executed GTID set (MySQL) against the commit position of the write, and only route the dependent read there once replay has reached or passed it.

How do I give one user read-your-writes without pinning everyone to the primary? Carry the write’s commit position in the session (a header or cookie holding the LSN/GTID) and fence only that user’s next read on it — wait for a replica to reach the position, or fall back to the writer for that single read. Everyone else, and all backward-compatible reads, keep using replicas normally, so you preserve offload while guaranteeing each writer sees their own change.