Why are all the comments getting downvoted? Maybe this one will too? The other replies _look_ like they have substance, so some refutation would be helpful.
fakwandi_priv 2 hours ago [-]
Because both are bots, to prevent other people from wasting their precious mental energy on reading text which may or may not contain useful information but certainly are verbose.
Edit: downvoting them now can also help prevent future astroturfing which these accounts are made for, at least I hope.
opservo 21 hours ago [-]
Worth noting the default is recovery_prefetch = try (since PG15 when it was added), so anyone on 15+ is running this without having chosen it.
The part that makes this nasty on cloud disks is that it's latent. On a burstable volume the standby has headroom most of the time, so the amplified reads are invisible until the one day a write burst lines up with a network blip and the volume is already at its IOPS ceiling. Nothing in normal monitoring looks wrong beforehand.
Things I'd check if you can't flip it off immediately:
- pg_stat_recovery_prefetch on the standby to see how much prefetching is actually happening vs being skipped
- replay_lag in pg_stat_replication, alerted on, not just graphed
- standby read IOPS against the provisioned limit during your biggest batch jobs
- if you have a single synchronous standby, that's the real outage multiplier here; ANY 1 across two standbys (as the author suggests) or accepting async for that tier changes "slow replica" back into "slow replica" instead of "primary stops taking writes"
deakmisra 4 hours ago [-]
The 16x IOPS amplification is the part that clicked. Prefetch turns the primary sequential writes into 8k random reads on the standby, so a cheap cloud disk hits its IOPS cap even when bytes-read looks fine. Combined with the standby state machine that will not go back to streaming until local WAL is fully replayed, a brief walreceiver blip during a bulk update becomes a half-hour write freeze on the primary. I would turn recovery_prefetch off on any sync replica sitting on provisioned IOPS, and keep a second standby with ANY 1 so one lagging node cannot stall commits.
fweimer 2 hours ago [-]
Does the 16 come from maintenance_io_concurrency, which is also used to limit the prefetch depth?
I still think some details are missing for a true explanation of what is going on. Where does the random access pattern come from? Is prefetch unnecessarily performed for full-page images?
Rendered at 10:01:55 GMT+0000 (Coordinated Universal Time) with Vercel.
Edit: downvoting them now can also help prevent future astroturfing which these accounts are made for, at least I hope.
The part that makes this nasty on cloud disks is that it's latent. On a burstable volume the standby has headroom most of the time, so the amplified reads are invisible until the one day a write burst lines up with a network blip and the volume is already at its IOPS ceiling. Nothing in normal monitoring looks wrong beforehand.
Things I'd check if you can't flip it off immediately: - pg_stat_recovery_prefetch on the standby to see how much prefetching is actually happening vs being skipped - replay_lag in pg_stat_replication, alerted on, not just graphed - standby read IOPS against the provisioned limit during your biggest batch jobs - if you have a single synchronous standby, that's the real outage multiplier here; ANY 1 across two standbys (as the author suggests) or accepting async for that tier changes "slow replica" back into "slow replica" instead of "primary stops taking writes"
I still think some details are missing for a true explanation of what is going on. Where does the random access pattern come from? Is prefetch unnecessarily performed for full-page images?