Hello, recently one of my tables needed a vacuum (to prevent wraparound) and of course it happened at a really bad time, so since then I've been learning about how Transaction ID Wraparound works and its associated parameters.
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!
