측정이 먼저 — pg_stat_statements와 auto_explain
데이터베이스 쿼리 최적화는 백엔드 성능의 출발점이다. 애플리케이션이 잘 짜여 있어도 느린 쿼리 하나가 응답 시간을 끌어내리고, 동시 접속이 늘면 커넥션이 밀리며 서비스 전체가 느려진다. 그런데 «체감상 느린 화면»과 «통계상 비용이 큰 쿼리»는 자주 다르다. 그래서 첫걸음은 도구 도입이 아니라 측정이다.
pg_stat_statements — 어떤 쿼리가 시간을 가장 많이 쓰나
PostgreSQL 문서 기준으로 pg_stat_statements는 공유 메모리가 필요해 postgresql.conf의 shared_preload_libraries에 넣고 재시작해야 하며, 쿼리 식별자 계산이 켜져 있어야 한다(compute_query_id가 auto나 on이면 자동). 주요 열은 실행 횟수(calls), 총 실행 시간(total_exec_time), 평균(mean_exec_time), 처리 행 수(rows), 공유 버퍼 적중·읽기(shared_blks_hit·shared_blks_read)다.
-- 총 실행 시간이 큰 쿼리 상위 10개 + 버퍼 적중률
SELECT query,
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
rows,
round(100.0 * shared_blks_hit /
nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;정렬 기준은 평균이 아니라 총 시간이다. 한 번에 2초 걸리는 쿼리보다 20ms짜리가 하루 수백만 번 불리는 쪽이 전체 부하를 더 많이 차지하는 경우가 흔하다. 통계를 비우는 pg_stat_statements_reset()은 기본으로 슈퍼유저만 쓸 수 있고, 비우면 비교 기준도 사라지므로 바꾸기 전후를 잴 때만 의도적으로 쓴다.
auto_explain — 느린 쿼리의 계획을 자동으로 남기기
auto_explain 모듈은 느린 문장의 실행 계획을 손으로 EXPLAIN하지 않아도 로그에 남긴다. auto_explain.log_min_duration(밀리초, 기본 -1 = 끔)을 넘는 문장만 기록한다. 문서는 auto_explain.log_analyze를 켜면 기록 여부와 상관없이 모든 문장에 노드별 시간 측정이 붙어 성능에 매우 나쁜 영향을 줄 수 있다고 경고한다. 운영에서 켠다면 log_timing을 끄는 식으로 부담을 줄인다.
응답 지연을 대시보드로 함께 보면 «어느 API가 어느 쿼리 때문에 느린가»를 잇기 쉽다. 지연 히스토그램과 SLI 식은 실시간 성능 모니터링과 자동 스케일링에 정리했다.
실행 계획 읽기
쿼리 옵티마이저는 SQL을 받아 가능한 실행 경로(인덱스 스캔, 순차 스캔, 조인 방식 등) 가운데 비용이 가장 낮을 것으로 추정되는 경로를 고른다. 핵심은 «추정»이다. 판단은 테이블 통계(행 수, 값 분포)에 기대므로 통계가 실제와 어긋나면 잘못된 계획을 고른다. AI 도구든 사람이든 가장 먼저 보는 것이 이 통계와 실행 계획이다.
-- 실제 실행 계획과 버퍼 사용량까지 확인 EXPLAIN (ANALYZE, BUFFERS) SELECT o.id, o.total, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.created_at >= now() - interval '7 days' AND o.status = 'paid' ORDER BY o.created_at DESC LIMIT 50;
| 계획에서 보이는 것 | 뜻 | 먼저 해 볼 것 |
|---|---|---|
| 추정 rows와 actual rows가 크게 다름 | 통계가 실제와 어긋남 | ANALYZE 테이블 · 상관된 열이면 확장 통계 |
| 큰 테이블에 Seq Scan | 쓸 만한 인덱스가 없거나 선택도가 낮음 | 조건·정렬 열 인덱스 후보 |
| Sort Method: external merge | 정렬이 메모리를 넘어 디스크로 | 정렬 방향까지 맞춘 인덱스 · work_mem 검토 |
| Nested Loop 안쪽이 수만 번 반복 | 바깥 행 추정이 틀렸을 가능성 | 통계 갱신 · 조인 열 인덱스 |
| shared read가 hit보다 훨씬 큼 | 캐시에 없는 블록을 많이 읽음 | 읽는 범위 줄이기(부분 인덱스·필요한 열만) |
주의할 점이 하나 있다. EXPLAIN ANALYZE는 쿼리를 실제로 실행한다. UPDATE·DELETE의 계획을 볼 때는 트랜잭션 안에서 실행하고 ROLLBACK하거나, ANALYZE 없이 추정 계획만 본다.
인덱스 설계와 안전한 적용
워크로드에 맞춘 복합 인덱스
효과가 큰 작업은 자주 쓰는 조건·정렬·조인 열에 맞는 인덱스를 거는 것이다. 복합 인덱스는 열 순서가 성능을 가른다. 등호 조건 열을 앞에, 범위·정렬 열을 뒤에 둔다. 앞의 주문 조회(status = 'paid' 등호 + created_at 범위·정렬)에는 다음 부분 인덱스가 맞는다. paid 행만 담아 크기를 줄이고, 정렬 방향까지 맞춰 별도 Sort를 없앤다.
-- 부분 인덱스: 조건이 고정된 status 는 WHERE 로 빼고, 정렬 열은 방향까지 맞춤
CREATE INDEX CONCURRENTLY idx_orders_paid_recent
ON orders (created_at DESC)
WHERE status = 'paid';
-- 조인 열(외래 키)은 PostgreSQL 이 자동으로 인덱스를 만들지 않는다
CREATE INDEX CONCURRENTLY idx_orders_customer
ON orders (customer_id);
ANALYZE orders;CONCURRENTLY의 조건
운영 중인 큰 테이블에는 CREATE INDEX CONCURRENTLY를 쓴다. 일반 CREATE INDEX는 만드는 동안 쓰기를 막는다. PostgreSQL 문서가 적는 조건은 세 가지다.
- 트랜잭션 블록 안에서는 못 쓴다 — 마이그레이션 도구가 파일 하나를 트랜잭션으로 감싸는지 먼저 확인한다.
- 더 오래 걸린다 — 테이블을 두 번 훑고, 인덱스를 쓰거나 고칠 수 있는 기존 트랜잭션이 끝나기를 기다린다.
- 실패하면 INVALID 인덱스가 남는다 — 조회에는 쓰이지 않지만 쓰기 부담은 계속 든다. 문서가 권하는 복구는 그 인덱스를 지우고 다시 만드는 것이다.
-- 실패해서 남은 INVALID 인덱스 찾기 SELECT indexrelid::regclass AS index_name FROM pg_index WHERE NOT indisvalid;
AI 도구를 끼워 넣는 자리
AI 기반 최적화를 마케팅 용어가 아니라 작업 단계로 보면, AI가 잘하는 일과 사람이 남겨야 할 일이 갈린다.
| 단계 | AI(대화형 모델·분석 도구)에 맡기기 좋은 일 | 사람·DB가 확인할 일 |
|---|---|---|
| 1. 후보 찾기 | pg_stat_statements 상위 목록을 묶고 패턴 정리 | 그 쿼리가 실제로 중요한 경로인지 |
| 2. 계획 해석 | 긴 EXPLAIN 출력에서 추정·실제 차이, 디스크 정렬 짚기 | 통계 갱신 뒤에도 같은지 |
| 3. 인덱스·재작성 제안 | 열 순서, 부분 인덱스, 서브쿼리 → 조인 같은 재작성안 | 결과가 같은지(행 수·정렬), 쓰기 부담 |
| 4. 검증 | 검증 SQL 초안 작성 | 가상 인덱스·사본 DB에서 계획과 시간 측정 |
| 5. 적용·기록 | 마이그레이션 파일 초안 | CONCURRENTLY 조건, 되돌리기 계획, 변경 기록 |
가상 인덱스로 먼저 확인 — HypoPG
AI가 제안한 인덱스를 바로 만들면 큰 테이블에서는 시간과 디스크가 든다. HypoPG 확장은 실제로는 만들지 않는 가상 인덱스를 만들어, 플래너가 그 인덱스를 쓸지 EXPLAIN(ANALYZE 없이)으로 미리 보여 준다. 가상 인덱스는 지금 세션에만 있어 다른 연결에 영향을 주지 않는다. EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로 가상 인덱스를 무시한다는 점만 기억하면 된다.
CREATE EXTENSION IF NOT EXISTS hypopg; -- AI 가 제안한 인덱스를 가상으로 만들어 보기 SELECT * FROM hypopg_create_index( 'CREATE INDEX ON orders (created_at DESC) WHERE status = ''paid'''); -- 플래너가 그 인덱스를 고르는지 확인 (ANALYZE 없이) EXPLAIN SELECT id, total FROM orders WHERE status = 'paid' AND created_at >= now() - interval '7 days' ORDER BY created_at DESC LIMIT 50; SELECT hypopg_reset(); -- 이 세션의 가상 인덱스 모두 지우기
AI에 쿼리와 계획을 넘길 때는 실제 데이터 값이 든 리터럴을 빼고, 테이블 구조와 계획만 넘긴다. 정규화된 쿼리 문자열(pg_stat_statements의 query는 상수를 자리표시자로 바꿔 둔다)을 쓰면 이 일이 쉬워진다. 도구가 «30% 빨라진다» 같은 예상치를 내놓더라도, 근거는 자기 데이터에서 다시 잰 숫자여야 한다.
PostgreSQL 18에서 바뀐 것 — 비동기 I/O와 uuidv7
PostgreSQL 공식 지원 정책 표 기준으로 지금 최신 안정 계열은 18(현재 18.6, 2025-09-25 첫 출시, 2030-11-14 지원 종료)이고, 19는 RC 단계다(2026-10-30 확인).
비동기 I/O — io_method
PostgreSQL 18에는 비동기 I/O 하위 시스템이 들어왔다. 설정 io_method는 비동기 I/O를 실행하는 방식을 고르고, 값은 세 가지다. worker(기본, I/O 작업 프로세스 사용 · 개수는 io_workers, 기본 3), io_uring(Linux io_uring, 해당 옵션으로 빌드해야 함), sync(비동기 대상 I/O를 동기로 실행)다. 서버 시작 때만 바꿀 수 있다. 효과는 저장 장치와 읽기 패턴에 따라 달라지므로, 판을 올린 뒤 1절의 상위 쿼리를 같은 조건에서 다시 재서 비교한다.
uuidv7 — 시간순 UUID
PostgreSQL 18 문서 기준으로 uuidv7()은 밀리초 단위 Unix 타임스탬프와 그 아래 정밀도, 난수로 만든 시간순(버전 7) UUID를 돌려준다. 무작위인 gen_random_uuid()·uuidv4()와 달리 새 값이 늘 B-tree 인덱스 끝쪽에 붙으므로, 삽입이 많은 테이블에서 기본 키 인덱스가 여기저기 흩어져 쓰이는 일이 줄어든다. uuid_extract_timestamp()로 값에서 생성 시각을 꺼낼 수 있지만, 문서는 그 값이 생성 시각과 정확히 같지는 않을 수 있다고 적는다.
-- PostgreSQL 18: 새 행의 기본 키를 시간순 UUID 로 ALTER TABLE orders ALTER COLUMN id SET DEFAULT uuidv7();
시간순 UUID는 생성 시각을 드러낸다. 외부에 노출되는 식별자라면 이 점을 받아들일 수 있는지 함께 판단한다. 관리형 PostgreSQL에서 판 선택과 권한 설정은 Supabase 실전 가이드에서도 다뤘다.
같은 DB 안의 벡터 검색 — pgvector
RAG 챗봇이나 의미 검색을 위해 별도 벡터 DB를 들이기 전에, 이미 쓰는 PostgreSQL에 pgvector 확장을 얹는 선택지를 먼저 본다. pgvector 저장소 기준 최신판은 0.8.7이다(2026-10-30 확인).
CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE documents ADD COLUMN embedding vector(1536);
-- HNSW 인덱스 (m, ef_construction 은 기본값 16, 64)
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
-- 검색 품질과 속도 조절 (기본 40)
SET hnsw.ef_search = 100;
-- 필터를 함께 걸 때 결과가 모자라지 않게 (0.8.0 부터)
SET hnsw.iterative_scan = relaxed_order;
SELECT id, title
FROM documents
WHERE category = 'faq'
ORDER BY embedding <=> $1 -- $1: 질의 임베딩
LIMIT 5;| 항목 | pgvector 문서 기준 |
|---|---|
| HNSW 기본값 | m 16 · ef_construction 64 · hnsw.ef_search 40 |
| HNSW로 인덱싱할 수 있는 차원 | vector 2,000 · halfvec 4,000 |
| 필터 + 근사 검색 | 0.8.0부터 반복 인덱스 스캔(hnsw.iterative_scan)으로 결과가 모자라는 문제 완화 |
차원이 2,000을 넘는 임베딩 모델을 쓰면 vector 열에 HNSW 인덱스를 걸 수 없으므로 halfvec으로 바꾸거나 차원을 줄인 모델을 고른다. 근사 검색이라 ef_search를 올리면 정확도가 오르고 느려진다. 이 값과 인덱스 크기를 실제 데이터로 재 본 뒤에 «전용 벡터 DB가 필요한가»를 판단한다. 데이터 모델 쪽 고민은 NoSQL 데이터 모델링 패턴 가이드와 비교해 볼 만하다.
정리 — 최적화 점검 순서
도구는 빨리 바뀌어도 측정하고 검증하는 원칙은 오래간다. AI는 1·2·3단계를 빠르게 해 주고, 4·5단계는 데이터베이스와 사람이 확인한다.
| 순서 | 할 일 | 확인 |
|---|---|---|
| 1 | pg_stat_statements 총 시간 상위 10개 | 바꾸기 전 기준값 저장 |
| 2 | EXPLAIN (ANALYZE, BUFFERS)로 계획 읽기 | 추정·실제 행 차이 · 디스크 정렬 |
| 3 | 통계 갱신 → 인덱스·재작성 후보(AI 제안 포함) | 결과 행·정렬이 같은지 |
| 4 | HypoPG 가상 인덱스 · 사본 DB에서 측정 | 플래너가 실제로 고르는지 |
| 5 | CREATE INDEX CONCURRENTLY로 적용 | INVALID 인덱스 없음 · 1번 기준값과 비교 |
자주 묻는 것
AI가 추천한 인덱스를 바로 만들어도 되나요?
바로 만들기보다 HypoPG 같은 가상 인덱스로 플래너가 그 인덱스를 실제로 고르는지 EXPLAIN으로 먼저 확인하고, 사본 DB에서 시간을 잰 뒤 CREATE INDEX CONCURRENTLY로 적용하는 편이 안전합니다. 결과 행과 정렬이 바뀌지 않는지도 함께 봅니다.
느린 쿼리는 어떻게 찾나요?
pg_stat_statements를 shared_preload_libraries에 넣고 켠 뒤 total_exec_time 순으로 상위 쿼리를 봅니다. 평균보다 총 시간이 전체 부하를 더 잘 보여 줍니다. 계획까지 자동으로 남기려면 auto_explain의 log_min_duration을 설정합니다.
CREATE INDEX CONCURRENTLY는 언제나 안전한가요?
쓰기를 막지 않는 대신 트랜잭션 블록 안에서는 쓸 수 없고, 테이블을 두 번 훑어 더 오래 걸립니다. 중간에 실패하면 INVALID 인덱스가 남아 쓰기 부담만 주므로, 그 인덱스를 지우고 다시 만듭니다.
PostgreSQL 18의 uuidv7은 왜 쓰나요?
uuidv7은 밀리초 타임스탬프를 앞에 둔 시간순 UUID라 새 값이 B-tree 인덱스 끝쪽에 붙습니다. 무작위 UUID보다 삽입이 많은 테이블에서 기본 키 인덱스가 흩어져 쓰이는 일이 줄어듭니다. 다만 값에서 생성 시각이 드러난다는 점은 함께 고려합니다.
참고 자료
- PostgreSQL 18 Docs. pg_stat_statements — shared_preload_libraries·compute_query_id·주요 열·reset 권한(2026-10-30 확인). postgresql.org
- PostgreSQL 18 Docs. auto_explain — log_min_duration 기본 -1·log_analyze 성능 경고(2026-10-30 확인). postgresql.org
- PostgreSQL 18 Docs. CREATE INDEX — CONCURRENTLY 트랜잭션 블록 불가·두 번 스캔·INVALID 인덱스 복구(2026-10-30 확인). postgresql.org
- PostgreSQL 18 Docs. Resource Consumption — io_method(worker 기본·io_uring·sync)·io_workers 기본 3(2026-10-30 확인). postgresql.org
- PostgreSQL 18 Docs. UUID Functions — uuidv7·uuidv4·uuid_extract_timestamp(2026-10-30 확인). postgresql.org
- PostgreSQL. Versioning Policy — 18.6·첫 출시 2025-09-25·지원 종료 2030-11-14(2026-10-30 확인). postgresql.org
- HypoPG README — 가상 인덱스·EXPLAIN만·세션 한정·hypopg_reset(2026-10-30 확인). github.com
- pgvector README — 0.8.7·HNSW 기본값·ef_search 40·iterative_scan(0.8.0)·차원 한도(2026-10-30 확인). github.com