프로덕션 환경에서 PostgreSQL을 실행하면 성능이 저하될 수 있으며 사용자도 모르게 트랜잭션이 부풀어오르게 될 수 있습니다. 이 문서에서는 PostgreSQL을 원활하게 실행하는 데 필요한 유지 관리 기본 사항을 다룹니다.
1. VACUUM: 데드 튜플 정리
PostgreSQL은 MVCC(다중 버전 동시성 제어)를 사용합니다. 이는 업데이트 및 삭제 후에도 이전 행 버전(데드 튜플)이 테이블에 남아 있음을 의미합니다. 선택하지 않으면 테이블이 부풀어 오르고 성능이 저하됩니다.
-- Run a standard VACUUM (does not block other operations)
VACUUM;
-- Reclaim space to the OS (acquires table lock)
VACUUM FULL;
프로덕션 환경에는 기본적으로 autovacuum이 활성화되어 있지만 사용 중인 테이블에는 조정된 설정이 필요할 수 있습니다.
autovacuum_vacuum_scale_factor = 0.01
autovacuum_vacuum_threshold = 50
autovacuum_naptime = 30s
2. 분석: 통계를 최신 상태로 유지
쿼리 플래너는 테이블 통계를 사용하여 실행 계획을 선택합니다. 새로운 ANALYZE 없이 데이터를 많이 변경하면 인덱스 선택이 잘못되고 쿼리 속도가 느려질 수 있습니다.
ANALYZE;
-- Or combine with VACUUM
VACUUM ANALYZE;
Autovacuum은 이를 자동으로 처리하지만 대규모 데이터를 가져온 후에는 수동으로 ANALYZE를 실행하는 것이 좋습니다.
3. 지수 전략 검토
사용하지 않는 인덱스 제거
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0;
idx_scan이 0이면 인덱스가 한 번도 사용되지 않았음을 의미합니다. 그러나 삭제하기 전에 주의 깊게 확인하십시오. 일부 인덱스는 월별 일괄 작업에서만 사용됩니다.
부풀린 인덱스 재구축
시간이 지남에 따라 인덱스가 조각화되고 성능이 저하됩니다.
REINDEX INDEX index_name;
REINDEX TABLE table_name;
REINDEX DATABASE database_name;
4. pg_stat_statements로 쿼리 성능 모니터링
pg_stat_statements 확장은 어떤 쿼리가 가장 많은 시간을 소비하는지 보여줍니다.
-- Enable in postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
-- Find top 10 slowest queries
SELECT query, calls, total_exec_time / calls AS avg_time_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
이 데이터를 사용하여 인덱스를 추가할 위치를 결정하거나 느린 SQL을 다시 작성할 수 있습니다.
5. 백업 전략
| 방법 | 특징 | 권장 주파수 |
|---|---|---|
pg_dump | 테이블별 논리적 백업 | 매일 |
pg_basebackup | 물리적 백업, PITR 지원 | 매일 |
| WAL 아카이빙 | 지속적인 아카이빙, 특정 시점 복구 | 항상 |
pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).sql
WAL 보관과 결합된 물리적 백업은 가능한 경우 가장 안전한 접근 방식입니다.
6. 연결 풀링
PostgreSQL은 클라이언트 연결별로 프로세스를 포크하므로 동시 연결이 많아 메모리에 부담을 줄 수 있습니다. PgBouncer와 같은 연결 풀러는 여러 클라이언트에서 소수의 데이터베이스 연결을 효율적으로 공유합니다.
샘플 유지보수 일정
| 빈도 | 작업 |
|---|---|
| 매일 | autovacuum 모니터링, 오류 로그 확인 |
| 주간 | 쿼리 성능 분석(pg_stat_statements) |
| 월간 | 사용하지 않는 인덱스 검토, 수동 분석 |
| 분기별 | REINDEX, 유지 관리 기간 동안 VACUUM FULL |
| 반기별 | 백업에서 복원 테스트 |
요약
PostgreSQL은 적절한 관리를 통해 매우 안정적입니다. VACUUM, ANALYZE, 인덱스 관리 및 백업을 일상의 일부로 만들면 생산에 따른 예상치 못한 일이 크게 줄어듭니다. 현재 쿼리 성능 기준을 이해하려면 pg_stat_statements을 활성화하는 것부터 시작하세요.