Postgres recovery_prefetch can cause outages

Ardent Performance Computing Search Home About Happiness Hints All Writing Links Archives Old Links (Not Maintained) Downloads Old Links (Not Maintained) Downloads Postgres recovery_prefetch Considered Harmful and Causing Outages Another story of something I saw in the field which surprised me. Turns out recovery_prefetch can cause outages. Something which has been happening quietly in the background all the time, but we didn’t notice until we looked close enough to see it. After a lot of work I discovered this was partly a case where someone had dropped an email to the postgres hackers list years ago “hey what about this” and everyone thought “nah it can’t be that bad” … and now here we are seeing production outages! Exhibit A: Feb 2021, while the feature was being developed, a year and a half before it shipped, Stephen Frost Issuing unnecessary prefetches for blocks we’ve already sent a prefetch for is arguably a concern even if FPWs are off but the benefit of doing the prefetching almost certainly will outweight that and mean that finding a way to address it is something we could certainly do later as a future improvement. I wouldn’t have any issue with that. Just doesn’t seem as clear-cut to me when thinking about the FPW-enabled case. Ultimately, if you, Andres and Munro are all not concerned about it and no one else speaks up then I’m not going to pitch a fuss over it being committed, but, as you said above, it seemed like a good point to raise for everyone to consider. https://www.postgresql.org/message-id/20210214231815.GO27507@tamriel.snowman.net Exhibit B: Mar 2025, clear report from the field, Michael Zhilin (Postgres Pro) One customer complained that physical standby has significant replay lag (few hours) on modern powerful bare metal servers. According to output of “top”, startup process and few kernel workers utilized CPU, mostly in kernel space. The incident has been resolved by disabling recovery_prefetch after quick analysis of perf and strace outputs. Actual XLog prefetching code doesn’t consider that shared buffer may be loaded from WAL FPI rather than from disk and makes fadvise calls in such cases. https://www.postgresql.org/message-id/9bdb428a-fc8e-4801-8fff-1ad0bc6fa76b%40postgrespro.ru Both of these emails were on the right track, but didn’t quite get the complete picture yet. First: FPI/FPW is ubiquitous today. While it’s very expensive in terms of performance, it’s the only way Postgres has of protecting from torn writes on OS restarts. As a result, the majority of production Postgres installations today (community oss version) enable this. Second: synchronous replication is becoming more common. There are really only two ways to completely protect community open source postgres against data loss on hardware failures: (1) storage layer replication with oss tech like DRBD or expensive proprietary storage & clustering technology or (2) database layer protection with postgres synchronous replication. The second option is the normal solution with kubernetes and commodity hardware. Third: something that hasn’t previously been diagnosed/reported – the problem with recovery_prefetch isn’t just about FPIs, it’s also about the fact that when the primary instance does large sequential I/O, recovery_prefetch converts those operations into 8k random reads. For the same number of bytes read, this amplifies IOPS as much as 16x on replicas. This can easily exhaust available IOPS, causing replicas to significantly lag after write activity. The read IOPS amplification combined with Postgres design for synchronous replication is what turns recovery_prefetch into an actual outage-inducing killer. And it turns out this is trivial to reproduce once you know the steps. Start up postgres with synchronous replication to a standby, on small servers with inexpensive hard drives (for example: cloud disk with 500 IOPS, 2 CPUs, 8GB memory) Initialize pgbench scale 640 for about 10GB data Disable autovac on pgbench_accounts just so it doesn’t interfere with the experiment. Run pgbench at 5 TPS fixed in a terminal you can watch. Update 5% of the table. (Checkpoint and update pgbench_accounts where aid%20 = 0.) This generates a burst of write activity. Pgbench will slow down until it finishes. Wait for this to complete. Kill the walreceiver process to emulate network/system hang or instability that disrupts this connection. TPS drops to zero and all writes hang for over 30 minutes (can be hours with larger pgbench table). It really is this easy to consistently reproduce this outage. https://github.com/ardentperf-labs/walreceiver-outage-repro And for what it’s worth, VACUUM itself is quite capable of producing write activity too. The recipe for an outage is simply “enough bad luck”: a network glitch that interrupts streaming replication at the same time as a burst of write activity. To understand why the whole system hangs, it really comes down to two simple things: (1) synchronous replication and (2) the core “state machine” that defines a Postgres Standby Server. Remember that with synchronous replication, we’re depending on at least one healthy standby server – otherwise we can’t take writes. (We want to guarantee durability to clients and ensure every transaction is on a remote machine before commit.) The core standby server state machine is defined in src/backend/access/transam/xlogrecovery.c: WaitForWALToBecomeAvailable(XLogRecPtr RecPtr, bool randAccess, bool fetching_ckpt, XLogRecPtr tliRecPtr, TimeLineID replayTLI, XLogRecPtr replayLSN, bool nonblocking) { static TimestampTz last_fail_time = 0; TimestampTz now; bool streaming_reply_sent = false; /*------- * Standby mode is implemented by a state machine: * * 1. Read from either archive or pg_wal (XLOG_FROM_ARCHIVE), or just * pg_wal (XLOG_FROM_PG_WAL) * 2. Check for promotion trigger request * 3. Read from primary server via walreceiver (XLOG_FROM_STREAM) * 4. Rescan timelines * 5. Sleep wal_retrieve_retry_interval milliseconds, and loop back to 1. * * Failure to read from the current source advances the state machine to * the next state. ... ... Fundamentally it looks like this: Basically, when a Postgres standby switches into WAL-restore mode, it will not go back to streaming until it’s caught up. A burst of write activity combined with IOPS amplification on all the replicas that causes significant lag can easily mean the database won’t start streaming again for hours. The IOPS amplification is very clear. Here are the disk stats for the primary (maybe 5 minutes around 300 IOPS): Here are the disk status for the replica/standby during the same time period (40 minutes at the disk limit of 500 IOPS): These charts don’t illustrate it, but the number of bytes read from disk is about the same. What happens if we disable recovery_prefetch? (Note that FPWs remain enabled.) https://github.com/ardentperf-labs/walreceiver-outage-repro/blob/main/variations/README.md#recovery_prefetchoff There’s still the pause during UPDATE but we didn’t see the long pause after UPDATE completes. There is no burst of read IOPS, and the standby server basically stays caught up – applying the 5% update in less than 10 minutes. Right now, I’m leaning toward disabling the recovery_prefetch setting across the board but I want to see where the discussions go. I think its benefits are a little dubious with FPWs enabled by default. Appendix: Postgres Standby Server Replay Process Here’s a more detailed refresher on the various processes involved in the core restore and replay logic of a postgres standby server. Importantly, note the direction of the arrow between the WAL sender and WAL receiver. With synchronous replication in Postgres this connection is critical – the primary can’t accept writes without it. It’s also very fragile, because if the connection disappears the primary can only sit and wait, blocking all writes, hoping a standby will reconnect soon! This is a reason to consider two standbys and quorum commit (ANY 1) with postgres. While it does increase cost to run with three total replicas instead of two, it significantly increases the robustness of the database. Share this: Share on X (Opens in new window) X Share on Facebook (Opens in new window) Facebook Share on LinkedIn (Opens in new window) LinkedIn Share on Reddit (Opens in new window) Reddit Share on WhatsApp (Opens in new window) WhatsApp Share on Telegram (Opens in new window) Telegram Email a link to a friend (Opens in new window) Email Share on Threads (Opens in new window) Threads Share on Mastodon (Opens in new window) Mastodon Share on X (Opens in new window) X Share on Bluesky (Opens in new window) Bluesky Related About Jeremy Discussion One thought on “Postgres recovery_prefetch Considered Harmful and Causing Outages” Leave a New Comment Cancel reply Comment Name Email Website Turn on JavaScript to leave a comment. Δ Disclaimer This is my personal website. The views expressed here are mine alone and may not reflect the views of my employer.I am currently looking for consulting and/or contracting work in the USA around the oracle database ecosystem. contact: 312-725-9249 or schneider @ ardentperf.com (a) Email Updates Enter your email address to receive notifications of new posts by email. Email Address: Subscribe Recent Posts Postgres recovery_prefetch Considered Harmful and Causing Outages October 20, 2026 Agents Gone Awry on Postgres SBOMs: Start Over September 15, 2026 Misc Learnings: SBOMs, Provenance and Attestations September 11, 2026 Why Postgres Breaks Kubernetes container_memory_working_set_bytes Metric August 18, 2026 Postgres Checkpoint Followup and Collation Visualization and Codex Luna August 12, 2026 Recent Comments Matt on Postgres recovery_prefetch Considered Harmful and Causing Outages Agents Gone Awry on Postgres SBOMs: Start Over – Ardent Performance Computing on Misc Learnings: SBOMs, Provenance and Attestations Postgres vs Kubernetes container_memory_working_set_bytes – Curated SQL on Why Postgres Breaks Kubernetes container_memory_working_set_bytes Metric Postgres Checkpoint Followup and Collation Visualization and Codex Luna | Ardent Performance Computing on Happiness Hint: Alarm on Checkpoint Time Alerting on Checkpoint Time in Postgres – Curated SQL on Happiness Hint: Alarm on Checkpoint Time Ardent Performance Computing Create a website or blog at WordPress.com