I'm trying this query to see how close my tables are to hitting the vacuum_freeze_table_age threshold (150M in my case):
SELECT
relname,
age(relfrozenxid) as xid_age,
ROUND(100.0 * age(relfrozenxid) / current_setting('vacuum_freeze_table_age')::numeric, 1) || '%' AS "% til vacuum freeze"
FROM
pg_class
WHERE relkind = 'r';
For now, assume my tables have no storage parameters that override the defaults.
I was surprised at the results, almost all my tables look like:
my_table | 160589343 | 107.1%
Or about 160m transactions old. I would have thought with my current settings:
vacuum_freeze_min_age = 50m
vacuum_freeze_table_age = 150m
autovacuum_freeze_max_age = 200m
that the autovacuumer would have already forced a vacuum freeze on all these tables. According to the docs, "a whole table sweep is forced if the table hasn't been fully scanned for vacuum_freeze_table_age minus vacuum_freeze_min_age transactions" which would be 100m transactions.
I'm guessing my understanding here is wrong. What did I miss?
Just in case it matters, here's my current txid:
mydb=# select txid_current();
txid_current
--------------
1485894081
(1 row)
Thanks!