Re: PROPOSAL: tracking aggregated numbers from pg_stat_database

On 4/13/13 12:44 PM, Tomas Vondra wrote:
> I'm currently struggling with (quite uncommon) deployments where
> databases are created/dropped regularly (not to mention tables and
> indexes), and it's surprisingly difficult to process such stats to get
> reasonable values.

Yes, it's a pain. If you aggregate the table level data available now,
you'll end up with some missing activity. Work done between the last
snapshot and when the drop happened is gone right now, whereas your
aggregated stats view would preserve that activity. The real fun is if
the new table has the same name as the old one, which gives you all the
negative value headaches a pg_stat_reset introduces too.

It's possible to make a case for why database level aggregate statistics
are useful. I don't know that yours is compelling enough to justify
itself though, given that as you say this is an uncommon situation. In
something similar to your setup I've just accepted that I have to save
the snapshots into a table, will occasionally lose some mid-snapshot
data, and I use a combination of correction updates to that table and
SQL window functions to provide a useful view. It's a pain to do and I
end up having to customize this approach for seemingly every install,
but it can be made accurate enough for most uses.

The gigantic hole in this area I was most interested in for 9.4
development was the lack of write statistics. Not having
pg_stat_database.blks_write, pg_statio_user_tables.heap_blks_write or
pg_statio_user_indexes.idx_blks_write is a crippling operations/planning
limitation of the database. From that perspective, now isn't quite the
right time to add more aggregation on top of that data, since the
aggregation will make adding additional counters a bit more complicated.
It's not a big difference, but thinking in that direction doesn't help
your suggestion.

--
Greg Smith 2ndQuadrant US greg(at)2ndQuadrant(dot)com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com