Re: Faster way of estimating database size

Hi Sam,

When behind a terminal I use \l+ to show the size of the databases, since it is handy to remember. It shows db size in
a"pretty size".
 

Timing both commands, i see that \l+ takes more or less the same time your query takes, but I think your query better
fitsthe monitoring purpose.
 

But the real point here is why are you running the command every ~3 seconds? (and perhaps, why 200 databases?)

I would focus on that issue, and keep using your query.


regards,

fabio pardi


On 15/06/18 02:29, Sam Saffron wrote:
> Hi there,
>
> At the moment we are using:
>
> SELECT pg_database.datname, pg_database_size(pg_database.datname) as
> size FROM pg_database
>
> To gather size of databases for monitoring purposes in Prometheus.
>
> Our pg stat logging now shows this is our number one query cause we
> seem to be running it for some crazy reason 19 times a minute. Clearly
> this is too much and we should (and will) cache results for a bit.
>
> Nonetheless, I notice it take 400ms to run on our cluster (with a few
> 200 dbs) and was wondering if there is a quicker way of estimating
> this number?
>
> Sam
>