zlacker

> Another problem with the autovacuum in PostgreSQL is that it may get blocked by long-running transactions, which can result in the accumulation of more dead tuples and stale statistics. Failing to clean expired versions in a timely manner leads to numerous performance problems, causing more long-running transactions that block the autovacuum process. It becomes a vicious cycle, requiring humans to intervene manually by killing long-running transactions.

Oh man, a previous company I worked at had an issue with a hot table (frequent reads + writes) interfering with autovacuum. Many fires over a six month period arose from all of that. I was (luckily) only on an adjacent team, so I don't know the details, other than vacuums taking over 24 hours! I'm sure it could have been prevented, but it seemed horrible to debug

>>elijah+(OP)
yeah, this is called "cancellation." Autovacuum is very polite and tries to let go of a lock when there's a conflict. So it lets go, over and over, until it triggers a heuristic deciding "no, not succeeding in this session could be dangerous!" and then people begin to notice it.

Last I checked (....a few years ago, so things may have changed,) the theory of autovacuum heuristics may not have changed much since the turn of the millennium, they're probably about due.