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/
