PostgreSQL에서 자주 쓰는 모니터링 명령어 — pg_stat_activity랑 pg_stat_user_tables

데이터베이스 모니터링을 pg_stat_activity랑 pg_stat_user_tables 없이 한다는 건 체온만 재고 건강을 체크하는 거랑 똑같아. 전체적인 그림만 보면 어디가 문제인지 절대 모름. 이 두 가지 PostgreSQL 핵심 명령어로 단순히 보는 것뿐만 아니라, 실제로 데이터베이스에서 무슨 일이 일어나는지 제대로 분석할 수 있어.

pg_stat_activity가 뭐야?

pg_stat_activity는 PostgreSQL의 시스템 뷰로, 데이터베이스에 연결된 모든 커넥션 정보를 보여줘. 누가 데이터베이스에 붙어있는지, 지금 어떤 쿼리가 실행 중인지, 그리고 어떤 커넥션이 그냥 놀고 있는지(idle 상태)까지 다 알 수 있음. 서버에서 현재 무슨 일이 벌어지고 있는지 분석할 때 필수 도구야.

이제 pg_stat_activity에서 볼 수 있는 주요 필드들을 살펴보자. datname은 클라이언트가 붙은 데이터베이스 이름, usename은 연결한 유저 이름이야. application_name은 커넥션을 쓰는 앱 이름, client_addr는 서버에 붙은 클라이언트의 IP 주소. backend_start는 클라이언트가 서버에 연결된 시간, state는 커넥션의 현재 상태(active, idle, idle in transaction 등), 그리고 query는 지금 실행 중이거나 마지막으로 실행된 쿼리야.

예시 1: 모든 활성 커넥션 보기

활성화된 커넥션을 보려면 아래 쿼리를 실행하면 돼:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

query 필드에 주목! 지금 실행 중인 쿼리가 뭔지 보여줘. 만약 쿼리가 너무 오래 걸린다면, 뭔가 문제가 있을 수도 있어.

예시 2: 트랜잭션 상태 분석

가끔 커넥션이 idle in transaction 상태에서 멈춰있을 때가 있어. 이건 트랜잭션이 시작됐는데 끝나지 않은 상태라서, 락이 걸릴 수도 있음.

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

어떻게 해결하지? 만약 "멈춰있는" 트랜잭션을 찾았다면, 아래 명령어로 종료시킬 수 있어:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

개발자들이 이거에 너무 맛들릴 수도 있는데, 먼저 팀원한테 이 프로세스 "죽여도" 되는지 꼭 물어봐. 아, 미안, "죽인다" 말고 — 커넥션을 종료한다는 뜻이야 ㅎㅎ.

테이블 사용 모니터링: pg_stat_user_tables 뷰

pg_stat_activity가 커넥션을 모니터링한다면, pg_stat_user_tables는 테이블 성능을 보여줘. 이걸로 어떤 테이블이 얼마나 자주 읽히고 쓰이는지, 자주 쓰이는 테이블이 뭔지, 성능 이슈가 어디 있는지 알 수 있음.

테이블 분석에 도움 되는 pg_stat_user_tables의 주요 필드는 이렇다. relname은 테이블 이름, seq_scan은 테이블 전체를 순차적으로 읽은 횟수, idx_scan은 인덱스를 이용한 스캔 횟수. n_tup_ins는 테이블에 insert된 row 수, n_tup_upd는 update된 row 수, n_tup_del은 delete된 row 수야.

예시 1: 인덱스 사용과 순차 스캔 비교

만약 인덱스 사용(idx_scan)이 거의 없다면, 이 테이블 쿼리를 최적화할 여지가 많다는 뜻이야.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

결과 예시:

만약 orders 테이블이 순차 스캔(seq_scan)이 엄청 많고, 인덱스 스캔은 100번밖에 없다면, 인덱스를 추가하는 걸 고민해봐. 예를 들어 orders가 3500번 순차 스캔, 100번 인덱스 스캔이고, employees는 50번 순차 스캔, 1000번 인덱스 스캔이면 — 이건 명백히 최적화 신호야.

예시 2: 테이블별 작업 수 분석

테이블에 얼마나 "생동감" 있게 데이터가 들어오고 나가는지 보려면, insert/ update/ delete row 수를 확인해봐:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

뭘 알 수 있을까? insert(n_tup_ins)랑 delete(n_tup_del)가 많은 테이블은 데이터베이스에서 "핫스팟"일 수 있어. 이런 테이블은 성능에 더 신경 써야 해.

실전 성능 분석: pg_stat_activity랑 pg_stat_user_tables 데이터 조합하기

데이터베이스 성능을 분석할 때, 두 소스의 데이터를 같이 보면 좋아. 먼저 pg_stat_activity로 오래 걸리는 쿼리를 찾고, 그 쿼리가 어떤 테이블을 쓰는지 pg_stat_user_tables로 확인해봐. 만약 seq_scan이 높은 테이블에서 쿼리가 오래 걸린다면, 쿼리 최적화나 인덱스 추가를 고민해보자.

쿼리 예시:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';