PostgreSQL 쿼리 최적화·오류 트러블슈팅 완전 가이드: EXPLAIN/인덱스/VACUUM/락/데드락

PostgreSQL 쿼리 최적화·오류 트러블슈팅 완전 가이드: EXPLAIN/인덱스/VACUUM/락/데드락

PostgreSQL 쿼리 최적화와 쿼리 오류 해결은 서비스 성능과 안정성을 좌우합니다. 본 글은 PostgreSQL 쿼리 최적화를 목표로 실행계획 해석, 인덱스 설계, 조인·정렬·페이징, VACUUM/ANALYZE, 락·데드락 진단까지 현업 기준으로 정리한 실전 가이드입니다.

 

PostgreSQL 쿼리 최적화를 위해 EXPLAIN/EXPLAIN ANALYZE로 실행계획을 읽고, B-Tree·GIN·GiST·BRIN 인덱스 전략, 조인/정렬/페이징 튜닝, VACUUM/ANALYZE 파라미터, 락·데드락 트러블슈팅까지 단계별로 설명합니다. 바로 복붙 가능한 SQL 스니펫과 체크리스트, 이미지 제안을 제공합니다.

반응형

 

 

---

 

 

목차

 

1. 왜 PostgreSQL 쿼리 최적화인가

 

2. 실행계획 읽기: EXPLAIN/EXPLAIN ANALYZE

 

3. 인덱스 전략: B-Tree/Hash/GIN/GiST/BRIN

 

4. 조인·정렬·페이징 최적화

 

5. VACUUM·ANALYZE·Autovacuum 튜닝

 

6. 락·데드락 트러블슈팅(pg_locks)

 

7. 실전 체크리스트·FAQ

 

8. 결론

 

 

---

 

1. 왜 PostgreSQL 쿼리 최적화인가

 

핵심 요약: 지연과 비용을 동시에 줄이는 가장 확실한 방법은 쿼리와 인덱스다.

 

PostgreSQL 쿼리 최적화는 단순히 속도 문제가 아니라 SLA·인프라 비용·장애 회피와 직결됩니다. 같은 하드웨어에서도 적절한 인덱스·통계·조인 전략만으로 수십 배 성능 차이가 납니다. 또한 잘못된 설계는 쿼리 오류(타임아웃, 메모리 초과, 데드락)로 이어지므로, 개발 단계에서부터 실행계획 기반 개발을 습관화해야 합니다.

 

PostgreSQL 쿼리 최적화의 첫 단계는 문제의 재현과 측정입니다. 동일 데이터셋/파라미터에서 EXPLAIN으로 계획을 비교하고, 실험 결과를 버전 관리하면 회귀를 빠르게 잡을 수 있습니다.

 

 

---

 

2. 실행계획 읽기: EXPLAIN/EXPLAIN ANALYZE

 

핵심 요약: 노드 타입·예상/실측 행수·코스트·루프 수를 먼저 본다.

EXPLAIN/ANALYZE 실행계획 주요 필드와 읽는 순서

 

-- 계획만 확인
EXPLAIN SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 day'
  AND c.tier = 'gold'
ORDER BY o.created_at DESC
LIMIT 50;

-- 실제 실행(시간/행수/버퍼) 포함
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT ...;

• 대표 노드
  • Seq Scan: 테이블 전체 스캔. 작은 테이블/조건 없을 때는 정상.
  • Index Scan: 조건에 맞는 범위를 인덱스로 탐색 후 테이블 방문.
  • Bitmap Index/Heap Scan: 넓은 범위에는 비트맵으로 모아 한번에 읽음.
  • Hash/Merge/Nested Loop Join: 데이터 분포와 정렬 여부에 따라 선택.
• 중요 지표
  • rows vs actual rows: 통계 불일치면 ANALYZE 또는 다중 컬럼 통계 확장.
  • cost=Startup..Total: 계획 비교용 상대값.
  • Loops: 반복 실행 횟수. 내부 루프의 높은 반복은 병목 신호.
  • Buffers: shared hit/read 비율로 캐시 효과를 판단.

 

팁: 조건이 인덱스를 잘 못 타면 함수 인덱스 또는 표현식 정규화를 고려하세요.

-- WHERE date(created_at) = '2025-09-01' 는 인덱스 사용이 어렵다
CREATE INDEX idx_orders_created_at_date ON orders ((created_at::date));
-- 혹은 범위로 바꾸기
SELECT ... WHERE created_at >= '2025-09-01' AND created_at < '2025-09-02';

 

 

---

 

3. 인덱스 전략: B-Tree/Hash/GIN/GiST/BRIN

 

핵심 요약: 80%는 B-Tree, 나머지는 데이터 특성에 맞게 선택.

B-Tree·GIN·GiST·BRIN 인덱스 선택 기준 요약

 

3.1 B-Tree(기본)

대부분의 등호/부등호/정렬에 적합. 다중 컬럼 인덱스는 선두 컬럼이 중요합니다.

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);

• 선두 컬럼으로 선택도 높은 필드(카디널리티 높음)를 두면 효과적.
• LIKE 'abc%'는 B-Tree로 가능하나 '%abc'는 불가(함수 인덱스 또는 trgm).

3.2 Hash

등호 비교 전용. 10.x 이후 WAL/복제 지원. 범위/정렬에는 부적합.

3.3 GIN

JSONB/ARRAY/Full-text에 강함. 역색인 구조라 포함 검색/다중 키 매칭에 유리.

-- JSONB 예시
CREATE INDEX idx_users_prefs_gin ON users USING GIN (prefs jsonb_path_ops);
SELECT * FROM users WHERE prefs @> '{"theme":"dark"}';

3.4 GiST

지리공간, 범위 타입, 근접 검색(KNN GiST)에 사용. R-Tree 유사 구조.

3.5 BRIN

아주 큰 테이블의 물리적 정렬(시간 순 등)을 이용해 범위를 요약. 저장 공간이 작고 생성이 빠르지만 정밀도는 낮습니다.

3.6 부분/함수/커버링 인덱스

• Partial: 액티브 데이터만

CREATE INDEX idx_orders_active ON orders (status) WHERE status IN ('paid','shipping');

• Functional

CREATE INDEX idx_norm_email ON users (lower(email));

• Include(커버링)

CREATE INDEX idx_orders_cover ON orders (user_id) INCLUDE (created_at, amount);

 

주의: 인덱스는 쓰기 비용을 증가시킵니다. 핵심 쿼리 기준으로 최소화하세요.

 

 

---

 

4. 조인·정렬·페이징 최적화

 

핵심 요약: 조인 순서·정렬 비용·페이징 방식이 체감 속도를 좌우한다.

조인 전략·정렬 비용·Keyset 페이징 비교 다이어그램

 

4.1 조인

• Nested Loop: 작은 외부→큰 내부 인덱스 조회. 소량·지연 짧음.
• Hash Join: 중간 크기. work_mem에 해시가 올라가야 빠름.
• Merge Join: 양쪽 정렬이 되어 있거나 인덱스 정렬 활용.

SET work_mem='128MB'; -- 해시/정렬 실패로 디스크 스필이 잦다면 상향

4.2 정렬

• ORDER BY는 큰 비용. 인덱스 정렬을 재사용하거나 LIMIT과 함께 사용.
• 넓은 SELECT는 I/O 증가. 필요한 컬럼만 선택.

4.3 페이징

• OFFSET 100000은 금물. Keyset Pagination으로 대체.

-- Keyset: 마지막 키 이후부터 조회
SELECT * FROM orders
WHERE (created_at, id) < ($1, $2)
ORDER BY created_at DESC, id DESC
LIMIT 50;

 

 

---

 

5. VACUUM·ANALYZE·Autovacuum 튜닝

 

핵심 요약: MVCC 특성상 청소와 통계가 필수. 놓치면 부풀어 오른다.

VACUUM/ANALYZE/Autovacuum 동작 흐름도

 

• VACUUM: 죽은 튜플을 재활용. 빈번한 업데이트 테이블은 주기적으로 수행.
• ANALYZE: 통계 수집. 실행계획 정확도를 좌우.
• Autovacuum 파라미터 예시

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_analyze_scale_factor = 0.02,
  autovacuum_vacuum_cost_limit = 4000
);

• Bloat 감지: 인덱스/테이블 부풀음은 쿼리 비용 상승 요인.
• Freeze/Wraparound: 장기간 VACUUM 미수행 시 위험. 경고 로그 주의.

 

 

---

 

6. 락·데드락 트러블슈팅(pg_locks)

 

핵심 요약: 누가 누구를 막는지 식별하고, 트랜잭션 범위를 줄인다.

pg_locks로 보는 차단/대기 관계와 데드락 예시

 

-- 현재 대기/차단 관계 확인(개념 예시)
SELECT bl.pid AS blocked_pid, ka.query AS blocking_query, now()-ka.query_start AS blocking_for
FROM pg_locks bl
JOIN pg_locks kl ON bl.locktype = kl.locktype AND bl.database = kl.database AND bl.relation = kl.relation AND bl.pid <> kl.pid
JOIN pg_stat_activity ka ON kl.pid = ka.pid
WHERE NOT bl.granted;

• Row-level lock: SELECT ... FOR UPDATE/SHARE 사용 시 충돌 주의.
• Deadlock: 잠금 순서를 통일하고, 짧은 트랜잭션을 유지.
• 격리수준: READ COMMITTED 기본. 필요 이상으로 SERIALIZABLE을 쓰지 말 것.
• 인덱스와 락: 업데이트 시 인덱스가 많을수록 잠금 시간이 늘어납니다.

 

 

---

 

7. 실전 체크리스트·FAQ

 

핵심 요약: 계획→인덱스→조인/정렬→청소→락 순서로 점검.

 

체크리스트

• 문제 쿼리를 **EXPLAIN (ANALYZE, BUFFERS)**로 측정했다
• 통계 최신화: 대상 테이블에 ANALYZE 수행
• 인덱스: 선두 컬럼/부분/함수/커버링 검토
• 조인: 해시가 메모리에 올라가도록 work_mem 점검
• 페이징: OFFSET → Keyset 전환
• VACUUM/Autovacuum 파라미터 조정 및 모니터링
• 락·데드락: 차단 세션 식별 및 쿼리/트랜잭션 단축

FAQ

• Q. EXPLAIN 결과가 매번 달라요.
  A. 통계/캐시 상태, 파라미터에 따라 계획이 바뀝니다. ANALYZE, 파라미터 고정, 바인드 변수 사용을 고려하세요.
• Q. 인덱스를 추가했는데 더 느려졌어요.
  A. 쓰기 비용 증가, 잘못된 컬럼 순서, 선택도 낮음이 원인일 수 있습니다. 사용 빈도 낮은 인덱스는 제거하세요.
• Q. 타임아웃/메모리 초과가 자주 나요.
  A. 응답 축소(SELECT 컬럼 제한), work_mem/maintenance_work_mem 조정, 집계 버킷 축소, 배치 처리로 전환하세요.

 

 

---

 

결론

 

핵심 요약: PostgreSQL 쿼리 최적화는 습관이다—측정하고, 가설을 세우고, 작은 변경을 반복하라.

 

PostgreSQL 쿼리 최적화를 제대로 하려면 실행계획 기반의 개발 문화가 필요합니다. 인덱스와 조인 전략을 데이터 특성에 맞게 설계하고, VACUUM/ANALYZE로 통계를 최신 상태로 유지하세요. 락·데드락은 모니터링과 짧은 트랜잭션으로 줄일 수 있습니다. 오늘의 체크리스트와 SQL 스니펫을 팀 표준으로 문서화하면 쿼리 오류와 성능 이슈를 선제적으로 막을 수 있습니다.

 

 

함께 보면 좋은 글

OpenSearch/Elasticsearch 쿼리 오류 완전 가이드: DSL 사용법·매핑 함정·페이징·집계·성능 튜닝

 

RAG 파이프라인 구축: 벡터DB 선택과 프롬프트 전략까지 (실전 가이드)