Re: Autovacuum worker spawning strategy

On Tue, Jul 19, 2022 at 12:40:06PM -0300, Rafael Thofehrn Castro wrote:
> PG prioritizes databases that need to be frozen and since a temporary table
> can't
> be frozen by a process other than the session that created it, that DB will
> remain
> a priority until the table is dropped.
>
> I acknowledge that having a temp table existing for long enough to reach
> `autovacuum_freeze_max_age`
> is a problem itself as the table will never be frozen and if age reaches 2
> billion
> the instance will shut down. That being said, perhaps there is room for
> improvement
> in the AV worker spawning strategy to avoid leaving other DBs in the dark.
>
> This database where I spotted the problem is from a customer that consumes
> 100m xacts/hour
> and makes extensive uses of temp tables to load data, so that scenario can
> actually
> happen.

I wonder if it's worth tracking a ѕeparate datfrozenxid that does not
include stuff that is beyond autovacuum's control, like temporary tables.

--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com