PostgreSQL OpenTelemetry Receiver Dashboards¶

Dashboards for monitoring PostgreSQL databases using OpenTelemetry's PostgreSQL receiver.

Overview¶

This dashboard provides visibility into PostgreSQL database performance, connections, transactions, and I/O metrics.

Dashboards¶

Dashboard	File	Description
Overview	`02-overview-esql.yaml`	ES\|QL-based overview with KPIs, time series charts, and database metrics

Dashboard Definitions¶

Overview (02-overview-esql.yaml)

---
# PostgreSQL OpenTelemetry Overview Dashboard (ES|QL Version)
# Comprehensive PostgreSQL metrics dashboard using OpenTelemetry PostgreSQL receiver
# Uses ES|QL with TS command for time-series optimization
#
# Requirements:
#   - data_stream.dataset == "postgresqlreceiver.otel"
#   - Enable postgresql.connection.max metric for the Max Connections KPI panel
#   - Enable postgresql.database.count metric for the Database Count KPI panel
#
# Metrics reference: https://github.com/open-telemetry/opentelemetry-collector-contrib/tree/main/receiver/postgresqlreceiver
dashboards:
  - id: postgresql-otel-overview-esql
    name: '[Metrics PostgreSQL] Overview'
    description: Comprehensive PostgreSQL metrics dashboard using OpenTelemetry PostgreSQL receiver
    controls:
      - type: options
        label: Database Name
        data_view: metrics-*
        field: resource.attributes.postgresql.database.name
      - type: options
        label: Instance
        data_view: metrics-*
        field: resource.attributes.service.instance.id
    filters:
      - field: data_stream.dataset
        equals: postgresqlreceiver.otel
      - exists: resource.attributes.postgresql.database.name
    panels:
      # KPI Metrics Row (y=0, h=6)
      - title: Total Databases
        hide_title: true
        size: {w: 12, h: 4}
        esql:
          type: metric
          query:
            - FROM metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE resource.attributes.postgresql.database.name IS NOT NULL
            - STATS total_databases = COUNT_DISTINCT(resource.attributes.postgresql.database.name)
          primary:
            field: total_databases
            label: Databases
            format:
              type: number
              decimals: 0
      - title: Active Connections
        hide_title: true
        size: {w: 12, h: 4}
        esql:
          type: metric
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.backends IS NOT NULL
            - STATS active_backends = SUM(LAST_OVER_TIME(postgresql.backends))
          primary:
            field: active_backends
            label: Active Backends
            format:
              type: number
              decimals: 0
      - title: Max Connections
        hide_title: true
        size: {w: 12, h: 4}
        description: Requires postgresql.connection.max metric (enable in OTel collector)
        esql:
          type: metric
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.connection.max IS NOT NULL
            - STATS max_connections = MAX(LAST_OVER_TIME(postgresql.connection.max))
          primary:
            field: max_connections
            label: Max Connections
            format:
              type: number
              decimals: 0
      - title: Database Count
        hide_title: true
        size: {w: 12, h: 4}
        description: Requires postgresql.database.count metric (enable in OTel collector)
        esql:
          type: metric
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.database.count IS NOT NULL
            - STATS db_count = MAX(LAST_OVER_TIME(TO_LONG(postgresql.database.count)))
          primary:
            field: db_count
            label: Total DB Count
            format:
              type: number
              decimals: 0

      # Performance Summary DataTable (y=6)
      - title: Database Performance Summary
        size: {w: 48, h: 18}
        description: Peak metrics per database over the selected time range. Counter metrics show maximum cumulative values.
        esql:
          type: datatable
          query:
            - FROM metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE resource.attributes.postgresql.database.name IS NOT NULL
            - STATS backends_count = ROUND(MAX(postgresql.backends), 0), size_bytes = MAX(postgresql.db_size), commit_total = MAX(TO_LONG(postgresql.commits)),
              rollback_total = MAX(TO_LONG(postgresql.rollbacks)), blocks_read_total = MAX(TO_LONG(postgresql.blocks_read)) BY database = resource.attributes.postgresql.database.name
            - KEEP database, backends_count, size_bytes, commit_total, rollback_total, blocks_read_total
            - SORT database ASC
            - LIMIT 100
          breakdowns:
            - field: database
              label: Database
          metrics:
            - field: backends_count
              label: Active Backends
              format:
                type: number
                decimals: 0
            - field: size_bytes
              label: Size (Bytes)
            - field: commit_total
              label: Total Commits
              format:
                type: number
                decimals: 0
            - field: rollback_total
              label: Total Rollbacks
              format:
                type: number
                decimals: 0
            - field: blocks_read_total
              label: Blocks Read
              format:
                type: number
                decimals: 0

      # Connections Over Time (y=24)
      - title: Active Connections Over Time
        size: {w: 48, h: 10}
        esql:
          type: line
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.backends IS NOT NULL
            - STATS connections = MAX(AVG_OVER_TIME(postgresql.backends)) BY time_bucket = BUCKET(@timestamp, 20, ?_tstart, ?_tend), database
              = resource.attributes.postgresql.database.name
            - SORT time_bucket ASC
          legend:
            visible: show
            position: right
          dimension:
            field: time_bucket
            label: Time
            data_type: date
          metrics:
            - field: connections
              label: Active Connections
              format:
                type: number
          breakdown:
            field: database

      # Transaction Rates (y=34)
      - title: Commits Rate
        size: {w: 24, h: 10}
        esql:
          type: area
          mode: stacked
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.commits IS NOT NULL
            - STATS commits_rate = SUM(RATE(postgresql.commits)) BY time_bucket = BUCKET(@timestamp, 20, ?_tstart, ?_tend), database = resource.attributes.postgresql.database.name
            - SORT time_bucket ASC
          legend:
            visible: show
            position: right
          dimension:
            field: time_bucket
            label: Time
            data_type: date
          metrics:
            - field: commits_rate
              label: Commits/sec
              format:
                type: number
                decimals: 2
          breakdown:
            field: database
      - title: Rollbacks Rate
        size: {w: 24, h: 10}
        esql:
          type: area
          mode: stacked
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.rollbacks IS NOT NULL
            - STATS rollbacks_rate = SUM(RATE(postgresql.rollbacks)) BY time_bucket = BUCKET(@timestamp, 20, ?_tstart, ?_tend), database = resource.attributes.postgresql.database.name
            - SORT time_bucket ASC
          legend:
            visible: show
            position: right
          dimension:
            field: time_bucket
            label: Time
            data_type: date
          metrics:
            - field: rollbacks_rate
              label: Rollbacks/sec
              format:
                type: number
                decimals: 2
          breakdown:
            field: database

      # Block I/O (y=44)
      - title: Block I/O by Source
        size: {w: 48, h: 12}
        esql:
          type: line
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.blocks_read IS NOT NULL
            - STATS blocks_rate = SUM(RATE(postgresql.blocks_read)) BY time_bucket = BUCKET(@timestamp, 20, ?_tstart, ?_tend), source = attributes.source
            - SORT time_bucket ASC
          legend:
            visible: show
            position: right
          dimension:
            field: time_bucket
            label: Time
            data_type: date
          metrics:
            - field: blocks_rate
              label: Blocks Read/sec
          breakdown:
            field: source

      # Database Operations (y=56)
      - title: Operations by Type
        size: {w: 48, h: 12}
        esql:
          type: bar
          mode: stacked
          query:
            - TS metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.operations IS NOT NULL
            - STATS operations_rate = SUM(RATE(postgresql.operations)) BY time_bucket = BUCKET(@timestamp, 20, ?_tstart, ?_tend), operation =
              attributes.operation
            - SORT time_bucket ASC
          legend:
            visible: show
            position: right
          dimension:
            field: time_bucket
            label: Time
            data_type: date
          metrics:
            - field: operations_rate
              label: Operations/sec
          breakdown:
            field: operation

      # Distribution Charts (y=68)
      - title: Database Sizes
        size: {w: 24, h: 10}
        esql:
          type: pie
          query:
            - FROM metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.db_size IS NOT NULL
            - STATS db_size = MAX(postgresql.db_size) BY database = resource.attributes.postgresql.database.name
            - SORT db_size DESC
            - LIMIT 20
          metrics:
            - field: db_size
              label: Size
              format:
                type: bytes
                decimals: 2
          breakdowns:
            - field: database
              label: Database
          appearance:
            donut: medium
      - title: Connection States
        size: {w: 24, h: 10}
        esql:
          type: pie
          query:
            - FROM metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE postgresql.backends IS NOT NULL
            - WHERE attributes.state IS NOT NULL
            - STATS connections = MAX(postgresql.backends) BY state = attributes.state
            - SORT connections DESC
            - LIMIT 10
          metrics:
            - field: connections
              label: Connections
              format:
                type: number
                decimals: 0
          breakdowns:
            - field: state
              label: State
          appearance:
            donut: medium

      # Metadata Table (y=78)
      - title: Database Metadata
        size: {w: 48, h: 12}
        description: List of monitored databases and their host locations
        esql:
          type: datatable
          query:
            - FROM metrics-*
            - WHERE data_stream.dataset == "postgresqlreceiver.otel"
            - WHERE resource.attributes.postgresql.database.name IS NOT NULL
            - STATS BY database = resource.attributes.postgresql.database.name, instance = resource.attributes.service.instance.id
            - KEEP database, instance
            - SORT database ASC
            - LIMIT 100
          breakdowns:
            - field: database
              label: Database
            - field: instance
              label: Instance

Prerequisites¶

PostgreSQL: PostgreSQL database server
OpenTelemetry Collector: Collector with PostgreSQL receiver configured
Kibana: Version 9.2 or later (dashboards use ES|QL TS command)

Data Requirements¶

Data stream dataset: postgresqlreceiver.otel
Data view: metrics-*

OpenTelemetry Collector Configuration¶

receivers:
  postgresql:
    endpoint: localhost:5432
    transport: tcp
    username: ${env:POSTGRES_USER}
    password: ${env:POSTGRES_PASSWORD}
    databases:
      - postgres
      - myapp_db
    collection_interval: 60s
    metrics:
      postgresql.backends:
        enabled: true
      postgresql.connection.max:
        enabled: true
      postgresql.database.count:
        enabled: true
      postgresql.commits:
        enabled: true
      postgresql.rollbacks:
        enabled: true
      postgresql.blocks_read:
        enabled: true
      postgresql.db_size:
        enabled: true
      postgresql.operations:
        enabled: true

Metrics Reference¶

Default Metrics¶

Metric	Type	Unit	Description	Attributes
`postgresql.backends`	Sum	`1`	Number of backends	—
`postgresql.bgwriter.buffers.allocated`	Sum	`{buffers}`	Number of buffers allocated	—
`postgresql.bgwriter.buffers.writes`	Sum	`{buffers}`	Number of buffers written	`source`
`postgresql.bgwriter.checkpoint.count`	Sum	`{checkpoints}`	Number of checkpoints performed	`type`
`postgresql.bgwriter.duration`	Sum	`ms`	Time spent writing/syncing during checkpoints	`type`
`postgresql.bgwriter.maxwritten`	Sum	`1`	Times background writer stopped	—
`postgresql.blocks_read`	Sum	`1`	Number of blocks read	`source`
`postgresql.commits`	Sum	`1`	Number of commits	—
`postgresql.connection.max`	Gauge	`{connections}`	Maximum client connections allowed	—
`postgresql.database.count`	Sum	`{databases}`	Number of user databases	—
`postgresql.db_size`	Sum	`By`	Database disk usage	—
`postgresql.index.scans`	Sum	`{scans}`	Number of index scans on a table	—
`postgresql.index.size`	Gauge	`By`	Size of the index on disk	—
`postgresql.operations`	Sum	`1`	Number of db row operations	`operation`
`postgresql.replication.data_delay`	Gauge	`By`	Amount of data delayed in replication	`replication_client`
`postgresql.rollbacks`	Sum	`1`	Number of rollbacks	—
`postgresql.rows`	Sum	`1`	Number of rows in the database	`state`
`postgresql.table.count`	Sum	`{table}`	Number of user tables in a database	—
`postgresql.table.size`	Sum	`By`	Disk space used by a table	—
`postgresql.table.vacuum.count`	Sum	`{vacuum}`	Number of times a table has been vacuumed	—
`postgresql.wal.age`	Gauge	`s`	Age of oldest WAL file	—
`postgresql.wal.lag`	Gauge	`s`	WAL replication lag time	`operation`, `replication_client`

Optional Metrics (disabled by default)¶

Metric	Type	Unit	Description	Attributes
`postgresql.blks_hit`	Sum	`{blks_hit}`	Cache buffer hits	—
`postgresql.blks_read`	Sum	`{blks_read}`	Disk blocks read	—
`postgresql.database.locks`	Gauge	`{lock}`	Number of database locks	`relation`, `mode`, `lock_type`
`postgresql.deadlocks`	Sum	`{deadlock}`	Number of deadlocks	—
`postgresql.sequential_scans`	Sum	`{sequential_scan}`	Sequential scan count	—
`postgresql.temp.io`	Sum	`By`	Data written to temporary files	—
`postgresql.temp_files`	Sum	`{temp_file}`	Number of temp files	—

Metric Attributes¶

Attribute	Values	Description
`source` (bgwriter)	`backend`, `backend_fsync`, `checkpoints`, `bgwriter`	Buffer write source
`source` (blocks_read)	`heap_read`, `heap_hit`, `idx_read`, `idx_hit`, `toast_read`, `toast_hit`, `tidx_read`, `tidx_hit`	Block I/O source
`type` (checkpoint)	`requested`, `scheduled`	Checkpoint type
`type` (duration)	`sync`, `write`	Duration type
`operation`	`ins`, `upd`, `del`, `hot_upd`	Row operation type
`state`	`dead`, `live`	Row state
`operation` (wal.lag)	`flush`, `replay`, `write`	WAL operation

Resource Attributes¶

Attribute	Description
`postgresql.database.name`	Database name
`postgresql.index.name`	Index name
`postgresql.schema.name`	Schema name
`postgresql.table.name`	Table name
`service.instance.id`	Service instance identifier

Metrics Not Used in Dashboards¶

The following metrics are available from the PostgreSQL receiver but are not currently visualized in the dashboards:

Default Metrics Not Used¶

Metric	Type	Unit	Description	Attributes
`postgresql.bgwriter.buffers.allocated`	Sum	`{buffers}`	Number of buffers allocated	—
`postgresql.bgwriter.buffers.writes`	Sum	`{buffers}`	Number of buffers written	`source`
`postgresql.bgwriter.checkpoint.count`	Sum	`{checkpoints}`	Number of checkpoints performed	`type`
`postgresql.bgwriter.duration`	Sum	`ms`	Time spent writing/syncing during checkpoints	`type`
`postgresql.bgwriter.maxwritten`	Sum	`1`	Times background writer stopped	—
`postgresql.index.scans`	Sum	`{scans}`	Number of index scans on a table	—
`postgresql.index.size`	Gauge	`By`	Size of the index on disk	—
`postgresql.replication.data_delay`	Gauge	`By`	Amount of data delayed in replication	`replication_client`
`postgresql.rows`	Sum	`1`	Number of rows in the database	`state`
`postgresql.table.count`	Sum	`{table}`	Number of user tables in a database	—
`postgresql.table.size`	Sum	`By`	Disk space used by a table	—
`postgresql.table.vacuum.count`	Sum	`{vacuum}`	Number of times a table has been vacuumed	—
`postgresql.wal.age`	Gauge	`s`	Age of oldest WAL file	—
`postgresql.wal.lag`	Gauge	`s`	WAL replication lag time	`operation`, `replication_client`

Optional Metrics Not Used¶

Metric	Type	Unit	Description	Attributes
`postgresql.blks_hit`	Sum	`{blks_hit}`	Cache buffer hits	—
`postgresql.blks_read`	Sum	`{blks_read}`	Disk blocks read	—
`postgresql.database.locks`	Gauge	`{lock}`	Number of database locks	`relation`, `mode`, `lock_type`
`postgresql.deadlocks`	Sum	`{deadlock}`	Number of deadlocks	—
`postgresql.sequential_scans`	Sum	`{sequential_scan}`	Sequential scan count	—
`postgresql.temp.io`	Sum	`By`	Data written to temporary files	—
`postgresql.temp_files`	Sum	`{temp_file}`	Number of temp files	—