Re: finding tables about to be vacuum freezed

On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff(dot)janes(at)gmail(dot)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.

>