pg_stat_statements로 느린 쿼리 분석하기

실제 프로젝트에서 일하다 보면, 네 앱에 동시에 수천 명의 유저가 붙어서 이것저것 요청하잖아. 데이터베이스에 쿼리 날리고, 데이터 추가하고, 읽고, 업데이트하고... 그러다 보면 어느 순간 서버가 "으으..." 하고 힘들어하는 소리가 들려. 이건 네 쿼리가 최적화가 안 됐다는 신호야. 종이에선 멀쩡해 보였던 쿼리가 실제론 성능을 완전 망칠 수도 있거든. 이럴 때 pg_stat_statements가 등장하는 거지.

pg_stat_statements로 할 수 있는 건:

1. 느린 쿼리 추적하기.
2. 각 쿼리가 몇 번 실행됐는지 알기.
3. 얼마나 시간이 걸렸는지 확인하기.
4. 쿼리 평균 실행 시간 보기.
5. 앱 전체를 갈아엎는 대참사를 막기!

pg_stat_statements 구조 살펴보기

익스텐션을 활성화하면 DB에 pg_stat_statements라는 특별한 뷰가 생겨. 여기에 실행된 쿼리 데이터가 다 저장돼. 일단 뭐가 들어있는지부터 보자:

SELECT * FROM pg_stat_statements LIMIT 1;

결과는 대충 이렇게 나올 수 있어 (간단히 보여줄게):

간단 설명:

• query — SQL 쿼리 자체야.
• calls — 이 쿼리가 몇 번 실행됐는지.
• total_time — 쿼리가 총 얼마나 시간 썼는지.
• rows — 쿼리가 반환한 행 개수.
• shared_blks_read — 읽은 블록 수 (캐시 안 쓰면 디스크에서 읽는 거야).

결과 분석하기

이제 pg_stat_statements가 켜졌으니, 느린 쿼리를 어떻게 찾는지 보자.

가장 느린 쿼리 찾기

어떤 쿼리가 제일 시간 많이 잡아먹는지 보려면 이렇게 하면 돼:

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

여기서:

• mean_time — 쿼리 한 번 실행하는 데 걸린 평균 시간 (total_time / calls).
• ORDER BY total_time DESC — 총 실행 시간 기준으로 내림차순 정렬.

자주 실행되는 쿼리

가끔 문제는 느린 쿼리가 아니라, 너무 자주 실행되는 쿼리일 수도 있어. 예를 들어:

SELECT query, calls
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 5;

쿼리 최적화하기

1. 인덱스 사용하기

특정 컬럼으로 쿼리가 느리게 실행된다면, 그 컬럼에 인덱스가 있는지 확인해봐. 예를 들어 students 테이블에 행이 엄청 많고, last_name 필드로 자주 조회한다면 인덱스를 만들어야 해:

CREATE INDEX idx_students_last_name ON students (last_name);

2. 쿼리 다시 쓰기

예를 들어 SELECT * FROM orders WHERE amount > 1000 같은 쿼리가 너무 오래 걸린다면, "다 가져오기" 말고 필요한 컬럼만 골라서 써봐:

SELECT order_id, amount FROM orders WHERE amount > 1000;

통계 초기화하기

최적화 후에 새로운 결과만 보고 싶으면 pg_stat_statements 데이터를 초기화해야 해. 이렇게 하면 돼:

SELECT pg_stat_statements_reset();

계산기에서 "초기화" 버튼 누르는 거랑 똑같아. 실행하면 통계가 새로 쌓이기 시작해.

문제 쿼리 찾기

네가 대학 DB 관리자라고 치자. 학생들이 "내 마이페이지 너무 느려요!"라고 단체로 불평해. 그럼 pg_stat_statements를 확인해보는 거지:

1단계: 제일 느린 쿼리 찾기

SELECT query, total_time, calls, mean_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

SELECT * FROM students WHERE status = 'active' 같은 쿼리가 30초나 걸리는 걸 발견했어. 헐. 빨리 고쳐야지.

2단계: 인덱스 확인 students 테이블을 분석해보니 status 컬럼에 인덱스가 없어. 바로 추가해:

CREATE INDEX idx_students_status ON students (status);

3단계: 결과 확인 최적화 후에 다시 pg_stat_statements를 확인하면 쿼리가 0.5초 만에 끝나. 대박!

pg_stat_statements 쓸 때 자주 하는 실수

쿼리 분석하다가 관리자들이 자주 하는 실수들:

1. 익스텐션 활성화 안 함. shared_preload_libraries에 pg_stat_statements를 안 넣으면 통계가 아예 안 쌓여.
2. 인덱스 무시. 쿼리가 느려 보여도, 인덱스만 잘 추가하면 바로 해결될 수도 있어.
3. 통계 초기화 안 함. pg_stat_statements_reset() 안 해주면 옛날 데이터 때문에 현재 상황 분석이 힘들어져.

pg_stat_statements를 잘 쓰면 DB의 GPS 내비게이션 같은 거야: 어디서 "정체"되는지 딱 알려주고, 우회로까지 힌트 줘. 제대로 세팅해서 쓰면 DB 성능이 확 달라질 거야.