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:
| Backfill | WAL | Largest replay gap | Longest replay lag |
|---|---|---|---|
One UPDATE | 716 MB | 18 MB | 0.21 s |
| Batches of 10,000 | 716 MB | 8 MB | 0.29 s |
| Batches, pausing while the gap exceeds 16 MB | 720 MB | 7 MB | 0.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:
| Feedback | New order on replica | Report | Primary's vacuum |
|---|---|---|---|
| off | after 29.8 s | cancelled at 31 s | removed 198,285 |
| on | at once | finished, 60 s | removed 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_lagbefore 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_delaytrades 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.