들어가며

PostgreSQL 모니터링 지표를 알아도, 실제 장애 상황에서는 “어디서부터 봐야 하지?”라는 막막함이 있습니다. 이 글에서는 실무에서 자주 마주치는 문제 상황별 트러블슈팅 방법을 정리합니다. AWS Aurora PostgreSQL 환경도 함께 다룹니다.

실무에서 자주 마주치는 상황들

“갑자기 쿼리가 느려졌어요”

가장 흔한 문의입니다. 원인은 다양하지만 대부분 아래 순서로 확인합니다:

1. 활성 쿼리 확인 - 장시간 실행 중인 쿼리가 있는지

   SELECT pid, now() - query_start as duration, state, left(query, 80)
   FROM pg_stat_activity
   WHERE state != 'idle'
   ORDER BY duration DESC LIMIT 10;

2. 락 경합 확인 - 다른 쿼리를 블로킹하는 트랜잭션이 있는지

   SELECT blocked_locks.pid AS blocked_pid,
          blocking_locks.pid AS blocking_pid,
          blocked_activity.query AS blocked_query
   FROM pg_locks blocked_locks
   JOIN pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
       AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
       AND blocking_locks.pid != blocked_locks.pid
   JOIN pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
   WHERE NOT blocked_locks.granted;

3. 테이블 bloat 확인 - dead tuple이 과도하게 쌓였는지

4. 실행 계획 변경 - 통계 정보가 오래되어 플래너가 잘못된 계획을 선택했는지

“커넥션이 자꾸 끊겨요” / “커넥션을 못 맺어요”

애플리케이션에서 DB 커넥션 에러가 발생할 때:

1. 현재 커넥션 수 vs max_connections

   SELECT count(*) as current_connections,
          (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') as max_conn
   FROM pg_stat_activity;

2. 커넥션 상태 분포 - idle in transaction 상태가 많으면 문제

   SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

   출력 예시와 해석:

   state                    | count
   -------------------------+-------
   (null)                   | 19      -- 백그라운드 프로세스 (autovacuum, WAL writer 등)
   active                   | 1       -- 현재 쿼리 실행 중
   idle in transaction      | 2       -- ⚠️ 트랜잭션 열어둔 채 대기 중
   idle                     | 670     -- 대기 중 (정상)

   각 상태의 의미:

   • (null) 또는 빈 값: PostgreSQL 내부 백그라운드 프로세스 (autovacuum launcher, background writer, WAL writer, checkpointer 등). 정상입니다.
   • active: 쿼리 실행 중. 너무 많으면 부하 확인 필요
   • idle: 커넥션은 맺어져 있지만 아무 작업 안 함. 커넥션 풀에서 대기 중인 상태
   • idle in transaction: 주의 필요! BEGIN 후 COMMIT/ROLLBACK 안 한 상태. 락을 잡고 있을 수 있음
   • idle in transaction (aborted): 트랜잭션 내에서 에러 발생 후 롤백 대기 중

3. idle in transaction 문제 해결

   -- idle in transaction 상태인 커넥션 상세 확인
   SELECT pid, now() - xact_start as transaction_duration,
          now() - state_change as idle_duration,
          application_name, client_addr,
          left(query, 80) as last_query
   FROM pg_stat_activity
   WHERE state = 'idle in transaction'
   ORDER BY xact_start;

   원인과 해결책:

   • 애플리케이션 버그: 트랜잭션 시작 후 commit/rollback 누락 → 코드 수정
   • 커넥션 풀 설정: 트랜잭션 중인 커넥션을 풀에 반환 → 풀 설정 확인
   • 긴 작업 중 대기: 외부 API 호출 등으로 트랜잭션 중 블로킹 → 트랜잭션 밖으로 이동

   긴급 조치 (오래된 idle in transaction 강제 종료):

   -- 10분 이상 idle in transaction 상태인 커넥션 종료
   SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE state = 'idle in transaction'
     AND now() - state_change > interval '10 minutes';

   예방 설정:

   -- idle in transaction 자동 타임아웃 (PostgreSQL 9.6+)
   ALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';
   SELECT pg_reload_conf();

4. 오래된 idle 커넥션 - 커넥션 풀 설정 문제일 수 있음

   SELECT pid, now() - state_change as idle_time, application_name
   FROM pg_stat_activity
   WHERE state = 'idle'
   ORDER BY idle_time DESC LIMIT 10;

실무 팁: PgBouncer 같은 커넥션 풀러를 쓰는 경우, PgBouncer 로그와 PostgreSQL 로그를 함께 확인해야 합니다.

“디스크 용량이 갑자기 늘었어요”

데이터가 늘어난 건지, bloat 때문인지 구분해야 합니다:

1. 테이블별 실제 크기 vs 예상 크기

   SELECT schemaname, relname,
          pg_size_pretty(pg_total_relation_size(relid)) as total_size,
          n_live_tup,
          n_dead_tup,
          round(n_dead_tup::numeric / nullif(n_live_tup, 0) * 100, 2) as dead_ratio
   FROM pg_stat_user_tables
   ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;

2. WAL 파일 누적 - 아카이브가 밀리거나 슬롯이 막혀있을 때

   SELECT slot_name, active, restart_lsn,
          pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) as lag_bytes
   FROM pg_replication_slots;

3. 임시 파일 - 큰 정렬이나 해시 조인 시 work_mem 부족

“Replica가 뒤처져요”

읽기 분산을 위한 Replica에서 최신 데이터가 안 보일 때:

1. 복제 지연 확인 (Primary에서)

   SELECT client_addr, state,
          pg_wal_lsn_diff(sent_lsn, replay_lsn) as lag_bytes,
          replay_lag
   FROM pg_stat_replication;

2. Replica에서 장시간 쿼리 확인 - 긴 쿼리가 WAL replay를 막을 수 있음

   -- Replica에서 실행
   SELECT pid, now() - query_start as duration, query
   FROM pg_stat_activity
   WHERE state = 'active'
   ORDER BY duration DESC;

3. hot_standby_feedback 설정 확인 - Primary의 vacuum을 막고 있을 수 있음

“배포 후 DB가 느려졌어요”

새 코드 배포 후 성능 저하가 발생할 때:

1. 새로 실행되는 쿼리 패턴 확인

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

2. 인덱스 미사용 쿼리 - 새 쿼리가 적절한 인덱스를 타지 않을 수 있음

   SELECT relname, seq_scan, idx_scan,
          seq_tup_read, idx_tup_fetch
   FROM pg_stat_user_tables
   WHERE seq_scan > 0
   ORDER BY seq_tup_read DESC LIMIT 10;

3. N+1 쿼리 패턴 - 호출 횟수(calls)가 비정상적으로 많은 쿼리

“autovacuum이 계속 돌아요”

autovacuum이 특정 테이블에서 계속 실행될 때:

1. autovacuum 진행 상황

   SELECT relname, phase, heap_blks_scanned, heap_blks_total,
          round(100.0 * heap_blks_scanned / nullif(heap_blks_total, 0), 2) as pct
   FROM pg_stat_progress_vacuum;

2. 테이블별 autovacuum 임계값

   SELECT relname, n_dead_tup,
          (SELECT setting::bigint FROM pg_settings WHERE name = 'autovacuum_vacuum_threshold') +
          (SELECT setting::float FROM pg_settings WHERE name = 'autovacuum_vacuum_scale_factor') * n_live_tup
          as vacuum_threshold
   FROM pg_stat_user_tables
   WHERE n_dead_tup > 0
   ORDER BY n_dead_tup DESC LIMIT 10;

핫 테이블의 경우 테이블별로 autovacuum 설정을 조정하는 것이 좋습니다:

ALTER TABLE hot_table SET (autovacuum_vacuum_scale_factor = 0.01);

“DB 서버 CPU가 100%예요”

가장 긴급한 상황 중 하나입니다. 체계적으로 원인을 찾아가는 과정을 정리합니다.

Step 1: OS 레벨에서 어떤 프로세스가 CPU를 쓰는지 확인

# PostgreSQL 프로세스별 CPU 사용량
top -c -p $(pgrep -d',' postgres)

# 또는 ps로 확인
ps aux --sort=-%cpu | grep postgres | head -20

출력에서 PID를 확인합니다. PostgreSQL은 프로세스 기반이므로 각 백엔드 프로세스가 별도 PID를 가집니다.

Step 2: CPU를 많이 쓰는 PID가 어떤 쿼리를 실행 중인지 확인

-- 특정 PID의 쿼리 확인
SELECT pid, state, now() - query_start as duration,
       wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE pid = <위에서_확인한_PID>;

-- 또는 CPU 순으로 정렬 (active 상태인 것들)
SELECT pid, state, now() - query_start as duration,
       wait_event_type, wait_event,
       left(query, 100) as query
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start ASC;  -- 오래 실행 중인 순

Step 3: 원인별 대응

Case 1: 특정 쿼리가 원인인 경우

-- 해당 쿼리의 실행 계획 확인
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
<문제가_되는_쿼리>;

흔한 원인들:

• Seq Scan on 대용량 테이블: 인덱스 누락 또는 통계 정보 부정확
• Nested Loop with 많은 rows: 조인 조건 누락 또는 잘못된 조인
• Sort/Hash 작업: work_mem 부족으로 디스크 사용

긴급 조치 (쿼리 강제 종료):

-- 쿼리만 취소 (트랜잭션은 유지)
SELECT pg_cancel_backend(<pid>);

-- 커넥션 자체를 끊기 (트랜잭션 롤백)
SELECT pg_terminate_backend(<pid>);

Case 2: 여러 쿼리가 동시에 CPU를 사용하는 경우

-- 동일한 쿼리가 여러 번 실행되는지 확인
SELECT count(*), left(query, 80) as query
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY left(query, 80)
ORDER BY count DESC;

원인:

• 트래픽 급증: 애플리케이션 레벨에서 rate limiting 필요
• N+1 쿼리: ORM 설정 문제, 쿼리 최적화 필요
• 캐시 미스: 애플리케이션 캐시 만료 또는 cold start

Case 3: autovacuum이 원인인 경우

-- autovacuum 프로세스 확인
SELECT pid, query, now() - query_start as duration
FROM pg_stat_activity
WHERE query LIKE 'autovacuum:%';

-- 어떤 테이블을 vacuum 중인지
SELECT relname, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

긴급 조치:

-- autovacuum 프로세스 종료 (다음 스케줄에 다시 실행됨)
SELECT pg_terminate_backend(<autovacuum_pid>);

장기 해결책:

-- autovacuum 리소스 제한 조정
ALTER SYSTEM SET autovacuum_vacuum_cost_limit = 200;  -- 기본값 200
ALTER SYSTEM SET autovacuum_vacuum_cost_delay = '20ms';  -- 기본값 2ms (v12+)
SELECT pg_reload_conf();

Case 4: 인덱스 생성/재생성이 원인인 경우

-- 인덱스 생성 진행 상황
SELECT a.pid, p.phase, p.blocks_done, p.blocks_total,
       round(100.0 * p.blocks_done / nullif(p.blocks_total, 0), 2) as pct
FROM pg_stat_progress_create_index p
JOIN pg_stat_activity a ON p.pid = a.pid;

CREATE INDEX CONCURRENTLY는 CPU를 많이 사용하지만, 서비스 영향을 최소화합니다. 일반 CREATE INDEX는 더 빠르지만 테이블을 잠급니다.

Case 5: 체크포인트가 원인인 경우

-- 체크포인트 빈도 확인
SELECT checkpoints_timed, checkpoints_req,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

checkpoints_req(요청된 체크포인트)가 많으면 WAL 생성이 너무 빠른 것:

-- checkpoint 관련 설정 확인
SHOW checkpoint_timeout;
SHOW max_wal_size;

Step 4: 실시간 모니터링 쿼리

CPU 100% 상황에서 계속 모니터링할 때 유용한 쿼리:

-- 1초마다 갱신되는 활성 쿼리 현황
SELECT pid, state,
       now() - query_start as duration,
       wait_event_type || ':' || wait_event as wait,
       left(query, 60)
FROM pg_stat_activity
WHERE state != 'idle'
  AND pid != pg_backend_pid()
ORDER BY query_start;

# watch로 주기적 실행
watch -n 1 'psql -c "SELECT pid, state, now() - query_start as dur, left(query,50) FROM pg_stat_activity WHERE state != '\''idle'\'' ORDER BY query_start"'

CPU 100% 트러블슈팅 플로우차트

문제 상황별 확인 지표 요약

AWS Aurora PostgreSQL 환경

실무에서는 AWS Aurora PostgreSQL을 많이 사용합니다. Aurora는 일반 PostgreSQL과 아키텍처가 다르기 때문에 모니터링 포인트도 다릅니다.

Aurora vs PostgreSQL 차이점

Aurora 전용 모니터링 지표

CloudWatch에서 확인해야 할 Aurora 전용 지표들:

Aurora 핵심 지표 (CloudWatch)
├── CPUUtilization          # Writer/Reader별 CPU
├── DatabaseConnections     # 커넥션 수
├── FreeableMemory         # 가용 메모리
├── AuroraReplicaLag       # Reader 지연 (보통 20ms 이하)
├── BufferCacheHitRatio    # 버퍼 캐시 히트율 (99% 이상 유지)
├── DMLLatency             # INSERT/UPDATE/DELETE 지연
├── SelectLatency          # SELECT 지연
├── CommitLatency          # 커밋 지연
├── DDLLatency             # DDL 작업 지연
└── Deadlocks              # 데드락 발생 수

Aurora에서 자주 발생하는 문제

“Reader 인스턴스가 느려요”

Aurora Reader는 Writer와 스토리지를 공유하지만, 버퍼 캐시는 공유하지 않습니다.

-- Reader의 버퍼 캐시 히트율 확인
SELECT
    sum(blks_hit) * 100.0 / nullif(sum(blks_hit) + sum(blks_read), 0) as hit_ratio
FROM pg_stat_database;

원인과 해결:

• Reader 웜업 안 됨: 새로 추가된 Reader는 캐시가 비어있음 → 트래픽 점진적 증가
• Reader 인스턴스 크기 부족: 버퍼 캐시 크기는 인스턴스 메모리에 비례 → 스케일업
• 핫 데이터가 Reader 캐시에 없음: Reader별 워크로드가 다르면 캐시 효율 저하

“Failover 후 커넥션 에러”

Aurora Writer 장애 시 자동으로 Reader가 승격되지만, 애플리케이션 커넥션이 끊깁니다.

Failover 과정:
1. Writer 장애 감지 (~5초)
2. Reader 승격 (~15-30초)
3. DNS 업데이트 (~5초)
4. 애플리케이션 재연결 필요

해결책:

# HikariCP 설정 - Aurora 권장
spring:
  datasource:
    hikari:
      connection-timeout: 30000
      validation-timeout: 5000
      max-lifetime: 1800000
      # 중요: 커넥션 유효성 검사
      connection-test-query: SELECT 1

Aurora 전용 JDBC 드라이버 사용 권장:

// build.gradle.kts
dependencies {
    implementation("software.amazon.jdbc:aws-advanced-jdbc-wrapper:2.3.1")
}

# Aurora JDBC Wrapper 사용
spring:
  datasource:
    url: jdbc:aws-wrapper:postgresql://mycluster.cluster-xxx.ap-northeast-2.rds.amazonaws.com:5432/mydb
    driver-class-name: software.amazon.jdbc.Driver

“Aurora 스토리지가 계속 늘어나요”

Aurora 스토리지는 자동으로 늘어나지만, 줄어들지 않습니다 (2024년 기준 일부 리전에서 축소 지원 시작).

-- 테이블별 크기 확인
SELECT schemaname, relname,
       pg_size_pretty(pg_total_relation_size(relid)) as total_size
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- bloat 확인 (Aurora에서도 유효)
SELECT schemaname, relname, n_dead_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup + n_dead_tup, 0) * 100, 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

“Aurora Serverless v2 스케일링이 느려요”

Serverless v2는 ACU(Aurora Capacity Unit) 기반으로 자동 스케일링되지만, 즉각적이지 않습니다.

스케일링 관찰 포인트 (CloudWatch):
- ServerlessDatabaseCapacity: 현재 ACU
- ACUUtilization: ACU 사용률
- CPUUtilization: CPU 기준 스케일링 트리거

권장 설정:

-- 최소 ACU를 너무 낮게 설정하면 콜드 스타트 발생
-- 프로덕션에서는 최소 2 ACU 이상 권장

Aurora Performance Insights 활용

RDS Performance Insights는 Aurora에서 가장 유용한 모니터링 도구입니다.

Performance Insights에서 확인할 것:
├── Top SQL        # 가장 부하가 큰 쿼리
├── Top Waits      # 대기 이벤트 분석
├── Top Hosts      # 어느 서버에서 부하가 오는지
└── Top Users      # 어느 DB 유저가 부하를 주는지

주요 Wait Event (Aurora):

• IO:DataFileRead: 스토리지에서 데이터 읽기 → 버퍼 캐시 부족
• LWLock:BufferContent: 버퍼 경합 → 동시 접근 과다
• Lock:Relation: 테이블 락 대기 → DDL 작업 또는 락 경합
• CPU: CPU 대기 → 쿼리 최적화 필요
• IO:XactSync: 트랜잭션 커밋 동기화 → 쓰기 부하 과다

Aurora 모니터링 체크리스트

Aurora vs 일반 PostgreSQL 명령어 차이

-- ❌ Aurora에서 사용 불가 또는 다르게 동작
pg_stat_replication          -- Writer에서만 제한적 정보
pg_current_wal_lsn()         -- 동작하지만 의미가 다름
VACUUM FULL                  -- 가능하지만 권장하지 않음 (스토리지 구조상)

-- ✅ Aurora에서 동일하게 사용 가능
pg_stat_activity
pg_stat_statements
pg_stat_user_tables
pg_locks
EXPLAIN ANALYZE

Aurora 알람 설정 예시 (CloudWatch)

# terraform 예시
resource "aws_cloudwatch_metric_alarm" "aurora_cpu" {
  alarm_name          = "aurora-high-cpu"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "CPUUtilization"
  namespace           = "AWS/RDS"
  period              = 60
  statistic           = "Average"
  threshold           = 80
  alarm_description   = "Aurora CPU 80% 초과"
  dimensions = {
    DBClusterIdentifier = "my-aurora-cluster"
  }
}

resource "aws_cloudwatch_metric_alarm" "aurora_connections" {
  alarm_name          = "aurora-high-connections"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 2
  metric_name         = "DatabaseConnections"
  namespace           = "AWS/RDS"
  period              = 60
  statistic           = "Average"
  threshold           = 200  # max_connections의 70% 수준
  alarm_description   = "Aurora 커넥션 수 과다"
  dimensions = {
    DBClusterIdentifier = "my-aurora-cluster"
  }
}

resource "aws_cloudwatch_metric_alarm" "aurora_replica_lag" {
  alarm_name          = "aurora-replica-lag"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = 3
  metric_name         = "AuroraReplicaLag"
  namespace           = "AWS/RDS"
  period              = 60
  statistic           = "Average"
  threshold           = 100  # 100ms 이상이면 이상
  alarm_description   = "Aurora Reader 지연"
  dimensions = {
    DBClusterIdentifier = "my-aurora-cluster"
  }
}

정리

PostgreSQL 트러블슈팅의 핵심:

1. 침착하게 현상 파악: pg_stat_activity로 현재 상황 확인
2. 원인 분류: 쿼리 문제인지, 리소스 문제인지, 락 문제인지
3. 긴급 조치: pg_cancel_backend(), pg_terminate_backend()로 즉시 대응
4. 근본 원인 해결: 인덱스 추가, 쿼리 최적화, 설정 조정
5. Aurora 특화: CloudWatch + Performance Insights 활용

다음 글에서는 MySQL 모니터링에 대해 다룹니다.

참고 자료

• PostgreSQL Statistics Collector
• AWS Aurora PostgreSQL Best Practices
• Aurora Performance Insights