Phonemos can export aggregated PostgreSQL statement statistics to Prometheus. Use this to find expensive Hasura queries and other database hotspots.

How it works

The Phonemos backend reads a tracing view every minute and publishes gauges on its own Prometheus endpoint.

pg_stat_statements records statement calls and execution time (requires the extension to be preloaded).
View tracing.statement_performance groups those statements by alias (tracing.alias plus fallbacks such as <other hasura query>).
The backend DatabaseMetricsReporter queries that view via Hasura once per minute and updates Kamon gauges.
Kamon exposes those gauges on the backend Prometheus endpoint. Cluster Prometheus scrapes that endpoint. You query the series in Prometheus or Grafana.

Enable pg_stat_statements

Add the following to PostgreSQL (Helm postgres-standalone.db.additionalConfigs):

postgres-standalone:
  db:
    additionalConfigs:
      - shared_preload_libraries=pg_stat_statements
      - pg_stat_statements.max=10000
      - pg_stat_statements.track=all
    trackActivityQuerySize: 20000

trackActivityQuerySize (PostgreSQL track_activity_query_size) must be large enough that Hasura SQL is not truncated. Aliases match on full statement text.

shared_preload_libraries only applies after a PostgreSQL restart. Changing Helm values is not enough until the database process has been restarted.

Activate the tracing view

Hasura migration tracing.setup() creates tracing.statement_performance only if pg_stat_statements was already preloaded when that migration first ran. If you enable the extension later, the view stays as an empty stub (Statement performance (deactivated)) and Prometheus will show no statement series.

After preload is live, run once as a superuser:

SHOW shared_preload_libraries;
-- must contain pg_stat_statements

SELECT tracing.setup();

SELECT obj_description('tracing.statement_performance'::regclass);
-- want: Statement performance based on pg_stat_statements
-- not:  Statement performance (deactivated)

SELECT * FROM tracing.statement_performance LIMIT 10;

Re-applying Helm or restarting the backend does not replace the stub view. You must call tracing.setup() again after the library is loaded.

Where Prometheus gets the metrics

The backend embeds a Kamon Prometheus server on port 9095 (path /metrics). That is the scrape target. Phonemos does not push metrics to Grafana or another system.

Kubernetes (Helm chart)

The backend Service is annotated for scrape:

prometheus.io/scrape: "true"
prometheus.io/port: "9095"

Cluster Prometheus (pod labels app.kubernetes.io/instance: monitoring-prometheus / namespace role cluster-monitoring) scrapes that Service. Network policies already allow Prometheus to reach the backend metrics port.

From Prometheus the series are available to Grafana (or any PromQL client) in the same monitoring stack. Typical place to inspect them: Grafana Explore, data source Prometheus, query phonemos_database_statement_count.

Self-hosted / docker-compose

Point your Prometheus scrape config at the backend container, port 9095, path /metrics. Then visualize in your Grafana (or equivalent). See also System Requirements (Prometheus is recommended optional infrastructure).

Metric names

Published about one minute after the view returns rows. Labels: database="postgres" and statementAlias (for example TopicRoute, <other hasura query>, <all other>).

phonemos_database_statement_count — number of executions
phonemos_database_statement_time_total — total execution time
phonemos_database_statement_time_mean — mean execution time
phonemos_database_statement_time_min — minimum execution time
phonemos_database_statement_time_max — maximum execution time

Troubleshooting

Series missing: check the view comment. If it says deactivated, run SELECT tracing.setup(); after confirming preload.
Preload not listed in SHOW shared_preload_libraries: PostgreSQL was not restarted after the config change.
View empty but extension is on: wait for traffic, then wait up to one minute for the reporter.
Backend scrape has no phonemos_database_statement_*: the reporter only calls updateGauge when the view returns rows. A stub view produces no series.

Phonemos User Guide

Analyse Database Perfomance Bottlenecks