Detect and Measure Stale Reads in a Replicated Database

Updated 13 November 2026 · Standard formula, shown below

This guide explains how to detect and measure stale reads in a replicated database setup. You’ll learn practical methods to quantify replication lag and identify when reads return outdated data, helping you maintain consistency and troubleshoot performance issues.

What You’ll Need

Access to the primary (source) and replica (standby) database instances.
Database client tools (e.g., mysql, psql, sqlplus) or a programming language with a database driver.
Permission to run queries on both primary and replica (at least SELECT and, for some methods, CREATE TEMPORARY TABLE).
For time-based measurements, ensure system clocks on primary and replica are synchronized (e.g., using NTP).
Basic understanding of your database’s replication mechanism (e.g., MySQL binary logs, PostgreSQL WAL, etc.).

Step-by-Step

Identify the replication lag metric provided by your database. Most databases expose a built-in metric. For MySQL, run SHOW SLAVE STATUS\G and look at Seconds_Behind_Master. For PostgreSQL, query SELECT now() - pg_last_xact_replay_timestamp() AS lag on the replica. For MongoDB, use rs.status() and check the secondaryDelaySecs or the difference in optime between primary and replica. This gives a quick, high-level estimate.
Create a baseline by measuring lag at a quiet time. Run the lag query several times over a minute when there is little write activity. Record the average and maximum values. This tells you the typical lag and helps you spot anomalies later.
Use a timestamp-based method for precise measurement. On the primary, create a table (or use an existing one) and insert a row with a timestamp. For example, in MySQL: CREATE TABLE IF NOT EXISTS lag_test (id INT AUTO_INCREMENT PRIMARY KEY, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP); INSERT INTO lag_test (ts) VALUES (NOW()); Then immediately read the same row on the replica and compute the difference between the replica’s current time and the stored timestamp. This measures the time it took for that specific write to become visible on the replica.
Repeat the timestamp test with a high write rate. To measure under load, run a loop that inserts a row every second for a minute on the primary, then on the replica, query the maximum timestamp and compare it to the replica’s current time. The difference is the approximate lag at that moment. This is more realistic than a single write.
Use a sequence or auto-increment counter to detect ordering issues. On the primary, insert a row with a monotonically increasing value (e.g., an auto-increment ID). On the replica, read the maximum ID. The difference between the primary’s max ID and the replica’s max ID, divided by the write rate, gives an estimate of lag in seconds. For example, if the primary has ID 1000 and the replica has 990, and writes happen at 10 per second, lag is about 1 second.
Perform a read-your-writes test from an application perspective. Write a script that writes a record to the primary, then immediately tries to read it from the replica. If the read fails (record not found), that indicates a stale read. Measure the time until the read succeeds. Repeat this multiple times to get an average detection time.
Monitor the lag over time with a polling script. Write a script that runs every few seconds, records the lag (using any of the above methods), and logs it. This helps you see how lag varies with load and identify peak periods.
Analyze the results. Compare the measured lag against your application’s tolerance. If reads must be within 1 second of writes, and you see lag consistently above that, you need to adjust your architecture (e.g., use a faster replica, reduce write load, or implement read-after-write consistency in the application).

Common Mistakes

Relying only on the built-in lag metric. Seconds_Behind_Master can be inaccurate if the replica is not actively processing events (e.g., it can be 0 even when lag exists). Always verify with a direct test.
Ignoring clock skew. If primary and replica clocks differ, timestamp-based measurements will be wrong. Always synchronize clocks using NTP.
Measuring at the wrong time. Lag can vary dramatically with load. Measuring only during quiet periods gives an unrealistically low number.
Not accounting for replication filters. If you replicate only a subset of tables, a lag test on a non-replicated table will show no lag, giving a false sense of security.
Using a single sample. One measurement is not representative. Always take multiple samples over time.

Tips

Use a dedicated test table. Create a small table specifically for lag testing to avoid interfering with production data.
Automate the monitoring. Set up a cron job or a monitoring tool (like Prometheus with a custom exporter) to collect lag metrics continuously.
Combine methods. Use the built-in metric for a quick check and the timestamp method for a precise measurement when you suspect issues.
Test under realistic write load. If possible, run the lag test during peak hours to see the worst-case scenario.
Consider using a proxy that routes reads to the primary when lag is high. Some databases (e.g., PostgreSQL with pgbouncer) can be configured to do this, but it’s an application-level decision.

Troubleshooting

If the built-in lag metric shows 0 but you still get stale reads, check if the replica is actually applying changes. Look at the replica’s SQL thread status (e.g., Slave_SQL_Running in MySQL). If it’s stopped, lag is infinite but the metric may not reflect it.
If timestamp-based measurements show negative lag, your clocks are not synchronized. Reconfigure NTP and re-test.
If the lag is unexpectedly high, check for large transactions on the primary that may be taking a long time to apply on the replica. Also check for disk I/O or CPU bottlenecks on the replica.
If your test writes are not appearing on the replica at all, verify that the table is included in the replication filters. Check the replica’s configuration for replicate-do-db or replicate-ignore-db settings.
If you cannot create tables on the primary, use an existing table that is replicated and has a timestamp column. Or, use a sequence-based method with an existing auto-increment column.

Frequently Asked Questions

What is a stale read?

A stale read is when a query on a replica returns data that is older than the most recent write on the primary. This happens because replication is asynchronous by default in most systems.

How much lag is acceptable?

It depends on your application. For some applications, a few seconds may be fine; for others, even milliseconds matter. Define a Service Level Objective (SLO) based on user expectations.

Can I eliminate stale reads completely?

You can reduce them by using synchronous replication (e.g., PostgreSQL’s synchronous replication or MySQL’s semi-synchronous replication), but that can impact write performance. Alternatively, route reads that require up-to-date data to the primary.

Does the built-in lag metric always work?

No. It can be inaccurate, especially with parallel replication or when the replica is not actively applying events. It’s a good starting point but should be verified with direct measurements.

How often should I measure lag?

Continuously, if possible. At minimum, run a test every few minutes during business hours and record the results. This helps you spot trends and react before users notice issues.

What is the difference between lag and stale reads?

Lag is the time delay between a write on the primary and its application on the replica. A stale read is a read that returns data that is older than the most recent write. High lag leads to stale reads, but even low lag can cause stale reads if the read happens before the write is replicated.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.