No much improvement on max transaction id after vacuum full-CodePudding

We did a vacuum full on our table and toast. The dead tuples dropped drastically, however the max transaction id stays pretty much the same. My question is, why did it the max transaction id not go down as dead tuples go down drastically?

Before

select relname,last_autovacuum ,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze,vacuum_count,autovacuum_count from pg_stat_all_tables where relname in ('examples','pg_toast_16450');
 relname        | last_autovacuum.              | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | vacuum_count | autovacuum_count 
---------------- ------------------------------- ----------- ------------ --------------- ------------ ------------ --------------------- -------------- ------------------
 examples       | 2022-01-18 23:26:52.432808 00 | 57712813  | 9818       | 48386674      | 3601588   | 306558 | 42208 | 0 | 44
 pg_toast_16450 | 2022-01-17 23:14:42.516933 00 | 0         | 5735566377 | 0             | 3763818   | 805501171 | 11472355929 | 0 | 51

SELECT max(age(datfrozenxid)) FROM pg_database;
    max
-----------
 199857797

After

select relname,last_autovacuum ,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze,vacuum_count,autovacuum_count from pg_stat_all_tables where relname in ('examples','pg_toast_16450');
    relname     |        last_autovacuum        | n_tup_upd |  n_tup_del  | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | vacuum_count | autovacuum_count
---------------- ------------------------------- ----------- ------------- -------------- ------------ ------------ --------------------- -------------- ------------------
 examples     | 2022-02-01 15:41:17.722575 00 | 120692014 |        9818 |      98148003 |    4172134 |      17666 |            150566 |            1 |             4064
 pg_toast_16450 | 2022-02-01 20:49:30.552251 00 |         0 | 16169731895 |             0 |    5557218 |      33365 |       32342853690 |            0 |            15281

SELECT max(age(datfrozenxid)) FROM pg_database;
    max
-----------
 183888023

CodePudding user response：

Yes, that is as expected. You need VACUUM to freeze tuples. VACUUM (FULL) doesn't.

Users tend to be confused, because both are triggered by the VACUUM statement, but VACUUM (FULL) is actually something entirely different from VACUUM. It is not just “a more thorough VACUUM”. The only thing they have in common is that they get rid of dead tuples. VACUUM (FULL) does not modify tuples, as freezing has to do, it just copies them around (or doesn't, if they are dead).