Re: PROPOSAL: tracking aggregated numbers from pg_stat_database

On 13.4.2013 15:01, Peter Eisentraut wrote:
> On Sat, 2013-04-06 at 21:51 +0200, Tomas Vondra wrote:
>> This more or less works in stable environments, but once you start
>> dropping databases (think of hosting with shared DB server) it gets
>> unusable because after DROP DATABASE the database suddenly disappears
>> from the sum.
>>
>> Therefore I do propose tracking the aggregated stats, similar to the
>> pg_stat_bgwriter view. 
> 
> It seems like this will open a can of worms.  Maybe someone wants
> aggregated stats for pg_stat_user_tables?  Or maybe instead of the sum,
> someone wants to track the average?  etc.  I don't think we should turn

What I propose is a simple cumulative counter, just like the other
counters we do have right now. I don't think tracking an average (or any
other statistics) makes much sense here. And as the number of objects
changes over time (e.g. dbs may be created/dropped), I'm wondering what
would be the definition of average?

BTW I've proposed tracking aggregated table/index stats in my second
message in this thread.

> the statistics collector into a poor man's data warehouse or statistics
> engine.  Couldn't you transfer the data to some other system for

I certainly don't want to overcomplicate the stats system, and I don;t
think I'm turning it into a DWH or statistics engine. And even with
these aggregated counters, it still requires snapshotting and additional
processing. It's just a bunch of counters.

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.

The point is this allows tracking data that are otherwise effectively
lost. With the current stats you have to discard intervals where
databases were dropped (because well, the data disappear so you don't
know what is the actual snapshot diff). Depending on the number of DB
drops and snapshot interval, this may very well be most of the time.

> long-term analysis?  Maybe you could even use event triggers to have
> DROP DATABASE do that automatically.

I don't think event triggers are a good solution, although I'm wondering
how that's supposed to work.

Tomas