As Keith stated; due to lack of updates and deletes the counters are not necessarily going to trigger an auto vacuum. Have a look at your auto vacuum settings and freeze settings to determine the best approach. You want to be more aggressive with those settings or manually vacuum the table periodically. What you don’t want to occur to an extremely large table that has never been vacuumed is have it issue a freeze wraparound vacuum. The select freezing the tuples is a good thing here as it helps advance the xmin age. Really don’t want wraparound vacuums to occur; as that would indicate not enough vacuums are occurring for your current workload/volume. Here’s a set of queries that I use to monitor transaction id age of the database and tables. create or replace function util.numeric_format(_percision int, _scale int) returns varchar(100) as $$ declare _grpCnt int; _decCnt int; begin _grpCnt := ((_percision - _scale) / 3); _decCnt := ((_percision - _scale) % 3); return trim(leading ',' from (repeat('9', _decCnt) || repeat('G999', _grpCnt) || 'D' || repeat('9', _scale))); end; $$ language plpgsql immutable returns null on null input ; comment on function numeric_format(int, int) is 'Returns a format string for a numeric value given the percision.' ; with stat as ( select datname as database , (datfrozenxid::text)::bigint as frozen_txid , age(datfrozenxid) as txid_age , (2^31 - 1000000) as max_txid , (2^31 - 1000000) - age(datfrozenxid) as txid_free , current_setting('autovacuum_freeze_max_age'::text)::double precision as freeze_age , case pg_is_in_recovery() when true then 0 else txid_current() end & (~(-1::bigint << 31)) as current_txid , case pg_is_in_recovery() when true then 0 else txid_current() end as epoch_txid from pg_database where datname = current_database() ) select stat.database , to_char(stat.frozen_txid, util.numeric_format(10,0)) as frozen_txid , to_char(stat.current_txid, util.numeric_format(10,0)) as current_txid , to_char(stat.epoch_txid, util.numeric_format(10,0)) as epoch_txid , to_char(txid_age, numeric_format(10, 0)) as txid_age , to_char(txid_free, numeric_format(10, 0)) as txid_free , to_char(txid_age / max_txid * 100, '9G999D99%') as used_txid_pct , to_char(txid_age / freeze_age * 100, '9G999D99%') as used_freeze_pct , now() as asof from stat ; select n.nspname as schema , c.relname as table_name , c.relpages::bigint as pages , pg_size_pretty(c.relpages::bigint * current_setting('block_size')::bigint) as size , to_char((c.relfrozenxid::text)::bigint, numeric_format(15, 0)) as frozen_xtid , to_char(age(c.relfrozenxid), numeric_format(15, 0)) as txid_age , to_char((2^31 - 1000000) - age(c.relfrozenxid), numeric_format(15, 0)) as txid_free , to_char(age(c.relfrozenxid) / (2^31 - 1000000) * 100, '9G999D99%') as used_txid_pct , to_char(age(c.relfrozenxid) / current_setting('autovacuum_freeze_max_age'::text)::double precision * 100, '9G999D99%') as used_freeze_pct from pg_class c join pg_namespace n on n.oid = c.relnamespace where c.relkind in ('r', 'm') order by (c.relfrozenxid::text)::bigint , c.relpages::bigint desc limit 50 ; |
