Re: Proposal: Extending the PostgreSQL Protocol with Command Metadata

Kir Shatrov <sigkirs(at)gmail(dot)com> writes:

Hi,

> ## Unlocking Greater Extensions
>
> Extensions leveraging this metadata protocol could allow to build things with very little overhead that are not possible
> today:
>
> - Multi-tenant applications tracking per-query resource consumption (buffer hits/misses, CPU/IO time)
> - Connection poolers making routing decisions based on server load
> - Performance monitoring without separate instrumentation queries
> - Applications implementing custom rate limiting based on DB metrics
> - Distributed tracing integration at the protocol level
>
> ## Why not session variables?
>
> You could argue this could be achieved by reading session variables after each query, but that quickly becomes not
> practical: leveraging session variables breaks multiplexing (e.g. PgBouncer) and hitting the server with yet another
> query when it's already busy just increases the number of incoming queries that it needs to handle.

Does pg_stat_statements [1] work for you? I think some differents
between pg_stat_statements and command level metrics are:

1. pg_stat_statements is a aggreated data and command level metrics is a
raw data for each statement.
2. the entries in pg_stat_statments in limitted by
pg_stat_statements.max.

But IMO we need some analysis or aggregated data on these metrics rather
than use the raw data, Could you explain more how to use these command
level metrics to make decision?

[1]
https://www.postgresql.org/docs/current/pgstatstatements.html#PGSTATSTATEMENTS-PG-STAT-STATEMENTS

--
Best Regards
Andy Fan