Robert Bernier: Grafana Dashboards: A PoC Implementing the PostgreSQL Extension pg_stat_monitor

This PoC demonstrates how to install and configure pg_stat_monitor in order to extract useful and actionable metrics from a PostgreSQL database and display them on a Grafana dashboard. About the environment Grafana: version 10.0.0 Grafana database backend: Prometheus version 2.15.2+d PostgreSQL version 13 pgbench version 13 In order to investigate the potential opportunities for implementing constructive and useful metrics derived from PostgreSQL into Grafana, it is necessary to generate loading using pgbench. Configuring Grafana For our purposes, the Grafana datasource used in this PoC is also the Postgres data cluster that is generating the data to be monitored. pg_stat_monitor About pg_stat_monitor is a Query Performance Monitoring tool for PostgreSQL. It collects various statistics data such as query statistics, query plan, SQL comments, and other performance insights. The collected data is aggregated and presented in a single view.  pg_stat_monitor takes its inspiration from pg_stat_statements. Unlike pg_stat_statements, which aggregates its metrics from the last time it was zeroed, pg_stat_monitor possesses the ability to bucket its output within a set number of aggregated results, thus saving user efforts from doing it themselves. pg_stat_monitor tracks the following operations: statements queries functions stored procedures and other non-utility statements Features Time Interval Grouping: Instead of supplying one set of ever-increasing counts, pg_stat_monitor computes stats for a configured number of time intervals – time buckets. This allows for much better data accuracy, especially in the case of high-resolution or unreliable networks. Multi-Dimensional Grouping: While pg_stat_statements groups counters by userid, dbid, queryid, pg_stat_monitor uses a more detailed group for higher precision. This allows a user to drill down into the performance of queries. Captur[...]