Postgres에서 실행 중인 쿼리 확인 및 종료 방법

Source: Dev.to

무언가가 느려지고 있습니다. 페이지 로드가 영원히 걸리거나, 마이그레이션이 멈춰 있거나, Supabase 대시보드가 계속 회전하고 있을지도 모릅니다. 데이터베이스 어딘가에 쿼리가 걸려 있다고 의심하지만, 무슨 일이 일어나고 있는지 확인할 방법이 없습니다 — Postgres는 자동으로 이런 정보를 보여주지는 않거든요.
하지만 실제로는 보여줍니다. 단지 물어볼 필요가 있을 뿐입니다.

Postgres는 pg_stat_activity 라는 시스템 뷰에 모든 활성 연결과 그 상태를 기록합니다. 이 뷰를 일반 테이블처럼 쿼리하면 됩니다:

SELECT pid, state, query, age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

이 쿼리는 idle 상태가 아닌 모든 프로세스를 반환합니다 — 프로세스 ID, 현재 상태, 실행 중인 SQL, 그리고 실행된 시간(지속 시간)까지 보여줍니다. 몇 분씩 실행 중인데 실제로는 밀리초 안에 끝나야 할 쿼리를 발견하면 문제가 바로 그곳에 있다는 뜻이죠.

컬럼에 대해 알아두면 좋은 점

• pid — 프로세스 ID. 해당 프로세스를 강제로 종료하고 싶을 때 필요합니다.
• state — 보통 active(현재 실행 중), idle in transaction(열린 트랜잭션 안에서 아무 일도 안 함), 혹은 idle(작업 대기) 중 하나입니다.
• query — 실제 SQL 텍스트.
• query_start — 현재 쿼리가 시작된 시점.

사용자와 데이터베이스 정보를 함께 보고 싶다면 다음과 같이 하면 됩니다:

SELECT pid, usename, datname, state, query, age(clock_timestamp(), query_start) AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

오랫동안 실행 중인 active 쿼리는 보통 단순히 느린 경우입니다. 반면 idle in transaction 상태는 다른 종류의 문제를 의미합니다 — 누군가(또는 코드)가 트랜잭션을 열어두고 COMMIT이나 ROLLBACK을 하지 않은 경우죠. 연결 자체는 아무 작업도 하지 않지만, 여전히 락을 잡고 있어 다른 쿼리의 실행을 방해할 수 있습니다. 이런 상황이 누적되면 연쇄적인 성능 저하가 발생합니다. 오래된 idle in transaction 연결을 발견하면, 애플리케이션 코드에 COMMIT을 빼먹었거나 예외 처리에서 정리를 하지 않았거나, 커넥션 풀에서 세션을 제대로 회수하지 못한 버그가 원인일 가능성이 높습니다.

문제 프로세스(pids)를 찾았다면 두 가지 선택지가 있습니다

1. 부드러운 방법 — 쿼리 취소 요청

SELECT pg_cancel_backend(12345);

실행 중인 쿼리에 취소 신호를 보냅니다. 프로세스가 active 상태라면 쿼리가 중단되고 연결은 다시 idle 상태가 됩니다. Ctrl+C를 눌러 작업을 중단하는 것과 비슷하지만, 세션 자체는 살아 있습니다.

2. 강제 방법 — 연결 완전히 종료

SELECT pg_terminate_backend(12345);

백엔드 프로세스를 완전히 종료합니다. 연결이 끊기고, 열려 있던 트랜잭션은 롤백되며 클라이언트는 연결이 끊어진 것을 인지합니다. pg_cancel_backend가 효과가 없을 때 사용합니다 — 특히 idle in transaction 세션은 취소할 쿼리가 없기 때문에 이 방법이 필요합니다.

12345 부분을 실제 pg_stat_activity에서 확인한 pid 로 교체하면 됩니다.

여러 개의 걸린 연결을 한 번에 정리하고 싶다면

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND query_start < now() - interval '5 minutes';

위 쿼리는 5분 이상 idle in transaction 상태인 모든 연결을 종료합니다. 필요에 따라 interval 값을 조정하세요.

Supabase에서 사용하기

Supabase 대시보드의 SQL Editor에서도 위 모든 명령을 실행할 수 있습니다. pg_stat_activity 뷰와 pg_cancel_backend, pg_terminate_backend 함수 모두 동일하게 동작합니다. 별도의 권한 부여가 필요 없으며, 기본 postgres 역할이 이미 접근 권한을 가지고 있습니다.

주의: Supabase는 Realtime, Auth, PostgREST 등을 위한 백그라운드 프로세스를 실행합니다. 이들 역시 pg_stat_activity에 나타나며, 보통 supabase_admin이나 authenticator 같은 사용자 이름을 가집니다. 이러한 시스템 프로세스를 죽이지 마세요 — 애플리케이션 자체 역할에서 생성된 연결만 종료하도록 합니다.