PostgreSQL 인덱스 기초 가이드: 만들기 전에 확인할 3가지

PostgreSQL 인덱스 기초 가이드: 만들기 전에 확인할 3가지

인덱스를 하나 추가했다고 쿼리가 바로 빨라지겠지, 싶었습니다. 실제로는 잠깐 빨라졌다가 어느 순간 쓰기가 눈에 띄게 느려졌죠. PostgreSQL 인덱스 기초 가이드를 다시 정리하면서 배운 것들, 그대로 적어 둡니다.

인덱스란 정확히 무엇인가

한 줄로 정리하면, 인덱스는 표 안에서 특정 값을 빨리 찾아내기 위한 정렬된 목록입니다. 그게 전부예요.

공식 문서를 확인하면 인덱스 종류가 여섯 가지로 나뉘어 있고, 각각이 잘 맞는 상황이 분명히 다릅니다. 아무 인덱스나 만드는 게 아니라, 데이터가 들어오는 모양에 맞춰 골라야 하는 이유죠.

조건을 안 걸고 전체를 훑는 방식을 순차 스캔이라고 합니다. 조건에 맞는 행이 몇 건 안 되는데도 전체를 읽는다면 인덱스를 쓰는 편이 훨씬 낫고요. 반대로 대부분이 걸려서 순차 스캔이 낫다고 플래너가 판단할 수도 있습니다.

⚠️

인덱스는 읽기를 빨리 하는 대신 쓰기를 늦게 만듭니다. 추가 전엔 그 쿼리가 정말 느린지부터 확인하세요.

종류별 비교표: 어떤 인덱스를 언제 쓸까

인덱스 종류	주로 쓰는 방식	잘 맞는 상황
B-tree	등호, 범위 조건	기본값. 거의 모든 출발점
Hash	등호 조건만	크기 비교가 필요 없는 온열 데이터
GIN	배열, jsonb, 전문 검색	jsonb 속성 조회, 전문 검색
GiST	기하, 범위 자료형	PostGIS, 범주 자료형
SP-GiST	공간 파티셔닝 자료형	네트 주소, 코드 범위
BRIN	큰 정렬된 표	시간 순으로 쌓이는 로그 표

실전: 인덱스를 하나씩 붙이는 순서

1. 느린 쿼리부터 찾기 EXPLAIN (ANALYZE, BUFFERS) 로 실행 계획을 봅니다. 순차 스캔이 반복해서 뜨면 인덱스 후보예요.
2. 조건을 담을 컬럼 고르기 WHERE, JOIN, ORDER BY에 자주 등장하는 컬럼부터 봅니다.
3. 동시 생성으로 막힘 줄이기 CREATE INDEX CONCURRENTLY 는 기존 쓰기를 오래 막지 않습니다. 대신 시간이 더 걸리고, 트랜잭션 블록 안에서는 실행할 수 없습니다.
4. 생성 직후 ANALYZE 통계가 갱신돼야 옵티마이저가 새 인덱스를 후보로 올려줍니다.
5. 안 쓰는 인덱스는 정리 pg_stat_user_indexes에서 idx_scan 이 0인 인덱스를 찾고, 제약과 연결돼 있지 않은지 확인한 뒤 DROP INDEX CONCURRENTLY 로 정리합니다.

제 시간 낭비 이야기 하나. 큰 테이블에 인덱스를 그냥 만들었다가, 잠깐 동안 쓰기가 다 막혔습니다. 원인은 의외로 단순했어요. 스크립트를 트랜잭션으로 감싸고 그 안에서 인덱스를 만들었거든요. CREATE INDEX CONCURRENTLY cannot run inside a transaction block 이라는 오류가 그대로 떴습니다. 이 에러를 한참 붙잡고 있다가 결국 트랜잭션 밖에서 단독 실행하라는 답을 찾아 해결했습니다. 뻔한 건데도 반복할 뻔했어요.

숫자로 본 PostgreSQL 인덱스 기초 가이드의 현재

• 인덱스 종류는 6가지입니다.
• 주요 버전은 매년 9월쯤 하나씩 올라가고, 각 주 버전은 5년 동안 지원됩니다.
• PostgreSQL 18 릴리스 노트에 따르면 B-tree 스킵 스캔과 비동기 입출력 지원이 추가됐습니다.
• PostgreSQL 13에서 들어온 B-tree 중복 키 제거 덕분에, 키가 겹치는 표에서는 인덱스 크기가 줄고 캐시 적중률이 올라갑니다.

이게 말해주는 건 하나예요. 어떤 인덱스를 만들지보다, 지금 어느 버전에서 돌고 있고 데이터가 어떻게 들어와 있는가가 먼저입니다. 스택오버플로우 답변에 따르면 인덱스는 보통 테이블 단위로 하나씩 붙이고, 유니크 제약이나 외부 키에는 내부적으로 인덱스가 자동으로 만들어진다고 합니다. 외래 키 컬럼에 인덱스를 안 두면 조인할 때 전체를 훑게 되죠.

•

테이블에 붙은 인덱스 목록이 궁금하면 스택오버플로우 답변을 보시면 됩니다. pg_indexes 뷰와 pg_index 정의 두 가지 방법이 나옵니다.

앞으로 어떻게 변할까

앞으로 1~2년 사이에는 세 갈래로 갈립니다.

• B-tree 범위 확장: 스킵 스캔 덕분에 첫 번째 컬럼 조건이 약해도 인덱스를 탈 수 있게 됐습니다.
• 대기 시간 개선: 비동기 입출력이 늘면서 디스크 기반 환경에서도 지연이 눈에 띄게 줄고요.
• 통계와 자동화: 옵티마이저가 힌트 없이도 더 자주 무난한 계획을 고릅니다.

현업에서는 "인덱스를 더" 보다는 "안 쓰는 인덱스를 덜" 하는 쪽이 이득인 경우가 많습니다. 쓰기 비중이 높은 표일수록요.

마무리

정리하면 이렇습니다. 인덱스는 읽기와 쓰기의 균형 맞추기, 데이터 모양에 맞는 종류 선택, 안 쓰는 것 정리하기. 이 세 가지만 챙겨도 대부분의 성능 고민이 해결됩니다. 그리고 막히면 실행 계획을 먼저 보세요. 거의 다 거기로 수렴하더라고요.

오늘은 여기까지. 도움이 되셨으면 좋겠네요.

자주 묻는 질문

Q. 인덱스를 하나 더 추가하면 항상 빨라지나요? A. 아닙니다. 쓰기 부하가 늘어날 수 있고, 플래너가 아예 인덱스를 무시하는 경우도 있습니다. 실행 계획으로 확인하세요.

Q. CREATE INDEX 와 CREATE INDEX CONCURRENTLY 는 어떻게 다른가요? A. 동시 생성은 기존 쓰기를 막지 않아 대신 시간이 더 걸립니다. 트랜잭션 블록 안에서는 실행할 수 없다는 점도 기억해 두세요.

Q. 안 쓰는 인덱스는 어떻게 찾나요? A. pg_stat_user_indexes 를 조회해 idx_scan 이 0인 인덱스를 후보로 봅니다. 제약이나 외부 키와 연결돼 있지 않은지 확인한 뒤 정리하세요.

Q. jsonb 필드에 인덱스를 만들면 어떻게 하나요? A. GIN 인덱스를 씁니다. 일반적인 경로 조건은 jsonb_ops, 특정 경로 조건은 jsonb_path_ops를 씁니다. 경로가 고정돼 있다면 식 인덱스가 더 가벼울 때도 있어요.

Q. 인덱스가 있는데도 순차 스캔으로 실행되면? A. 통계가 오래됐거나, 조건이 인덱스와 맞지 않거나, 반환 행이 너무 많아 플래너가 풀 스캔을 택한 경우입니다. ANALYZE 로 통계부터 갱신해 보세요.

끝까지 읽어주셔서 고맙습니다. 궁금한 점은 댓글로 알려주세요. 이 글이 조금이라도 도움이 됐다면 다음 글도 기대해 주세요.

#PostgreSQL #인덱스 #데이터베이스 #DB최적화 #PostgreSQL기초