Diagnóstico do seu Postgres em dois minutos, sem entregar a senha.

-- DataRunner · Postgres health check (read-only, Postgres 10+)
SELECT json_build_object(
  'v', 1,
  'version', current_setting('server_version'),
  'database', current_database(),
  'size_bytes', pg_database_size(current_database()),
  'max_connections', current_setting('max_connections')::int,
  'stats_reset', (SELECT stats_reset FROM pg_stat_database WHERE datname = current_database()),
  'cache_hit_pct', (
    SELECT round(100.0 * sum(blks_hit) / nullif(sum(blks_hit) + sum(blks_read), 0), 2)
    FROM pg_stat_database WHERE datname = current_database()),
  'connections', (
    SELECT coalesce(json_object_agg(state, n), '{}')
    FROM (SELECT coalesce(state, 'other') AS state, count(*) AS n
          FROM pg_stat_activity WHERE backend_type = 'client backend' GROUP BY 1) c),
  'dead_tuples', (
    SELECT coalesce(json_agg(t), '[]') FROM (
      SELECT schemaname || '.' || relname AS name, n_live_tup AS live, n_dead_tup AS dead,
             greatest(last_autovacuum, last_vacuum) AS last_vacuum
      FROM pg_stat_user_tables WHERE n_dead_tup > 1000
      ORDER BY n_dead_tup DESC LIMIT 10) t),
  'unused_indexes', (
    SELECT coalesce(json_agg(t), '[]') FROM (
      SELECT s.schemaname || '.' || s.indexrelname AS name, s.relname AS "table",
             pg_relation_size(s.indexrelid) AS bytes
      FROM pg_stat_user_indexes s JOIN pg_index i ON i.indexrelid = s.indexrelid
      WHERE s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
      ORDER BY pg_relation_size(s.indexrelid) DESC LIMIT 10) t),
  'no_primary_key', (
    SELECT coalesce(json_agg(t), '[]') FROM (
      SELECT n.nspname || '.' || c.relname AS name, c.reltuples::bigint AS rows
      FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE c.relkind = 'r' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
        AND n.nspname NOT LIKE 'pg_toast%'
        AND NOT EXISTS (SELECT 1 FROM pg_constraint k WHERE k.conrelid = c.oid AND k.contype = 'p')
      ORDER BY c.reltuples DESC LIMIT 10) t),
  'long_transactions', (
    SELECT coalesce(json_agg(t), '[]') FROM (
      SELECT pid, state, application_name AS app,
             extract(epoch FROM now() - xact_start)::int AS seconds
      FROM pg_stat_activity
      WHERE xact_start IS NOT NULL AND now() - xact_start > interval '5 minutes'
        AND backend_type = 'client backend' AND pid <> pg_backend_pid()
      ORDER BY xact_start LIMIT 10) t),
  'sequences', (
    SELECT coalesce(json_agg(t), '[]') FROM (
      SELECT schemaname || '.' || sequencename AS name, last_value, max_value,
             round(100.0 * last_value / max_value, 2) AS pct
      FROM pg_sequences
      WHERE last_value IS NOT NULL AND increment_by > 0 AND last_value::numeric / max_value > 0.5
      ORDER BY 4 DESC LIMIT 10) t),
  'largest_tables', (
    SELECT coalesce(json_agg(t), '[]') FROM (
      SELECT schemaname || '.' || relname AS name, pg_total_relation_size(relid) AS bytes
      FROM pg_statio_user_tables ORDER BY 2 DESC LIMIT 5) t)
) AS datarunner_health;