Re: Adding percentile metrics to pg_stat_statements module

On Thu, Oct 31, 2019 at 12:51:17PM -0300, Igor Calabria wrote:
>Hi everyone,
>
>I was taking a look at pg_stat_statements module and noticed that it does
>not collect any percentile metrics. I believe that It would be really handy
>to have those available and I'd love to contribute with this feature.
>
>The basic idea is to accumulate the the query execution times using an
>approximation structure like q-digest or t-digest and add those results to
>the pg_stat_statements table as fixed columns. Something like this
>
>p90_time:
>p95_time:
>p99_time:
>p70_time:
>...
>
>Another solution is to persist de digest structure in a binary column and
>use a function to extract the desired quantile ilke this SELECT
>approx_quantile(digest_times, 0.99) FROM pg_stat_statements
>

IMO having some sort of CDF approximation (being a q-digest or t-digest)
would be useful, although it'd probably need to be optional (mostly
becuase of memory consumption).

I don't see why we would not store the digests themselves. Storing just
some selected percentiles would be pretty problematic due to losing a
lot of information on restart. Also, pg_stat_statements is not a table
but a view on in-memory hash table.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services