PostgreSQL 성능 병목 현상 상위 7가지와 해결 방법

PostgreSQL 성능 작업은 모든 느린 요청에 대해 "인덱스를 추가하세요" 또는 "메모리를 늘리세요"라는 동일한 답변을 할 때 잘못됩니다. 때로는 그것이 맞습니다. 때로는 데이터베이스가 잠금을 기다리거나, 정렬을 디스크로 유출하거나, 유휴 연결에 빠져 있거나, 오토배큠이 뒤처져서 테이블 페이지를 10배 더 많이 읽고 있을 수 있습니다.

유용한 습관은 무엇이든 변경하기 전에 병목 현상을 식별하는 것입니다. 느린 API 엔드포인트는 단지 증상일 뿐입니다. 데이터베이스는 일반적으로 시간이 스캔, 조인, 정렬, 디스크 읽기, 다른 트랜잭션 대기 또는 너무 많은 세션 열기에 소요되었는지 알려줄 수 있습니다.

1. 비효율적인 쿼리 실행 계획

느린 성능의 가장 빈번한 원인 중 하나는 최적화되지 않은 SQL 쿼리입니다. PostgreSQL의 쿼리 플래너는 정교하지만, 특히 복잡한 쿼리나 오래된 통계로 인해 때때로 비효율적인 실행 계획을 생성할 수 있습니다.

병목 현상 식별

EXPLAIN 및 EXPLAIN ANALYZE를 사용하여 PostgreSQL이 쿼리를 실행하는 방식을 이해하세요. EXPLAIN은 계획된 실행을 보여주고, EXPLAIN ANALYZE는 실제로 쿼리를 실행하여 실제 타이밍과 행 수를 제공합니다.

-- 실행 계획을 보려면:
EXPLAIN SELECT * FROM users WHERE email LIKE 'john.doe%';

-- 계획 및 실제 실행 세부 정보를 보려면:
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'john.doe%';

다음 사항을 확인하세요:

인덱스가 유용할 큰 테이블에 대한 순차 스캔.
실제 행 수와 비교한 큰 행 추정 오류.
해시 조인 또는 병합 조인이 더 적절할 때의 중첩 루프 조인.

해결 방법

적절한 인덱스 추가: WHERE, JOIN, ORDER BY 및 GROUP BY 절에 사용된 열에 대한 인덱스가 있는지 확인하세요. 선행 와일드카드(%)가 있는 LIKE 절의 경우 B-트리 인덱스는 종종 효과적이지 않습니다. 전체 텍스트 검색 또는 트라이그램 인덱스를 고려하세요.
쿼리 다시 작성: 때로는 더 간단하거나 다르게 구조화된 쿼리가 더 나은 계획으로 이어질 수 있습니다.
통계 업데이트: PostgreSQL은 통계를 사용하여 조건자의 선택성을 추정합니다. 오래된 통계는 플래너를 잘못된 방향으로 이끌 수 있습니다.
```
ANALYZE table_name;
-- 또는 모든 테이블에 대해:
ANALYZE;
```
쿼리 플래너 매개변수 조정: work_mem 및 random_page_cost는 플래너의 선택에 영향을 줄 수 있지만, 이러한 매개변수는 주의해서 조정해야 합니다.

2. 누락되었거나 비효율적인 인덱스

인덱스는 빠른 데이터 검색에 중요합니다. 인덱스가 없으면 PostgreSQL은 순차 스캔을 수행하여 일치하는 데이터를 찾기 위해 테이블의 모든 행을 읽어야 하므로 큰 테이블의 경우 매우 느립니다.

병목 현상 식별

EXPLAIN ANALYZE 출력: 쿼리 계획에서 큰 테이블에 대한 Seq Scan을 찾으세요.
데이터베이스 모니터링 도구: pg_stat_user_tables와 같은 도구는 테이블 스캔 횟수를 보여줄 수 있습니다.

해결 방법

B-트리 인덱스 생성: 가장 일반적인 유형이며 동등(=), 범위(<, >, <=, >=) 및 LIKE(선행 와일드카드 없음) 연산에 적합합니다.
```
CREATE INDEX idx_users_email ON users (email);
```
다른 인덱스 유형 사용:
- GIN/GiST: 전체 텍스트 검색, JSONB 연산 및 기하학적 데이터 유형용.
- 해시 인덱스: 동등 검사용(최신 PostgreSQL 버전에서는 B-트리 개선으로 덜 일반적임).
- BRIN(블록 범위 인덱스): 물리적으로 상관된 데이터가 있는 매우 큰 테이블용.
부분 인덱스: 행의 하위 집합만 인덱싱하며, 쿼리가 특정 조건을 자주 대상으로 할 때 유용합니다.
```
CREATE INDEX idx_orders_pending ON orders (order_date) WHERE status = 'pending';
```
표현식 인덱스: 함수 또는 표현식의 결과를 인덱싱합니다.
```
CREATE INDEX idx_users_lower_email ON users (lower(email));
```
중복 인덱스 피하기: 인덱스가 너무 많으면 쓰기 작업(INSERT, UPDATE, DELETE)이 느려지고 디스크 공간을 소모할 수 있습니다.

3. 과도한 오토배큠 활동 또는 기아

PostgreSQL은 MVCC(다중 버전 동시성 제어) 시스템을 사용하므로 UPDATE 및 DELETE 작업이 행을 즉시 제거하지 않습니다. 대신, 행을 더 이상 사용되지 않는 것으로 표시합니다. VACUUM은 이 공간을 회수하고 트랜잭션 ID 랩어라운드를 방지합니다. 오토배큠은 이 프로세스를 자동화합니다.

병목 현상 식별

높은 CPU/IO 부하: 오토배큠은 리소스를 많이 사용할 수 있습니다.
테이블 블로트: 큰 pg_class.relpages 및 pg_class.reltuples와 실제 데이터 크기 또는 예상 행 수의 불일치로 표시됩니다.
pg_stat_activity: 장기 실행 autovacuum worker 프로세스를 찾으세요.
pg_stat_user_tables: n_dead_tup(데드 튜플 수) 및 last_autovacuum/last_autoanalyze 시간을 모니터링하세요.

해결 방법

오토배큠 매개변수 조정: postgresql.conf 또는 테이블별 설정에서 조정하세요.
- autovacuum_vacuum_threshold: 배큠을 트리거하는 최소 데드 튜플 수.
- autovacuum_vacuum_scale_factor: 배큠을 고려할 테이블 크기의 비율.
- autovacuum_analyze_threshold 및 autovacuum_analyze_scale_factor: ANALYZE에 대한 유사한 매개변수.
- autovacuum_max_workers: 병렬 오토배큠 작업자 수.
- autovacuum_work_mem: 각 작업자에게 사용 가능한 메모리.
테이블별 설정 예시:
```
ALTER TABLE large_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02);
```
수동 VACUUM: 즉각적인 공간 회수 또는 오토배큠이 따라잡지 못할 때 사용합니다.
```
VACUUM (VERBOSE, ANALYZE) table_name;
```
VACUUM FULL은 테이블을 잠그고 전체 테이블을 다시 쓰므로 매우 파괴적일 수 있으므로 꼭 필요한 경우에만 사용하세요.
오래된 트랜잭션 관찰: 장기 실행 트랜잭션은 이전 행 버전을 열어두고 정리를 방지할 수 있습니다.
트랜잭션 ID 기간 모니터링: vacuum_freeze_min_age, autovacuum_freeze_max_age 및 데이터베이스 age(datfrozenxid)를 이해하는 것은 랩어라운드 비상 상황을 방지하는 데 중요합니다.

4. 불충분한 하드웨어 리소스(CPU, RAM, IOPS)

PostgreSQL의 성능은 기본 하드웨어에 직접적으로 연결됩니다. 불충분한 CPU, RAM 또는 느린 디스크 I/O는 심각한 병목 현상을 만들 수 있습니다.

병목 현상 식별

시스템 모니터링 도구: Linux의 top, htop, iostat, vmstat; Windows의 성능 모니터.
pg_stat_activity: 잠금을 기다리는 쿼리 찾기(wait_event_type = 'IO', 'LWLock' 등).
높은 CPU 사용률: 지속적으로 100%에 가까움.
높은 디스크 I/O 대기 시간: 디스크 작업을 기다리는 데 많은 시간을 소비하는 시스템.
낮은 사용 가능 메모리 / 높은 스왑 사용량: RAM이 불충분함을 나타냅니다.

해결 방법

CPU: 특히 동시 워크로드의 경우 충분한 코어가 있는지 확인하세요. PostgreSQL은 병렬 쿼리 실행(최신 버전에서) 및 백그라운드 프로세스에 여러 코어를 효과적으로 활용합니다.
RAM (shared_buffers, work_mem):
- shared_buffers: 데이터 블록용 캐시. 일반적인 권장 사항은 시스템 RAM의 25%이지만 워크로드에 따라 조정하세요.
- work_mem: 정렬, 해싱 및 기타 중간 작업에 사용됩니다. 불충분한 work_mem은 디스크로의 유출을 강제합니다.
디스크 I/O:
- SSD 사용: 데이터베이스 워크로드의 경우 HDD보다 훨씬 빠릅니다.
- RAID 구성: 읽기/쓰기 성능 최적화(예: RAID 10).
- 별도의 WAL 드라이브: WAL(Write-Ahead Log)을 별도의 빠른 드라이브에 배치하면 쓰기 성능이 향상될 수 있습니다.
네트워크: 특히 분산 환경에서 클라이언트-서버 통신을 위한 충분한 대역폭과 낮은 지연 시간을 보장하세요.

하드웨어 증상에는 증거가 필요합니다. CPU가 높고 디스크 대기가 낮으면 비용이 많이 드는 계획, 표현식이 많은 쿼리, JSON 처리 또는 너무 많은 활성 작업자를 찾으세요. I/O 대기가 높으면 EXPLAIN (ANALYZE, BUFFERS)의 버퍼 읽기, 체크포인트 동작 및 핫 테이블이 메모리에 맞는지 확인하세요. 스왑이 활성화된 경우 더 많은 쿼리 동시성을 추가하기 전에 연결 압력 또는 메모리 설정을 줄이세요.

5. 잘못 구성된 `postgresql.conf`

PostgreSQL의 postgresql.conf 파일에는 동작을 제어하는 수백 개의 매개변수가 포함되어 있습니다. 기본 설정은 종종 보수적이며 특정 워크로드나 하드웨어에 최적화되어 있지 않습니다.

병목 현상 식별

전반적인 느림: 전반적으로 느린 쿼리 시간.
과도한 디스크 I/O: 사용 가능한 RAM과 비교하여.
메모리 사용량: 메모리 압력 징후를 보이는 시스템.
성능 튜닝 가이드 참조: 일반적인 최적 값 이해.

해결 방법

고려해야 할 주요 매개변수:

shared_buffers: (위에서 언급한 대로) 데이터 블록용 캐시. 시스템 RAM의 약 25%부터 시작하세요.
work_mem: 정렬/해시용 메모리. 디스크 유출을 보여주는 EXPLAIN ANALYZE 출력을 기반으로 조정하세요.
maintenance_work_mem: VACUUM, CREATE INDEX, ALTER TABLE ADD FOREIGN KEY용 메모리. 값이 클수록 이러한 작업 속도가 빨라집니다.
effective_cache_size: 플래너가 OS 및 PostgreSQL 자체에서 캐싱에 사용할 수 있는 메모리 양을 추정하는 데 도움이 됩니다.
wal_buffers: WAL 쓰기용 버퍼. 쓰기 부하가 높으면 늘리세요.
checkpoint_completion_target: 시간이 지남에 따라 체크포인트 쓰기를 분산하여 I/O 급증을 줄입니다.
max_connections: 적절하게 설정하세요. 너무 높으면 리소스가 고갈될 수 있습니다.
log_statement: 디버깅에 유용하지만 ALL 문을 로깅하면 성능에 영향을 줄 수 있습니다.

팁: pgtune과 같은 도구를 사용하여 하드웨어 기반 시작 권장 사항을 얻으세요. 프로덕션에 적용하기 전에 항상 스테이징 환경에서 변경 사항을 테스트하세요.

PostgreSQL 구성의 한 가지 함정은 모든 설정을 속도 조절 장치로 취급하는 것입니다. work_mem이 좋은 예입니다. 서버 전체에 한 번이 아니라 작업당 할당됩니다. 단일 쿼리가 여러 번 사용할 수 있으며, 많은 동시 쿼리가 빠르게 곱해질 수 있습니다. 전역적으로 4MB에서 128MB로 올리면 하나의 보고서 쿼리에 도움이 될 수 있지만 트래픽 중에 전체 서버에 해를 끼칠 수 있습니다. 알려진 쿼리를 테스트하려면 먼저 세션 수준 변경을 사용하세요:

SET work_mem = '128MB';
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

계획이 디스크로 유출되는 것을 멈추고 지연 시간이 개선되면 유용한 정보를 얻은 것입니다. 쿼리를 다시 작성할지, 인덱스를 추가할지, 보고 역할에 대한 메모리를 설정할지, 아니면 전역 값을 변경할지 여전히 결정해야 합니다.

6. 연결 풀링 문제

새 데이터베이스 연결을 설정하는 것은 비용이 많이 드는 작업입니다. 빈번하고 수명이 짧은 데이터베이스 상호 작용이 있는 애플리케이션에서 연결을 반복적으로 열고 닫으면 상당한 성능 병목 현상이 될 수 있습니다.

병목 현상 식별

높은 연결 수: pg_stat_activity에 매우 많은 수의 연결이 표시되며, 그중 다수는 유휴 상태입니다.
느린 애플리케이션 시작/응답 시간: 데이터베이스 연결이 자주 이루어질 때.
서버 리소스 고갈: 연결 관리로 인한 높은 CPU 또는 메모리 사용량.

해결 방법

연결 풀링 구현: PgBouncer 또는 Odyssey와 같은 연결 풀러를 사용하세요. 이러한 도구는 열린 데이터베이스 연결 풀을 유지 관리하고 들어오는 클라이언트 요청에 대해 이를 재사용합니다.
- PgBouncer: 가볍고 성능이 뛰어난 연결 풀러. 트랜잭션, 세션 또는 명령문 풀링 모드로 작동할 수 있습니다.
- Odyssey: SCRAM-SHA-256과 같은 프로토콜을 지원하는 보다 현대적이고 기능이 풍부한 연결 풀러.
풀러 적절하게 구성: 애플리케이션 요구 사항 및 데이터베이스 용량에 따라 풀 크기, 시간 초과 및 풀링 모드를 조정하세요.
애플리케이션 측 풀링: 일부 애플리케이션 프레임워크는 내장된 연결 풀링 기능을 제공합니다. 이것들이 올바르게 구성되었는지 확인하세요.

연결 풀링 문제는 배포 확장 후에 자주 나타납니다. 20개의 연결 풀이 있는 하나의 애플리케이션 인스턴스는 괜찮을 수 있습니다. 동일한 풀 설정을 가진 30개의 인스턴스는 실제 트래픽이 도착하기 전에 600개의 가능한 데이터베이스 세션을 만들 수 있습니다. PostgreSQL은 연결당 프로세스를 사용하므로 유휴 세션은 무료가 아닙니다. 애플리케이션 풀을 작게 유지하고, 많은 수명이 짧은 요청이 예상될 때 PgBouncer를 앞에 두고, 애플리케이션 이름별로 pg_stat_activity를 모니터링하여 누가 세션을 소유하고 있는지 알 수 있도록 하세요.

7. 잠금 경합

여러 트랜잭션이 동시에 동일한 데이터에 액세스하고 수정하려고 할 때 충돌하는 잠금을 획득하면 서로 기다려야 할 수 있습니다. 과도한 잠금 경합은 애플리케이션을 크롤링 상태로 만들 수 있습니다.

병목 현상 식별

pg_stat_activity: wait_event_type이 Lock인 행을 찾으세요.
애플리케이션 성능 저하: 특정 작업이 매우 느려집니다.
데드락: 트랜잭션이 무기한 서로를 기다리는 경우.
장기 실행 트랜잭션: 장기간 잠금을 보유합니다.

해결 방법

트랜잭션 최적화: 트랜잭션을 짧고 간결하게 유지하세요. 가능한 한 빨리 커밋하거나 롤백하세요.
애플리케이션 로직 검토: 잠재적인 경합 조건 또는 비효율적인 잠금 패턴을 식별하세요.
적절한 잠금 수준 사용: PostgreSQL은 다양한 잠금 수준(예: ACCESS EXCLUSIVE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE)을 제공합니다. 필요한 가장 덜 제한적인 잠금을 이해하고 사용하세요.
SELECT ... FOR UPDATE / SELECT ... FOR NO KEY UPDATE: 트랜잭션이 완료되기 전에 다른 트랜잭션이 행을 변경하지 못하도록 수정을 위해 행을 잠가야 할 때 현명하게 사용하세요.
정기적으로 VACUUM 실행: 앞서 언급했듯이 VACUUM은 데드 튜플을 정리하는 데 도움이 되며, 이는 길어진 VACUUM 작업을 방지하여 간접적으로 잠금 경합을 줄일 수 있습니다.

pg_locks 확인: pg_locks를 쿼리하여 어떤 프로세스가 다른 프로세스를 차단하고 있는지 확인하세요.

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

PostgreSQL이 느려지면 시스템을 변경하기 전에 증거를 수집하세요: 워크로드 형태를 위한 pg_stat_statements, 쿼리 경로를 위한 EXPLAIN (ANALYZE, BUFFERS), 대기 및 연결을 위한 pg_stat_activity, CPU, 메모리 및 I/O를 위한 호스트 메트릭. 시간이 실제로 어디에 소요되는지 알면 수정 사항이 훨씬 명확해집니다.

PostgreSQL 성능 병목 현상 상위 7가지와 해결 방법

1. 비효율적인 쿼리 실행 계획

병목 현상 식별

해결 방법

2. 누락되었거나 비효율적인 인덱스

병목 현상 식별

해결 방법

3. 과도한 오토배큠 활동 또는 기아

병목 현상 식별

해결 방법

4. 불충분한 하드웨어 리소스(CPU, RAM, IOPS)

병목 현상 식별

해결 방법

5. 잘못 구성된 postgresql.conf

병목 현상 식별

해결 방법

6. 연결 풀링 문제

병목 현상 식별

해결 방법

7. 잠금 경합

병목 현상 식별

해결 방법

5. 잘못 구성된 `postgresql.conf`