Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
Is there a good way to monitor the number of updates/deletes to a table since the last autovacuum? I'm trying to find tables that aren't updated frequently enough to avoid autovacuum (to prevent wraparound).
I was thinking of using n_dead_tup from pg_stat_all_tables, but is this a good idea/is there a better way?
I'm using Postgres 9.6.
Thanks
n_dead_tup in pg_stat_all_tables is the perfect way to monitor that, because that is the value that triggers autovacuum.
From v13 on, you also have to watch n_ins_since_vacuum, because autovacuum is also triggered by inserts there.
Normally, there is no reason to be afraid of anti-wraparound vacuum runs. PostgreSQL 9.6 introduced an optimization that allows such VACUUM runs to skip pages with only frozen tuples.
The exception might be a table that receives almost only inserts. You could potentially get massive anti-wraparound VACUUM runs there. A way to avoid that is to drastically lower autovacuum_freeze_max_age for such tables, so that you get anti-wraparound VACUUM runs there earlier and more often. This problem has been abolished with PostgreSQL v13.
