On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.janes@xxxxxxxxx> wrote:
vacuum_freeze_table_age controls when it promotes a vacuum *which is already going to occur* so that it scans the whole table. It doesn't specially schedule a vacuum to occur. When those tables see enough activity to pass autovacuum_vacuum_scale_factor then the vacuum that occurs will get promoted to be a full scan.
Thank you Jeff, that really helps understand this.
If they never see that amount of activity, then the tables will continue to age until autovacuum_freeze_max_age, at which point a vacuum will get launched specifically for the purpose of advancing relfrozenxid.
So based on this, I created this query to show autovacuum and analyze data, including if the next autovacuum will be a freeze; and how close the table is to being force-freezed. This borrows heavily from queries I found at Heroku (https://github.com/heroku/heroku-pg-extras/blob/master/lib/heroku/command/pg.rb):
My output looks like:
I'm thinking I'm in for a world of pain when all my tables in my 3.5TB db simultaneously hit autovacuum_freeze_max_age, and I think I'm about 83% of the way there.
What do you think? I'm thinking I should start doing a VACUUM FREEZE on tables at night to head this off.
