pg_stat_statements로 느린 쿼리 추적하기

pg_stat_statements는 PostgreSQL에 내장된 확장 기능이야. 이걸로 실제로 어떤 쿼리들이 데이터베이스에서 실행되고 있는지, 그리고 그 쿼리들이 어떻게 동작하는지 볼 수 있어. 말하자면, 조용하지만 관찰력 좋은 도우미 같은 거지. 어떤 SQL 쿼리가 실행됐는지, 얼마나 걸렸는지, 얼마나 자주 실행됐는지, 시스템에 얼마나 부담을 줬는지 다 기록해줘.

이게 왜 필요하냐고? 첫째, 문제 있는 쿼리를 찾으려고. 가끔 DB가 느려지는 게 딱 한 놈 때문이 아니라, 비슷한 무거운 쿼리들이 너무 자주 실행돼서 그런 경우가 많거든. 둘째, 통계를 보면 어떤 쿼리가 CPU, 메모리, 디스크 같은 리소스를 잡아먹는지 알 수 있어. 그리고 인덱스가 제대로 동작하는지도 파악할 수 있지. 어쩌면 인덱스가 전혀 안 쓰이고 있거나, 반대로 인덱스가 부족한 곳이 있을 수도 있어.

pg_stat_statements를 쓰면 그냥 추측만 하는 게 아니라, 실제 수치를 보고 결론을 내리고 최적화할 수 있어.

느린 쿼리는 어떻게 찾지?

이제 진짜 재밌는 부분이야! pg_stat_statements 테이블을 이용해서 오래 걸리거나 서버에 부담을 주는 쿼리를 찾을 수 있어.

핵심 아이디어:

pg_stat_statements 테이블의 각 행은 하나의 쿼리에 대한 통계야. 쿼리들은 텍스트(즉, query 필드)로 그룹화되고, 각각에 대해 다음과 같은 메트릭이 계산돼:

• total_time — 쿼리 실행에 걸린 전체 시간(밀리초 단위).
• calls — 쿼리가 실행된 횟수.
• mean_time — 쿼리의 평균 실행 시간(total_time / calls).
• rows — 쿼리가 반환한 행의 수.

간단한 분석 예시

평균 실행 시간이 가장 긴 느린 쿼리들을 찾아보자:

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

이 쿼리는 평균 실행 시간이 가장 긴 TOP 5 쿼리를 보여줄 거야. mean_time 필드를 잘 봐봐. 만약 값이 500~1000 밀리초를 넘는다면, 그 쿼리는 최적화가 필요하다는 신호야.

느린 쿼리 분석 예시

예시를 하나 보자:

아까 쿼리의 실행 결과는 이래:

뭘 볼 수 있을까?

orders 테이블 쿼리: 실행 횟수는 적어(딱 10번), 근데 매번 1만 행이나 끌고 와. 아마 테이블이 엄청 크고, 인덱스를 안 쓰는 것 같아.

products 테이블 쿼리: 수천 번(혹은 수백만 번) 호출돼. 아마 애플리케이션에서 루프 돌면서 계속 부르는 듯. 한 번에 100행만 반환하지만, 너무 자주 실행돼서 이것도 문제일 수 있어.

customers 테이블 쿼리: 실행은 빠른 편(543ms)이지만, 호출 횟수가 너무 많아.

느린 쿼리 최적화

이제 문제 쿼리를 찾았으니, EXPLAIN ANALYZE로 실행 계획을 확인해보자. 예를 들어 orders 테이블 쿼리라면:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'new';

뭘 볼 수 있을까?

Seq Scan: 만약 쿼리가 순차 스캔을 쓴다면, 인덱스를 추가해야 해:

CREATE INDEX idx_orders_status ON orders (status);

필터링 문제: 쿼리가 너무 많은 행을 끌고 온다면, 쿼리 자체를 다시 생각해봐. 조건을 더 추가하거나 결과를 제한하는 게 좋을 수도 있어:

SELECT * FROM orders WHERE status = 'new' LIMIT 100;

실행 시간 통계 출력

가끔 문제 쿼리가 바로 안 보일 수도 있어. 예를 들어, 자주 함수나 서브쿼리를 호출하는 쿼리들. 이럴 땐 total_time 컬럼을 보면 좋아:

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

이 쿼리는 전체 실행 시간이 가장 큰 "비싼" 쿼리들을 보여줄 거야.

인덱싱 최적화

느린 쿼리의 원인은 보통 필요한 인덱스가 없어서야. pg_stat_statements를 써서 어떤 쿼리가 인덱스를 안 쓰는지 파악해봐. 만약 status 같은 필드로 자주 필터링하는 쿼리가 많은데 느리다면, 해당 인덱스를 추가해:

CREATE INDEX idx_orders_status ON orders (status);

그 다음 EXPLAIN ANALYZE로 쿼리 성능을 다시 확인해봐.

pg_stat_statements를 활용하면 쿼리 성능을 효과적으로 모니터링하고, "병목"을 찾아서 네 데이터베이스 성능을 높일 수 있어. 쿼리 분석을 일찍 시작할수록 전체 시스템 최적화가 훨씬 쉬워진다는 거 꼭 기억해!