PostgreSQL 커버링 인덱스 설계와 사용법

커버링 인덱스란 무엇인가

커버링 인덱스(covering index)는 쿼리가 요구하는 모든 컬럼을 인덱스만으로 충족해 테이블의 실제 행을 읽지 않고 결과를 반환할 수 있는 인덱스를 말한다. PostgreSQL에서는 인덱스의 키 컬럼과 INCLUDE 절로 추가한 포함 컬럼을 조합해 커버링 인덱스를 구성한다. 이를 통해 디스크 I/O와 랜덤 액세스를 줄여 응답 시간을 개선할 수 있다.

왜 커버링 인덱스를 사용하는가

쿼리가 인덱스만으로 처리되면 인덱스 전용 스캔(index-only scan)이 가능해진다. 인덱스 전용 스캔은 락 경쟁과 워크로드에 민감한 읽기 성능을 크게 향상시킨다. 특히 넓은 테이블에서 자주 조회되는 선택적 컬럼 집합을 가진 쿼리에 효과적이다.

설계 원칙

1) 조회 패턴 우선 분석

어떤 쿼리가 가장 자주 실행되는지, WHERE 절과 ORDER BY에 사용되는 컬럼을 먼저 파악한다. 인덱스 키에는 필터링과 정렬에 자주 사용되는 컬럼을 둔다.

2) INCLUDE로 불필요한 키 확장 방지

정렬이나 검색에 사용되지 않는 조회 전용 컬럼은 인덱스 키가 아니라 INCLUDE 절로 추가한다. 이렇게 하면 인덱스의 b-tree 구조가 커지지 않아 탐색 비용을 낮출 수 있다.

3) 컬럼 순서 최적화

복합 인덱스에서는 자주 필터링되는 컬럼을 앞에 배치한다. 또한 좌측 접두사(left-prefix) 성질을 고려해 설계한다.

4) 빈도와 선택도 고려

높은 선택도(특이값이 많은 컬럼)를 앞에 둬 인덱스의 효율을 높인다. 반대로 선택도가 낮은 컬럼만으로는 큰 이득이 없다.

구현 예제

간단한 예제로 users 테이블에서 자주 조회되는 컬럼 집합을 인덱스로 구성한다.

CREATE INDEX idx_users_email_status_created_at ON users (email, status) INCLUDE (last_login, display_name);

위 인덱스는 email과 status로 필터 및 정렬을 지원하고, last_login과 display_name을 포함해 인덱스만으로 SELECT 절의 데이터 제공이 가능하게 한다.

인덱스 전용 스캔 확인 방법

EXPLAIN ANALYZE로 쿼리 계획을 확인해 index-only scan 사용 여부를 본다.

EXPLAIN ANALYZE
SELECT email, status, last_login, display_name
FROM users
WHERE email = 'alice@example.com' AND status = 'active';

계획에 "Index Only Scan"이 표시되면 성공적으로 커버링되어 있음을 의미한다.

visibility map과 VACUUM의 역할

PostgreSQL의 인덱스 전용 스캔은 튜플의 가시성 정보를 visibility map에 의존한다. 오래된 버전의 튜플이 남아 있거나 visibility map이 갱신되지 않으면 index-only scan이 불가능하다. 따라서 정기적인 VACUUM(특히 autovacuum) 설정이 중요하다.

부분 인덱스와 조합하기

특정 상태나 범위에 대해 자주 조회한다면 partial index를 사용해 인덱스 크기를 작게 유지하면서 커버링 효과를 얻을 수 있다.

CREATE INDEX idx_active_users_covering ON users (email) INCLUDE (status, display_name)
WHERE status = 'active';

주의사항과 성능 고려

• 인덱스는 쓰기 비용을 증가시킨다. INSERT/UPDATE/DELETE 빈도가 높은 컬럼에 인덱스를 과다하게 추가하면 전체 성능을 저하시킬 수 있다.
• INCLUDE로 많은 컬럼을 포함하면 인덱스의 크기가 커져 메모리와 디스크 사용량이 늘어난다. 필요한 컬럼만 포함한다.
• ORDER BY와 LIMIT 조합에서 커버링 인덱스는 특히 유용하지만, 컬럼 순서와 정렬 방향이 쿼리와 일치해야 한다.

검증과 모니터링 절차

변경 후에는 다음 절차로 효과를 검증한다.

• 1) EXPLAIN ANALYZE로 계획과 실행 시간을 비교
• 2) pg_stat_user_indexes, pg_stat_user_tables로 히트율과 사용률 확인
• 3) autovacuum 동작과 vacuum 빈도 검토

요약

커버링 인덱스는 적절히 설계하면 읽기 성능을 크게 높여준다. 핵심은 실제 쿼리 패턴을 분석해 인덱스 키와 INCLUDE 컬럼을 구분하는 것이다. 또한 visibility map과 VACUUM 관리를 병행하고, 쓰기 비용과 인덱스 크기 증가를 균형 있게 고려하면 운영 환경에서 안정적인 성능 개선을 얻을 수 있다.