Re: How to introspect autovacuum analyze settings

2016-11-21 14:44 GMT+01:00 Benedikt Grundmann <bgrundmann(at)janestreet(dot)com>:

> 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/

--
Guillaume.
http://blog.guillaume.lelarge.info
http://www.dalibo.com