데이터베이스 모니터링 지표 정리 (3) PostgreSQL

핵심 요약

최근 데이터베이스 일일 점검을 자동화하는 프로젝트를 맡게 되면서, DBA가 매일 들여다보는 모니터링 지표들을 처음부터 정리하고 있습니다. 첫 글에서 MySQL, 두 번째 글에서 Oracle을 다뤘는데, 이번 PostgreSQL이 시리즈의 마지막입니다. PostgreSQL은 같은 관계형 데이터베이스이면서도 데이터를 관리하는 방식이 앞의 둘과 또 달라서, 지표 이름에도 다른 DB에는 없는 단어들이 보였습니다. Dead Tuple, VACUUM, Age, autovacuum 같은 말이 그렇습니다. 그래서 이번에도 하나씩 정리해 두기로 했습니다.

이 글에서는 PostgreSQL(및 PostgreSQL on RDS)에서 자주 보는 지표들을 ① 무엇을 의미하는지 ② 평소 어느 정도 값이 정상인지(실제 관측 범위·평균) ③ 값이 튀면 무엇을 의심해야 하는지 ④ 함께 보면 좋은 지표 ⑤ 직접 확인하는 쿼리로 묶어 정리했습니다. PostgreSQL만의 개념(프로세스 기반 backend, MVCC, Dead Tuple, VACUUM·autovacuum, 트랜잭션 ID wraparound와 Age, shared_buffers, WAL)은 지표를 보기 전에 먼저 한 번 풀어 두었습니다. 이 개념들이 그대로 뒤의 지표로 이어지기 때문입니다. 각 지표마다 그것을 실제로 움직이게 만드는 쿼리 패턴을 "나쁜 예 → 고친 예" SQL로 가능한 한 함께 적었고, 실제 장애로 이어지는 시나리오도 붙였습니다.

본문의 "관측값"은 실제 운영 인스턴스 한 대에서 1분 간격으로 한 달간 수집한 값입니다. 인스턴스마다 다르므로 절대적인 기준은 아니고, "보통 이 정도구나"를 감 잡는 용도입니다.

---

PostgreSQL은 어떤 데이터베이스인가

지표를 보기 전에, PostgreSQL이 MySQL·Oracle과 어떻게 다른지부터 짚고 가겠습니다.

• PostgreSQL: 오픈소스이면서 표준 준수와 확장성이 강점입니다. 복잡한 쿼리, 다양한 데이터 타입(JSON·배열·지리정보 등), 풍부한 기능, 사용자 정의 확장(extension)이 필요한 곳에 잘 맞습니다. "기능이 많고 정석에 충실한 오픈소스 DB" 라는 평을 오래 받아 왔습니다.
• MySQL: 오픈소스이고 가볍고 빠릅니다. 읽기가 많은 웹 서비스에서 오래 사랑받아 왔고, 스토리지 엔진(InnoDB 등)을 갈아 끼울 수 있는 것이 구조적 특징입니다.
• Oracle: 상용 데이터베이스의 대표 격으로, 대기업의 핵심 업무 시스템에 많이 씁니다. 기능이 강력하고 안정적인 대신 라이선스 비용이 비쌉니다.

세 데이터베이스 모두 CPU·메모리·세션·I/O 같은 공통 지표를 보지만, 그 위에 각자 고유한 지표가 얹힙니다. MySQL은 InnoDB에서 나오는 지표(버퍼풀 적중률, Row Lock Waits)가 많았고, Oracle은 Redo·Undo·Library Cache·파싱에서 나오는 지표가 많았습니다. PostgreSQL은 MVCC·Dead Tuple·VACUUM·트랜잭션 ID(Age) 에서 나오는 지표가 그 DB만의 색깔입니다. 이 단어들이 처음이면 지표 이름이 외계어처럼 보이므로, 핵심 개념을 먼저 쉽게 풀어 두겠습니다. 아래 개념들이 그대로 뒤의 지표로 이어집니다.

PostgreSQL의 핵심 개념 먼저 풀기

• 프로세스 기반 backend: PostgreSQL은 클라이언트가 접속할 때마다 별도의 운영체제 프로세스(backend process) 를 하나씩 띄웁니다. MySQL이 연결마다 "스레드" 를 띄우는 것과 달리, PostgreSQL은 연결마다 "프로세스" 를 띄웁니다. 그래서 PostgreSQL 지표에서 세션 수를 "Backend(백엔드)" 라고 부릅니다. 연결이 800개면 backend 프로세스가 800개 떠 있는 셈입니다. 프로세스는 스레드보다 만드는 비용이 커서, PostgreSQL에서 커넥션 풀(또는 PgBouncer 같은 별도 풀러)이 특히 중요한 이유가 여기 있습니다.
• MVCC(다중 버전 동시성 제어): PostgreSQL은 데이터를 바꿀 때 원래 행을 그 자리에서 덮어쓰지 않습니다. 대신 새 버전의 행을 따로 만들고, 옛 버전은 "이제 안 쓰는 행" 으로 표시만 해 둡니다. 이렇게 한 데이터의 여러 버전을 동시에 두는 방식이 MVCC입니다. 덕분에 누군가 데이터를 바꾸는 중에도 다른 사람은 막히지 않고 바뀌기 전의 깨끗한 버전을 읽을 수 있습니다(읽기가 쓰기를 막지 않음). Oracle이 변경 전 값을 Undo라는 별도 공간에 두는 것과 달리, PostgreSQL은 옛 버전을 테이블 안에 그대로 남겨 둔다는 점이 다릅니다.
• Dead Tuple(죽은 행): 위 MVCC 때문에, UPDATE나 DELETE를 하면 옛 버전의 행이 테이블 안에 "죽은 행(dead tuple)" 으로 남습니다. UPDATE 한 번은 실제로는 "새 버전 추가 + 옛 버전을 죽은 행으로 표시" 입니다. 죽은 행은 아무도 안 보지만 디스크 공간은 계속 차지합니다. 그래서 UPDATE·DELETE가 많은 테이블은 시간이 지나면 죽은 행이 쌓여 실제 데이터보다 훨씬 커지는데, 이것을 "테이블 팽창(bloat)" 이라고 합니다. 청소를 안 한 방에 빈 상자가 쌓이는 것과 비슷합니다.
• VACUUM·autovacuum(죽은 행 청소): 쌓인 죽은 행을 정리해 그 공간을 다시 쓸 수 있게 회수하는 작업이 VACUUM 입니다. 사람이 직접 VACUUM 명령을 돌릴 수도 있지만, 보통은 PostgreSQL이 백그라운드에서 알아서 돌리는 autovacuum 이 이 일을 합니다. autovacuum이 죽은 행을 제때 치워 주면 테이블이 깨끗하게 유지되고, 못 따라가면 죽은 행이 쌓여 테이블이 팽창하고 쿼리가 느려집니다. 청소부가 부지런하면 방이 깨끗하고, 게으르면 빈 상자가 쌓이는 것과 같습니다.
• 트랜잭션 ID wraparound와 Age: PostgreSQL은 모든 트랜잭션에 번호(트랜잭션 ID, XID)를 매깁니다. 그런데 이 번호는 약 21억(32비트)에서 한 바퀴 돌아 다시 0으로 돌아오는 구조입니다. 이것을 wraparound(랩어라운드) 라고 합니다. 만약 아주 오래된 행을 적절히 "freeze(동결)" 처리하지 않은 채 번호가 한 바퀴 돌면, 과거 데이터가 미래로 보이는 심각한 문제가 생겨 DB가 새 쓰기를 거부하고 멈춥니다. 그래서 PostgreSQL은 가장 오래된 트랜잭션이 지금으로부터 얼마나 떨어졌는지를 Age(나이) 로 추적하고, 이 나이가 위험 수준에 가까워지면 autovacuum이 freeze 작업을 해서 한 바퀴 도는 사고를 막습니다. 도서관 대출증 번호가 언젠가 다시 처음 번호로 돌아온다면, 너무 오래된 기록은 미리 정리해 둬야 새 번호와 헷갈리지 않는 것과 비슷합니다. 뒤의 Age Used Ratio·Current Age 지표가 바로 이 나이를 봅니다.
• shared_buffers(버퍼 캐시): PostgreSQL이 자주 쓰는 데이터·인덱스 블록을 캐싱해 두는 공유 메모리 공간입니다. MySQL의 버퍼풀, Oracle의 버퍼 캐시에 해당합니다. 디스크에서 한 번 읽어 온 블록은 여기 올려 두고, 다음에 같은 블록이 필요하면 디스크까지 안 가고 여기서 바로 꺼냅니다. 뒤의 Cache Hit Ratio·Blks Hit·Blks Read 지표가 이 캐시의 적중을 봅니다.
• WAL(변경 로그): 데이터를 바꿀 때마다 "무엇을 어떻게 바꿨는지" 를 먼저 적어 두는 기록입니다(Write-Ahead Log). 장애로 DB가 갑자기 죽어도 이 기록을 다시 적용해 복구합니다. Oracle의 Redo에 해당합니다. 데이터 변경이 많으면 WAL도 많이 발생합니다.
• 블록(block): PostgreSQL이 데이터를 읽고 쓰는 최소 단위입니다(보통 8KB). 행 하나하나가 아니라 여러 행이 담긴 블록 단위로 디스크와 메모리를 오갑니다. 뒤의 Blks Read·Blks Hit가 이 블록 수를 셉니다.

이 개념들을 머리에 넣고 보면, 뒤에 나오는 32개 지표가 따로 노는 숫자가 아니라 하나의 그림으로 이어집니다. 특히 "UPDATE/DELETE → 죽은 행 발생 → VACUUM이 청소 → 못 따라가면 팽창·Age 상승" 이라는 한 줄기가 PostgreSQL 지표를 관통합니다.

---

들어가기 전에 — 지표를 보는 두 가지 관점

지표를 볼 때는 보통 두 가지 기준을 같이 씁니다.

첫째는 절대 임계값입니다. "CPU 사용률 90% 이상이면 위험" 처럼 미리 정한 선을 넘었는지 봅니다. 기준이 명확하지만, DB마다 정상 범위가 달라 임계값도 DB별로 다르게 잡아야 합니다. 실제로 같은 CPU 사용률이라도 PostgreSQL 인스턴스는 경고 85% / 위험 90%로, MySQL·Oracle(경고 60% / 위험 80%)보다 높게 잡혀 있습니다. 인스턴스 특성에 따라 평소 부하 수준이 다르기 때문입니다.

둘째는 평소 대비 변화입니다. 임계값을 넘지 않았더라도 "어제까지 평균 10이던 값이 오늘 40으로 뛰었다" 면 무언가 달라진 것입니다. 비유하자면 절대 임계값은 건강검진의 정상 수치표이고, 평소 대비 변화는 평소 내 컨디션과 비교하는 것입니다. 둘 다 봐야 놓치는 것이 없습니다. PostgreSQL에는 Tup Updated·Tup Deleted나 Temp Bytes처럼 평소엔 작다가 배치 때 크게 튀는 지표가 많아서, 특히 "평소 대비 변화" 가 중요합니다.

그리고 한 가지 더, 지표는 거의 혼자 튀지 않습니다. 그래서 각 지표마다 "함께 확인할 지표" 를 붙였습니다. 어떤 값이 튀었을 때 옆의 지표가 같이 올랐는지로 원인의 방향이 갈리기 때문입니다.

---

1. CPU · 메모리

서버가 얼마나 바쁜지를 보는 기본 영역입니다. 식당에 비유하면 CPU는 주방의 바쁜 정도, 메모리는 조리대의 넓이입니다. 주문이 몰리면 주방이 바빠지고(CPU↑), 재료를 미리 꺼내둘 조리대가 좁으면 그때그때 창고를 오가야 합니다(메모리 부족 → 디스크 접근↑).

이 영역에 처음 보면 막히는 단어를 먼저 짧게 풀어 두겠습니다.

• 스왑(swap): 메모리가 모자랄 때 운영체제가 디스크의 일부를 메모리처럼 빌려 쓰는 것입니다. 메모리는 빠르고 디스크는 훨씬 느리기 때문에, 스왑이 시작되면 모든 작업이 갑자기 느려집니다.
• work_mem(작업 메모리): PostgreSQL이 정렬(ORDER BY)·해시 조인·그룹핑(GROUP BY) 같은 작업을 할 때 쓰는 backend 한 개당 작업 공간입니다. 이 공간 안에서 끝나면 빠르지만, 처리할 양이 이 한도를 넘으면 임시 파일(temp file)을 디스크에 만들어 처리해 느려집니다(뒤의 Temp Bytes·Temp Files가 이것을 봅니다).

(OS) Cpu Usage (%)

서버의 CPU 사용률입니다. 가장 먼저 보는 지표이고 의미도 직관적입니다. CPU는 결국 쿼리를 실제로 계산하는 일꾼이라, 무거운 연산(정렬·조인·집계·풀 스캔)이 많아질수록 올라갑니다. PostgreSQL에서는 autovacuum이 큰 테이블을 청소할 때도 CPU를 어느 정도 씁니다.

• 관측값: 평균 약 18.0%, 범위 약 8.3 ~ 52.4%. MySQL 인스턴스보다 높게 깔려 있고, 순간적으로 50%대까지 오르기도 합니다.
• 위험 신호: 이 인스턴스의 임계는 경고 85% / 위험 90%로 잡혀 있습니다. 90%를 넘어 지속되면 쿼리 응답이 느려지기 시작합니다.
• 튀면 의심할 것: 무거운 쿼리(풀 스캔·정렬·조인), 갑자기 늘어난 활성 backend, 대량 배치, 큰 테이블에 대한 autovacuum 동작. CPU만 단독으로 튀는 경우는 드물고 보통 Active Backend나 읽기량(Blks Read·Blks Hit)이 같이 올라갑니다.

PostgreSQL에서 CPU를 끌어올리는 전형적인 쿼리는 "할 일을 데이터베이스가 떠안는" 형태입니다. 인덱스를 못 타는 조건, 함수로 감싼 컬럼이 대표적입니다.

-- 나쁜 예: created_at에 인덱스가 있어도 함수(date)로 감싸면 인덱스가 무력화된다
-- 풀 스캔 + 함수 연산이 겹쳐 CPU를 크게 쓴다
SELECT * FROM orders
WHERE date(created_at) = DATE '2026-06-18';

-- 고친 예: 함수 없이 범위 조건으로 바꾸면 인덱스를 탄다
SELECT order_id, member_id, total_amount, created_at
FROM   orders
WHERE  created_at >= TIMESTAMP '2026-06-18 00:00:00'
  AND  created_at <  TIMESTAMP '2026-06-19 00:00:00';

지금 무엇이 CPU를 쓰고 있는지는 실행 중인 쿼리부터 봅니다.

-- 지금 active 상태인 backend가 어떤 쿼리를 돌리고 있는지 (오래 걸린 순서)
SELECT pid, usename, state,
       now() - query_start AS running_for,
       query
FROM   pg_stat_activity
WHERE  state = 'active'
  AND  backend_type = 'client backend'
ORDER  BY running_for DESC;

• 함께 확인할 지표: Blks Read·Blks Hit가 같이 오르면 → 인덱스를 못 타는 풀 스캔/무거운 읽기 쿼리가 CPU를 먹는 것입니다. Active Backend가 같이 오르면 → 쿼리가 빨리 끝나지 못하고 쌓이는 중입니다. Tup Updated·Tup Deleted가 같이 뛰면 → 대량 변경 배치가 돌며 죽은 행을 만들고 autovacuum까지 일을 시작한 것일 수 있습니다.

개발자 관점 — 정렬·집계·문자열 가공을 전부 DB 쿼리에 몰아넣으면 그 계산 부담이 그대로 CPU로 옵니다. 애플리케이션에서 처리할 수 있는 가공은 코드 쪽으로 옮기거나, 자주 쓰는 집계는 미리 계산한 집계 테이블을 두면 완화됩니다. 또 컬럼을 함수로 감싸는 조건(date(col) = ...)은 인덱스를 무력화해 풀 스캔을 유발하므로, 범위 조건으로 바꾸는 것이 기본입니다.

(OS) Memory Percent (%)

운영체제 관점의 메모리 사용률(%)입니다.

• 관측값: 평균 약 79.4%, 범위 78.2 ~ 82.3%로 평탄하게 유지됐습니다. MySQL(약 54%)·Oracle(약 56%) 인스턴스보다 높게 깔려 있는데, 이는 인스턴스 크기·shared_buffers 설정에 따른 차이입니다. 그래서 절대값보다 추세가 중요합니다.
• 위험 신호: 경고 90%, 위험 95%. 다만 남은 메모리가 꾸준히 줄어드는 추세가 더 위험합니다. 고갈되면 OS가 스왑(디스크를 메모리처럼 쓰는 것)을 시작하고, 그때부터 성능이 급격히 나빠집니다.
• 튀면 의심할 것: backend 폭증(연결마다 프로세스가 메모리를 씀), 큰 정렬·해시 조인으로 work_mem 사용 증가, shared_buffers 설정 과다.

PostgreSQL은 연결마다 프로세스를 띄우므로, backend가 늘면 메모리도 그만큼 늘어납니다. 또 정렬·해시 작업이 work_mem을 넘으면 디스크 temp로 넘어가는데, 이것도 함께 봅니다.

-- 임시 파일(temp) 사용량 — 정렬/해시가 메모리에서 끝나지 못하고 디스크로 넘어가는지
-- temp_files·temp_bytes가 빠르게 늘면 work_mem이 부족하다는 신호
SELECT datname, temp_files, temp_bytes
FROM   pg_stat_database
WHERE  datname = current_database();

• 함께 확인할 지표: Numbackends가 같이 우상향하면 → 연결(프로세스) 누수로 메모리가 새는 것입니다. Temp Bytes·Temp Files가 같이 오르면 → 정렬·해시가 디스크로 넘어가며 메모리를 압박하는 중입니다. 남은 메모리(Free Memory)가 줄며 Read IOPS가 오르면 → 스왑이 시작됐을 수 있어 즉시 점검합니다.

개발자 관점 — PostgreSQL은 연결마다 프로세스를 띄우므로 커넥션 풀을 과하게 크게 잡으면 메모리 압박이 MySQL보다 직접적으로 커집니다. 풀 크기를 적정하게 잡고, 한 번에 거대한 결과셋을 정렬·그룹핑하는 쿼리를 줄이며, 인덱스로 정렬을 대신하게 하면 work_mem이 디스크 temp로 넘어가는 경우가 줄어듭니다.

Free Memory (Bytes)

여유 메모리(freeable memory)입니다. 인스턴스에 남아 있는 쓸 수 있는 메모리를 바이트로 봅니다. (OS) Memory Percent와 짝으로, 한쪽은 비율을, 한쪽은 절대 여유량을 봅니다.

• 관측값: 평균 약 14.2GB, 범위 약 12.2 ~ 15.0GB로 평탄합니다. 여유가 안정적으로 유지되는 상태입니다.
• 위험 신호: 메모리 사용률과 마찬가지로 절대값보다 여유 메모리가 꾸준히 줄어드는 추세가 위험합니다. 고갈되면 스왑이 시작됩니다.
• 튀면(줄면) 의심할 것: backend 누수(반납 안 된 연결이 쌓임), 큰 정렬·해시 작업, shared_buffers·캐시가 메모리를 점유.
• 함께 확인할 지표: (OS) Memory Percent와 반대로 움직입니다(여유가 줄면 사용률이 오름). Numbackends가 같이 우상향하면 → 연결 누수로 메모리가 새는 것입니다. Read IOPS가 같이 오르면 → 스왑 시작 신호일 수 있어 즉시 점검합니다.

실무 시나리오 — 여유 메모리가 늘 14GB 안팎으로 평탄하면 한눈에 정상임을 알 수 있습니다. 문제는 천천히 새는 경우입니다. 애플리케이션이 연결을 닫지 않으면 backend 프로세스가 쌓이며 여유 메모리가 시간당 일정하게 줄어드는 우하향 그래프가 그려집니다. 어느 순간 스왑이 시작되면 모든 쿼리가 느려지므로, 추세가 꺾이기 전에 잡는 것이 중요합니다.

---

2. 세션 · 연결

체감상 가장 자주 보는 영역입니다. 핵심은 "연결" 과 "활성" 을 구분하는 것입니다. 카페로 치면 매장 안 손님 전체가 연결(Numbackends)이고, 그중 지금 계산대에서 주문 중인 손님이 활성(Active Backend)입니다. 매장에 손님이 많아도 계산대 앞은 보통 한두 명인 것이 정상입니다.

이 영역의 용어 몇 개를 먼저 풀어 두겠습니다.

• backend(백엔드): PostgreSQL이 클라이언트 연결 하나를 처리하려고 띄우는 프로세스입니다. MySQL이 연결마다 스레드를 띄우는 것과 달리 PostgreSQL은 프로세스를 띄우고, 그것을 backend라고 부릅니다. 쉽게 말해 "지금 붙어 있는 DB 연결 한 개" 입니다.
• 커넥션 풀(Connection Pool): 연결을 매번 새로 만들었다 끊는 것은 비용이 큽니다. 특히 PostgreSQL은 연결마다 프로세스를 띄워 더 비쌉니다. 그래서 애플리케이션이 연결을 미리 여러 개 만들어 두고 빌려 쓰고 반납하거나, PgBouncer 같은 별도 풀러를 앞에 둡니다. 공구를 쓸 때마다 사 오지 않고 공구함에서 꺼내 쓰고 다시 넣어 두는 것과 같습니다. 빌린 연결을 반납하지 않으면 "연결 누수" 가 생깁니다.
• idle 연결: 연결은 되어 있지만 지금은 아무 쿼리도 돌리지 않고 다음 일을 기다리는 상태(state = 'idle')입니다. 커넥션 풀이 미리 만들어 둔 연결들은 대부분 이 idle 상태로 대기합니다. 그래서 연결 수가 수백 개여도 실제로 일하는 것은 몇 개뿐인 경우가 정상입니다. 특히 트랜잭션을 연 채로 노는 idle in transaction 상태는 락과 죽은 행 정리를 막을 수 있어 주의해서 봅니다(뒤에서 다시 다룹니다).

Active Backend (개)

지금 실제로 일하고 있는(active 상태인) backend 수입니다. 부하를 가장 잘 나타냅니다.

• 관측값: 평균 약 0.88, 범위 0 ~ 177. 뒤에 나오는 Numbackends가 약 837인데 활성은 1 안팎입니다. 즉 대부분의 연결은 놀고 있다는 뜻이고, 이것이 정상입니다. 다만 순간 최댓값이 177까지 튄 적이 있는데, 이 순간이 바로 활성이 한꺼번에 몰린 위험 신호입니다.
• 위험 신호: 경고 30, 위험 40. 활성이 갑자기 수십 개로 뛰면 쿼리들이 빨리 끝나지 못하고 쌓이고 있다는 신호입니다. 관측 최댓값 177은 임계를 크게 넘긴 값입니다.
• 읽는 법: "연결(Numbackends)은 그대로인데 활성만 뛰었다" 면 쿼리가 느려진 것이고, "연결도 같이 뛰었다" 면 트래픽이 몰린 것입니다. 이 구분이 원인 추적의 출발점입니다.

활성 backend가 갑자기 오르는 흔한 원인은, 어떤 무거운 쿼리가 배포되어 그 쿼리를 돌리는 backend들이 동시에 오래 머무는 경우입니다.

-- 지금 active 상태인 backend를 오래 걸린 순서로
SELECT pid, usename, state,
       now() - query_start AS running_for,
       wait_event_type, wait_event, query
FROM   pg_stat_activity
WHERE  state = 'active'
  AND  backend_type = 'client backend'
ORDER  BY running_for DESC;

• 함께 확인할 지표: CPU·Blks Read가 같이 오르면 → 느린(풀 스캔) 쿼리가 안 끝나고 쌓이는 것입니다. Locks Waiting이 같이 오르면 → 락 대기로 활성이 막혀 쌓인 것(쿼리 자체보다 다른 트랜잭션이 원인). Numbackends가 같이 뛰면 → 트래픽 급증입니다.

개발자 관점 — 활성 backend는 쿼리 하나하나의 응답 속도에 직접 좌우됩니다. 느린 쿼리 한 종류가 배포되면 그 쿼리를 실행하는 backend들이 동시에 오래 머물러 활성 수가 한꺼번에 올라갑니다. 쿼리 응답 시간을 짧게 유지하는 것(인덱스·적정 결과 크기)이 활성 수를 낮게 유지하는 근본 대책입니다.

실무 시나리오 — 평소 활성 backend가 1~2였는데 어느 배포 직후 갑자기 수십~177로 치솟았다고 해보겠습니다. pg_stat_activity로 잡아 보니 같은 query를 가진 backend들이 수십 개씩 동시에 active로 떠 있고, 그 쿼리가 인덱스를 못 타는 풀 스캔이었습니다. 한 요청이 느려지자 뒤따라 들어온 요청들이 끝나지 못하고 쌓인 것입니다. 해당 컬럼에 인덱스를 추가하니 활성 backend가 다시 1~2로 돌아옵니다. 관측 최댓값 177은 이런 "느린 쿼리가 동시에 몰린" 순간의 흔적입니다.

Numbackends (개)

지금 연결되어 있는 전체 backend 수입니다. 아무 일도 안 하는(idle) backend도 포함합니다. (delta로 표기되지만 실질적으로 현재 연결 수를 봅니다.)

• 관측값: 평균 약 837, 범위 726 ~ 1,385. 커넥션 풀이 큰 환경이라 수백 개가 연결된 채 유지됩니다. 연결 수 자체보다 "평소 수준에서 벗어났는가" 가 포인트입니다.
• 튀면 의심할 것: 연결 누수(반납 안 된 연결), 풀 설정 변경, 트래픽 급증.

-- 상태별·사용자별 연결 수 (idle가 한쪽으로 쌓이면 누수 단서)
SELECT usename, state, COUNT(*) AS cnt
FROM   pg_stat_activity
WHERE  backend_type = 'client backend'
GROUP  BY usename, state
ORDER  BY cnt DESC;

• 함께 확인할 지표: Active Backend는 그대로인데 Numbackends만 우상향하면 → 일 안 하는 idle 연결이 쌓이는 누수입니다. Connection Ratio가 같이 오르면 → max_connections 한도에 가까워지는 것이라 위험합니다. Free Memory가 같이 줄면 → backend 프로세스마다 쓰는 메모리가 누적되는 중입니다.

개발자 관점 — 커넥션 풀 크기를 너무 크게 잡거나, 예외 경로에서 연결을 반납하지 않으면(try-with-resources나 finally 누락) 연결이 계속 쌓입니다. PostgreSQL은 연결마다 프로세스를 띄워 비용이 크므로, 풀 크기는 max_connections와 함께 신중히 설계해야 하고, 연결이 많이 필요하면 PgBouncer 같은 풀러를 앞단에 두는 것을 고려합니다.

Connection Ratio (%)

연결 사용률입니다. 산식은 numbackends / max_connections * 100, 즉 "허용된 최대 연결 중 몇 %를 쓰나" 입니다. 주차장 만차율과 같아서, 100%면 새 차(연결)가 못 들어옵니다.

• 관측값: 평균 약 16.8%, 범위 14.5 ~ 27.7%. 최대 연결 대비 여유가 많은 상태입니다.
• 위험 신호: 경고 85%, 위험 90%. 100%에 도달하면 새 연결이 거부되어 "too many clients already" 에러가 납니다. 서비스 장애로 직결됩니다.
• 튀면 의심할 것: 연결 누수, 풀 크기 과다 설정, max_connections가 너무 작게 잡힘.

연결 누수는 대개 코드에서 "빌린 연결을 반납하지 않는" 경로에서 생깁니다.

-- (개념용 의사코드) 나쁜 예: 예외가 나면 연결을 닫지 못하고 샌다
-- conn = pool.getConnection();
-- stmt.executeQuery(...);   // 여기서 예외가 나면
-- conn.close();             // 이 줄에 도달하지 못한다 → 연결 누수

-- 고친 예: try-with-resources / finally 로 반드시 반납
-- try (Connection conn = pool.getConnection()) {
--     ... 쿼리 ...
-- }  // 블록을 벗어나면 예외가 나도 conn이 자동 close 된다

-- 현재 사용률 직접 계산
SELECT
  (SELECT COUNT(*) FROM pg_stat_activity) AS connected,
  setting::int AS max_conn,
  ROUND((SELECT COUNT(*) FROM pg_stat_activity)::numeric
        / setting::int * 100, 2) AS usage_pct
FROM pg_settings
WHERE name = 'max_connections';

• 함께 확인할 지표: Numbackends가 함께 우상향하면 → 연결 누수가 사용률을 밀어 올리는 것입니다. Active Backend는 낮은데 사용률만 오르면 → idle 연결이 쌓여 한도를 잠식하는 것(누수 가능성)입니다.

개발자 관점 — 사용률은 결국 현재 연결 / max_connections이므로, 연결 반납 누락이 있으면 분자가 꾸준히 우상향합니다. 연결은 빌리면 반드시 반납되는 구조(try-with-resources/finally)로 쓰고, 풀과 max_connections를 함께 설계해 여유를 둬야 합니다. 사용률이 서서히 우상향하는 그래프 자체가 누수 코드의 신호입니다.

실무 시나리오 — 가장 흔한 장애 패턴입니다. 애플리케이션이 연결을 쓰고 반납하지 않으면 연결이 하나씩 새어 나갑니다. 그래프상 Connection Ratio가 서서히 우상향하다가, 100%에 닿는 순간 신규 요청이 전부 "too many clients already" 로 실패하며 서비스가 멈춥니다. 재기동하면 일시 복구되지만 누수 코드를 못 잡으면 며칠 뒤 같은 시점에 또 터집니다. 그래서 "위험선을 넘었나" 뿐 아니라 "꾸준히 우상향하는가" 를 같이 봅니다.

Long Active Backend (개)

먼저 용어부터 풀어 보겠습니다. backend는 PostgreSQL이 클라이언트 연결 하나를 처리하려고 띄우는 프로세스입니다. 그 backend가 쿼리 하나를 5초가 넘도록 끝내지 못하고 붙잡고 있을 때, 이것을 "장기 실행 backend" 로 셉니다.

보통의 쿼리는 수 밀리초면 끝납니다. 그런데 한 쿼리가 5초씩 걸린다는 것은, 그동안 그 연결이 계속 묶여 있고 CPU·메모리·디스크를 잡아먹으며, 경우에 따라 다른 쿼리까지 막고 있다는 뜻입니다. 계산대에서 한 손님이 5초 넘게 안 끝나면 뒤에 줄이 생기는 것과 같습니다.

• 관측값: 평균 약 0.02(범위 0 ~ 2). 0이 정상입니다.
• 위험 신호: 경고 1, 위험 2. 1개만 나타나도 들여다볼 가치가 있습니다.

어떤 상황에서 생기나 — 그리고 어떤 쿼리가 범인인가

대부분은 인덱스를 제대로 타지 못해 테이블을 통째로 훑는 쿼리입니다. 인덱스는 책 뒤의 색인과 같습니다. 색인이 있으면 원하는 항목을 바로 펴고, 없으면 첫 장부터 끝까지 넘겨야 합니다. 데이터가 적을 땐 끝까지 넘겨도 금방이지만, 수백만 행이 되면 5초, 10초로 늘어납니다. 실무에서 자주 나오는 유형을 코드로 보겠습니다.

(1) WHERE 조건 컬럼에 인덱스가 없는 경우

-- phone 컬럼에 인덱스가 없으면, 회원 500만 명을 전부 훑는다
SELECT * FROM members WHERE phone = '010-1234-5678';

(2) LIKE 앞에 와일드카드(%)가 붙은 경우

-- 'name%' 은 인덱스를 쓸 수 있지만, '%...%' 는 인덱스를 못 쓰고 전체를 스캔한다
SELECT * FROM products WHERE name LIKE '%케이스%';

부분 검색이 꼭 필요하면 PostgreSQL의 전문 검색(tsvector/GIN 인덱스)이나 pg_trgm 확장으로 푸는 방법이 있습니다.

(3) 컬럼을 함수로 감싼 경우

-- created_at에 인덱스가 있어도, 함수로 감싸면 인덱스가 무력화된다
SELECT * FROM orders WHERE date(created_at) = '2026-06-18';

-- 이렇게 범위로 바꾸면 인덱스를 탄다
SELECT * FROM orders
WHERE created_at >= TIMESTAMP '2026-06-18 00:00:00'
  AND created_at <  TIMESTAMP '2026-06-19 00:00:00';

(4) 깊은 페이징(OFFSET이 큰 경우)

-- 100만 건을 건너뛰고 20건을 가져오려면, 100만 건을 다 읽어야 한다
SELECT * FROM posts ORDER BY id OFFSET 1000000 LIMIT 20;

-- 고친 예: 마지막으로 본 id 기준으로 잘라 읽는다(keyset 페이징)
SELECT * FROM posts WHERE id > 1000000 ORDER BY id LIMIT 20;

(5) 대량 UPDATE / DELETE

-- 조건에 걸리는 수백만 행을 한 번에 지우면, 스레드가 묶이고 락도 오래 잡으며
-- 죽은 행(dead tuple)도 한꺼번에 쏟아진다
DELETE FROM access_logs WHERE created_at < '2025-01-01';

PostgreSQL에서는 대량 DELETE/UPDATE가 죽은 행을 한꺼번에 만들어 이후 autovacuum 부담까지 키우므로, 몇 천 건씩 나눠서 처리하는 것이 특히 안전합니다.

어떻게 확인하나

장기 실행 backend가 보이면, 먼저 지금 무엇이 오래 돌고 있는지 찾습니다.

-- 5초 넘게 실행 중인 쿼리만 골라 본다
SELECT pid, usename, now() - query_start AS running_for,
       wait_event_type, wait_event, query
FROM   pg_stat_activity
WHERE  state = 'active'
  AND  now() - query_start > interval '5 seconds'
ORDER  BY running_for DESC;

범인 쿼리를 찾았으면 EXPLAIN (ANALYZE, BUFFERS) 로 실행 계획을 봅니다. Seq Scan(순차 스캔)이 큰 테이블에 걸려 있거나 읽은 블록 수가 매우 크면 인덱스를 못 타고 있다는 뜻입니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM members WHERE phone = '010-1234-5678';
-- 결과에 Seq Scan on members ... rows=5000000 이 보이면 풀 스캔, 인덱스 필요

• 함께 확인할 지표: ① 먼저 pg_stat_activity의 running_for로 어떤 쿼리가 오래 걸리는지 찾고 EXPLAIN으로 순차 스캔을 확인합니다. ② CPU·Blks Read가 동반 상승이면 → 풀 스캔/무거운 쿼리입니다. ③ Locks Waiting이 동반이면 → 락 대기(쿼리 자체보다 다른 트랜잭션이 막은 것)입니다. ④ Active Backend가 같이 쌓이면 → 느린 쿼리가 뒤를 막는 중입니다.

개발자 관점 — 장기 실행 쿼리는 대부분 ORM이나 직접 작성한 쿼리가 인덱스를 못 타는 데서 나옵니다. 조회 조건 컬럼에 인덱스가 없거나, 함수로 컬럼을 감싸거나, 깊은 OFFSET 페이징을 쓰면 데이터가 쌓일수록 어느 날 갑자기 느려집니다. 코드를 짤 때 조회 조건과 정렬 컬럼의 인덱스 설계를 먼저 챙기고, 대량 변경은 한 번에 하지 말고 나눠서 처리해야 합니다.

실무 시나리오 — 출시 초기에는 모든 화면이 빨랐는데 몇 달 뒤 특정 검색 화면만 느려졌다고 가정해 봅니다. 모니터링을 보면 그 시간대에 Long Active Backend가 1~2로 올라오고 CPU와 Blks Read도 같이 뜁니다. pg_stat_activity로 잡아 보니, 인덱스 없는 컬럼을 조건으로 건 검색 쿼리가 매번 수백만 행을 순차 스캔하고 있었습니다. 데이터가 적던 초기에는 0.01초였던 쿼리가, 데이터가 쌓이면서 5초로 늘어난 것입니다. 해당 컬럼에 인덱스를 추가하자 Long Active Backend는 다시 0으로 돌아옵니다. 장기 실행 backend는 이렇게 "느려진 쿼리가 있다" 는 가장 먼저 켜지는 경고등 역할을 합니다.

Locks Waiting (개)

지금 락을 얻으려고 대기 중인 전체 backend 수입니다. 락 경합을 직접 보여 주는 지표입니다. (락 자체는 아래 6번 락 섹션에서 자세히 다룹니다.)

• 관측값: 평균 약 0.003, 범위 0 ~ 5. 0이 정상이고, 0이 아니면 지금 누군가 다른 트랜잭션 때문에 막혀 있는 것입니다.
• 위험 신호: 경고 1, 위험 2. 작은 값이라도 반복되면 특정 쿼리·트랜잭션이 자주 충돌하고 있다는 뜻입니다.
• 튀면 의심할 것: 한 트랜잭션이 행을 쥔 채 커밋을 미룸(특히 idle in transaction), 같은 행에 대한 동시 수정 경합.

-- 지금 락을 기다리는 backend와, 누가 막고 있는지 (PostgreSQL 9.6+ 내장 함수)
SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, query
FROM   pg_stat_activity
WHERE  cardinality(pg_blocking_pids(pid)) > 0;

• 함께 확인할 지표: Active Backend가 같이 쌓이면 → 대기가 부하로 번지는 중입니다. Deadlocks가 같이 오르면 → 경합이 교착으로 번진 것입니다. 자세한 분석은 6번 락 섹션을 참고합니다.

개발자 관점 — 대기 backend가 있다는 것은 누군가 락을 오래 쥐고 있다는 뜻입니다. 보통 락을 잡은 트랜잭션이 커밋을 미루는 경우(특히 트랜잭션을 연 채 노는 idle in transaction)이므로, 변경 후 즉시 커밋하도록 트랜잭션 경계를 좁히는 것이 핵심입니다.

---

3. 트랜잭션 · 처리량

이 영역은 PostgreSQL이 얼마나 많은 트랜잭션을 처리하고, 그 과정에서 행을 얼마나 읽고 쓰는지를 봅니다. 가게의 품목별 판매 건수 같은 것이라, 절대값보다 평소 대비 패턴이 중요합니다.

먼저 용어를 짚어 두겠습니다.

• 트랜잭션(Transaction): 여러 DB 작업을 "전부 성공 아니면 전부 취소" 로 묶은 한 묶음입니다. 다 잘 됐으면 COMMIT, 문제가 생기면 ROLLBACK 합니다.
• 튜플(tuple): PostgreSQL에서 "행(row)" 을 부르는 말입니다. Tup으로 시작하는 지표(Tup Fetched·Returned·Inserted·Updated·Deleted)는 모두 "몇 개의 행을 다뤘나" 를 셉니다.
• sigma 지표: 이 영역 다수 지표 앞에 붙은 (sigma) 는 누적 카운터의 구간 합을 뜻합니다. 1분 동안 발생한 양을 더한 값으로 보면 됩니다.

TPS (건/초)

초당 트랜잭션 수입니다. 커밋·롤백으로 끝난 트랜잭션을 초당 단위로 본 것입니다.

• 관측값: 평균 약 799, 범위 0 ~ 47,500. 평소에도 꽤 높고, 순간 4만을 넘긴 적이 있습니다. PostgreSQL 인스턴스가 MySQL·Oracle보다 트랜잭션 처리량이 훨씬 높은 환경임을 보여 줍니다.
• 튀면 의심할 것: 대량 배치(잦은 단건 커밋), 트래픽 급증, 한 작업을 잘게 쪼개 커밋하는 코드.

쓰기 쪽 TPS가 비정상적으로 높은 흔한 원인은 한 건씩 커밋하는 코드입니다.

-- 나쁜 예: 한 건씩 INSERT + 매 건 COMMIT (트랜잭션 수가 폭증)
-- 애플리케이션이 루프를 돌며 매 건 커밋하면 TPS와 WAL flush가 함께 치솟는다
-- for (row : rows) { insert(row); conn.commit(); }

-- 고친 예: 여러 건을 모아 한 번에 커밋 (트랜잭션 수가 크게 준다)
-- conn.setAutoCommit(false);
-- for (row : rows) { insert(row); }
-- conn.commit();   // 한 번만 커밋

• 함께 확인할 지표: Xact Commit·Xact Rollback이 같이 오르면 → 트랜잭션 수 자체가 늘어난 것입니다(아래). Active Backend·CPU가 동반이면 → 실제 부하 증가입니다. Write IOPS가 같이 오르면 → 커밋이 디스크 쓰기(WAL flush)로 내려가는 중입니다.

개발자 관점 — TPS는 커밋·롤백 횟수에 비례합니다. 매 건마다 커밋하는 코드는 트랜잭션 수를 잘게 늘리고 WAL flush 부담을 키웁니다. 한 묶음 단위로 커밋하면 TPS와 디스크 쓰기 부담이 함께 줄어듭니다. 단, 너무 크게 묶으면 그 트랜잭션이 오래 살아 죽은 행 정리(VACUUM)를 막으므로, 적당한 단위(수천~수만 건)로 나누는 것이 균형점입니다.

Xact Commit (건) / Xact Rollback (건)

각각 Commit / Rollback으로 끝난 트랜잭션 수입니다(1분 구간 합).

• 관측값: Xact Commit 평균 약 3,989 / 904 ~ 237,484, Xact Rollback 평균 약 7.1 / 0 ~ 37. 커밋이 압도적으로 많고 롤백은 거의 없습니다. 정상적인 모습입니다.
• 읽는 법: Rollback이 평소보다 늘면 트랜잭션이 자주 실패해 되돌려지고 있다는 뜻입니다. 락 경합·교착(deadlock)·제약조건 위반·애플리케이션 예외 등을 의심합니다.
• 튀면 의심할 것: Commit 급증 → 대량 배치·잦은 단건 커밋. Rollback 급증 → 트랜잭션 실패 증가(에러·교착).

-- 커밋/롤백 누적 카운터 확인 (현재 DB 기준)
SELECT datname, xact_commit, xact_rollback
FROM   pg_stat_database
WHERE  datname = current_database();

• 함께 확인할 지표: Xact Commit·TPS·Write IOPS가 함께 오르면 → 정상적인 쓰기 부하입니다. Xact Rollback이 Deadlocks·Locks Waiting과 함께 오르면 → 교착·락 경합으로 트랜잭션이 되돌려지는 것일 수 있습니다.

개발자 관점 — 롤백이 잦다는 것은 비즈니스 로직이 실패를 자주 만난다는 뜻입니다. 교착(deadlock)으로 인한 롤백이라면 트랜잭션 안에서 여러 테이블·행을 건드리는 순서를 코드 전반에서 일정하게 맞춰 교착 가능성을 줄입니다. 제약조건 위반이 잦다면 사전 검증을 강화합니다.

Trx Time (초)

트랜잭션 비교 서머리 지표로, 트랜잭션이 진행된 시간을 가늠하는 값입니다. 한 트랜잭션이 얼마나 오래 살아 있는지를 봅니다.

• 관측값: 평균 약 0.24초, 범위 0 ~ 1,017.48초. 평소엔 매우 짧은데 어떤 순간 1,000초(약 17분)를 넘긴 트랜잭션이 있었습니다. 이 긴 트랜잭션이 위험 신호입니다.
• 튀면 의심할 것: 트랜잭션을 열어 둔 채 오래 방치(idle in transaction), 외부 작업을 트랜잭션 안에서 기다림, 거대한 단일 트랜잭션.

PostgreSQL에서 긴 트랜잭션은 특히 조심해야 합니다. 트랜잭션이 오래 살아 있으면 그 시점 이후의 죽은 행을 VACUUM이 청소하지 못합니다. 그 트랜잭션이 옛 버전을 아직 볼 수도 있다고 보기 때문입니다. 그래서 긴 트랜잭션 하나가 테이블 팽창과 Age 상승을 유발할 수 있습니다.

-- 오래 살아 있는 트랜잭션 찾기 (idle in transaction 포함)
SELECT pid, usename, state,
       now() - xact_start AS xact_age,
       now() - state_change AS in_state_for,
       query
FROM   pg_stat_activity
WHERE  xact_start IS NOT NULL
ORDER  BY xact_age DESC;

• 함께 확인할 지표: Current Age·Age Used Ratio가 같이 오르면 → 긴 트랜잭션이 죽은 행 정리를 막아 노화가 진행되는 중입니다. Locks Waiting이 같이 오르면 → 그 트랜잭션이 락을 쥐고 안 놓는 것입니다.

개발자 관점 — 트랜잭션은 짧게 가져가는 것이 PostgreSQL에서 특히 중요합니다. 트랜잭션 안에 외부 API 호출·파일 처리·사용자 입력 대기 같은 긴 작업을 넣으면 그 시간만큼 트랜잭션이 살아 있어 VACUUM을 막고 락을 쥡니다. DB 변경 구간만 짧게 묶어 즉시 커밋하고, 긴 작업은 트랜잭션 밖으로 빼야 합니다.

Tup Fetched (건) / Tup Returned (건)

• Tup Fetched: 쿼리로 실제로 가져온(fetched) 행 수입니다. 인덱스 등을 통해 실제로 클라이언트에 전달된 행에 가깝습니다.
• Tup Returned: 쿼리가 처리 과정에서 읽어 훑은(returned/scanned) 행 수입니다. 순차 스캔으로 훑은 행까지 포함하므로, 풀 스캔이 많으면 이 값이 커집니다.
• 관측값: Tup Fetched 평균 약 5,723 / 1,203 ~ 43,031, Tup Returned 평균 약 63,236 / 10,030 ~ 약 1.36억. Returned의 최댓값이 1억을 넘는 것이 눈에 띕니다. 순간적으로 엄청난 양의 행을 훑었다는 뜻입니다.
• 읽는 법: Returned가 Fetched보다 훨씬 크면, 많이 훑었는데 실제로 쓴 행은 적다는 뜻 — 즉 비효율적으로 풀 스캔하고 있을 가능성이 높습니다. 다음 지표(Rows Hit Ratio)가 바로 이 비율을 봅니다.
• 튀면 의심할 것: 인덱스를 못 타는 풀 스캔, 넓은 범위 조회, 대형 조인.

-- 가져온 행 / 훑은 행 누적 카운터
SELECT datname, tup_fetched, tup_returned
FROM   pg_stat_database
WHERE  datname = current_database();

• 함께 확인할 지표: Tup Returned가 Tup Fetched보다 훨씬 크고 Blks Read·CPU가 동반이면 → 인덱스를 못 타는 풀 스캔입니다. Rows Hit Ratio가 같이 떨어지면 → 훑은 것 대비 쓴 행이 적은 비효율입니다.

개발자 관점 — Returned가 Fetched보다 훨씬 크다는 것은 데이터베이스가 많이 읽고 대부분을 버린다는 뜻입니다. WHERE 조건에 맞는 인덱스가 없어 전부 훑은 뒤 거르는 경우가 대표적입니다. 조회 조건·조인 키에 인덱스를 설계해 "필요한 행만 골라 읽도록" 만들면 두 값의 격차가 줄어듭니다.

Tup Inserted (건) / Tup Updated (건) / Tup Deleted (건)

각각 쿼리로 삽입·갱신·삭제된 행 수입니다(1분 구간 합). PostgreSQL에서 이 세 지표, 특히 Update와 Delete는 Dead Tuple과 직결되므로 중요합니다.

• 관측값:
• Tup Inserted: 평균 약 1,041 / 223 ~ 3,267
• Tup Updated: 평균 약 408 / 103 ~ 970
• Tup Deleted: 평균 약 93 / 0 ~ 35,123
• 읽는 법: Insert는 죽은 행을 거의 안 만들지만, Update와 Delete는 옛 버전 행을 죽은 행으로 남깁니다. Update 한 번은 "새 버전 추가 + 옛 버전을 죽은 행으로 표시" 이고, Delete는 그 행을 죽은 행으로 표시합니다. 그래서 Tup Updated·Tup Deleted가 크면 그만큼 죽은 행이 쌓여 이후 autovacuum이 일해야 합니다. Tup Deleted 최댓값 35,123은 대량 삭제 배치가 돈 순간으로 보입니다.
• 튀면 의심할 것: 대량 배치(적재·갱신·삭제), 데이터 마이그레이션. 특히 Update/Delete 급증 뒤에는 죽은 행 누적과 autovacuum 동작이 따라옵니다.

대량 변경을 한 번에 몰아 하면 죽은 행이 한꺼번에 쏟아집니다. 나눠서 처리하면 분산됩니다.

-- 나쁜 예: 수백만 행을 한 번에 갱신 → 죽은 행이 한꺼번에 쏟아진다
UPDATE orders SET status = 'ARCHIVED'
WHERE created_at < '2025-01-01';

-- 고친 예: 몇 만 건씩 나눠서 갱신 (죽은 행 발생을 분산, VACUUM 부담 완화)
-- 한 번에 5만 건씩 처리하고 그때그때 커밋한다
WITH target AS (
  SELECT order_id FROM orders
  WHERE created_at < '2025-01-01' AND status <> 'ARCHIVED'
  LIMIT 50000
)
UPDATE orders o SET status = 'ARCHIVED'
FROM target t WHERE o.order_id = t.order_id;
-- 위 UPDATE를 영향 행이 0이 될 때까지 반복하며 묶음마다 커밋

-- 테이블별 삽입·갱신·삭제와 현재 죽은 행 수를 함께 본다
SELECT relname, n_tup_ins, n_tup_upd, n_tup_del,
       n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC;

• 함께 확인할 지표: Tup Updated·Tup Deleted 급증 뒤에 pg_stat_user_tables의 n_dead_tup이 오르면 → 죽은 행이 쌓이는 것입니다. 이후 CPU와 Write IOPS가 오르면 → autovacuum이 청소를 시작한 것입니다. Age Used Ratio가 함께 오르면 → 정리가 못 따라가 노화가 진행되는 중입니다.

개발자 관점 — PostgreSQL에서 UPDATE/DELETE는 단순히 데이터를 바꾸는 것 이상으로, 죽은 행을 만들어 VACUUM 부담을 남깁니다. 대량 변경을 하나의 큰 트랜잭션으로 몰면 죽은 행이 한꺼번에 쌓이고 테이블이 팽창합니다. 배치는 몇 만 건 단위로 나눠 커밋하고, 자주 갱신되는 테이블은 불필요한 UPDATE(값이 안 바뀌는데도 갱신)를 줄이는 것이 좋습니다. 같은 행을 반복 갱신하는 패턴이라면 HOT update가 잘 일어나도록 인덱스 컬럼을 자주 안 바꾸는 설계도 도움이 됩니다.

실무 시나리오 — 매일 새벽 대량 UPDATE 배치가 도는 테이블이 점점 느려진다고 해보겠습니다. 매번 수십만 행을 한 트랜잭션으로 갱신하면서 죽은 행이 쏟아지는데, autovacuum이 그 속도를 못 따라가 n_dead_tup이 계속 쌓이고 테이블이 실제 데이터의 몇 배로 팽창합니다. 팽창한 테이블은 같은 쿼리도 더 많은 블록을 읽어 느려집니다. 배치를 5만 건 단위로 나눠 커밋하고 해당 테이블의 autovacuum을 더 자주 돌도록 조정하면, 죽은 행이 제때 정리되며 팽창과 속도 저하가 잡힙니다.

---

4. 캐시 · 읽기

PostgreSQL도 자주 쓰는 데이터를 메모리(shared_buffers, 버퍼 캐시)에 캐싱해 디스크 접근을 줄입니다. 자주 보는 자료를 서가까지 가지 않고 책상 위에 올려두는 것과 같습니다. 책상에서 바로 찾으면 빠르고(메모리), 서가까지 가야 하면 느립니다(디스크).

이 영역의 용어를 먼저 풀어 두겠습니다.

• 논리 읽기(Blks Hit) / 물리 읽기(Blks Read): "데이터를 읽어 달라" 는 요청이 들어오면 먼저 버퍼 캐시(메모리)부터 찾아봅니다. 메모리에서 바로 찾은 것이 Blks Hit(논리 읽기) 이고, 캐시에 없어 디스크까지 내려가 읽은 것이 Blks Read(물리 읽기) 입니다. Blks Hit는 빠르고 Blks Read는 느립니다.
• 적중률(Cache Hit Ratio): 전체 읽기 중에서 디스크까지 가지 않고 메모리에서 바로 찾은 비율입니다. 높을수록 디스크를 덜 건드려 빠릅니다.

Cache Hit Ratio (%)

버퍼 캐시 적중률입니다. 산식은 100 * blksHit / (blksRead + blksHit), "디스크까지 안 가고 메모리에서 바로 찾은 비율" 입니다.

• 관측값: 평균 약 99.13%, 범위 99.02 ~ 99.22%. 거의 모든 읽기가 메모리에서 해결됩니다. 이 정도가 정상입니다.
• 위험 신호: 눈에 띄게 떨어지면(예: 95% 이하) 디스크를 자주 읽고 있다는 뜻이고 성능이 나빠집니다.
• 튀면(떨어지면) 의심할 것: shared_buffers 부족, 캐시에 없는 대량 데이터를 갑자기 조회(대형 풀 스캔), 콜드 스타트(재기동 직후 캐시가 비어 있음), 테이블 팽창으로 같은 데이터가 더 많은 블록에 흩어짐.

적중률을 떨어뜨리는 대표 원인은 "큰 테이블을 한 번에 풀 스캔" 해서 책상(캐시)을 그 데이터로 가득 채워 평소 쓰던 데이터를 밀어내는 경우입니다.

-- 나쁜 예: 인덱스 없이 거대한 테이블 전체를 끌어와 캐시를 오염시킨다
SELECT * FROM event_logs ORDER BY created_at;

-- 고친 예: 필요한 범위만, 인덱스로 좁혀서 읽는다
SELECT id, type, created_at
FROM   event_logs
WHERE  created_at >= TIMESTAMP '2026-06-18 00:00:00'
  AND  created_at <  TIMESTAMP '2026-06-19 00:00:00';

-- 현재 DB의 캐시 적중률 직접 계산
SELECT datname,
       ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_pct
FROM   pg_stat_database
WHERE  datname = current_database();

• 함께 확인할 지표: 적중률 하락과 함께 Blks Read·Read IOPS가 오르면 → 디스크 읽기가 실제로 늘어난 것입니다. Tup Returned도 같이 뛰면 → 대형 풀 스캔이 캐시를 오염시키는 중입니다. 재기동 직후 일시적으로 낮으면 → 콜드 스타트로, 데이터가 다시 채워지면 회복됩니다.

개발자 관점 — 자주 쓰지 않는 대용량 데이터를 한꺼번에 훑으면(예: 전체 테이블 집계) 캐시에 있던 자주 쓰는 데이터가 밀려나 적중률이 떨어집니다. 대량 처리는 운영 트래픽과 시간대를 분리하고, 꼭 필요한 범위만 인덱스로 좁혀 읽도록 짜는 것이 좋습니다.

Rows Hit Ratio (%)

쿼리 row 적중률입니다. 산식은 100 * tup_fetched / tup_returned, "훑은 행 중 실제로 가져다 쓴 행의 비율" 입니다. 캐시 적중률(블록 단위)과는 다른, 행 단위의 효율 지표입니다.

• 관측값: 평균 약 13.2%, 범위 0 ~ 50.9%. 낮게 깔려 있습니다. 이 값이 낮다는 것은 훑은 행에 비해 실제로 쓰는 행이 적다는 뜻으로, 순차 스캔 비중이 크다는 신호로 읽을 수 있습니다. 다만 워크로드 특성에 크게 좌우되므로 절대값보다 추세로 봅니다.
• 튀면(떨어지면) 의심할 것: 인덱스를 못 타고 많이 훑어 거르는 쿼리가 늘어남, 넓은 범위를 읽고 일부만 쓰는 패턴.
• 함께 확인할 지표: Rows Hit Ratio가 떨어지며 Tup Returned·Blks Read·CPU가 오르면 → 인덱스를 못 타는 풀 스캔이 늘어난 것입니다. 반대로 평탄하면 워크로드의 정상 특성으로 봅니다.

개발자 관점 — 이 비율이 낮다는 것은 "많이 읽고 적게 쓴다" 는 뜻이라, 인덱스로 필요한 행만 골라 읽도록 바꾸면 개선됩니다. 다만 워크로드에 따라 본래 낮을 수 있어, 절대값보다 갑자기 더 떨어졌는지를 봅니다.

Blks Read (blocks) / Blks Hit (건)

• Blks Read: 캐시에 없어 디스크에서 직접 읽은 블록 수(물리 읽기). 관측 평균 약 3,551, 범위 208 ~ 약 1,520만.
• Blks Hit: 캐시(메모리)에서 바로 찾은 블록 수(논리 읽기). 관측 평균 약 46,252, 범위 11,977 ~ 약 611만.

평소 Blks Hit가 Blks Read보다 훨씬 크기 때문에 적중률이 99%대로 나옵니다. Blks Read가 평소보다 크게 늘면 "캐시에 없는 데이터를 많이 읽고 있다" 는 신호입니다. Blks Read 최댓값 1,520만은 대형 풀 스캔이 디스크를 통째로 훑은 순간으로 보입니다.

-- 디스크 읽기(Blks Read)를 많이 유발하는 테이블 찾기
SELECT relname, heap_blks_read, heap_blks_hit,
       idx_blks_read, idx_blks_hit
FROM   pg_statio_user_tables
ORDER  BY heap_blks_read DESC
LIMIT  20;

• 함께 확인할 지표: Blks Read가 오르며 Cache Hit Ratio가 떨어지면 → 캐시 미스가 실제로 늘어난 것입니다(둘은 같은 현상의 앞뒷면). Read IOPS·Tup Returned가 동반이면 → 대량 풀 스캔이 디스크 읽기를 유발하는 중입니다.

개발자 관점 — Blks Read(디스크 읽기)가 늘었다는 것은 코드가 캐시에 없는 블록을 자주 요청한다는 뜻입니다. 작업 집합(자주 쓰는 데이터)이 캐시에 들어올 만큼 조회 범위를 좁히고, 한 번 쓰고 마는 대용량 스캔은 시간대를 분리하면 디스크 읽기가 줄어듭니다. 테이블 팽창도 같은 데이터를 더 많은 블록에 흩어 놓아 Blks Read를 늘리므로, VACUUM이 잘 도는지도 함께 봅니다.

Read IOPS Max (건/초) / Write IOPS Max (건/초)

초당 디스크 읽기/쓰기 I/O 연산 수의 최대치입니다. IOPS는 "초당 몇 번 디스크에 접근했나" 를 뜻합니다.

먼저 용어를 짧게 풀어 두겠습니다.

• IOPS(초당 입출력 횟수): 1초에 디스크를 몇 번 읽거나 썼는지, 그 횟수입니다. "서가를 1초에 몇 번 다녀왔나" 에 해당합니다.
• Throughput(처리량): 1초에 실제로 몇 바이트를 주고받았는지, 그 양입니다(뒤의 네트워크 지표에서 다룹니다). 횟수가 적어도 한 번에 큰 짐을 나르면 처리량은 클 수 있습니다.
• 관측값: Read IOPS Max 평균 약 520(범위 55 ~ 54,427), Write IOPS Max 평균 약 2,800(범위 612 ~ 102,877). MySQL과 달리 쓰기 IOPS가 평소에도 꽤 높은데, TPS가 높은(쓰기·커밋이 많은) 워크로드라 그렇습니다. 순간 최댓값이 읽기·쓰기 모두 수만까지 튑니다.
• 튀면 의심할 것: Read IOPS가 오르면 → 캐시 미스(물리 읽기 증가, Blks Read 동반). Write IOPS가 오르면 → 대량 쓰기·커밋·죽은 행 정리(VACUUM). 클라우드(RDS)에서는 IOPS에 한도가 있어, 한도에 부딪히면 전체가 느려집니다.
• 함께 확인할 지표: Read IOPS는 Blks Read·Cache Hit Ratio와 함께 봅니다 — 적중률이 떨어지며 Read IOPS가 오르면 캐시 미스입니다. Write IOPS는 Xact Commit·Tup Inserted/Updated/Deleted와 함께 보면 → 쓰기와 죽은 행 정리가 디스크로 내려가는 양을 가늠할 수 있습니다.

개발자 관점 — Read IOPS는 캐시에서 못 찾은 읽기가, Write IOPS는 변경·커밋·VACUUM이 디스크로 내려가는 양입니다. 매 건마다 커밋하는 코드는 디스크 쓰기(WAL flush)를 잘게 늘리므로 묶어서 커밋하면 부담이 줍니다. 클라우드 DB는 IOPS에 한도가 있어, 한도를 넘기는 패턴(잦은 단건 커밋·대량 풀 스캔·과도한 죽은 행 정리)은 코드와 운영 단계에서 줄여야 합니다.

---

5. Vacuum · MVCC (트랜잭션 ID)

이 영역이 PostgreSQL 지표 중 가장 PostgreSQL다운 부분입니다. 앞에서 풀어 둔 MVCC·Dead Tuple·VACUUM·트랜잭션 ID wraparound 개념이 여기서 지표로 나타납니다.

한 줄기로 정리하면 이렇습니다. UPDATE·DELETE가 죽은 행(dead tuple)을 만들고 → VACUUM(보통 autovacuum)이 그 죽은 행을 청소하며, 동시에 아주 오래된 행을 freeze(동결)해 트랜잭션 ID가 한 바퀴 도는 사고(wraparound)를 막습니다. 만약 변경량이 많은데 VACUUM이 못 따라가면, 죽은 행이 쌓여 테이블이 팽창하고, 가장 오래된 트랜잭션의 나이(Age)가 위험 수준으로 올라갑니다. 이 영역의 두 지표가 바로 그 "나이" 를 봅니다.

용어를 다시 한 번 짧게 정리하면:

• 트랜잭션 ID(XID): PostgreSQL이 모든 트랜잭션에 매기는 번호. 약 21억에서 한 바퀴 돌아 0으로 돌아오는 구조입니다(wraparound).
• Age(나이): 가장 오래된(freeze 안 된) 트랜잭션이 지금으로부터 몇 개의 트랜잭션만큼 떨어져 있는지입니다. 이 값이 wraparound 한계(약 21억)에 가까워지면 위험합니다.
• autovacuum_freeze_max_age: 이 나이에 도달하면 PostgreSQL이 강제로 freeze 작업을 시작하는 기준선입니다. Age Used Ratio가 이 기준 대비 비율을 봅니다.

Age Used Ratio (%)

autovacuum_freeze_max_age 기준으로 현재 나이가 얼마나 찼는지의 비율입니다. 트랜잭션 ID가 한 바퀴 돌기까지 얼마나 여유가 있는지를 한 숫자로 보여 줍니다. 사전 기준상 85% 이하를 권장합니다.

• 관측값: 평균 약 95.3%, 범위 81.7 ~ 100.0%. 권장선(85%)보다 높게 깔려 있고, 순간 100%를 넘긴 적도 있습니다. 이 인스턴스는 나이가 꾸준히 높게 유지되는 편이라, freeze가 임박한 상태를 자주 지난다는 뜻입니다. 다만 PostgreSQL은 이 기준에 도달하면 autovacuum이 강제로 freeze를 돌리도록 설계돼 있어, 100%에 닿아도 곧바로 장애는 아니고 "지금 freeze가 필요한 상태" 라는 신호로 읽습니다.
• 위험 신호: 사전 기준 85% 이하 권장. 이를 넘어 지속적으로 높으면 autovacuum이 freeze를 못 따라가는 것일 수 있어 들여다봐야 합니다. 진짜 위험은 이 나이가 wraparound 한계까지 계속 올라가 PostgreSQL이 새 쓰기를 거부하고 멈추는 것입니다.
• 튀면 의심할 것: 오래 살아 있는 트랜잭션(idle in transaction)이 freeze를 막음, 대량 변경에 비해 autovacuum이 너무 느리게 설정됨, 특정 테이블이 매우 커서 freeze가 오래 걸림.

-- 데이터베이스별 나이 — wraparound까지의 여유를 본다
SELECT datname, age(datfrozenxid) AS xid_age
FROM   pg_database
ORDER  BY xid_age DESC;

-- 나이가 큰(freeze가 시급한) 테이블 찾기
SELECT relname, age(relfrozenxid) AS xid_age, last_autovacuum
FROM   pg_class c
JOIN   pg_stat_user_tables s ON s.relid = c.oid
WHERE  c.relkind = 'r'
ORDER  BY xid_age DESC
LIMIT  20;

• 함께 확인할 지표: Current Age가 같이 오르면 → 같은 현상(나이 상승)을 다른 각도로 본 것입니다. Trx Time(긴 트랜잭션)이 같이 크면 → 오래 살아 있는 트랜잭션이 freeze를 막는 원인일 수 있습니다. Tup Updated·Tup Deleted가 많은데 last_autovacuum이 오래됐으면 → autovacuum이 못 따라가는 것입니다.

개발자 관점 — Age는 직접 코드로 줄이는 값은 아니지만, 코드가 긴 트랜잭션을 만들면 freeze를 막아 나이를 올립니다. 트랜잭션을 연 채 노는 idle in transaction을 없애고(작업 끝나면 즉시 커밋/롤백), 트랜잭션을 짧게 가져가는 것이 가장 큰 기여입니다. 변경이 많은 테이블은 autovacuum 설정을 더 공격적으로 잡는 운영 조정도 함께 필요합니다.

실무 시나리오 — 어떤 backend가 트랜잭션을 연 채 idle in transaction으로 몇 시간 방치됐다고 해보겠습니다. 그동안 PostgreSQL은 그 트랜잭션이 옛 버전을 볼 수 있다고 보고 freeze와 죽은 행 정리를 미루며, Age Used Ratio가 계속 올라갑니다. 방치된 트랜잭션을 찾아 정리하면 autovacuum이 다시 진행되며 나이가 내려갑니다. 그래서 긴 idle in transaction 세션은 PostgreSQL에서 특히 위험 신호로 봅니다.

Current Age (age)

현재 DB에서 사용 중인 가장 오래된 트랜잭션 ID의 나이입니다. Age Used Ratio가 비율(%)이라면, 이쪽은 나이의 절대값입니다.

• 관측값: 평균 약 1.91억(age), 범위 약 1.63억 ~ 2.0억. wraparound 한계(약 21억)에 비하면 여유가 있지만, autovacuum_freeze_max_age(기본 2억) 기준으로 보면 거의 그 근처에서 움직이고 있습니다. 그래서 Age Used Ratio가 95%대로 높게 나오는 것입니다.
• 튀면 의심할 것: Age Used Ratio와 동일 — 긴 트랜잭션, autovacuum 지연, 거대한 테이블의 freeze 지연.
• 함께 확인할 지표: Age Used Ratio와 같은 방향으로 움직이는 것이 정상입니다(같은 현상의 절대값/비율 표현). 둘 다 꾸준히 우상향하면 freeze가 못 따라가는 것이므로 autovacuum 동작과 긴 트랜잭션을 점검합니다.

개발자 관점 — Current Age가 꾸준히 우상향한다면, 변경량 대비 freeze가 느리다는 뜻입니다. 코드 쪽에서는 긴 트랜잭션을 없애고, 운영 쪽에서는 autovacuum의 freeze 동작이 제때 돌도록 설정을 조정합니다. 이 지표는 PostgreSQL 고유의 "방치하면 DB가 멈출 수 있는" 항목이라, 절대값이 wraparound 한계로 향하는 추세를 특히 주의 깊게 봅니다.

---

6. 락 · 동시성

같은 데이터를 동시에 바꾸려 할 때 락(Lock) 으로 순서를 정합니다. 화장실 한 칸을 한 사람씩 쓰는 것과 같아서, 한 사람이 너무 오래 쓰면 뒤에 줄이 길어집니다.

이 영역의 개념을 먼저 짧게 풀어 두겠습니다. (아래 지표는 이 개념들을 전제로 합니다.)

• 트랜잭션(Transaction): 여러 DB 작업을 "전부 성공 아니면 전부 취소" 로 묶은 한 묶음입니다. 다 잘 됐으면 COMMIT, 문제가 생기면 ROLLBACK 합니다.
• 행 락(Row Lock): PostgreSQL에서 어떤 행을 고치기 시작하면, 커밋할 때까지 그 행에 락을 걸어 둡니다. 다른 트랜잭션이 같은 행을 고치려면 그 락이 풀릴 때까지 기다립니다. 테이블 전체가 아니라 고치는 그 행만 잠그므로, 같은 테이블이라도 다른 행은 동시에 작업할 수 있습니다.
• 교착(Deadlock): 두 트랜잭션이 서로 상대가 쥔 락을 기다리며 영원히 못 풀리는 상태입니다. A가 1번 행을 쥐고 2번 행을 기다리는데, B가 2번 행을 쥐고 1번 행을 기다리면 둘 다 멈춥니다. PostgreSQL은 이를 감지하면 한쪽 트랜잭션을 강제로 롤백시켜 풉니다.

Locks Waiting (개)

지금 락을 얻으려고 대기 중인 전체 backend 수입니다. (앞의 2번 세션 섹션에도 나왔지만, 락 관점에서 다시 봅니다.) 락 경합을 직접 보여 주는 지표입니다.

• 관측값: 평균 약 0.003, 범위 0 ~ 5. 0이 정상이고, 0이 아니면 지금 누군가 다른 트랜잭션 때문에 막혀 있는 것입니다.
• 위험 신호: 경고 1, 위험 2. 작은 값이라도 반복되면 특정 쿼리·트랜잭션이 자주 충돌하고 있다는 뜻입니다.
• 튀면 의심할 것: 한 트랜잭션이 행을 쥔 채 커밋을 미룸(특히 idle in transaction), 같은 행에 대한 동시 수정 경합.

락 대기는 거의 "트랜잭션을 열어놓고 커밋을 미루는" 코드에서 생깁니다.

-- 나쁜 예: 행을 수정한 뒤 외부 작업(API 호출·파일 처리 등)을 하느라 커밋이 늦어진다
-- 그동안 같은 행(id=1)을 건드리려는 다른 트랜잭션은 전부 대기한다
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- ... 여기서 외부 API 호출 등으로 수 초~수십 초 지연 ...
COMMIT;   -- 너무 늦게 커밋 → 그 사이 account_id=1을 노리는 작업들이 락 대기

-- 고친 예: DB 변경 구간만 빠르게 묶어 곧바로 커밋한다 (외부 작업은 트랜잭션 밖에서)
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
COMMIT;   -- 즉시 커밋 → 락 점유 시간이 짧아 대기가 거의 없다

-- 지금 락 경합이 있는지 — 막은 backend와 막힌 backend 찾기
SELECT blocked.pid          AS blocked_pid,
       blocked.query        AS blocked_query,
       blocking.pid         AS blocking_pid,
       blocking.query       AS blocking_query
FROM   pg_stat_activity blocked
JOIN   LATERAL unnest(pg_blocking_pids(blocked.pid)) AS bp(pid) ON true
JOIN   pg_stat_activity blocking ON blocking.pid = bp.pid
WHERE  cardinality(pg_blocking_pids(blocked.pid)) > 0;

• 함께 확인할 지표: Active Backend가 같이 쌓이면 → 대기 세션이 부하로 번지는 중입니다. Deadlocks가 같이 오르면 → 경합이 교착으로 번진 것입니다. Trx Time(긴 트랜잭션)이 크면 → 그 긴 트랜잭션이 락을 쥐고 있는 원인일 수 있습니다.

개발자 관점 — 트랜잭션 범위 안에 외부 API 호출이나 긴 로직을 넣으면 그동안 행 락을 쥐고 있어 같은 행을 건드리는 다른 요청이 대기합니다. 트랜잭션은 짧게 가져가고, 외부 호출이나 무거운 처리는 트랜잭션 밖으로 빼야 합니다. 여러 트랜잭션이 같은 행을 자주 갱신하는 설계 자체도 경합을 늘리는 원인입니다.

실무 시나리오 — 한 트랜잭션이 어떤 행을 수정한 뒤 커밋하지 않고 외부 결제 API 응답을 기다린다고 해보겠습니다. 그 응답이 늦어지는 동안 같은 행(예: 같은 상품 재고)을 수정하려는 요청들이 줄줄이 대기하면서 Locks Waiting이 오르고, 대기하는 만큼 Active Backend가 쌓여 CPU와 응답시간까지 동반 악화됩니다. 위 pg_blocking_pids 쿼리로 막은 backend를 찾아 정리하면 한꺼번에 풀립니다. 근본 해결은 외부 호출을 트랜잭션 밖으로 빼고 커밋을 미루지 않는 것입니다.

Deadlocks (건)

교착(deadlock)이 발생한 건수입니다(1분 구간 합). 두 트랜잭션이 서로의 락을 기다려 못 풀리는 상태가 감지되어 한쪽이 강제 롤백된 횟수입니다.

• 관측값: 평균 거의 0(범위 0 ~ 1). 한 달간 거의 발생하지 않았습니다. 0이 정상입니다.
• 위험 신호: 1건만 나타나도 들여다볼 가치가 있습니다. 반복되면 특정 트랜잭션 조합이 자주 충돌하고 있다는 뜻입니다.
• 튀면 의심할 것: 여러 트랜잭션이 여러 행·테이블을 서로 다른 순서로 잠그는 코드. 락 획득 순서가 엇갈리면 교착이 생깁니다.

교착은 락을 잡는 순서를 코드 전반에서 일정하게 맞추면 크게 줄어듭니다.

-- 나쁜 예: 한 트랜잭션은 A→B 순서로, 다른 트랜잭션은 B→A 순서로 잠근다 → 교착 위험
-- 트랜잭션 1: UPDATE account A; ... UPDATE account B;
-- 트랜잭션 2: UPDATE account B; ... UPDATE account A;

-- 고친 예: 항상 같은 순서(예: id 오름차순)로 잠그도록 통일한다
-- 두 트랜잭션 모두: 작은 id부터 큰 id 순서로 UPDATE
UPDATE accounts SET balance = balance - 100
WHERE account_id IN (1, 2)
ORDER BY account_id;   -- 락 획득 순서를 통일

-- 교착 누적 건수 확인
SELECT datname, deadlocks
FROM   pg_stat_database
WHERE  datname = current_database();

• 함께 확인할 지표: Xact Rollback이 같이 오르면 → 교착으로 트랜잭션이 강제 롤백되는 것입니다. Locks Waiting이 함께 오르면 → 락 경합이 심해진 끝에 교착까지 번진 상황입니다.

개발자 관점 — 교착은 거의 항상 "락 획득 순서가 코드마다 다른" 데서 생깁니다. 여러 행·여러 테이블을 한 트랜잭션에서 건드린다면, 항상 같은 순서(예: 기본키 오름차순)로 접근하도록 코드 전반에서 규칙을 통일하면 교착 가능성이 크게 줍니다. PostgreSQL은 교착을 감지하면 한쪽을 롤백시켜 풀어 주므로, 애플리케이션은 교착 예외를 잡아 재시도하는 처리를 함께 두는 것이 좋습니다.

---

7. 네트워크 · 스토리지 · Temp

DB가 결과를 주고받는 네트워크 트래픽, 데이터를 담는 파일 시스템 사용량, 그리고 메모리에서 못 끝낸 정렬·해시가 디스크로 흘러간 임시 파일을 봅니다. 버퍼 캐시가 책상이라면, 여기는 손님에게 들고 나간 짐(네트워크)의 양과 창고 사용량(파일 시스템), 그리고 책상이 모자라 바닥에 임시로 펼친 작업물(temp)입니다.

Network Transmit Throughput (Bytes/초) / Network Receive Throughput (Bytes/초)

DB 인스턴스가 주고받는 네트워크 트래픽입니다. Transmit(송신)은 DB가 결과를 내보내는 양, Receive(수신)는 DB로 들어오는 쿼리·데이터의 양입니다.

• 관측값: 송신 평균 약 967KB/s(범위 227KB ~ 약 2.4MB/s), 수신 평균 약 795KB/s(범위 235KB ~ 약 2.2MB/s). 평탄하게 유지됩니다.
• 튀면 의심할 것: 대량 결과를 반환하는 쿼리(SELECT *로 큰 테이블 전체 반환 등), 대량 적재, 복제 트래픽.

-- 나쁜 예: 페이징 없이 큰 테이블 전체를 모든 컬럼으로 반환 → 송신 트래픽 폭증
SELECT * FROM products;

-- 고친 예: 필요한 컬럼만, 페이징으로 잘라서 반환
SELECT product_id, name, price
FROM   products
ORDER  BY product_id
LIMIT  100 OFFSET 0;

• 함께 확인할 지표: 송신 트래픽 급증과 Tup Returned·Tup Fetched가 동반이면 → 큰 결과셋을 퍼내는 쿼리입니다. 수신 트래픽과 Tup Inserted·Write IOPS가 동반이면 → 대량 적재가 들어오는 중입니다. 둘 다 평탄한데 한쪽만 튀면 복제 트래픽 등 특정 경로를 의심합니다.

개발자 관점 — 페이징 없이 큰 결과를 통째로 반환하거나 SELECT *로 불필요한 컬럼까지 퍼내면 송신 트래픽이 커집니다. 필요한 컬럼만, 페이지 단위로 잘라 반환하면 송신량이 줄고 응답도 빨라집니다. 대량 적재 쪽은 한 건씩 넣는 대신 COPY나 묶음 INSERT로 보내면 수신 트래픽과 왕복 횟수를 함께 줄일 수 있습니다.

Filesystem Usage (Bytes)

파일 시스템 사용량입니다. 인스턴스의 디스크가 실제로 얼마나 차 있는지를 바이트로 봅니다.

• 관측값: 평균 약 226GB, 범위 약 209 ~ 245GB. 완만하게 움직입니다.
• 위험 신호: 절대 임계는 비활성이지만, 디스크가 가득 차면(특히 데이터·WAL 영역) 쓰기 자체가 멈춰 장애로 직결됩니다. 그래서 꾸준히 우상향하는 추세를 봅니다.
• 튀면(늘면) 의심할 것: 대량 적재로 데이터 증가, 정리되지 않고 쌓이는 WAL, 테이블 팽창(죽은 행이 VACUUM으로 회수되지 않아 공간을 계속 차지), temp 영역을 크게 쓰는 정렬.

PostgreSQL에서 파일 시스템이 예상보다 빨리 차는 흔한 원인 하나가 테이블 팽창입니다. 죽은 행이 회수되지 않으면 실제 데이터보다 훨씬 큰 공간을 차지합니다.

-- 테이블별 디스크 점유 크기와 죽은 행 수를 함께 본다 (팽창 점검)
SELECT relname,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
       n_live_tup, n_dead_tup, last_autovacuum
FROM   pg_stat_user_tables
ORDER  BY pg_total_relation_size(relid) DESC
LIMIT  20;

• 함께 확인할 지표: Tup Updated·Tup Deleted가 많은데 last_autovacuum이 오래됐으면 → 죽은 행 누적으로 팽창이 공간을 먹는 중입니다. Temp Bytes가 크면 → 정렬 임시 파일이 공간을 쓰는 중입니다. WAL이 쌓이면 별도 경로로 함께 봅니다.

개발자 관점 — 스토리지는 한 번 차면 쓰기가 멈춰 가장 치명적입니다. PostgreSQL에서는 일반적인 데이터 증가 외에 테이블 팽창이 공간을 갉아먹으므로, 변경이 많은 테이블의 VACUUM이 잘 도는지 함께 봐야 합니다. 오래된 데이터는 주기적으로 정리·아카이브하고, 대량 적재·삭제는 한 번에 몰지 말고 나눠서 처리합니다.

실무 시나리오 — 파일 시스템 사용량이 평소엔 완만하다가 어느 날부터 매일 일정량씩 빠르게 느는 우하향(여유 기준) 추세가 그려진다면, 정리되지 않는 WAL이나 멈추지 않는 적재 배치, 혹은 autovacuum이 못 따라가는 테이블 팽창을 의심합니다. 추세대로면 며칠 뒤 공간이 바닥나 쓰기가 멈추므로, 절대값이 위험선에 닿기 전에 추세를 보고 미리 손쓰는 것이 핵심입니다.

Temp Bytes (Bytes) / Temp Files (건)

• Temp Bytes: 임시 파일(temporary file)에 쓰인 데이터 양입니다(1분 구간 합).
• Temp Files: 쿼리로 생성된 전체 임시 파일 수입니다(1분 구간 합).

임시 파일은 정렬·해시·그룹핑 작업이 backend의 작업 메모리(work_mem) 안에서 못 끝나고 디스크로 넘쳐 났을 때 만들어집니다. 즉 이 지표가 0이 아니면 "메모리에서 끝나지 못한 무거운 정렬·해시가 있었다" 는 신호입니다.

• 관측값: Temp Bytes 평균 약 131KB(범위 0 ~ 약 1.07GB), Temp Files 평균 거의 0(범위 0 ~ 1). 평소엔 거의 0이다가 어떤 순간 1GB가 넘는 임시 파일이 생긴 적이 있습니다. 이 순간이 큰 정렬·해시가 디스크로 넘친 시점입니다.
• 튀면 의심할 것: 인덱스 없는 대량 ORDER BY, 큰 해시 조인, 대량 GROUP BY·DISTINCT, work_mem 부족.

큰 정렬이 메모리를 넘으면 디스크 임시 파일이 생깁니다. 인덱스로 정렬을 대신하게 하거나 결과를 좁히면 줄어듭니다.

-- 나쁜 예: 인덱스 없는 컬럼으로 대량 정렬 → work_mem을 넘겨 디스크 temp 발생
SELECT * FROM logs ORDER BY message;

-- 고친 예: 정렬 기준 컬럼에 인덱스를 두고, 필요한 범위·컬럼만 좁혀 읽는다
-- (인덱스가 정렬 순서를 제공하면 디스크 정렬이 사라진다)
SELECT id, level, created_at
FROM   logs
WHERE  created_at >= TIMESTAMP '2026-06-18 00:00:00'
ORDER  BY created_at        -- created_at 인덱스가 정렬을 대신
LIMIT  100;

-- 어떤 쿼리가 디스크 정렬을 하는지 — 실행계획에서 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM logs ORDER BY message;
-- 결과에 'Sort Method: external merge  Disk: ...kB' 가 보이면 디스크 정렬(temp 발생)

• 함께 확인할 지표: Temp Bytes·Temp Files가 오르며 Blks Read·Read IOPS·Write IOPS가 동반이면 → 큰 정렬·해시가 디스크를 오가는 중입니다. CPU도 같이 오르면 무거운 정렬 연산이 부하로 잡힌 것입니다. Filesystem Usage가 일시적으로 오르면 → 큰 임시 파일이 공간을 차지한 것입니다.

개발자 관점 — 임시 파일은 정렬·해시·그룹핑이 work_mem을 넘쳐 디스크로 흘렀다는 신호입니다. 인덱스로 정렬을 대신하게 하고(ORDER BY 컬럼에 인덱스), 결과 크기를 페이지 단위로 줄이며, 필요한 컬럼만 읽으면 디스크 임시 파일이 줄어듭니다. 같은 무거운 집계가 반복된다면 미리 계산한 집계 테이블이나 구체화 뷰(materialized view)를 두는 것도 방법입니다.

실무 시나리오 — 평소 Temp Files가 0이던 인스턴스에서 특정 시간대에만 1GB가 넘는 Temp Bytes가 찍힌다고 해보겠습니다. 그 시간에 도는 리포트 쿼리가 인덱스 없이 큰 테이블을 정렬·집계하면서 work_mem을 넘겨 디스크 정렬을 한 것입니다. EXPLAIN으로 보면 Sort Method: external merge Disk가 나옵니다. 정렬 기준 컬럼에 인덱스를 추가하거나 쿼리를 다시 짜서 디스크 정렬을 없애면 Temp Bytes가 0으로 돌아오고 그 쿼리도 빨라집니다.

---

8. 기타

(참고) Postgresql Slow Query Monitor (ms)

PostgreSQL Slow Query를 모니터링하는 지표인데, 환경에 따라 1분 단위 메트릭으로 들어오지 않는 경우가 있어(이 인스턴스에서는 미제공) 들어올 때만 참고합니다. 느린 쿼리 자체는 앞에서 본 Long Active Backend·Active Backend·Blks Read 같은 지표와 pg_stat_activity로 충분히 추적할 수 있고, 운영 환경에서는 pg_stat_statements 확장을 켜 두면 어떤 쿼리가 누적으로 가장 무거운지 정확히 볼 수 있습니다.

-- (운영 환경에서 pg_stat_statements가 켜져 있다면) 누적 수행시간이 큰 쿼리 찾기
SELECT query,
       calls,
       ROUND(total_exec_time::numeric, 1) AS total_ms,
       ROUND(mean_exec_time::numeric, 2)  AS mean_ms
FROM   pg_stat_statements
ORDER  BY total_exec_time DESC
LIMIT  20;

개발자 관점 — 별도의 slow query 지표가 없어도, "느린 쿼리" 는 결국 인덱스를 못 타거나 결과를 너무 많이 다루는 쿼리입니다. pg_stat_statements로 평균 수행시간이 긴 쿼리를 찾아 EXPLAIN (ANALYZE, BUFFERS)로 실행계획을 보고 인덱스·결과 크기를 손보는 흐름은 앞의 다른 지표들과 같습니다.

---

상황별로 보기 — 배치·백업이 돌 때 어떤 지표가 움직이나

지금까지는 지표를 하나씩 봤지만, 실제로는 하나의 작업이 여러 지표를 동시에 움직입니다. 특히 정기 배치나 백업처럼 정해진 시간에 도는 작업은 매번 비슷한 지표 묶음을 끌어올립니다. PostgreSQL은 변경 작업이 죽은 행과 VACUUM으로 이어지는 특성이 있어서, 배치의 종류를 지표 패턴으로 구분하기 좋습니다.

배치 유형별 예상 지표 변화

배치 유형	함께 오르는 지표	이유
대량 UPDATE/DELETE 배치	Tup Updated·Tup Deleted, (직후) n_dead_tup·autovacuum으로 CPU·Write IOPS, Locks Waiting, Filesystem Usage	변경할 때마다 죽은 행이 쌓이고, 이후 autovacuum이 청소하며, 정리가 늦으면 테이블이 팽창함
대량 적재 (INSERT)	Tup Inserted, Xact Commit·TPS, Write IOPS, Network 수신, Filesystem Usage	행을 대량으로 쓰면 커밋·WAL flush·디스크 쓰기가 늘고 데이터가 증가함
집계·리포트 (대량 SELECT)	Tup Returned, Blks Read, Read IOPS, Temp Bytes·Temp Files, CPU / Cache Hit Ratio는 하락	큰 테이블을 훑어 집계·정렬하므로 읽은 블록·디스크 읽기·임시 파일이 급증하고 캐시가 밀림
백업	Read IOPS, Network, CPU(약간)	데이터 전체를 읽어 내보내므로 디스크 읽기·네트워크가 오름
긴 트랜잭션 방치 (idle in transaction)	Trx Time, Age Used Ratio·Current Age, Locks Waiting	트랜잭션이 오래 살아 VACUUM·freeze를 막아 나이가 오르고, 락도 오래 쥠

예를 들면

매일 새벽 통계 배치가 큰 테이블을 풀 스캔해 집계·정렬한다고 해보겠습니다. 그 시간에는 Tup Returned가 수천만~수억으로 뛰고, Blks Read와 Read IOPS가 따라 오르며, 정렬이 work_mem을 넘겨 Temp Bytes·Temp Files가 생기고, 그 데이터가 캐시를 밀어내 Cache Hit Ratio가 잠깐 떨어집니다. CPU도 집계·정렬 연산으로 같이 오릅니다. 이 묶음이 같은 시각에 함께 움직이는 것이 이 배치의 지문 같은 패턴입니다.

월말 대량 UPDATE/DELETE 배치라면 반대로 Tup Updated·Tup Deleted가 같이 오릅니다. 그 직후 죽은 행(n_dead_tup)이 쌓이고 autovacuum이 돌기 시작하면서 CPU와 Write IOPS가 한 번 더 오릅니다. 만약 이때 변경을 하나의 큰 트랜잭션으로 묶어 오래 끌면, 그 트랜잭션이 VACUUM을 막아 죽은 행이 정리되지 않고 테이블이 팽창하며 Age Used Ratio까지 오를 수 있습니다. 그래서 대량 작업은 너무 크지도 너무 잘지도 않은 묶음(수천~수만 건)으로 나눠 커밋하는 것이 균형점입니다.

핵심 — "언제" 올랐는지가 중요하다

여기서 중요한 점은, 이 지표들이 배치 시간대에 함께 오르는 것은 정상이라는 것입니다. 정해진 작업이 정해진 시간에 자원을 쓰는 것이니까요. 더하여 PostgreSQL에서는 대량 변경 배치 직후에 autovacuum이 따라 도는 것 또한 정상적인 리듬입니다(죽은 행을 치워 주는 고마운 동작입니다). 문제가 되는 경우는 다음과 같습니다.

• 배치가 없는 시간대에 같은 패턴이 나타날 때 — 누군가 수동으로 무거운 쿼리를 돌렸거나, 의도치 않은 작업이 돈다는 신호입니다.
• 배치 시간대인데 평소보다 훨씬 크게, 오래 튈 때 — 데이터가 그만큼 늘었거나, 쿼리가 비효율적으로 바뀌었을 수 있습니다.
• 변경 배치 뒤에 죽은 행이 줄지 않고 Age가 계속 오를 때 — autovacuum이 못 따라가거나 긴 트랜잭션이 정리를 막는 것입니다(PostgreSQL 고유의 점검 포인트).

그래서 모니터링에서는 절대값만큼 "평소 이 시간대와 비교해서 어떤가" 가 중요합니다. 매일 같은 시각에 같은 모양으로 오르내리는 그래프라면 그건 배치의 정상 리듬이고, 그 리듬에서 벗어나는 순간이 들여다볼 지점입니다.

---

정리하며

PostgreSQL 지표는 MySQL·Oracle과 공통된 부분(CPU·메모리·세션·읽기·락)이 많으면서도, MVCC·Dead Tuple·VACUUM·트랜잭션 ID(Age) 라는 그 DB만의 줄기가 분명했습니다. 핵심 개념을 먼저 잡아 두니, 지표들이 그 개념들의 동작으로 자연스럽게 이어졌습니다.

PostgreSQL에서 기억해 둘 핵심은 다음과 같습니다.

• "연결(Numbackends)" 과 "활성(Active Backend)" 은 다르다. 위 인스턴스도 연결은 약 837인데 활성은 1 안팎이었다. 부하는 활성으로 본다. PostgreSQL은 연결마다 프로세스를 띄우므로 커넥션 풀·PgBouncer가 특히 중요하다.
• UPDATE/DELETE는 죽은 행을 남긴다. Tup Updated·Tup Deleted가 크면 죽은 행이 쌓이고, VACUUM(autovacuum)이 청소한다. 대량 변경은 나눠서 커밋해 죽은 행이 한꺼번에 쏟아지지 않게 한다.
• Age(트랜잭션 ID 나이)는 PostgreSQL 고유의 위험 지표다. Age Used Ratio가 권장선(85%) 위로 꾸준히 높거나 Current Age가 wraparound 한계로 향하면 freeze가 못 따라가는 것이다. 긴 idle in transaction 트랜잭션이 가장 흔한 원인이다.
• 캐시 적중률(Cache Hit Ratio)은 99% 후반이 정상이고, 떨어지면 디스크를 읽고 있다는 신호다. Blks Read의 순간 최댓값이 크면 풀 스캔을 의심한다.
• Temp Bytes·Temp Files가 0이 아니면 정렬·해시가 메모리를 넘어 디스크로 흘렀다는 뜻이다. 인덱스로 정렬을 대신하게 한다.
• 대부분의 지표는 혼자 튀지 않는다. CPU·Active Backend·Blks Read·Tup·IOPS가 같은 시각에 함께 움직이는지를 보면 원인의 윤곽이 잡힌다. 그래서 각 지표마다 "함께 확인할 지표" 를 같이 적어 둔 것이다.

이로써 MySQL·Oracle·PostgreSQL 세 데이터베이스를 같은 틀로 정리해 봤습니다. 세 글을 나란히 놓고 보면, 같은 "데이터베이스" 라도 무엇을 가장 신경 써서 보는지가 조금씩 다르다는 것이 분명해집니다.

• MySQL은 스토리지 엔진인 InnoDB에서 색깔이 나옵니다. 버퍼풀 적중률, 행 락 대기, History list length(undo) 같은 지표가 InnoDB의 동작에서 비롯됩니다.
• Oracle은 Redo·Undo·Library Cache·파싱이 중심입니다. 변경량은 Redo·Undo로, 동시성·읽기 일관성은 Undo로, SQL 효율은 파싱(바인드 변수)으로 드러납니다.
• PostgreSQL은 MVCC와 그 청소(VACUUM) 가 중심입니다. 변경이 죽은 행을 남기고, 그것을 VACUUM이 치우며, 트랜잭션 ID의 나이(Age)를 관리하는 일이 이 DB의 고유한 점검 포인트입니다.

세 DB 모두 결국은 "변경 전 데이터를 어떻게 관리하고 정리하느냐" 라는 같은 문제를 각자 다른 방식으로 풉니다. Oracle은 Undo라는 별도 공간에, PostgreSQL은 테이블 안에 옛 버전을 남겼다가 VACUUM으로 치우고, MySQL의 InnoDB는 undo 로그(History list)로 관리합니다. 이 차이를 알고 보면, 같은 모니터링 화면이라도 어느 지표를 먼저 봐야 하는지가 DB마다 자연스럽게 달라집니다. 세 편에 걸쳐 정리한 내용이, 어떤 DB의 지표를 보든 "무엇을 의미하고 / 평소 어느 정도이고 / 튀면 뭘 의심하고 / 어떤 쿼리로 확인하는지" 라는 같은 틀로 접근하는 데 도움이 되면 좋겠습니다.

---