Re: [Proposal] More Vacuum Statistics

On 5/28/15 9:14 AM, Tom Lane wrote:
> Naoya Anzai <nao-anzai(at)xc(dot)jp(dot)nec(dot)com> writes:
>> In my much experience up until now,I have an idea that we can add
>> 2 new vacuum statistics into pg_stat_xxx_tables.
>
> Adding new stats in that way requires adding per-table counters, which
> bloat the statistics files (especially in database with very many tables).
> I do not think you've made a case for these stats being valuable enough
> to justify such overhead for everybody.

It occurs to me that there's no good reason for vacuum-derived stats to
be in the stats file; it's not like users run vacuum anywhere near as
often as other commands. It's stats could be kept in pg_class; we're
already keeping things like relallvisible there.

> As far as the first one goes, I don't even think it's especially useful.
> There might be value in tracking the times of the last few vacuums on a
> table, but knowing the time for only the latest one doesn't sound like it
> would prove much. So I'd be inclined to think more along the lines of
> scanning the postmaster log for autovacuum runtimes, instead of squeezing
> it into the pg_stats views.

You'd also want to know how many pages were scanned, since any decent
estimation would need to take table size into account.

As for history, that's a problem that exists for *all* our statistics,
so anyone that cares about that is going to setup some system to
periodically capture the contents of pg_stat_*.

> A possible alternative so far as the second one goes is to add a function
> (perhaps in contrib/pg_freespacemap) that simply runs through a table's
> VM and counts the number of set bits. This would be more accurate (no
> risk of lost counter updates) and very possibly cheaper overall: it would
> take longer to find out the number when you wanted it, but you wouldn't be
> paying the distributed overhead of tracking it when you didn't want it.

Seems like a reasonable addition to that contrib module regardless. As
Jeff Janes mentioned this info is available in pg_class, but it requires
an ANALYZE to update it.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.com