2

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

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.