Learning center

Backfill replica lag comes from replay conflicts too

A backfill writes a lot of WAL, and a replica has to replay it. On our machine the replica kept up with 716 MB of backfill WAL within a fraction of a second. What stalled it was a report running on the replica while the primary vacuumed the backfilled table: replay waited 30 seconds, then PostgreSQL cancelled the report. Turning on hot_standby_feedback kept the report alive and the replica current, and left 200,000 dead rows on the primary that vacuum could not remove.

Throughput alone#

We set up a streaming replica of the primary on the same host, both PostgreSQL 16.15, and backfilled 2,000,000 rows three ways while sampling pg_stat_replication every 50 ms:

BackfillWALLargest replay gapLongest replay lag
One UPDATE716 MB18 MB0.21 s
Batches of 10,000716 MB8 MB0.29 s
Batches, pausing while the gap exceeds 16 MB720 MB7 MB0.06 s

A replica on a slower disk, in another region, or behind a busy network link falls further behind. Measure your own with the same query; ours only shows the method. One thing did differ: right after the single UPDATE committed on the primary, a query on the replica still found all 2,000,000 rows unfilled, because none of the change is visible there until its commit record replays. Batches make the replica's view advance batch by batch.

Replay conflicts#

A query on the replica that still needs old row versions blocks the replay of a vacuum that removes them. We started a 60-second report on the replica, at REPEATABLE READ, then backfilled 200,000 rows on the primary, vacuumed the table, and inserted one more order:

FeedbackNew order on replicaReportPrimary's vacuum
offafter 29.8 scancelled at 31 sremoved 198,285
onat oncefinished, 60 sremoved 0, kept 200,000

The rows show hot_standby_feedback off and on, and the last column counts the dead rows the primary's vacuum removed or had to keep. With feedback off, replay paused for the report until max_standby_streaming_delay, 30 s by default, then cancelled it with canceling statement due to conflict with recovery (SQLSTATE 40001). Every read from the replica in those 30 seconds saw data from before the pause, including reads by the new release that expected backfilled rows. With feedback on, the replica told the primary which rows its query still needed, so the primary's vacuum kept them. The hot standby chapter describes both behaviors.

What to decide before the backfill#

  • Which readers use the replica. If the new release reads backfilled data from a replica, it needs the replica to be current. Check replay_lag before each read path is switched, or read from the primary for the duration.
  • What runs long on the replica. Reports and exports are the usual cause of conflicts. Schedule the backfill outside their windows, or accept cancelled reports.
  • Feedback or delay. Feedback trades replica freshness for dead rows on the primary, which vacuum between backfill batches to keep the table from doubling shows growing the table. A longer max_standby_streaming_delay trades it for a staler replica.

The rehearsal#

Run a batch of the backfill and a vacuum while a representative replica query runs, and watch replay_lag and the replica's logs. Pass when lag stays below what the replica's readers tolerate and no query you care about is cancelled. The backfill itself is in moving data while two application releases share one database.

One month of free code review.

New organizations get one calendar month of hosted code review for private and public GitHub repositories. No card or cloud connection required. Eligibility and fair-use terms.

Start free with GitHub

Related reading