Autovacuum worker spawning strategy - Mailing list pgsql-hackers

Hello all,

While investigating a problem in a PG14 instance I noticed that autovacuum workers
stop processing other databases when a database has a temporary table with age
older than `autovacuum_freeze_max_age`. To test that I added a custom logline showing
which database the about to spawned autovacuum worker will target. Here are the details:

```
test=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
 13757 | postgres
 32850 | test
     1 | template1
 13756 | template0
(4 rows)
```

Here are the loglines under normal circumstances:

```
2022-07-19 11:24:29.406 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13756)
2022-07-19 11:24:44.406 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=1)
2022-07-19 11:24:59.406 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=32850)
2022-07-19 11:25:14.406 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:25:29.417 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13756)
2022-07-19 11:25:44.417 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=1)
2022-07-19 11:25:59.418 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=32850)
2022-07-19 11:26:14.417 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:26:29.429 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13756)
2022-07-19 11:26:44.430 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=1)
2022-07-19 11:26:59.432 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=32850)
2022-07-19 11:27:14.429 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:27:29.442 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13756)
2022-07-19 11:27:44.441 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=1)
2022-07-19 11:27:59.446 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=32850)
2022-07-19 11:28:14.442 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:28:29.454 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13756)
2022-07-19 11:28:44.454 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=1)
2022-07-19 11:28:59.458 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=32850)
2022-07-19 11:29:14.443 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:29:29.465 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=32850)
2022-07-19 11:29:44.485 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=1)
2022-07-19 11:29:59.499 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13756)
```

But when I create a temp table and make it older than `autovacuum_freeze_max_age`
I get this:

```
2022-07-19 11:30:14.496 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:30:29.495 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:30:44.507 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:30:59.522 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:31:14.536 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:31:29.551 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:31:44.565 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:31:59.579 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:32:14.591 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:32:29.606 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:32:44.619 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:32:59.631 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:33:14.643 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:33:29.655 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:33:44.667 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:33:59.679 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:34:14.694 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:34:29.707 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:34:44.719 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:34:59.732 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
2022-07-19 11:35:14.743 -03 [18627] WARNING:  AUTOVACUUM WORKER SPAWNED (db=13757)
```

This is actually expected behavior judging by the code logic: https://github.com/postgres/postgres/blob/master/src/backend/postmaster/autovacuum.c#L1201

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.

Regards,

Rafael Castro — https://www.EnterpriseDB.com/