한양과학기술고등학교

SELECT pid, usename, state,
       now() - query_start AS 실행시간,
       wait_event_type, left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

SELECT pg_cancel_backend(pid);      -- 쿼리만 취소 (부드러움)
SELECT pg_terminate_backend(pid);   -- 커넥션 강제 종료 (마지막 수단)

-- 10분 넘는 쿼리 한 번에
SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '10 min';

SELECT blocked.pid AS 막힌pid, blocking.pid AS 막는pid,
       left(blocked.query, 60)  AS 막힌쿼리,
       left(blocking.query, 60) AS 막는쿼리
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

SELECT pg_size_pretty(pg_database_size('school'));

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS 총크기
FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

-- 캐시 적중률 (0.99 이상이 이상적)
SELECT sum(blks_hit)::float / nullif(sum(blks_hit) + sum(blks_read), 0)
FROM pg_stat_database;