YSQL System Tables

SELECT d.datname, cv.current_version, cv.last_breaking_version
FROM pg_yb_catalog_version cv
JOIN pg_database d ON d.oid = cv.db_oid;

SELECT tg.grpname, r.rolname as owner, ts.spcname as tablespace
FROM pg_yb_tablegroup tg
LEFT JOIN pg_roles r ON tg.grpowner = r.oid
LEFT JOIN pg_tablespace ts ON tg.grptablespace = ts.oid;

SELECT prfname, 
       prfmaxfailedloginattempts as max_failed_attempts,
       prfpasswordlocktime as lock_time_seconds
FROM pg_yb_profile
ORDER BY prfname;

SELECT r.rolname,
       p.prfname,
       CASE rp.rolprfstatus 
         WHEN 'o' THEN 'OPEN'
         WHEN 'l' THEN 'LOCKED'
       END as status,
       rp.rolprffailedloginattempts as failed_attempts,
       rp.rolprflockeduntil as locked_until
FROM pg_yb_role_profile rp
JOIN pg_roles r ON rp.rolprfrole = r.oid
JOIN pg_yb_profile p ON rp.rolprfprofile = p.oid;

SELECT major, minor, name,
       CASE WHEN time_applied IS NOT NULL 
         THEN to_timestamp(time_applied / 1000.0)
         ELSE NULL
       END as applied_at
FROM pg_yb_migration
ORDER BY major DESC, minor DESC;

SELECT d.datname, lcv.current_version
FROM pg_yb_logical_client_version lcv
JOIN pg_database d ON d.oid = lcv.db_oid;

SELECT d.datname,
       im.current_version,
       to_timestamp(im.message_time) as message_timestamp,
       length(im.messages) as message_bytes
FROM pg_yb_invalidation_messages im
JOIN pg_database d ON d.oid = im.db_oid
ORDER BY im.message_time DESC
LIMIT 10;

SELECT databasename, backend_pid, 
       substring(query_text, 1, 50) as query_snippet,
       termination_reason,
       query_end_time - query_start_time as duration
FROM yb_terminated_queries
ORDER BY query_end_time DESC
LIMIT 20;

SELECT sample_time, wait_event_class, wait_event, 
       query_id, pid, wait_event_type
FROM yb_active_session_history
WHERE sample_time > NOW() - INTERVAL '1 hour'
ORDER BY sample_time DESC;

SELECT *
FROM yb_local_tablets
ORDER BY table_name, tablet_id;

SELECT relname, COUNT(*) as tablet_count,
       COUNT(DISTINCT leader) as leader_count
FROM yb_tablet_metadata
WHERE db_name = current_database()
GROUP BY relname
ORDER BY tablet_count DESC;

SELECT *
FROM yb_wait_event_desc
ORDER BY component, class, event;

SELECT *
FROM yb_query_diagnostics_status;

SELECT *
FROM yb_servers_metrics
ORDER BY node_id;

SELECT d.datname, r.rolname, 
       s.queryid, s.planid, 
       s.calls, 
       round(s.avg_exec_time::numeric, 2) as avg_time_ms,
       s.hints
FROM yb_pg_stat_plans s
JOIN pg_database d ON s.dbid = d.oid
JOIN pg_roles r ON s.userid = r.oid
WHERE s.calls > 10
ORDER BY s.avg_exec_time DESC
LIMIT 20;

SELECT queryid, planid,
       round(avg_exec_time::numeric, 2) as avg_time,
       round(min_avg_exec_time::numeric, 2) as best_time,
       plan_require_evaluation,
       plan_min_exec_time
FROM yb_pg_stat_plans_insights
WHERE plan_require_evaluation = 'Yes'
ORDER BY queryid, avg_exec_time DESC;

SELECT d.datname,
       cv.current_version,
       cv.last_breaking_version,
       cv.current_version - cv.last_breaking_version as versions_since_break
FROM pg_yb_catalog_version cv
JOIN pg_database d ON d.oid = cv.db_oid
ORDER BY d.datname;

SELECT r.rolname,
       p.prfname,
       rp.rolprffailedloginattempts,
       rp.rolprflockeduntil,
       CASE WHEN rp.rolprflockeduntil > NOW() 
         THEN 'LOCKED'
         ELSE 'UNLOCKED'
       END as current_status
FROM pg_yb_role_profile rp
JOIN pg_roles r ON rp.rolprfrole = r.oid
JOIN pg_yb_profile p ON rp.rolprfprofile = p.oid
WHERE rp.rolprfstatus = 'l'
   OR rp.rolprffailedloginattempts > 0;

SELECT db_name, relname,
       COUNT(*) as total_tablets,
       COUNT(DISTINCT leader) as unique_leaders,
       COUNT(DISTINCT replicas) as replica_locations
FROM yb_tablet_metadata
GROUP BY db_name, relname
HAVING COUNT(*) > 1
ORDER BY total_tablets DESC;

SELECT d.datname,
       im.current_version,
       to_timestamp(im.message_time) as change_time,
       NOW() - to_timestamp(im.message_time) as age
FROM pg_yb_invalidation_messages im
JOIN pg_database d ON d.oid = im.db_oid
WHERE im.message_time > extract(epoch from NOW() - INTERVAL '1 hour')
ORDER BY im.message_time DESC;

SELECT yb_reset_analyze_statistics('my_table'::regclass);

SELECT yb_is_database_colocated();

SELECT yb_query_diagnostics(
  query_id => 12345,
  diagnostics_interval_sec => 300,
  explain_sample_rate => 10,
  explain_analyze => true
);

SELECT yb_cancel_query_diagnostics(12345);