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!
