Skip to main content
This example demonstrates comprehensive PostgreSQL monitoring using Query Exporter to collect metrics from PostgreSQL’s built-in statistics collector.

​
Overview

The PostgreSQL stats example collects metrics from various pg_stat_* system views to monitor:
  • Database activity (connections, transactions, queries)
  • Table statistics (scans, tuples, vacuum operations)
  • Index usage and performance
  • Cache hit ratios
  • I/O statistics
  • Process states

​
Configuration

​
Database Connection

databases:
  pg:
    dsn: !env PG_DATABASE_DSN
Set the PG_DATABASE_DSN environment variable with your PostgreSQL connection string: 
export PG_DATABASE_DSN="postgresql://username:password@localhost:5432/dbname"

​
Complete Example

# Collection of metrics and stats for PostgreSQL's stats collector.
#
# See https://www.postgresql.org/docs/current/monitoring-stats.html for more
# details.

databases:
  pg:
    dsn: !env PG_DATABASE_DSN

metrics:
  pg_process:
    type: gauge
    description: Number of PostgreSQL processes with their states
    labels: [state]
  pg_db_numbackends:
    type: gauge
    description: Number of backends currently connected to this database
    labels: [datname]
  pg_db_xact_commit:
    type: counter
    description: Number of transactions that have been committed
    labels: [datname]
  pg_db_xact_rollback:
    type: counter
    description: Number of transactions that have been rolled back
    labels: [datname]
  pg_db_blks_read:
    type: counter
    description: Number of disk blocks read
    labels: [datname]
  pg_db_blks_hit:
    type: counter
    description: Number of times disk blocks were found already in the buffer cache
    labels: [datname]
  pg_cache_hit_ratio:
    type: gauge
    description: Cache hit ratio

queries:
  process_stats:
    databases: [pg]
    metrics:
      - pg_process
    sql: |
      SELECT
        state,
        COUNT(*) AS pg_process
      FROM pg_stat_activity
      WHERE state IS NOT NULL
      GROUP BY state

  database_stats:
    databases: [pg]
    metrics:
      - pg_db_numbackends
      - pg_db_xact_commit
      - pg_db_xact_rollback
      - pg_db_blks_read
      - pg_db_blks_hit
    sql: |
      SELECT
        datname AS datname,
        numbackends AS pg_db_numbackends,
        xact_commit AS pg_db_xact_commit,
        xact_rollback AS pg_db_xact_rollback,
        blks_read AS pg_db_blks_read,
        blks_hit AS pg_db_blks_hit
      FROM pg_stat_database

  cache_stats:
    databases: [pg]
    metrics:
      - pg_cache_hit_ratio
    sql: |
      SELECT
        (SUM(heap_blks_hit) / (SUM(heap_blks_hit) + SUM(heap_blks_read))) * 100 AS pg_cache_hit_ratio
      FROM pg_statio_user_tables

​
Key Queries Explained

​
Process Statistics

Monitors the state of PostgreSQL backend processes: 
SELECT
  state,
  COUNT(*) AS pg_process
FROM pg_stat_activity
WHERE state IS NOT NULL
GROUP BY state
This query tracks how many processes are in each state (active, idle, idle in transaction, etc.).

​
Database Statistics

Collects key database-level metrics from pg_stat_database: 
SELECT
  datname AS datname,
  numbackends AS pg_db_numbackends,
  xact_commit AS pg_db_xact_commit,
  xact_rollback AS pg_db_xact_rollback,
  blks_read AS pg_db_blks_read,
  blks_hit AS pg_db_blks_hit
FROM pg_stat_database
This monitors:
  • numbackends: Number of active connections per database
  • xact_commit/rollback: Transaction counts (counters)
  • blks_read/hit: Disk I/O vs cache hits

​
Cache Hit Ratio

Calculates the percentage of queries served from cache: 
SELECT
  (SUM(heap_blks_hit) / (SUM(heap_blks_hit) + SUM(heap_blks_read))) * 100 AS pg_cache_hit_ratio
FROM pg_statio_user_tables
A healthy cache hit ratio is typically above 99%. Lower values may indicate insufficient shared_buffers or a cache-unfriendly workload.

​
Metrics Types

  • Counters: Monotonically increasing values (transactions, blocks read). Use rate() in Prometheus to calculate per-second rates.
  • Gauges: Point-in-time values (connection counts, cache hit ratio)

​
Running the Example

  1. Save the configuration as config.yaml
  2. Set your database connection: 
    export PG_DATABASE_DSN="postgresql://user:pass@localhost:5432/postgres"
  3. Start Query Exporter: 
    query-exporter config.yaml
  4. Access metrics at http://localhost:9560/metrics

​
Full Example Source

The complete PostgreSQL monitoring example with all metrics and queries is available in the Query Exporter repository. This example includes additional queries for:
  • Table statistics (vacuum, analyze operations)
  • Index usage and efficiency
  • I/O statistics per table
  • Sequence statistics

Build docs developers (and LLMs) love

Get started for freeTalk to us