RE: Temporary tables prevent autovacuum, leading to XID wraparound

From: Masahiko Sawada [mailto:sawada(dot)mshk(at)gmail(dot)com]
> On Thu, Jan 25, 2018 at 3:14 PM, Tsunakawa, Takayuki
> <tsunakawa(dot)takay(at)jp(dot)fujitsu(dot)com> wrote:
> > * Why does autovacuum launcher always choose only one database when that
> database need vacuuming for XID wraparound? Shouldn't it also choose other
> databases?
>
> Yeah, I'd also like to fix this issue. This can be problem even in other
> case; there are two databases that require anti-wraparound vacuum, and one
> of them has a very large table that could take a long time to vacuum. In
> this case, if autovacuum chooses the database having big table first,
> another database would not be selected until an autovacuum worker completes
> vacuum on the large table. To deal with it, I think we can make autovacuum
> workers tell that it is no longer necessary to launch a new autovacuum worker
> on the database to autovacuum launcher before exit. For example, autovacuum
> worker reports both the total number of relations and the number of relations
> that require an anti-wraparound vacuum to the stats collector.

Thanks for commenting. I believe you have deep knowledge and experience with vacuum because you did a great work for freeze map in 9.6, so I appreciate your help!

How would you use those two counts?

How about just modifying do_start_worker(), so that the launcher chooses a database in the following order?

1. wraparound-risky database not being vacuumed by any worker
2. non-wraparound-risky database not being vacuumed by any worker
3. wraparound-risky database being vacuumed by any worker
4. non-wraparound-risky database being vacuumed by any worker

Regards
Takayuki Tsunakawa