postgresqlvacuum

No much improvement on max transaction id after vacuum full

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
Solution

  • 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).