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 regionnode: Identifies the component typeinstance: The unique instance ID for the customerpod: The specific Kubernetes pod name (Note: These global labels replace the formernode_type="primary"label).
Service availability and uptime
Section titled “Service availability and uptime”Indicates whether the last scrape successfully connected to the server (Type: gauge)
| Label | Description | Values |
|---|---|---|
instance |
The unique instance ID | 1 = connection successful, 0 = connection failed |
This is the most important metric for alerting. When the value is 0, all other metrics are unavailable.
pg_postmaster_start_time_seconds
Section titled “pg_postmaster_start_time_seconds”Time at which the PostgreSQL postmaster process started, in seconds since the Unix epoch (Type: gauge)
Database size and disk space
Section titled “Database size and disk space”pg_database_size_bytes
Section titled “pg_database_size_bytes”Database file size in bytes (Type: gauge)
| Label | Description | Example values |
|---|---|---|
datname |
Name of the database | nextcloud, wordpress, crm |
This metric shows the current size of each database on the server and is important for capacity planning and growth trends.
Disk space and directory sizes
Section titled “Disk space and directory sizes”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 bytespg_disk_space_total_bytes{region="$region",instance="$instance",pod="$pod"} - pg_disk_space_left_bytes{region="$region",instance="$instance",pod="$pod"}Locks and conflicts
Section titled “Locks and conflicts”pg_locks_count
Section titled “pg_locks_count”Number of locks by mode and database (Type: gauge)
| Label | Description | Possible values |
|---|---|---|
datname |
Name of the database | nextcloud, wordpress, crm |
mode |
Lock mode | See table below |
High lock counts can indicate contention and performance issues. Under normal conditions, most locks should be at 0.
Lock modes
Section titled “Lock modes”| Lock mode | Usage | Conflicts With |
|---|---|---|
AccessShareLock |
SELECT queries | AccessExclusiveLock |
RowShareLock |
SELECT FOR UPDATE/SHARE | Exclusive, ShareRowExclusive, AccessExclusive |
RowExclusiveLock |
INSERT, UPDATE, DELETE | Share, ShareRowExclusive, Exclusive, AccessExclusive |
ShareUpdateExclusiveLock |
VACUUM, CREATE INDEX CONCURRENTLY | ShareUpdateExclusive and higher |
ShareLock |
CREATE INDEX | RowExclusive and higher |
ShareRowExclusiveLock |
Rarely used | RowExclusive and higher |
ExclusiveLock |
Blocks all concurrent access | RowExclusive and higher |
AccessExclusiveLock |
ALTER TABLE, DROP TABLE, TRUNCATE | All modes |
pg_stat_database_deadlocks
Section titled “pg_stat_database_deadlocks”Number of deadlocks detected in the database (Type: counter)
| Label | Description |
|---|---|
datname |
Name of the database |
Should ideally be 0. Frequent deadlocks require application-level query optimisation.
pg_stat_database_conflicts
Section titled “pg_stat_database_conflicts”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.
Connections
Section titled “Connections”pg_settings_max_connections
Section titled “pg_settings_max_connections”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.
pg_stat_database_numbackends
Section titled “pg_stat_database_numbackends”Number of currently active backend processes per database (Type: gauge)
| Label | Description |
|---|---|
datname |
Name of the database |
pg_stat_activity_count
Section titled “pg_stat_activity_count”Number of connections grouped by various attributes (Type: gauge)
| Label | Description | Possible values |
|---|---|---|
datname |
Name of the database | nextcloud, wordpress, crm |
state |
Connection state | See table below |
application_name |
Name of client application | Patroni heartbeat, pg_exporter |
backend_type |
Type of backend process | client backend |
wait_event |
Event being waited for | ClientRead |
wait_event_type |
Type of wait event | Client |
Connection states
Section titled “Connection states”| State | Meaning |
|---|---|
active |
Query is currently running |
idle |
Connection open but no query active |
idle in transaction |
Connection in transaction but no current query |
idle in transaction (aborted) |
Transaction failed, waiting for ROLLBACK |
fastpath function call |
Client running fast-path function |
disabled |
Tracking disabled for this connection |
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.
Long running transactions
Section titled “Long running transactions”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)
Memory, cache and I/O performance
Section titled “Memory, cache and I/O performance”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)
| Label | Description |
|---|---|
datname |
Name of the database |
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.
Detailed I/O subsystem
Section titled “Detailed I/O subsystem”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
| Label | Possible values |
|---|---|
context |
normal, vacuum, bulkread, bulkwrite |
object |
relation, temp relation |
type |
client backend, autovacuum worker, checkpointer, background worker |
Temporary files
Section titled “Temporary files”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)
Background writer and SLRU
Section titled “Background writer and SLRU”Background writer
Section titled “Background writer”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_totalpg_stat_bgwriter_checkpoints_timed_total/_req_totalpg_stat_bgwriter_checkpoint_write_time_total/_sync_time_totalpg_stat_bgwriter_maxwritten_clean_totalpg_stat_bgwriter_reset_time_seconds(Type: gauge)
SLRU (Simple least-recently-used)
Section titled “SLRU (Simple least-recently-used)”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.
| Label | Description | Example values |
|---|---|---|
name |
Name of the SLRU cache | transaction, multixact_offset, commit_timestamp |
WAL and recovery
Section titled “WAL and recovery”pg_is_in_recovery:1if the node is a replica/standby in recovery,0if primary. (Type: gauge)pg_wal_bytes: Total WAL bytes generated. (Type: counter)pg_wal_records: Number of WAL records generated. (Type: counter)
Vacuuming progress
Section titled “Vacuuming progress”Tracking for background and manual vacuum operations:
pg_vacuuming_progresspg_vacuuming_delay_timepg_vacuuming_dead_tuple_bytespg_vacuuming_indexes_processedpg_vacuuming_indexes_total
Settings and cgroups (System constraints)
Section titled “Settings and cgroups (System constraints)”Configuration boundaries
Section titled “Configuration boundaries”Exposes currently configured limits and boundaries. (Type: gauge)
| Metric | Description |
|---|---|
pg_settings_shared_buffers |
Allocated shared buffer memory. |
pg_settings_work_mem |
Memory allowed per query operation before spilling to temp files. |
pg_settings_max_parallel_workers |
Maximum parallel workers allowed globally. |
pg_settings_max_parallel_workers_per_gather |
Maximum parallel workers per node. |
pg_settings_max_wal_size |
Target maximum size of WAL directory before hard checkpoint. |
pg_settings_min_wal_size |
Target minimum size of WAL directory. |
Cgroup resource constraints
Section titled “Cgroup resource constraints”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 coresrate(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"}) * 100Recommended alerts
Section titled “Recommended alerts”| Alert | Condition | Severity |
|---|---|---|
| PostgreSQL Down | pg_up == 0 |
Critical |
| Connection Limit | pg_stat_activity_count / pg_settings_max_connections > 0.8 |
Warning |
| Connection Limit Critical | pg_stat_activity_count / pg_settings_max_connections > 0.9 |
Critical |
| Low Cache Hit Ratio | rate(pg_stat_database_blks_hit) / (rate(pg_stat_database_blks_hit) + rate(pg_stat_database_blks_read)) < 0.95 |
Warning |
| Temp File Usage | rate(pg_stat_database_temp_bytes[5m]) > 0 |
Warning |
| Too Many Locks | pg_locks_count > 100 |
Warning |
| Long Running Transactions | pg_stat_activity_count{state="idle in transaction"} > 0 for > 5 min |
Warning |
Monitoring Best Practices
Section titled “Monitoring Best Practices”- 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_memor 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_exporterand 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.