OpenTelemetry can be used to easily integrate basic metrics from the PostgreSQL statistics collector using the PostgreSQL receiver. However, this solution provides only insight into the overall health of your PostgreSQL instance. In many cases you have certain slow running queries in your application causing a problem and you might want to find out which ones they are or why they’re slow. PostgreSQL’s pg_stat_statements extension and its view are a popular source for such information. It can be enabled through an extension and provides aggregated metrics for each query (see PostgreSQL documentation). This blog shows you how you can ingest information from this view using OpenTelemetry capabilities that are already available.

Importing Query Specific Metrics from pg_stat_statements with OpenTelemetry

While the PostgreSQL receiver does not query data from the pg_stat_statements view, we can use the SQL query receiver for the OpenTelemetry Collector, to ingest those metrics. The SQL query receiver can be used to convert results from arbitrary queries into OpenTelemetry logs or metrics.

Below configuration snippet configures the SQL query receiver to collect information from the pg_stat_statements view and extract two metrics which are exposed on the collector’s Prometheus endpoint. Additional metrics related to the statement’s execution time, plan time, or cache usage can be extracted by adding objects to the metrics list.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

receivers:
  sqlquery/pgstatstatements:
    driver: postgres
    datasource: "host=db port=5432 user=oteluser password=otelpw sslmode=disable database=knexdb" 
    queries:
    - sql: "select * from pg_stat_statements"
      metrics:
      - metric_name: pg_stat_statements_total_exec_time
        value_column: total_exec_time
        attribute_columns:
          - queryid
          - userid
          - dbid
        value_type: double
        data_type: sum
      - metric_name: pg_stat_statements_mean_exec_time
        value_column: mean_exec_time
        attribute_columns:
          - queryid
          - userid
          - dbid
        value_type: double
        data_type: gauge
exporters:
  prometheus:
    endpoint: "0.0.0.0:8889"
    send_timestamps: true
    metric_expiration: 180m
    enable_open_metrics: true
    add_metric_suffixes: false
    resource_to_telemetry_conversion:
      enabled: true
service:
  pipelines:
    metrics:
      receivers: [sqlquery/pgstatstatements]
      exporters: [prometheus]

One problem with this is finding your query based on the query ID. The pg_stat_statement view provides the whole query in the query column, which could be added as a label for your metrics, but this will bloat up your metrics. I also think that adding a label in Prometheus should lead to the creation of a new dimension and a new time series. Since the query relates 1-1 to the query ID, this is not the case. The query ID can be logged or queried from the table to support interpretation. I suggest logging the query ID is the best approach. Query ID logging in PostgreSQL can be enabled with the following options:

compute_query_id = auto
log_statement_stats = on

Additionally, this may create up to 40 metrics per query, leading to a lot of time series. You should think about using the WHERE clause to filter queries. Filtering for the right queries is challenging and depends on your use case. In any case, I suggest at least filtering by database user to avoid PostgreSQL’s internal queries showing up.

Conclusion

We can incorporate a lot of query specific and generic information about PostgreSQL in our regular OpenTelemetry compliant observability systems. To do so:

• Use the PostgreSQL receiver to extract information from the statistics collector.
• Enable the pg_stat_statements extension and use the SQL query receiver to read metrics for your most important queries.

If you have any questions, are interested in hearing more, or sharing your thoughts with me, let me know on LinkedIn or Mastodon.