On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.ja...@gmail.com> 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 ): https://gist.github.com/skehlet/36aad599171b25826e82 My output looks like: https://gist.github.com/skehlet/592b00c1e4b381c83099 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. >
