On Thu 2024-12-26 12:21:08 -0800, Jeremy Schneider wrote: > On Mon, 16 Dec 2024 16:25:06 -0800 > Will Storey <will@xxxxxxxxxxxxx> wrote: > > > I would like to disable vacuum's truncate behaviour for autovacuum. > > Previously I had an outage due to its access exclusive lock when it > > was replicated to a hot standby. > > > > When that outage happened it was from a VACUUM call in a cronjob > > rather than autovacuum. I now run such VACUUMs with TRUNCATE false > > which avoids the issue for these. However I've realized that > > autovacuum could cause this as well. This is of interest to me > > because I'm looking at tuning autovacuum and getting rid of the > > cronjob, but I've realized relying on autovacuum could be dangerous > > because of the truncates. > > Can you tell us a little bit more about the outage? Autovacuum is > designed to quickly relinquish this lock if there is any contention, and > the dangers of disabling autovacuum are significant, so your statement > about autovac being "dangerous" will raise a lot of eyebrows. > > Did your outage involve hot standbys serving read-only traffic, or did > it only involve a read-write database? > > What was the exact nature of the outage and how did you narrow down the > cause to the exclusive lock held specifically during an autovacuum > truncation? My incident was actually not caused by autovacuum. A VACUUM was run against the primary by a cronjob. A web service running read queries against hot standbys went down for several minutes as its queries were stuck in a lock queue. While this was VACUUM, my understanding is that autovacuum could do this as well because it does not see the queries on the hot standby that could be blocked by it, so it won't know to stop its work. I think this issue is part of what lead to the addition of the vacuum_truncate reloption discussed in https://www.postgresql.org/message-id/flat/CAHGQGwE5UqFqSq1%3DkV3QtTUtXphTdyHA-8rAj4A%3DY%2Be4kyp3BQ%40mail.gmail.com, e.g. this message: https://www.postgresql.org/message-id/20190408044345.ndxsnveqqlj3m67g%40alap3.anarazel.de. I could be misunderstanding it though! As I recall, I confirmed the cause via query logs. I noticed the table was vacuumed at the time, which lead me to learning about the page truncation behaviour. It has been a couple years though. The cronjob still runs every night, but now with TRUNCATE false. I've been thinking of trying to get rid of it and rely more on autovacuum which is why I've been revisiting this. As well, we're no longer protected by old_snapshot_threshold disabling the page truncation globally, due to that being removed.
