We have a large >20TB system just pg_upgraded from 9.5 to 9.6 as per the
versions shown below.
The system does <5M transactions/day based on sum(commit + abort) from
pg_stat_database.
Autovac is running all possible threads now and upon investigating I see
that thousands of tables are now above the freeze threshold. Same
tables all appear ~50M xids older than they did yesterday and the
upgrade was less than 24 hours ago.
I have a "safety" snap made of the system before upgrade that can be
used for inspection.
Any ideas why the age jump?
select age(l.relfrozenxid), l.oid::regclass::text as relation, l.relkind, l.relpages, r.oid::regclass::text as "toast for"
from pg_class l
left join pg_class r on l.oid = r.reltoastrelid
where l.relkind in ('r', 't')
order by age desc, relation
limit :limit
ii postgresql-9.5 9.5.12-1.pgdg16.04+1 amd64 object-relational SQL database, version 9.5 server
ii postgresql-9.6 9.6.8-1.pgdg16.04+1 amd64 object-relational SQL database, version 9.6 server
Thanks
--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@xxxxxxxxxxx
p: 312.241.7800
