2

why does the transaction age of template0 DB increase while it is never been used?

   datname        |    age    | 
 ----------------------+-----------+
  template0            | 192232070
Rejo
  • 79
  • 4

1 Answers1

2

Every database has a datfrozenxid in its pg_database entry. This is identical to the minimum relfrozenxid of the pg_class entries of all tables in the database.

Whenever VACUUM freezes tuples in a table, it can advance these columns.

There is no real need to vacuum template0 regularly. However, even in that database a table receives an anti-wraparound autovacuum run whenever its relfrozenxid becomes older than autovacuum_freeze_max_age. This will be done very quickly: PostgreSQL will see in the visibility map that all pages are “all frozen” and advance relfrozenxid.

This is no real problem, so nobody sees a need to change or optimize that.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thank you for that. Kind of explains it, but quite deceiving when its for template0. Correct me if am wrong here, so ideally irrespective of zero transactions happening in the databases `template0, template1, postgres` - the default DBs, this value will grow for these DBs based on the user database. When autovacuum kicks in, template1 and postgres DB might/would refresh this to a lower number but since template0 is autovacuumed only at `autovacuum_freeze_max_age` value, it continues to grow – Rejo Jun 26 '20 at 17:41
  • `datfrozenxid` will be advanced for `template0` as well, but you are right that it will only be advanced during an anti-wraparound vacuum, which is the only time that autovacuum works on `template0` (only to conclude right away that there is nothing to do). Note that what grows is not `datfrozenxid` but the current transaction ID. – Laurenz Albe Jun 29 '20 at 05:49