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
thatdirection doesn't help 
 
your suggestion.

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