Postgres recovery_prefetch can cause outages

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.