PostgreSQL VACUUM: 자동 실행과 수동 튜닝으로 성능 회복

🔍 검색 키워드: PostgreSQL VACUUM, 데드 튜플 정리, PostgreSQL 성능 저하, autovacuum, PostgreSQL 디스크 사용량, VACUUM ANALYZE

PostgreSQL은 MVCC(Multi-Version Concurrency Control) 방식으로 동작하기 때문에, 데이터를 삭제해도 물리적으로 즉시 제거되지 않습니다. 시간이 지나면서 불필요한 "죽은" 데이터(데드 튜플)가 누적되어 디스크 용량을 낭비하고 쿼리 성능을 저하시킵니다. 이 글에서는 VACUUM의 동작 원리와 최적화 방법을 설명합니다.

증상: PostgreSQL의 숨은 성능 저하

PostgreSQL 서버를 운영하다 보면 나타나는 문제들:

# 문제 1: 디스크 사용량은 계속 증가하지만 DELETE했는데도?
SELECT pg_size_pretty(pg_total_relation_size('users'));
-- 5GB

# 문제 2: 같은 쿼리인데 시간이 점점 느려짐
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';
-- Seq Scan on orders (cost=0.00..50000.00 rows=100000)

# 문제 3: 특정 테이블에서만 SELECT 느림
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables
WHERE relname = 'orders';

-- 결과: n_live_tup=100000, n_dead_tup=500000 (데드 튜플이 많음!)

원인 분석: MVCC와 데드 튜플

PostgreSQL의 MVCC 동작 방식:

1. 트랜잭션 A가 행을 삭제하면, 실제로는 그 행을 "삭제됨" 표시
2. 트랜잭션 B가 여전히 이전 버전을 참조 중이면, 그 행은 메모리에 남아야 함
3. 모든 트랜잭션이 새 버전만 참조하게 되면, 그때 "데드 튜플" 처리 가능

데드 튜플이 문제인 이유:

- 디스크 용량 낭비: 삭제한 데이터도 차지
- 인덱스 비대화: 데드 튜플도 인덱스 스캔에 포함
- 쿼리 성능 저하: Seq Scan 비용 증가

원인 추적: 데드 튜플 상태 확인

데드 튜플 누적 상태를 확인합니다:

SELECT schemaname, relname, n_live_tup, n_dead_tup, 
       ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
       FROM pg_stat_user_tables
       ORDER BY n_dead_tup DESC
       LIMIT 10;

결과 해석:

- n_live_tup: 활성 행 수
- n_dead_tup: 데드 튜플 수
- dead_ratio: 데드 튜플 비율. 10% 이상이면 VACUUM 필요

해결방법 1: 자동 VACUUM (autovacuum) 설정 최적화

PostgreSQL은 기본적으로 autovacuum 데몬이 자동으로 VACUUM을 실행합니다. 하지만 기본 설정은 보수적이므로, 대용량 데이터베이스에서는 수동 튜닝이 필요합니다.

postgresql.conf 설정 확인:

SHOW autovacuum;
SHOW autovacuum_naptime;
SHOW autovacuum_vacuum_threshold;

권장 설정 (운영 환경):

postgresql.conf에 추가:

autovacuum = on
autovacuum_naptime = '10s'        # 10초마다 체크 (기본 1분)
autovacuum_vacuum_threshold = 50  # 50개 행 변경 시 VACUUM (기본 50)
autovacuum_analyze_threshold = 50 # 50개 행 변경 시 ANALYZE (기본 50)

# 테이블별 추가 튜닝
autovacuum_vacuum_scale_factor = 0.05  # 테이블의 5% 변경 시
autovacuum_vacuum_cost_limit = 1000    # I/O 부하 제한 (기본 -1)

재시작:

sudo systemctl restart postgresql

해결방법 2: 수동 VACUUM 실행

긴급 상황에서는 수동으로 VACUUM을 실행합니다:

# 기본 VACUUM (먹다 남은 공간 재사용)
VACUUM;

# ANALYZE 포함 (통계 업데이트)
VACUUM ANALYZE;

# 특정 테이블만 처리
VACUUM ANALYZE orders;

# FULL (여유 공간을 OS로 반환, 시간 오래 걸림)
VACUUM FULL;

주의: VACUUM FULL은 테이블을 잠금(LOCK)으로 처리하므로, 운영 중에는 피해야 합니다.

해결방법 3: 추가 최적화

1. REINDEX로 인덱스 재구성

데드 튜플로 비대화된 인덱스를 재구성:

REINDEX TABLE orders;

2. pg_repack 확장 모듈 (다운타임 없음)

CREATE EXTENSION pg_repack;
SELECT pg_repack.repack_table('orders');

pg_repack은 FULL VACUUM과 달리 테이블 잠금 없이 재구성합니다. 운영 중 사용 가능.

3. 정기적인 VACUUM ANALYZE 스케줄

크론 작업으로 자동 실행:

# 매일 새벽 2시에 VACUUM ANALYZE 실행
0 2 * * * psql -d mydb -c "VACUUM ANALYZE;"

성능 회복 비교

상황	실행 명령어	효과	소요 시간
경미한 데드 튜플	VACUUM ANALYZE	통계 업데이트, 쿼리 최적화	~초 단위
중간 정도 비대화	REINDEX TABLE	인덱스 재구성	~분 단위
심각한 비대화	pg_repack (비운영) 또는 VACUUM FULL	공간 회복	~분~시간 단위

모니터링 체크리스트

항목	확인 방법	권장 조치
데드 튜플 비율	pg_stat_user_tables에서 dead_ratio	10% 이상이면 VACUUM
자동 VACUUM 실행 여부	pg_stat_user_tables의 last_vacuum	안 실행되면 autovacuum_naptime 단축
인덱스 비대화	pg_indexes_size(table)	인덱스가 테이블보다 크면 REINDEX
디스크 사용량	pg_size_pretty(pg_database_size(...))	지속 증가면 VACUUM FULL 또는 pg_repack

PostgreSQL의 성능 저하는 대부분 데드 튜플 누적으로 인한 것입니다. 정기적인 VACUUM과 ANALYZE로 데이터베이스를 "청소"하면, 쿼리 성능과 디스크 효율을 동시에 개선할 수 있습니다.

MySQL slow query 완벽 진단 및 최적화 가이드

🔍 검색 키워드: MySQL slow query, 느린 쿼리 로그, MySQL 성능 진단, 쿼리 최적화, EXPLAIN, slow_query_log, MySQL 병목

MySQL 서버를 운영하다 보면 "어떤 쿼리가 느린지 알 수 없다"는 답답한 상황을 마주치게 됩니다. 특히 야간에 갑자기 데이터베이스 응답이 느려지면, 어디부터 손을 대야 할지 막막하죠. 이 글에서는 MySQL slow query log를 활용해 느린 쿼리를 찾아내고 체계적으로 최적화하는 방법을 알려드립니다.

증상: 느린 쿼리는 어떻게 나타나나?

MySQL이 느려질 때 보이는 전형적인 증상들:

- 애플리케이션이 데이터베이스 요청에서 멈춰 있음
- 커넥션 풀이 고갈되었다는 에러 메시지
- DB CPU 사용률이 100%에 가까움
- 특정 시간대(주로 야간 배치 시간)에 연쇄적으로 느려짐

원인 분석: slow query log 활성화 및 확인

1단계: slow query log 활성화

MySQL 설정 파일(my.cnf 또는 my.ini)에 다음을 추가합니다:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = 1

MySQL 재시작:

sudo systemctl restart mysql

2단계: slow query log 확인

cat /var/log/mysql/slow-query.log

또는 MySQL 클라이언트에서:

SET SESSION long_query_time = 0;
SELECT sleep(3);
SELECT * FROM large_table WHERE id > 1000000;

로그 파일에 다음과 같이 기록됩니다:

# Time: 2026-09-09T08:15:23.456789Z
# User@Host: user@[localhost]
# Query_time: 3.234567  Lock_time: 0.000123  Rows_sent: 5000  Rows_examined: 1000000
SELECT * FROM large_table WHERE id > 1000000;

중요한 메트릭 해석:

- Query_time: 쿼리 실행에 걸린 전체 시간
- Rows_examined: 서버가 검사한 행의 수 (클수록 비효율적)
- Rows_sent: 클라이언트로 반환된 행의 수
- 비율이 중요: Rows_examined / Rows_sent 비율이 크면 불필요한 행을 많이 검사 중

해결방법: EXPLAIN으로 원인 찾기 및 최적화

1단계: EXPLAIN으로 쿼리 실행 계획 분석

느린 쿼리 앞에 EXPLAIN을 붙여 실행 계획을 확인합니다:

EXPLAIN SELECT * FROM users WHERE created_at > '2026-01-01' AND status = 'active';

결과에서 확인할 항목:

- type: 조인 타입. ALL은 풀 테이블 스캔(매우 느림), ref나 eq_ref는 양호
- possible_keys: 사용할 수 있는 인덱스 목록
- key: 실제로 사용한 인덱스. NULL이면 인덱스를 사용하지 않음
- rows: 서버가 검사할 것으로 예상하는 행 수. 작을수록 좋음
- Extra: 추가 정보. Using filesort, Using temporary는 성능 악화 신호

2단계: 인덱스 추가 또는 최적화

느린 쿼리에서 자주 필터링되는 컬럼에 인덱스를 추가:

ALTER TABLE users ADD INDEX idx_status_created (status, created_at);

복합 인덱스(composite index) 작성 시 주의:

- WHERE 절에서 사용되는 순서대로 컬럼 배열
- 카디널리티가 높은 컬럼(고유값이 많은)을 먼저 배치

3단계: 쿼리 리팩토링

- 불필요한 컬럼 제거: SELECT * 대신 필요한 컬럼만 명시
- 조인 최적화: 큰 테이블 조인 전에 필터링
- 서브쿼리 제거: 불가피한 경우 JOIN으로 변경

예시:

# 느린 쿼리 (서브쿼리 + 풀 조인)
SELECT * FROM orders 
WHERE user_id IN (SELECT id FROM users WHERE status = 'active');

# 최적화된 쿼리 (JOIN + 인덱스)
SELECT o.* FROM orders o
INNER JOIN users u ON o.user_id = u.id
WHERE u.status = 'active';

실무 체크리스트

단계	확인 항목	개선 방법
진단	slow query log 활성화?	MySQL 설정 파일에 slow_query_log = 1 추가
분석	Rows_examined / Rows_sent 비율?	비율이 100 이상이면 인덱스 확인 필요
분석	EXPLAIN type = ALL?	풀 테이블 스캔 중. 인덱스 추가 필수
최적화	WHERE 절 컬럼에 인덱스?	복합 인덱스로 효율성 증대
최적화	불필요한 컬럼 선택?	SELECT *를 구체적인 컬럼으로 변경
모니터링	주기적으로 느린 쿼리 검토?	주 1회 slow query log 분석 스케줄 추천

느린 쿼리는 서비스 전체의 병목입니다. 정기적으로 slow query log를 점검하고 체계적으로 최적화하면, 사용자 경험은 물론 서버 리소스 효율도 크게 개선됩니다.

MySQL deadlock 완벽 해결 가이드

MySQL 고동시성 환경에서 deadlock은 흔한 문제입니다.

원인: 두 개 이상의 트랜잭션이 서로의 락을 기다리면서 교착상태 발생.

해결방법:
1. 트랜잭션 순서 통일 - 모든 트랜잭션에서 같은 순서로 테이블 접근
2. 타임아웃 설정 - innodb_lock_wait_timeout 단축
3. 애플리케이션 재시도 - deadlock 발생 시 자동 재시도
4. 격리 레벨 검토 - READ COMMITTED 사용
5. 인덱스 확인 - 전체 스캔 방지

가장 효과적인 방법은 접근 순서 통일과 재시도 로직입니다.

PostgreSQL Connection Pool 고갈 해결하기

🔍 검색 키워드: PostgreSQL connection pool 에러, 커넥션 풀 소진, 데이터베이스 연결 오류, 최대 연결 초과

PostgreSQL Connection Pool 고갈 해결하기

증상

애플리케이션이 실행되다가 갑자기 다음과 같은 에러가 발생한다:

FATAL: sorry, too many clients already

또는:

Error: connect ECONNREFUSED - PostgreSQL connection failed

또는 connection pool 라이브러리에서:

Error: timeout acquiring a connection from the pool
  Error: Client has already been released to the pool

특히 트래픽이 많아지거나 장시간 실행되는 배치 작업 후에 자주 발생한다. 새로운 요청이 들어와도 DB 연결을 못 하고 응답 불가 상태가 된다.

원인

PostgreSQL은 최대 동시 연결 수 제한이 있고 (기본값 100), connection pool은 재사용 가능한 연결 수를 미리 정해둔다. 고갈 현상은 다음 경우에 발생:

1. 연결을 반환 안 함: 쿼리 후 연결을 pool에 반환하지 않아 계속 증가
2. 타임아웃 후 좀비 연결: 응답 없는 연결이 pool에 남아있음
3. Pool 사이즈 설정 과소: 동시 사용자/요청량에 비해 pool이 너무 작음
4. 쿼리 오래 걸림: 느린 쿼리가 연결을 오래 점유
5. Connection leak: 예외 발생 시 연결을 close하지 않는 코드

해결방법

1. Connection Pool 설정 최적화

Node.js + pg (node-postgres):

const { Pool } = require('pg');
  
  const pool = new Pool({
    host: 'localhost',
      port: 5432,
        database: 'mydb',
          user: 'postgres',
            password: 'password',
              max: 20,                    // 최대 연결 수 (기본 10)
                idleTimeoutMillis: 30000,   // 30초 idle 후 닫기
                  connectionTimeoutMillis: 2000, // 연결 생성 타임아웃
                  });
                  
                  module.exports = pool;

Python + psycopg2:

import psycopg2.pool
  
  connection_pool = psycopg2.pool.SimpleConnectionPool(
      1,      // minimum connections
          20,     // maximum connections
              database="mydb",
                  user="postgres",
                      password="password",
                          host="localhost"
                          )
                          
                          // 사용
                          conn = connection_pool.getconn()
                          try:
                              // 쿼리 실행
                                  pass
                                  finally:
                                      connection_pool.putconn(conn)

Java + HikariCP:

HikariConfig config = new HikariConfig();
  config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");
  config.setUsername("postgres");
  config.setPassword("password");
  config.setMaximumPoolSize(20);          // 최대 연결
  config.setMinimumIdle(5);               // 최소 유휴 연결
  config.setConnectionTimeout(2000);       // 2초 타임아웃
  config.setIdleTimeout(600000);          // 10분 idle 타임아웃
  config.setMaxLifetime(1800000);         // 30분 최대 수명
  
  HikariDataSource dataSource = new HikariDataSource(config);

2. 연결이 제대로 반환되는지 확인

Node.js에서 연결 누수 확인:

const pool = new Pool(config);
  
  pool.on('error', (err, client) => {
    console.error('Unexpected error on idle client', err);
      process.exit(-1);
      });
      
      pool.on('connect', () => {
        console.log('New connection created');
        });
        
        // 항상 try-finally로 연결 반환 보장
        const client = await pool.connect();
        try {
          const result = await client.query('SELECT * FROM users WHERE id = $1', [1]);
            return result.rows;
            } finally {
              client.release();  // 반드시 실행
              }

또는 with 문 활용 (Python):

from contextlib import contextmanager
  
  @contextmanager
  def get_db_connection():
      conn = connection_pool.getconn()
          try:
                  yield conn
                      finally:
                              connection_pool.putconn(conn)
                              
                              // 사용
                              with get_db_connection() as conn:
                                  cursor = conn.cursor()
                                      cursor.execute("SELECT * FROM users WHERE id = %s", (1,))
                                          // 예외 발생해도 자동으로 반환됨

3. 느린 쿼리 최적화

-- 인덱스 추가
  CREATE INDEX idx_users_id ON users(id);
  
  -- 실행계획 확인
  EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';
  
  -- 필요 없는 조인 제거, WHERE 조건 최적화
  SELECT u.id, u.name
  FROM users u
  WHERE u.created_at > NOW() - INTERVAL '7 days'
    AND u.status = 'active'
    LIMIT 100;

4. PostgreSQL 서버 설정 확인

# PostgreSQL 최대 연결 수 조회
  psql -U postgres -c "SHOW max_connections;"
  
  # 현재 연결 수 조회
  psql -U postgres -c "SELECT count(*) FROM pg_stat_activity;"
  
  # 연결 상세 정보
  psql -U postgres -c "SELECT datname, count(*) FROM pg_stat_activity GROUP BY datname;"

필요하면 postgresql.conf에서:

max_connections = 200   # 기본값 100에서 증가

5. 좀비 연결 정리

-- 유휴 연결 종료
  SELECT pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE datname = 'mydb'
    AND state = 'idle'
      AND query_start < now() - interval '30 minutes';
      
      -- 슬로우 쿼리 강제 종료 (주의)
      SELECT pg_terminate_backend(pid)
      FROM pg_stat_activity
      WHERE query_start < now() - interval '5 minutes'
        AND state != 'idle';

정리표

상황	원인	해결법
연결 고갈 후 다시 증가	연결을 close하지 않음	try-finally 또는 context manager로 보장
Pool 타임아웃	pool이 너무 작음	max 크기 증가, idleTimeoutMillis 조정
"too many clients" 에러	PostgreSQL 최대 연결 도달	max_connections 증가 또는 app pool 감소
간헐적 연결 오류	connectionTimeoutMillis 너무 짧음	타임아웃 값 증가 또는 네트워크 확인
메모리 누수	좀비 연결이 메모리 점유	주기적으로 idle 연결 정리 (pg_terminate_backend)

TIP: 프로덕션에서는 반드시 connection pool 크기, 타임아웃, idle 설정을 검토하고, 정기적으로 pg_stat_activity로 모니터링하자.