Monitoring PostgreSQL Performance using OpenTelemetry

Monitoring PostgreSQL is essential to ensure the optimal performance, availability, and reliability of your PostgreSQL database.

This tutorial explains how you can use OpenTelemetry and Uptrace to monitor PostgreSQL performance, establish baseline metrics, and proactively address performance issues to ensure the optimal operation of your PostgreSQL database.

• What is OpenTelemetry Collector?
• OpenTelemetry PostgreSQL receiver
• OpenTelemetry Backend
• Available metrics
• What's next?

What is OpenTelemetry Collector?

OpenTelemetry Collector is an agent that pulls telemetry data from systems you want to monitor and export the collected data to an OpenTelemetry backend.

Otel Collector provides powerful data processing capabilities, allowing you to perform aggregation, filtering, sampling, and enrichment of telemetry data. You can transform and reshape the data to fit your specific monitoring and analysis requirements before sending it to the backend systems.

OpenTelemetry PostgreSQL receiver

Using OpenTelemetry Collector, you can monitor essential performance metrics provided by PostgreSQL, such as CPU usage, memory consumption, disk I/O, and network traffic.

To start monitoring PostgreSQL with Otel Collector, you need to configure PostgreSQL receiver in /etc/otel-contrib-collector/config.yaml using Uptrace DSN:

receivers:
  otlp:
    protocols:
      grpc:
      http:
  postgresql:
    endpoint: localhost:5432
    transport: tcp
    username: otel
    password: $POSTGRESQL_PASSWORD
    databases:
      - otel
    collection_interval: 10s
    tls:
      insecure: true

exporters:
  otlp:
    endpoint: otlp.uptrace.dev:4317
    headers: { 'uptrace-dsn': '<FIXME>' }

processors:
  resourcedetection:
    detectors: [env, system]
  cumulativetodelta:
  batch:
    timeout: 10s

service:
  pipelines:
    traces:
      receivers: [otlp]
      processors: [batch]
      exporters: [otlp]
    metrics:
      receivers: [otlp, postgresql]
      processors: [cumulativetodelta, batch, resourcedetection]
      exporters: [otlp]

Don't forget to restart the service:

sudo systemctl restart otelcol-contrib

You can also check OpenTelemetry Collector logs for any errors:

sudo journalctl -u otelcol-contrib -f

OpenTelemetry Backend

Once the metrics are collected and exported, you can visualize them using a compatible backend system. For example, you can use Uptrace to create dashboards that display metrics from the OpenTelemetry Collector.

Uptrace is a OpenTelemetry APM that supports distributed tracing, metrics, and logs. You can use it to monitor applications and troubleshoot issues.

Uptrace comes with an intuitive query builder, rich dashboards, alerting rules with notifications, and integrations for most languages and frameworks.

Uptrace can process billions of spans and metrics on a single server and allows you to monitor your applications at 10x lower cost.

In just a few minutes, you can try Uptrace by visiting the cloud demo (no login required) or running it locally with Docker. The source code is available on GitHub.

Available metrics

Uptrace automatically creates the following dashboard when PostgreSQL metrics are available:

What's next?

Next, you can learn more about configuring OpenTelemetry Collector. To start using OpenTelemetry and Uptrace, see Getting started with Uptrace.

• OpenTelemetry Redis
• OpenTelemetry Docker