PostgreSQL 인덱스 설계 총정리 — 부분·복합·커버링·표현식부터 GIN/BRIN까지

업무 코드에서 이런 인덱스를 처음 만났다고 하자.

CREATE INDEX idx_fd_prj_folders_proj
  ON flow_drive_project_folders (use_intt_id, colabo_srno)
  WHERE (trashed_at IS NULL);

(use_intt_id, colabo_srno) 두 컬럼을 묶은 복합 인덱스인데, 끝에 WHERE trashed_at IS NULL이 붙어 있다. 이게 **부분 인덱스(partial index)**다. "삭제되지 않은(=활성) 폴더"만 인덱싱하겠다는 뜻이다. PostgreSQL은 이렇게 인덱스를 필요한 행·필요한 형태로만 정밀하게 깎는 도구가 유난히 많다. 이 글은 그 기교들을 한 번에 정리한다.

인덱스는 공짜가 아니다

먼저 깔고 가자. 인덱스는 읽기를 빠르게 하는 대신,

• 쓰기를 느리게 한다. INSERT/UPDATE/DELETE마다 인덱스도 갱신된다.
• 디스크를 먹는다. 큰 테이블에선 인덱스가 테이블만큼 커지기도 한다.
• 플래너에 약간의 선택 비용을 더한다.

그래서 인덱스 설계의 본질은 "최소한의 인덱스로 최대한의 쿼리를 커버"하는 것이다. 아래 기법들은 대부분 더 작고 정확한 인덱스를 만드는 방법이다.

1. 부분 인덱스 (Partial Index)

조건을 만족하는 행만 인덱싱한다.

CREATE INDEX idx_folders_active
  ON folders (use_intt_id, colabo_srno)
  WHERE trashed_at IS NULL;

soft delete 테이블이 대표 사례다. 보통 쿼리는 "살아있는 행"만 본다(WHERE trashed_at IS NULL). 그런데 시간이 지나면 삭제된 행이 테이블의 절반 이상을 차지하기도 한다. 부분 인덱스는 살아있는 행만 담으므로:

• 인덱스가 작아져 캐시 효율·스캔 속도가 좋아진다.
• 삭제된 행을 건드려도 이 인덱스는 갱신하지 않는다(쓰기 비용 절감).

주의: 플래너가 이 인덱스를 쓰려면 쿼리의 WHERE가 인덱스 술어를 **함의(imply)**해야 한다.

-- 인덱스 사용됨
SELECT * FROM folders
WHERE use_intt_id = 7 AND colabo_srno = 100 AND trashed_at IS NULL;
 
-- 인덱스 사용 안 됨 (trashed_at 조건이 빠짐)
SELECT * FROM folders WHERE use_intt_id = 7 AND colabo_srno = 100;

또 하나 강력한 용도는 부분 유니크 인덱스다. "활성 행 중에서만 유일"을 강제할 수 있다.

-- 한 사용자당 '활성' 폴더 이름은 유일하되, 삭제된 동명 폴더는 허용
CREATE UNIQUE INDEX uq_folder_name_active
  ON folders (use_intt_id, name)
  WHERE trashed_at IS NULL;

2. 복합 인덱스와 컬럼 순서 (Multicolumn)

(use_intt_id, colabo_srno)처럼 여러 컬럼을 묶는다. 핵심은 컬럼 순서다. B-tree 복합 인덱스는 **왼쪽 접두사(leftmost prefix)**만 효율적으로 쓸 수 있다.

CREATE INDEX ON t (a, b, c);
-- 잘 쓰임:  WHERE a = ?
--          WHERE a = ? AND b = ?
--          WHERE a = ? AND b = ? AND c = ?
-- 비효율:  WHERE b = ?            (선두 a 가 없음)
--          WHERE c = ?

설계 원칙:

• 등치(=) 컬럼을 앞에, 범위(<, >, BETWEEN)·정렬 컬럼을 뒤에. 범위 컬럼 뒤의 컬럼은 인덱스로 더 좁히지 못한다.
• 자주 단독으로 쓰는 컬럼을 선두에.
• 선택도가 높은(값이 다양한) 컬럼을 앞에 두는 게 보통 유리하지만, 결국 쿼리 패턴이 가장 중요하다.

예: WHERE use_intt_id = ? AND created_at > ? 라면 (use_intt_id, created_at) — 등치 먼저, 범위 나중.

3. 커버링 인덱스와 INCLUDE (Index-Only Scan)

인덱스에 쿼리가 필요한 컬럼이 다 들어 있으면, PostgreSQL은 테이블(heap)을 안 보고 인덱스만으로 결과를 만든다 — index-only scan.

-- created_at 으로 찾고 title 만 반환하는 쿼리를 커버
CREATE INDEX ON posts (created_at) INCLUDE (title);
 
SELECT title FROM posts WHERE created_at > now() - interval '7 days';

INCLUDE(PG 11+) 컬럼은 인덱스 리프에 페이로드로만 저장된다. 검색·정렬엔 못 쓰지만 heap 접근을 없애준다. 키로 쓰지 않을 컬럼은 키에 넣지 말고 INCLUDE로 분리하라.

단서: index-only scan은 해당 행이 visibility map상 "모든 트랜잭션에 보임"으로 표시돼야 동작한다. 즉 VACUUM이 잘 돌아야 효과가 난다. EXPLAIN에 Index Only Scan + Heap Fetches: 0이면 성공.

4. 표현식 인덱스 (Expression Index)

컬럼이 아니라 식의 결과를 인덱싱한다.

-- 대소문자 무시 검색
CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = '[email protected]';   -- 사용됨
 
-- jsonb 필드
CREATE INDEX ON events ((data->>'user_id'));
SELECT * FROM events WHERE data->>'user_id' = '42';
 
-- 날짜 버킷
CREATE INDEX ON logs (date_trunc('day', created_at));

규칙: 쿼리의 식과 인덱스의 식이 정확히 일치해야 한다. lower(email) 인덱스는 email = ...엔 쓰이지 않는다.

5. 인덱스 종류 고르기

PostgreSQL의 기본은 B-tree지만, 데이터 모양에 따라 다른 타입이 압도적으로 낫다.

GIN — 배열·jsonb·전문검색

CREATE INDEX ON posts USING gin (tags);            -- text[] 배열
SELECT * FROM posts WHERE tags @> ARRAY['postgres'];
 
CREATE INDEX ON events USING gin (data);            -- jsonb 통째로
SELECT * FROM events WHERE data @> '{"type":"click"}';

GIN은 조회는 빠르지만 갱신이 무겁다. fastupdate 옵션이나 "데이터 적재 후 인덱스 생성"으로 완화한다.

BRIN — 시계열 대용량

-- created_at 이 대체로 증가하는 거대 로그 테이블
CREATE INDEX ON logs USING brin (created_at);

BRIN은 블록 범위별 min/max만 저장해 인덱스 크기가 KB 단위다. 단, 컬럼이 물리적으로 정렬돼 있어야(상관관계가 높아야) 효과가 난다.

6. 연산자 클래스 (Operator Class)

같은 타입이라도 어떤 연산을 빠르게 할지 연산자 클래스로 바꾼다.

LIKE 'prefix%' — text_pattern_ops

C 로케일이 아닌 DB에서 LIKE 'abc%'를 인덱스로 타려면 패턴 연산자 클래스가 필요하다.

CREATE INDEX ON users (email text_pattern_ops);
SELECT * FROM users WHERE email LIKE 'kim%';   -- 이제 인덱스 사용

(등치 =에는 필요 없다. 접두 LIKE에만.)

부분 일치·유사도 — pg_trgm

LIKE '%x%'(선두 와일드카드)나 오타 허용 검색은 트라이그램으로.

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX ON products USING gin (name gin_trgm_ops);
 
SELECT * FROM products WHERE name ILIKE '%note%';
SELECT * FROM products WHERE name % '노트북';     -- 유사도 검색

jsonb — jsonb_path_ops

포함(@>) 위주면 더 작고 빠른 연산자 클래스가 있다.

CREATE INDEX ON events USING gin (data jsonb_path_ops);  -- @> 전용, 더 컴팩트

7. 정렬을 인덱스로 (ORDER BY)

인덱스는 정렬된 구조라 ORDER BY를 공짜로 해결할 수 있다. 정렬 방향·NULL 위치까지 인덱스에 박을 수 있다.

-- 최신순 페이지네이션용 인덱스
CREATE INDEX ON posts (created_at DESC, id DESC);
SELECT * FROM posts ORDER BY created_at DESC, id DESC LIMIT 20;  -- 별도 sort 없음
 
-- NULL 위치 맞추기
CREATE INDEX ON t (score DESC NULLS LAST);
SELECT * FROM t ORDER BY score DESC NULLS LAST;

복합 인덱스의 컬럼 순서·방향이 ORDER BY와 맞아야 정렬 단계가 사라진다.

8. 유니크·배제 제약

PRIMARY KEY·UNIQUE는 내부적으로 유니크 인덱스를 만든다. 더 일반적인 "겹치면 안 됨"은 **배제 제약(exclusion constraint, GiST)**으로 표현한다.

-- 같은 방의 예약 시간대가 겹치면 거부
CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, during WITH &&);

(앞서 본 부분 유니크 인덱스도 여기 묶이는 강력한 기교다.)

9. 운영 기교

잠금 없이 만들기 — CONCURRENTLY

CREATE INDEX는 기본적으로 테이블에 쓰기 잠금을 건다. 운영 중엔 위험하다.

CREATE INDEX CONCURRENTLY idx_x ON t (col);

CONCURRENTLY는 쓰기를 막지 않고 만든다(대신 느리고, 트랜잭션 블록 안에선 못 쓴다). 도중 실패하면 INVALID 인덱스가 남으니 DROP INDEX 후 재시도. 재구성도 REINDEX INDEX CONCURRENTLY.

안 쓰는 인덱스 찾아 지우기

SELECT indexrelid::regclass AS index, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0이 오래 유지된 인덱스는 쓰기 비용만 내는 죽은 무게다(PK·유니크 제약용은 제외).

중복·잉여 인덱스

(a, b) 인덱스가 있으면 (a) 인덱스는 보통 잉여다(왼쪽 접두사로 커버됨). 같은 컬럼 집합의 인덱스가 둘 있지 않은지 점검하라.

fillfactor

UPDATE가 잦은 인덱스는 페이지에 여유를 둬 페이지 분할과 블로트를 줄인다.

CREATE INDEX ON t (col) WITH (fillfactor = 80);

검증은 EXPLAIN으로

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

인덱스를 만들었으면 반드시 EXPLAIN으로 Index Scan/Index Only Scan이 뜨는지, 의도대로 타는지 확인한다. 안 타면 통계부터 의심(ANALYZE).

10. 인덱스가 안 쓰이는 흔한 이유

만들었는데 seq scan이 뜬다면 대개 이 중 하나다.

• 컬럼에 함수/연산을 씌웠는데 표현식 인덱스가 없음 (WHERE lower(x) = ... ← lower(x) 인덱스 필요).
• 타입 불일치(예: bigint 컬럼을 문자열과 비교) → 암묵 캐스팅이 인덱스를 무력화.
• 선택도가 낮음 — 전체의 상당 비율을 가져오면 seq scan이 더 싸서 플래너가 일부러 안 쓴다(정상).
• LIKE '%x%'처럼 선두 와일드카드(→ 트라이그램 필요).
• 통계가 낡음 → ANALYZE.
• 테이블이 작음 → 그냥 seq scan이 빠름(정상).

정리

• 인덱스는 작고 정확할수록 좋다. PostgreSQL은 그걸 위한 도구가 풍부하다.
• 부분 인덱스로 필요한 행만(soft delete의 활성 행), INCLUDE로 heap 접근 제거, 표현식 인덱스로 식을 인덱싱, 복합 인덱스의 컬럼 순서로 여러 쿼리를 한 인덱스로 커버.
• 데이터 모양이 특수하면 **GIN(jsonb·배열·검색)·BRIN(시계열)·GiST(범위·배제)**로 갈아탄다.
• 운영에선 CONCURRENTLY로 안전하게 만들고, pg_stat_user_indexes로 죽은 인덱스를 솎고, 항상 EXPLAIN으로 검증한다.

처음 본 그 WHERE trashed_at IS NULL 한 줄이, 사실은 이 모든 설계 사고로 들어가는 입구였던 셈이다.