Hi List,
I am doing some tests to understand vacuum_freeze_table_age and vacuum_freeze_min_age parameters.
Here is my configuration:
postgres=# select name, setting from pg_settings where name = 'vacuum_freeze_min_age';
name | setting
-----------------------+---------
vacuum_freeze_min_age | 50
(1 ligne)
postgres=# select name, setting from pg_settings where name = 'vacuum_freeze_table_age';
name | setting
-------------------------+---------
vacuum_freeze_table_age | 150
(1 ligne)
name | setting
-----------------------+---------
vacuum_freeze_min_age | 50
(1 ligne)
postgres=# select name, setting from pg_settings where name = 'vacuum_freeze_table_age';
name | setting
-------------------------+---------
vacuum_freeze_table_age | 150
(1 ligne)
test table has an age of 51.
hydrodb=# SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') and c.relname='test';
table_name | age
------------+-----
test | 51
(1 ligne)
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') and c.relname='test';
table_name | age
------------+-----
test | 51
(1 ligne)
I expected it not to be processed by vacuum freeze.
However it has been entirely frozen.
Moreover, among the 51 rows, only 1 was eligible for freeze because its XID was older than vacuum_freeze_min_age.
hydrodb=# vacuum verbose test;
INFO: vacuuming "hydrodb.public.test"
INFO: finished vacuuming "hydrodb.public.test": index scans: 0
pages: 0 removed, 447 remain, 1 scanned (0.22% of total)
tuples: 0 removed, 100595 remain, 0 are dead but not yet removable
removable cutoff: 1569, which was 0 XIDs old when operation ended
new relfrozenxid: 1569, which is 51 XIDs ahead of previous value
frozen: 1 pages from table (0.22% of total) had 51 tuples frozen
parcours d'index non nécessaire : 0 blocs de la table (0.00% au total) ont 0 versions mortes de lignes supprimées
vitesse moyenne de lecture : 0.000 Mo/s, vitesse moyenne d'écriture : 58.302 Mo/s
utilisation du cache : 8 récupérés, 0 ratés, 1 modifiés
utilisation des WAL : 3 enregistrements, 1 images complètes de blocs, 6302 octets
utilisation du système : CPU : utilisateur : 0.00 s, système : 0.00 s, temps passé : 0.00 s
VACUUM
Thanks for any explanation
Simon
