Postgres metric select statements

postgres_metrics.sql

-- bgwriter
SELECT checkpoints_timed FROM pg_stat_bgwriter;
SELECT checkpoints_req FROM pg_stat_bgwriter;
SELECT write_time FROM pg_stat_bgwriter;
SELECT sync_time FROM pg_stat_bgwriter;
SELECT buffers_checkpoint FROM pg_stat_bgwriter;
SELECT buffers_clean FROM pg_stat_bgwriter;
SELECT maxwritten_clean FROM pg_stat_bgwriter;
SELECT buffers_backend FROM pg_stat_bgwriter;
SELECT buffers_backend_fsync FROM pg_stat_bgwriter;
SELECT buffers_alloc FROM pg_stat_bgwriter;

-- locks
SELECT count(*)
  FROM pg_locks
  JOIN pg_class ON (pg_locks.relation = pg_class.oid)
  WHERE pg_locks.mode IS NOT NULL
  AND pg_class.relname NOT LIKE 'pg_%%'
  GROUP BY pg_class.relname, mode;

-- database
SELECT sum(numbackends) FROM pg_stat_database;
SELECT sum(xact_commit) FROM pg_stat_database;
SELECT sum(xact_rollback) FROM pg_stat_database;
SELECT sum(blks_read) FROM pg_stat_database;
SELECT sum(blks_hit) FROM pg_stat_database;
SELECT sum(tup_returned) FROM pg_stat_database;
SELECT sum(tup_fetched) FROM pg_stat_database;
SELECT sum(tup_inserted) FROM pg_stat_database;
SELECT sum(tup_updated) FROM pg_stat_database;
SELECT sum(tup_deleted) FROM pg_stat_database;
SELECT sum(deadlocks) FROM pg_stat_database;
SELECT sum(temp_bytes) FROM pg_stat_database;
SELECT sum(temp_files) FROM pg_stat_database;

-- relations
SELECT sum(seq_scan) FROM pg_stat_user_tables;
SELECT sum(seq_tup_read) FROM pg_stat_user_tables;
SELECT sum(idx_scan) FROM pg_stat_user_tables;
SELECT sum(idx_tup_fetch) FROM pg_stat_user_tables;
SELECT sum(n_tup_ins) FROM pg_stat_user_tables;
SELECT sum(n_tup_upd) FROM pg_stat_user_tables;
SELECT sum(n_tup_del) FROM pg_stat_user_tables;
SELECT sum(n_tup_hot_upd) FROM pg_stat_user_tables;
SELECT sum(n_live_tup) FROM pg_stat_user_tables;
SELECT sum(n_dead_tup) FROM pg_stat_user_tables;

-- indexes
SELECT sum(idx_scan) FROM pg_stat_user_indexes;
SELECT sum(idx_tup_read) FROM pg_stat_user_indexes;
SELECT sum(idx_tup_fetch) FROM pg_stat_user_indexes;

-- size
SELECT
  pg_size_pretty(sum(pg_table_size(pg_class.oid)))
  FROM pg_class
  LEFT JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND
  nspname !~ '^pg_toast' AND
  relkind IN ('r');

SELECT
  pg_size_pretty(sum(pg_indexes_size(pg_class.oid)))
  FROM pg_class
  LEFT JOIN pg_namespace N ON (N.oid = pg_class.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND
  nspname !~ '^pg_toast' AND
  relkind IN ('r');

SELECT
  pg_size_pretty(sum(pg_total_relation_size(pg_class.oid)))
  FROM pg_class
  LEFT JOIN pg_namespace N ON (N.oid = pg_class.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND
  nspname !~ '^pg_toast' AND
  relkind IN ('r');

-- cache hit rates
SELECT (sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS index_hit_rate
FROM pg_statio_user_indexes;

SELECT sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS table_hit_rate
FROM pg_statio_user_tables;