A cautious PostgreSQL health check—without connecting your database

WITH
database_stats AS (
  SELECT
    pg_database_size(current_database())::double precision AS database_bytes,
    stats_reset
  FROM pg_catalog.pg_stat_database
  WHERE datname = current_database()
),
relation_stats AS (
  SELECT
    schemaname::text AS schema_name,
    relname::text AS relation_name,
    pg_table_size(relid)::double precision AS table_bytes,
    pg_indexes_size(relid)::double precision AS index_bytes,
    pg_total_relation_size(relid)::double precision AS total_bytes,
    COALESCE(n_live_tup, 0)::double precision AS estimated_live_rows,
    COALESCE(n_dead_tup, 0)::double precision AS estimated_dead_rows,
    COALESCE(seq_scan, 0)::double precision AS seq_scan,
    COALESCE(seq_tup_read, 0)::double precision AS seq_tup_read,
    COALESCE(idx_scan, 0)::double precision AS idx_scan,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
  FROM pg_catalog.pg_stat_user_tables
  ORDER BY pg_total_relation_size(relid) DESC, schemaname, relname
  LIMIT 100
),
index_stats AS (
  SELECT
    schemaname::text AS schema_name,
    relname::text AS relation_name,
    indexrelname::text AS index_name,
    pg_relation_size(indexrelid)::double precision AS index_bytes,
    COALESCE(idx_scan, 0)::double precision AS idx_scan,
    COALESCE(idx_tup_read, 0)::double precision AS idx_tup_read,
    COALESCE(idx_tup_fetch, 0)::double precision AS idx_tup_fetch
  FROM pg_catalog.pg_stat_user_indexes
  ORDER BY pg_relation_size(indexrelid) DESC, schemaname, indexrelname
  LIMIT 150
),
connection_stats AS (
  SELECT
    current_setting('max_connections')::integer AS max_connections,
    count(*)::integer AS total,
    count(*) FILTER (WHERE state = 'active')::integer AS active,
    count(*) FILTER (WHERE state = 'idle')::integer AS idle,
    count(*) FILTER (WHERE state = 'idle in transaction')::integer AS idle_in_transaction,
    count(*) FILTER (WHERE wait_event_type IS NOT NULL)::integer AS waiting,
    count(*) FILTER (WHERE state = 'active' AND query_start < clock_timestamp() - interval '5 minutes')::integer AS long_running,
    count(*) FILTER (WHERE xact_start < clock_timestamp() - interval '5 minutes')::integer AS long_transaction
  FROM pg_catalog.pg_stat_activity
  WHERE datname = current_database()
    AND pid <> pg_backend_pid()
)
SELECT jsonb_build_object(
  'schema_version', 'databasemeter-health-v1',
  'generated_at', clock_timestamp(),
  'postgres', jsonb_build_object(
    'version', current_setting('server_version'),
    'version_num', current_setting('server_version_num')::integer,
    'database_name', current_database()
  ),
  'capabilities', jsonb_build_object(
    'pg_stat_statements', false,
    'stats_reset_known', (SELECT stats_reset IS NOT NULL FROM database_stats)
  ),
  'database', jsonb_build_object(
    'database_bytes', (SELECT database_bytes FROM database_stats),
    'stats_reset', (SELECT stats_reset FROM database_stats)
  ),
  'relations', COALESCE((SELECT jsonb_agg(to_jsonb(relation_stats) ORDER BY total_bytes DESC) FROM relation_stats), '[]'::jsonb),
  'indexes', COALESCE((SELECT jsonb_agg(to_jsonb(index_stats) ORDER BY index_bytes DESC) FROM index_stats), '[]'::jsonb),
  'connections', (SELECT to_jsonb(connection_stats) FROM connection_stats),
  'query_stats', '[]'::jsonb
) AS databasemeter_health_check;