PostgreSQL Index 구조와 활용
같은 “인덱스”지만 PostgreSQL과 MySQL InnoDB는 보관과 탐색 방식이 정반대다.
Heap + Secondary Index 모델
PostgreSQL에서 테이블은 Heap이라는 정렬되지 않은 페이지 묶음이다. 행은 들어온 순서대로 빈 자리에 채워지고, 인덱스는 그 행의 물리적 위치를 가리키는 별도 자료구조다. 위치 식별자는 TID (Tuple Identifier)로, (page_id, offset) 쌍이다.
Heap은 행 데이터의 실제 저장소다. 인덱스는 TID 포인터를 들고 있는 보조 자료구조이고, 행 데이터 자체는 들지 않는다.
InnoDB와 다른 두 가지 핵심.
- PK 인덱스도 Secondary Index와 똑같이 별도 자료구조다. PK 조회도 인덱스에서 TID를 얻은 뒤 Heap을 한 번 더 읽는 두 단계.
- 인덱스 탐색이 끝나도 Heap을 한 번 더 읽어야 한다. 이 두 번째 접근을 줄이는 게 PostgreSQL Index 운영의 큰 화두.
MySQL InnoDB는 반대 모델이다. PK 자체가 B+Tree로 구성돼 리프에 행 데이터가 통째로 들어 있다 (Clustered Index, MySQL InnoDB EXPLAIN과 인덱스 구성 전략 글에서 다뤘다). PostgreSQL은 PK도 별도 인덱스, 행은 Heap에 따로 두는 분리 모델이라 같은 PK 조회라도 내부 동작이 갈린다. 단건 PK 조회의 페이지 접근 횟수 비교를 정리하면 다음과 같다.
| 단계 | PostgreSQL | MySQL InnoDB |
|---|---|---|
| 1 | PK B+Tree 탐색 → TID 획득 | PK B+Tree 탐색 → 행 데이터 도착 |
| 2 | TID로 Heap 페이지 읽어 행 가져오기 | (필요 없음) |
| 합계 | 2회 접근 | 1회 접근 |
PK 자료구조 자체는 둘 다 B+Tree 계열로 같다 (PostgreSQL은 PRIMARY KEY 제약을 만들면 자동으로 B-Tree 인덱스를 생성한다). 차이는 B+Tree 리프에 무엇이 있느냐다. InnoDB는 리프에 행 데이터가 통째로 있어 한 번에 끝나고, PostgreSQL은 리프에 TID만 있어 Heap을 한 번 더 읽어야 한다.
인덱스는 키와 TID(
(page_id, offset))만 들고 있고, 실제 행 데이터는 Heap 페이지에 흩어져 있다.
인덱스 종류
PostgreSQL은 한 테이블에 여러 종류의 인덱스를 섞어 쓸 수 있다. 데이터 모양과 쿼리 패턴에 따라 선택한다.
B-Tree
기본값. 등호와 부등호, 범위, 정렬을 한 인덱스로 처리한다. PostgreSQL의 B-Tree는 이름과 달리 실제로는 B+Tree 변형이라 리프 간에 링크가 있고, 범위 스캔과 ORDER BY를 모두 인덱스 안에서 끝낸다. 별 다른 옵션 없이 CREATE INDEX하면 PostgreSQL은 B-Tree를 만든다.
CREATE INDEX idx_email ON users (email);id, email, created_at 같은 단일값 컬럼에 첫 선택지로 쓴다. 거의 모든 일반 컬럼이 B-Tree로 충분하다.
GIN
Generalized Inverted Index. 한 행이 여러 값을 가지는 데이터를 위한 역색인이다. 한 키가 여러 행을 가리키는 구조라, 컬렉션 안의 특정 원소를 찾는 쿼리에 강하다.
-- jsonb 안의 키 조회
CREATE INDEX idx_meta ON events USING gin (meta jsonb_path_ops);
-- full-text search
CREATE INDEX idx_doc ON documents USING gin (to_tsvector('simple', body));
-- array 멤버십
CREATE INDEX idx_tags ON posts USING gin (tags);쓰기마다 인덱스가 통째로 재구성되는 구조라 쓰기는 무겁고 읽기는 빠르다. 검색이 잦은 jsonb 컬럼, 태그 배열, full-text 본문에 적합하다.
GiST / SP-GiST
Generalized Search Tree. 비교 연산을 사용자가 정의할 수 있는 일반화된 검색 트리다. 좌표나 기하 객체, 범위처럼 표준 < > 비교가 안 되는 데이터를 다룬다.
PostGIS 공간 쿼리(“이 점이 이 폴리곤 안에 있는가”), tstzrange 같은 범위 타입, 트라이그램 기반 유사도 검색에 적합하다.
BRIN
Block Range Index. 한 페이지 묶음 단위로 “이 범위의 페이지에는 이런 값들이 있다”는 요약(min/max)만 저장한다. 인덱스 자체가 아주 작아서 거대 테이블에 부담이 없다.
CREATE INDEX idx_created ON events USING brin (created_at);같은 테이블에 B-Tree로 만들면 인덱스가 GB 단위인데, BRIN으로 만들면 MB 단위다. 단 물리 저장 순서와 논리 정렬이 일치해야 효과가 난다. created_at처럼 행이 시간 순으로 누적되는 시계열 로그, 이벤트 스토어, 감사 로그에 적합하다.
Hash
등호 전용. PostgreSQL 9.4 이전에는 WAL이 안 되는 위험한 옵션이었다. 9.4부터 WAL 지원으로 안전해졌지만, 범위와 정렬을 지원하지 않고 B-Tree보다 빠르지도 않아 실무에서는 거의 안 쓰인다.
| 타입 | 유스케이스 | 적합한 컬럼과 쿼리 |
|---|---|---|
| B-Tree | 일반 등호, 범위, 정렬 | id, email, created_at 같은 단일값 |
| GIN | 역색인 (multi-value) | jsonb, tsvector, tag[] |
| GiST | 사용자 정의 비교 | PostGIS 좌표, tstzrange, 트라이그램 |
| BRIN | 정렬된 거대 테이블 | 시계열 로그의 created_at |
| Hash | 등호 전용 | 9.4+ WAL 안전, 거의 안 씀 |
Index-Only Scan
Heap + Index 모델에서 가장 비싼 부분이 인덱스 탐색 후 Heap을 한 번 더 읽는 두 번째 접근이다. PostgreSQL은 이 두 번째 접근을 생략할 수 있다.
조건은 두 가지.
- 쿼리에 필요한 컬럼이 모두 인덱스에 들어있다 (covering)
- 페이지의 Visibility Map(VM)이 켜져 있다, 즉 해당 페이지의 모든 튜플이 모든 트랜잭션에 보이는 상태
CREATE INDEX idx_user_email ON users (id, email);
EXPLAIN SELECT id, email FROM users WHERE id = 100;
-- → Index Only Scan여기서 VACUUM과 직접 연결된다. VM은 VACUUM이 갱신하므로, VACUUM이 자주 돌수록 Index-Only Scan이 더 자주 동작한다. 자세한 건 PostgreSQL VACUUM과 Autovacuum 글에서 다뤘다.
인덱스에는 가시성 정보가 없다. PostgreSQL은 인덱스만 보고 “이 행이 지금 보여도 되는지” 판단할 수 없어서 기본적으로 Heap의 튜플을 읽어 xmin/xmax를 확인한다. VM이 켜진 페이지만 그 검사를 건너뛸 수 있다.
VM이 켜진 페이지는 인덱스만으로 가시성 판정이 끝나 Heap 접근을 건너뛴다.
PostgreSQL 11부터는 INCLUDE 절로 명시적 covering index를 만들 수 있다.
CREATE INDEX idx_user_email ON users (id) INCLUDE (email, name);INCLUDE 컬럼은 인덱스 키로 쓰이지 않고(B-Tree 정렬에 안 들어감) 리프 노드에만 데이터로 저장된다. covering 목적의 명시적 표현이다.
HOT update와 인덱스
UPDATE가 일어나면 PostgreSQL은 새 튜플을 만들고 옛 튜플은 dead로 표시한다. 보통은 인덱스도 새 튜플 위치를 가리키도록 갱신해야 한다.
다만 두 조건이 맞으면 인덱스를 건드리지 않는다. HOT(Heap-Only Tuple) update.
- 갱신되는 컬럼이 인덱스에 포함되지 않는다
- 새 튜플이 같은 페이지 안에 들어갈 자리가 있다 (
fillfactor여유)
HOT가 발동하면 인덱스의 TID 포인터는 그대로 두고, Heap의 옛 튜플이 새 튜플을 가리키는 chain을 만든다. 인덱스 갱신 비용과 dead tuple 회수 비용이 동시에 줄어든다.
거꾸로 말하면 인덱스가 많은 컬럼을 자주 갱신하는 테이블은 HOT가 발동하지 않는다. 인덱스 수와 갱신 빈도는 trade-off다.
Partial Index
조건을 만족하는 행만 인덱싱하는 옵션. 인덱스 크기가 줄고 쓰기 비용도 줄어든다.
-- 활성 사용자만 인덱싱
CREATE INDEX idx_active_users ON users (last_login)
WHERE status = 'active';
-- 미처리 주문만 인덱싱
CREATE INDEX idx_pending_orders ON orders (created_at)
WHERE status = 'pending';status='pending'이 전체 주문 중 1%인 상황이라면, partial index는 1% 크기에 1% 쓰기 비용으로 같은 효과를 낸다. 쿼리 옵티마이저는 WHERE 절이 인덱스 조건을 implies할 때 자동으로 partial index를 선택한다.
Expression Index
표현식 결과를 인덱싱한다. 함수가 씌워진 컬럼에도 인덱스를 태우려면 이 기능이 필요하다.
CREATE INDEX idx_lower_email ON users ((LOWER(email)));
SELECT * FROM users WHERE LOWER(email) = 'alice@a.com';
-- → 위 인덱스 사용MySQL은 8.0 이전에 이 기능이 없어서 generated column으로 우회해야 했다. PostgreSQL은 오랫동안 표준 기능으로 제공한다.
REINDEX CONCURRENTLY
인덱스도 시간이 지나면 bloat가 쌓인다 (B-Tree split과 dead key 누적). REINDEX는 인덱스를 새로 만드는 명령인데, 일반 모드는 ACCESS EXCLUSIVE 락을 잡아 운영 중에는 쓸 수 없다.
PostgreSQL 12부터 CONCURRENTLY 옵션이 생겨 짧은 락만 잡고 새 인덱스를 만들 수 있다.
REINDEX INDEX CONCURRENTLY idx_user_email;내부적으로는 새 인덱스를 만들고, 다 만들어지면 old swap 후 drop. 그동안 SELECT, INSERT, UPDATE는 정상 동작한다.
InnoDB와 비교
InnoDB에서 WHERE email = 'X'는 Secondary Index 리프에서 PK를 찾고, Clustered Index를 다시 탐색해 행을 가져오는 Bookmark Lookup이 일어난다. 인덱스를 두 번 탐색하는 셈이다.
PostgreSQL은 인덱스 한 번에 Heap 한 번이라 비용이 비슷하거나 더 싸다.
대신 PostgreSQL은 Index-Only Scan을 위해 VM이 항상 최신이어야 한다는 운영 부담이 있다.
InnoDB는 Secondary Index 자체에 PK가 들어있어 covering 효과가 자동으로 따라온다.
| 항목 | PostgreSQL | MySQL InnoDB |
|---|---|---|
| 모델 | Heap과 Secondary Index 분리 | Clustered Index (테이블 = PK B+Tree) |
| PK 조회 | Index → Heap (2회) | PK 인덱스 1회 |
| Secondary 조회 | Index → Heap (2회) | Secondary → PK B+Tree (Bookmark Lookup) |
| Covering 조건 | INCLUDE 또는 모든 SELECT 컬럼이 키 | Secondary Index가 자동으로 PK 포함 |
| Index-Only Scan | VM 의존 (VACUUM 갱신 필요) | 자동 |
| 인덱스 종류 | B-Tree, GIN, GiST, BRIN, Hash, SP-GiST | B-Tree, Hash(메모리), 함수와 공간 인덱스는 8.0+ |
같은
WHERE id = 100이라도 PG는 인덱스와 Heap 두 번, InnoDB는 PK B+Tree 리프 한 번으로 끝난다.
InnoDB 쪽 Bookmark Lookup과 Covering의 디테일은 MySQL InnoDB EXPLAIN과 인덱스 구성 전략 글에서 따로 다뤘다.
Things to note
안 쓰이는 인덱스 정리
인덱스는 디스크와 쓰기 비용을 잡아먹는다. 안 쓰이면 그 비용이 그대로 손해다. PostgreSQL은 사용 통계를 시스템 카탈로그에 기록한다.
SELECT relname AS table_name, indexrelname AS index_name, idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;idx_scan = 0이고 시스템이 충분히 오래 운영된 인덱스는 제거 후보다. 단 통계가 최근에 reset됐다면 오해할 수 있으니 pg_stat_get_db_stat_reset_time(oid)도 같이 본다.
인덱스 bloat 모니터링
B-Tree 인덱스도 dead key가 쌓이면 효율이 떨어진다. pgstattuple 익스텐션으로 측정한다.
CREATE EXTENSION pgstattuple;
SELECT * FROM pgstatindex('idx_user_email');avg_leaf_density가 낮으면 (보통 70% 미만) bloated. REINDEX CONCURRENTLY로 정리한다.
인덱스 무리하게 만들지 않기
인덱스 1개당 INSERT/UPDATE 비용이 추가된다. 모든 컬럼에 단일 인덱스를 거는 것은 안티패턴이다. 자세한 일반 전략은 Database Index Strategy 글에서 다뤘다.