Performance Monitoring (PG Extension)

Enabling

Enable native performance monitoring with pg_stat_statement.

This extension is already present in many postgres installations and docker images (even Alpine).

Enable extension

For docker-compose add this to /docker-entrypoint-initdb.d.

-- Enable extension (once per database)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Add configuration (Non-docker)

Add to postgresql.conf.

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

Then restart postgresql to activate it.

Add configuration (Docker)

For docker-compose images you can add to the postgres service:

docker-compose.yaml

services:
  db:
    image: postgres:xx-alpine
    ...
    command: >
      postgres
      -c shared_preload_libraries=pg_stat_statements
      -c pg_stat_statements.track=all
      -c pg_stat_statements.max=10000
      -c track_activity_query_size=2048

Check extension

-- Check if it's loaded
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';

Monitoring Queries

Slowest & most frequent

SELECT
  query,
  calls,
  total_time,
  mean_time,
  rows,
  (total_time / calls) AS avg_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

Monitor real-time query activity

SELECT
  pid,
  usename,
  datname,
  state,
  wait_event_type,
  wait_event,
  query_start,
  NOW() - query_start AS runtime,
  query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY runtime DESC;

Check buffer & cache usage (I/O health)

SELECT 
    relname AS table_name,
    heap_blks_read,
    heap_blks_hit,
    ROUND(100 * heap_blks_hit / NULLIF(heap_blks_hit + heap_blks_read, 0), 2) AS cache_hit_ratio
FROM pg_statio_user_tables
ORDER BY cache_hit_ratio ASC;

Monitor locks & blocking

-- See current locks
SELECT 
    pid, 
    relation::regclass, 
    mode, 
    granted, 
    query 
FROM pg_locks 
JOIN pg_stat_activity USING (pid)
WHERE relation IS NOT NULL;

-- See blocking chains
SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.query AS blocked_query,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
 AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
 AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
 AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
 AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
 AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
 AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Temp file usage (memory under pressure)

SELECT 
    relname,
    last_autovacuum,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_dead_tup,
    vacuum_count,
    autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Database load stats

SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched
FROM pg_stat_database
ORDER BY xact_commit DESC;