Search Postgresql Archives

Re: How to introspect autovacuum analyze settings

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



2016-11-21 14:44 GMT+01:00 Benedikt Grundmann <bgrundmann@xxxxxxxxxxxxxx>:
Hello all,

I have a quick question.  I feel like somewhere in section 23.1.6 there should be the answer but I couldn't find it yet.  Namely how can I query the database for total number of tuples inserted, updated, or deleted since the last ANALYZE?  pg_stat_user_tables.n_tup_{ins,upd,del,hot_upd} seems to not reset after an analyze[1].  But clearly the database has that knowledge somewhere persistent because otherwise how could autovacuum do its thing.  

Rationale for the question.  I have the strong suspicion that for some of our bigger tables autovacuum analyze is not hitting frequently enough (even so we already much more aggressive settings than the default).  So I want to set some custom settings for those tables.  But rather than doing it manually for the one table I found I would much rather write a query (ideally one taking any existing per table settings into account) that tells me likely candidates for tweaking.  But so far I fail to even get the relevant data.


What you are looking for is the n_mod_since_analyze column of the pg_stat_user_tables view. It's available since the 9.4 release. If you have an earlier version, you need this extension: http://pgxn.org/dist/mods_since_analyze/1.0.0/


--

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux