Re: questions about wraparound - Mailing list pgsql-general

Hi Luca,

On Mon, 22 Mar 2021 08:56:46 +0100
Luca Ferrari <fluca1978@gmail.com> wrote:

> I can confirm that freezing a template database is done by means of setting
> it age to zero.
[...]
> and here it is the situation after a restart:
> 
> testdb=> select datname, age( datfrozenxid ) from pg_database;  
>   datname  |   age
> -----------+----------
>  postgres  |     1234
>  backupdb  | 50000000
>  template1 |        0
>  template0 |        0
>  testdb    |        0
>  pgbench   | 50000000

The difference between a "vacuum" and "vacuum freeze" is whether the vacuum
process must scan non-frozen blocks as well, according to the visibility map.
The later is called "aggressive" vacuum because it scan all blocks, even the
clean ones, as far as they are not already marked as frozen in the visibility
map.

Whatever the vacuum you are launching, if the process find a rows older than
vacuum_freeze_min_age, it freezes it. Agressive vacuum is not different and
respect vacuum_freeze_min_age as well. That's why your oldest row in each
database is 50000000 after a "vacuum freeze" (aka. aggressive vacuum, aka.
"vacuum to avoid wraparound").

Try to temporary set vacuum_freeze_min_age=45000000 and freeze_table_age=0, then
run a simple vacuum on your database. This will effectively freeze you database
and set its age to the oldest row: your new vacuum_freeze_min_age, 45000000.

Considering the template databases, the default vacuum_freeze_min_age is forced
to 0 in source code. That's why you find a different age between template
databases and others after a freeze.

In regard with the databases age moving altogether, even when only one of them
is receiving writes. The XID space is shared between all the database. In other
words, a transaction can not be used in two different database, unless they
apply on shared relations (those in pg_global tablespace) and probably cloned
ones from templates.
So if a database alone is consuming XIDs all other are getting older and older
and will eventually need a vacuum.

> I'm not able to find this behavior in the documentation however,

I don't know if it is explained somewhere in doc, I couldn't find it either.
But you can find this information in function "do_autovacuum()" in
src/backend/postmaster/autovacuum.c:

  /*
   * Find the pg_database entry and select the default freeze ages. We use
   * zero in template and nonconnectable databases, else the system-wide
   * default.
   */
  tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(MyDatabaseId));
  if (!HeapTupleIsValid(tuple))
      elog(ERROR, "cache lookup failed for database %u", MyDatabaseId);
  dbForm = (Form_pg_database) GETSTRUCT(tuple);
  
  if (dbForm->datistemplate || !dbForm->datallowconn)
  {
      default_freeze_min_age = 0;
      default_freeze_table_age = 0;
      default_multixact_freeze_min_age = 0;
      default_multixact_freeze_table_age = 0;
  }
  else
  {
      default_freeze_min_age = vacuum_freeze_min_age;
      default_freeze_table_age = vacuum_freeze_table_age;
      default_multixact_freeze_min_age = vacuum_multixact_freeze_min_age;
      default_multixact_freeze_table_age = vacuum_multixact_freeze_table_age;
  }

> and still don't understand why a template database should have a different
> behavior (at least, I can imagine only to reduce the future workload of
> vacuuming a template database).

I suspect that it helps creating database with already frozen blocs, from a full
frozen template.

Regards,