Skip to content

Observability metrics

Last updated on

This document describes the Prometheus metrics exported by PostgreSQL (via pg_exporter) for the managed database service. All metrics include the label node_type="primary" since the service is configured with Patroni for high availability.

Global labels: All metrics automatically include the following base labels to identify the deployment. To reduce repetition, they are omitted from the individual metric tables below, but you can expect them on every query:

  • region: The deployment region
  • node: Identifies the component type
  • instance: The unique instance ID for the customer
  • pod: The specific Kubernetes pod name (Note: These global labels replace the former node_type="primary" label).

Indicates whether the last scrape successfully connected to the server (Type: gauge)

This is the most important metric for alerting. When the value is 0, all other metrics are unavailable.

Time at which the PostgreSQL postmaster process started, in seconds since the Unix epoch (Type: gauge)

Database file size in bytes (Type: gauge)

This metric shows the current size of each database on the server and is important for capacity planning and growth trends.

Total and available disk space for the PostgreSQL volume, as well as specific directory sizes. (Type: gauge)

  • pg_disk_space_total_bytes: Total disk space available on the volume.
  • pg_disk_space_left_bytes: Remaining free disk space.
  • pg_logdir_size_mb: Size of the log directory in megabytes.
  • pg_waldir_size_mb: Size of the WAL (Write-Ahead Log) directory in megabytes.

Example PromQL for disk usage:

# Disk usage %
(
(pg_disk_space_total_bytes{region="$region",instance="$instance",pod="$pod"} - pg_disk_space_left_bytes{region="$region",instance="$instance",pod="$pod"})
/ on(pod, instance, cluster, region)
pg_disk_space_total_bytes{pod="$pod"}
) * 100
# Disk usage size in bytes
pg_disk_space_total_bytes{region="$region",instance="$instance",pod="$pod"} - pg_disk_space_left_bytes{region="$region",instance="$instance",pod="$pod"}

Number of locks by mode and database (Type: gauge)

High lock counts can indicate contention and performance issues. Under normal conditions, most locks should be at 0.

Number of deadlocks detected in the database (Type: counter)

Should ideally be 0. Frequent deadlocks require application-level query optimisation.

Number of queries cancelled due to conflicts with recovery (Type: counter)

This metric is mainly relevant on standby/replica servers. A value of 0 is normal for primary nodes.

Maximum number of concurrent connections to the database server (Type: gauge)

The system uses up to 15 connections for internal essential processes such as backup, monitoring, etc. These connections are counted against the max_connections limit. When approaching this limit, connection pooling should be considered.

Number of currently active backend processes per database (Type: gauge)

Number of connections grouped by various attributes (Type: gauge)

idle in transaction for extended periods indicates application issues. Many idle connections show inefficient connection management.

Database statistics (Transactions and rows)

Section titled “Database statistics (Transactions and rows)”

Measures the core read/write throughput and MVCC workload. All metrics below are of Type: counter and use the datname label.

  • pg_stat_database_xact_commit: Number of successfully committed transactions. High values indicate intensive database activity.
  • pg_stat_database_xact_rollback: Number of rolled-back transactions.
  • pg_stat_database_xact_total: Total transactions executed.
  • pg_stat_database_tup_fetched: Number of rows fetched by queries. High values show intensive read activity.
  • pg_stat_database_tup_inserted: Number of rows inserted by queries.
  • pg_stat_database_tup_updated: Number of rows updated by queries. Updates create new row versions (MVCC), so VACUUM is important.
  • pg_stat_database_tup_deleted: Number of rows deleted by queries. High delete rates require regular VACUUM operations.
  • pg_long_running_transaction_count: Number of long-running transactions. (Type: gauge)
  • pg_long_running_transaction_oldest_timestamp_seconds: Timestamp of the oldest active transaction. (Type: gauge)

pg_stat_database_blks_hit and pg_stat_database_blks_read

Section titled “pg_stat_database_blks_hit and pg_stat_database_blks_read”

Measures traditional PostgreSQL shared buffer cache efficiency. (Type: counter)

The cache hit ratio is calculated as blks_hit / (blks_hit + blks_read). The goal is a cache hit ratio of >99% for good performance. High values for blks_read indicate insufficient shared buffers or cold cache.

Granular I/O monitoring metrics measuring operations by context and backend type. (Type: counter)

  • Operations: pg_io_hits, pg_io_reads, pg_io_writes, pg_io_extends, pg_io_evictions, pg_io_reuses, pg_io_writebacks, pg_io_fsyncs

  • Timings (if enabled): pg_io_read_time, pg_io_write_time, pg_io_extend_time, pg_io_writeback_time, pg_io_fsync_time, pg_io_reset_time

High values indicate queries that require more RAM than work_mem allows, spilling to disk. Frequent temp file usage should be addressed by increasing work_mem or optimising queries.

  • pg_stat_database_temp_bytes: Total amount of data written to temporary files in bytes. (Type: counter)
  • pg_stat_database_temp_files: Number of temp files created. (Type: counter)

Tracks the efficiency of the background writer process in cleaning dirty buffers. (All Type: counter, except reset time)

  • pg_stat_bgwriter_buffers_alloc_total / _clean_total / _backend_total / _backend_fsync_total / _checkpoint_total
  • pg_stat_bgwriter_checkpoints_timed_total / _req_total
  • pg_stat_bgwriter_checkpoint_write_time_total / _sync_time_total
  • pg_stat_bgwriter_maxwritten_clean_total
  • pg_stat_bgwriter_reset_time_seconds (Type: gauge)

Tracks SLRU caches (like subtrans, multixact, commit timestamps). Metrics include pg_slru_blks_exists, _hit, _read, _written, _zeroed, pg_slru_flushes, _truncates, and pg_slru_reset_time.

  • pg_is_in_recovery: 1 if the node is a replica/standby in recovery, 0 if primary. (Type: gauge)
  • pg_wal_bytes: Total WAL bytes generated. (Type: counter)
  • pg_wal_records: Number of WAL records generated. (Type: counter)

Tracking for background and manual vacuum operations:

  • pg_vacuuming_progress
  • pg_vacuuming_delay_time
  • pg_vacuuming_dead_tuple_bytes
  • pg_vacuuming_indexes_processed
  • pg_vacuuming_indexes_total

Exposes currently configured limits and boundaries. (Type: gauge)

Tracks the Linux control group constraints and consumption applied to the pod container. Labels include cgroup, collector, and host.

CPU metrics:

  • pg_cgroup_cpu_limit_cores: Maximum CPU cores assigned. (Type: gauge)
  • pg_cgroup_cpu_usage_seconds_total, pg_cgroup_cpu_system_seconds_total, pg_cgroup_cpu_user_seconds_total (Type: counter)

Example PromQL for CPU Usage:

# Percentage of CPU usage
(
rate(pg_cgroup_cpu_usage_seconds_total{pod="$pod"}[5m])
/ ignoring(cgroup, collector, host)
pg_cgroup_cpu_limit_cores{pod="$pod"}
) * 100
# CPU usage in cores
rate(pg_cgroup_cpu_usage_seconds_total{region="$region",instance="$instance", pod="$pod"}[5m])

Memory metrics (Type: gauge):

  • pg_cgroup_memory_limit_bytes: Maximum memory boundary before OOM.
  • pg_cgroup_memory_current_bytes: Total current memory consumption.
  • Cache/anon breakdown: pg_cgroup_memory_anon_bytes, pg_cgroup_memory_file_bytes (including active/inactive variants).

Example PromQL for Memory Usage:

# Percentage of memory usage
(
pg_cgroup_memory_current_bytes{region="$region", instance="$instance",pod="$pod"}
/ on(pod, instance, cluster,region)
pg_cgroup_memory_limit_bytes{region="$region", instance="$instance",pod="$pod"}
) * 100
  • Monitor cache efficiency: The ratio blks_hit / (blks_hit + blks_read) should consistently be >99%.
  • Connection pooling: When approaching max_connections, a connection pool library/application (e.g., pgBouncer) should be deployed.
  • Temp file usage: Should remain at 0. If not, increase work_mem or optimize queries.
  • Lock monitoring: Regular locks can indicate design issues.
  • Idle in transaction: These connections should not persist longer than a few seconds.
  • All metrics are provided via pg_exporter and collected by Prometheus.
  • The service runs with Patroni for high availability, hence the label node_type="primary".
  • The listed metrics form the basis for monitoring and alerting. Additional metrics for replication, background writer, and WAL archiving may be available depending on the setup.