stat-database

SELECT 
  datid,
  datname,
  numbackends,
  xact_commit,
  xact_rollback,
  blks_read,
  blks_hit,
  tup_returned,
  tup_fetched,
  tup_inserted,
  tup_updated,
  tup_deleted,
  conflicts,
  temp_files,
  temp_bytes,
  deadlocks,
  blk_read_time,
  blk_write_time,
  stats_reset,
  active_time
FROM pg_stat_database;

SELECT 
  datid,
  datname,
  numbackends,
  xact_commit,
  xact_rollback,
  blks_read,
  blks_hit,
  tup_returned,
  tup_fetched,
  tup_inserted,
  tup_updated,
  tup_deleted,
  conflicts,
  temp_files,
  temp_bytes,
  deadlocks,
  blk_read_time,
  blk_write_time,
  stats_reset
FROM pg_stat_database;

pg_stat_database_numbackends{datid="16384",datname="myapp"} 25
pg_stat_database_xact_commit{datid="16384",datname="myapp"} 1234567
pg_stat_database_xact_rollback{datid="16384",datname="myapp"} 123
pg_stat_database_blks_read{datid="16384",datname="myapp"} 987654
pg_stat_database_blks_hit{datid="16384",datname="myapp"} 98765432
pg_stat_database_tup_inserted{datid="16384",datname="myapp"} 5000000
pg_stat_database_deadlocks{datid="16384",datname="myapp"} 5

# Database cache hit ratio
sum(rate(pg_stat_database_blks_hit[5m])) /
(sum(rate(pg_stat_database_blks_hit[5m])) + 
 sum(rate(pg_stat_database_blks_read[5m])))

# Commits per second
rate(pg_stat_database_xact_commit[5m])

# Rollback ratio
rate(pg_stat_database_xact_rollback[5m]) /
(rate(pg_stat_database_xact_commit[5m]) + 
 rate(pg_stat_database_xact_rollback[5m]))

# Insert/Update/Delete rate
rate(pg_stat_database_tup_inserted[5m]) +
rate(pg_stat_database_tup_updated[5m]) +
rate(pg_stat_database_tup_deleted[5m])

# Temporary file creation rate
rate(pg_stat_database_temp_files[5m])

# Temporary data written per second
rate(pg_stat_database_temp_bytes[5m])