Your supporting SQL desk.

Rank normalized statements by their aggregate execution cost.

SELECT
  queryid,
  calls,
  round(total_exec_time::numeric, 1) AS total_exec_time_ms,
  round(mean_exec_time::numeric, 2) AS mean_exec_time_ms,
  rows,
  left(query, 180) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Inspect backend state, transaction age, query age, and current wait.

SELECT
  pid,
  usename AS user_name,
  application_name,
  state,
  wait_event_type,
  wait_event,
  now() - xact_start AS transaction_age,
  now() - query_start AS query_age,
  left(query, 160) AS query_preview
FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
ORDER BY transaction_age DESC NULLS LAST;

Locate blocked sessions and identify the backends preventing progress.

SELECT
  pid AS blocked_pid,
  pg_blocking_pids(pid) AS blocking_pids,
  now() - query_start AS blocked_for,
  wait_event,
  left(query, 160) AS blocked_query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0
ORDER BY blocked_for DESC;

Prioritise maintenance using dead-row estimates, autovacuum history, and XID age.

SELECT
  stats.schemaname,
  stats.relname,
  stats.n_live_tup,
  stats.n_dead_tup,
  stats.last_autovacuum,
  age(classes.relfrozenxid) AS transaction_id_age
FROM pg_stat_user_tables AS stats
JOIN pg_class AS classes ON classes.oid = stats.relid
ORDER BY transaction_id_age DESC, stats.n_dead_tup DESC
LIMIT 30;

Compare connected standbys and quantify the WAL distance to replay.

SELECT
  application_name,
  client_addr,
  state,
  sync_state,
  pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn) AS send_gap_bytes,
  pg_wal_lsn_diff(sent_lsn, write_lsn) AS write_gap_bytes,
  pg_wal_lsn_diff(write_lsn, replay_lsn) AS replay_gap_bytes
FROM pg_stat_replication
ORDER BY application_name;

Find inactive or lagging slots that retain WAL on the server.

SELECT
  slot_name,
  slot_type,
  active,
  active_pid,
  wal_status,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS retained_wal
FROM pg_replication_slots
ORDER BY pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) DESC NULLS LAST;

Find large indexes with few scans for careful workload review.

SELECT
  schemaname,
  relname AS table_name,
  indexrelname AS index_name,
  idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 40;

See the active value, where it came from, and whether changing it requires restart.

SELECT
  name,
  setting,
  unit,
  source,
  sourcefile,
  pending_restart
FROM pg_settings
WHERE name IN (
  'max_connections',
  'shared_buffers',
  'work_mem',
  'maintenance_work_mem',
  'autovacuum_max_workers',
  'max_wal_size'
)
ORDER BY name;