Postgres Pro
Downloads
Home   >   mailing lists

Re: finding tables about to be vacuum freezed - Mailing list pgsql-general

From Steve Kehlet
Subject Re: finding tables about to be vacuum freezed
Date
Msg-id CA+bfosGg9QkrfB-9sRmG0_AKH4hm6MWx8rd-e83iHhxiBH0+tA@mail.gmail.com
Whole thread Raw
In response to Re: finding tables about to be vacuum freezed  (Jeff Janes <jeff.janes@gmail.com>)
Responses Re: finding tables about to be vacuum freezed
List pgsql-general
Tree view
On Wed, May 6, 2015 at 9:46 AM Jeff Janes <jeff.janes@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):


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.

pgsql-general by date:

Previous
From: Jeff Janes
Date:
Subject: Re: finding tables about to be vacuum freezed
Next
From: Yves Dorfsman
Date:
Subject: detached query?