필사 모드: PostgreSQL 인덱스 완전 가이드: 설계부터 폐기까지 인덱스의 수명 주기

• 들어가며
• 1. 인덱스의 수명 주기라는 관점
• 2. 설계 — 어떤 컬럼을 어떤 순서로
  • 커버링 인덱스와 INCLUDE
• 3. 방식 선택 — 여섯 가지 중 무엇을
• 4. 부분 인덱스와 표현식 인덱스
• 5. 생성 — CONCURRENTLY와 실패 복구
  • 실패했을 때
  • 파티션 테이블의 예외
• 6. 검증 — 정말 쓰이고 있는가
• 7. 운영 — 부풀어 오른 인덱스 다루기
• 8. 폐기 — 되돌릴 수 있게 지우기
• 퀴즈: 실력을 확인해 보세요
• 마치며
• 참고 자료
• 이어서 읽기

들어가며

인덱스에 관한 글은 대부분 두 종류입니다. 하나는 B-tree가 무엇인지 설명하는 자료구조 이야기고, 다른 하나는 "만든 인덱스를 왜 안 타는가"를 다루는 옵티마이저 이야기입니다. 이 블로그에도 두 종류 모두 있습니다. PostgreSQL 고급 인덱싱이 전자에 가깝고, 인덱스를 만들었는데 안 타는 이유가 후자입니다.

이 글은 세 번째 관점을 잡습니다. 인덱스를 수명 주기를 가진 운영 자산으로 다루는 관점입니다. 인덱스는 만들어지고, 검증되고, 늙고, 결국 지워집니다. 현장에서 사고가 나는 지점은 대개 "어떤 자료구조인가"가 아니라 "언제 어떻게 만들고 언제 어떻게 지우는가"입니다. 운영 중인 테이블에 인덱스를 만들다가 쓰기를 몇 분간 막아 버리거나, 아무도 쓰지 않는 인덱스 열두 개가 매 INSERT마다 비용을 청구하고 있는 상황 말입니다.

기준 엔진은 PostgreSQL 18 입니다. 파라미터 기본값, 잠금 등급, EXPLAIN 동작은 모두 PostgreSQL 18 문서를 읽고 적었으며, 버전에 따라 달라지는 항목은 그때마다 표시했습니다. MySQL의 인덱스는 클러스터형 인덱스라는 전혀 다른 전제 위에 서 있으므로 이 글에서 섞지 않습니다.

1. 인덱스의 수명 주기라는 관점

인덱스를 하나 만들면 그때부터 데이터베이스는 세 가지 비용을 계속 지불합니다.

• 쓰기 비용 — 해당 테이블의 INSERT, UPDATE, DELETE마다 인덱스 엔트리를 갱신해야 합니다. 인덱스가 열 개면 열 번입니다.
• 공간 비용 — 인덱스도 디스크와 버퍼 캐시를 차지합니다. 캐시에 인덱스가 자리를 차지하면 그만큼 테이블 데이터가 밀려납니다.
• 유지보수 비용 — VACUUM은 테이블뿐 아니라 인덱스도 청소해야 합니다. 인덱스가 많으면 VACUUM이 길어집니다.

읽기 이득은 눈에 잘 보이지만 이 세 가지 비용은 잘 보이지 않습니다. 그래서 인덱스는 계속 늘어나기만 하고 줄어들지 않습니다. 수명 주기 관점은 이 비대칭을 교정하려는 시도입니다. 인덱스마다 "왜 만들었는가"와 "언제 지울 것인가"를 함께 관리하자는 것입니다.

수명 주기는 여섯 단계로 나눌 수 있습니다. 설계, 방식 선택, 생성, 검증, 운영, 폐기. 아래 절들은 이 순서를 그대로 따릅니다.

2. 설계 — 어떤 컬럼을 어떤 순서로

복합 인덱스에서 컬럼 순서는 성능을 몇 배가 아니라 몇 자릿수 단위로 바꿉니다. 순서를 정하는 규칙은 간단합니다.

등치 조건에 쓰이는 컬럼을 먼저, 범위 조건에 쓰이는 컬럼을 나중에 둡니다. B-tree 인덱스는 선행 컬럼부터 순서대로 정렬되어 있으므로, 범위 조건이 한 번 등장하면 그 뒤 컬럼은 더 이상 검색 조건으로 좁히는 데 쓰이지 못하고 필터로만 남습니다.

-- 자주 실행되는 쿼리
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- 좋은 순서: 등치(tenant_id, status) 다음에 범위/정렬(created_at)
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

-- 나쁜 순서: 범위가 앞에 오면 뒤의 등치 조건을 인덱스로 좁히지 못함
CREATE INDEX idx_orders_created_tenant_status
  ON orders (created_at DESC, tenant_id, status);

정렬까지 인덱스에 태우려면 ORDER BY 방향을 인덱스 정의에 반영해야 합니다. 위 예에서 created_at DESC로 만들어 두면 정렬 연산 자체가 사라집니다. PostgreSQL은 인덱스를 역방향으로도 읽을 수 있으므로 단일 컬럼 정렬이면 방향이 크게 중요하지 않지만, 복합 정렬(a ASC, b DESC)에서는 인덱스 정의가 정확히 맞아야 정렬을 생략합니다.

커버링 인덱스와 INCLUDE

인덱스만 읽고 테이블을 방문하지 않는 실행 방식을 Index Only Scan이라고 합니다. 조회하는 컬럼이 모두 인덱스에 있으면 가능합니다. 검색 조건에는 쓰이지 않지만 결과로 필요한 컬럼은 INCLUDE 절에 넣는 편이 낫습니다.

CREATE INDEX idx_orders_cover
  ON orders (tenant_id, status)
  INCLUDE (total_amount, created_at);

PostgreSQL 문서는 INCLUDE로 지정한 비키 컬럼에 대해 "비키 컬럼은 인덱스 스캔의 검색 조건에 사용될 수 없고, 유일성이나 배제 제약을 판정할 때도 무시된다"라고 명시합니다. 대신 인덱스 엔트리에서 값을 바로 꺼낼 수 있으므로 Index Only Scan이 가능해집니다. INCLUDE는 B-tree, GiST, SP-GiST에서만 지원되고, 비키 컬럼이 있는 B-tree 인덱스는 중복 제거(deduplication)가 꺼집니다.

주의할 점이 하나 있습니다. Index Only Scan이 실제로 테이블을 건너뛰려면 가시성 맵(visibility map)에서 해당 페이지가 전체 가시(all-visible)로 표시되어 있어야 합니다. VACUUM이 돌지 않은 갓 갱신된 테이블에서는 Index Only Scan이라고 표시되어도 Heap Fetches 값이 크게 나옵니다. 실행 계획에서 이 숫자를 반드시 확인하세요.

3. 방식 선택 — 여섯 가지 중 무엇을

PostgreSQL은 여섯 가지 인덱스 방식을 제공합니다. btree, hash, gist, spgist, gin, brin이며 USING 절을 생략하면 B-tree입니다. 자료구조 설명보다 판단 기준이 중요하므로 표로 정리합니다.

문서가 명시하는 B-tree의 추가 능력 두 가지를 기억하면 실무에서 자주 씁니다. 첫째, LIKE 'foo%'처럼 앞이 고정된 패턴은 B-tree가 처리합니다. LIKE '%foo'는 처리하지 못합니다. 둘째, B-tree만이 정렬된 순서로 데이터를 돌려줄 수 있습니다.

BRIN은 오해가 많은 방식입니다. BRIN은 연속된 블록 범위의 요약(최솟값과 최댓값)만 저장하므로 인덱스 크기가 극단적으로 작습니다. 다만 문서가 강조하듯 컬럼 값이 물리적 행 순서와 잘 상관되어 있을 때만 효과가 있습니다. 시간 순으로만 쌓이는 로그 테이블의 created_at에는 훌륭하고, 무작위로 갱신되는 상태 컬럼에는 무용지물입니다.

4. 부분 인덱스와 표현식 인덱스

인덱스를 작게 만드는 두 가지 도구입니다. 실무에서 가장 저평가된 기능이기도 합니다.

부분 인덱스는 WHERE 절로 색인 대상 행을 제한합니다. 전체 행의 1%만 조회 대상이라면 인덱스도 1%만 만들면 됩니다.

-- 미처리 작업만 조회하는 큐 테이블
CREATE INDEX idx_jobs_pending
  ON jobs (created_at)
  WHERE status = 'PENDING';

-- 소프트 삭제된 행을 제외한 유일 제약
CREATE UNIQUE INDEX idx_users_email_active
  ON users (lower(email))
  WHERE deleted_at IS NULL;

두 번째 예는 부분 인덱스와 표현식 인덱스를 함께 쓴 것으로, 소프트 삭제를 쓰는 스키마에서 "살아 있는 행끼리만 이메일이 유일해야 한다"는 요건을 제약으로 표현하는 정석입니다. 애플리케이션 코드로 검사하면 동시성 구멍이 생기지만 유일 인덱스는 데이터베이스가 보장합니다.

표현식 인덱스는 컬럼이 아니라 식에 인덱스를 겁니다. 주의할 점은 쿼리의 식과 인덱스의 식이 정확히 같아야 한다는 것입니다. lower(email)로 인덱스를 만들었다면 쿼리도 lower(email) = ... 이어야 하고, email ILIKE ...는 이 인덱스를 쓰지 못합니다.

표현식 인덱스는 통계에도 영향을 줍니다. PostgreSQL은 표현식 인덱스에 대해 별도 통계를 수집하므로, 인덱스를 만드는 것만으로 행 수 추정이 정확해지는 부수 효과가 있습니다. 인덱스 없이 통계만 원한다면 CREATE STATISTICS로 표현식 통계만 따로 만들 수도 있습니다.

5. 생성 — CONCURRENTLY와 실패 복구

여기가 운영 사고가 가장 많이 나는 지점입니다.

경고: 일반 CREATE INDEX는 대상 테이블의 쓰기를 완료될 때까지 막습니다. PostgreSQL 문서는 일반 CREATE INDEX가 "테이블에 대한 쓰기(삽입, 갱신, 삭제)를 완료될 때까지 잠근다"라고 명시합니다. 읽기는 허용되지만 쓰기는 대기합니다. 수억 행 테이블에서 이 명령은 수십 분 동안 서비스의 쓰기 경로를 정지시킵니다. 운영 중인 테이블에는 반드시 CONCURRENTLY를 붙이세요.

-- 운영 테이블에는 이것만 쓴다
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

CONCURRENTLY는 삽입, 갱신, 삭제를 막는 잠금을 취하지 않습니다. 대신 대가가 있습니다. 문서에 따르면 두 번의 테이블 스캔을 서로 다른 두 트랜잭션에서 수행하고, 각 스캔 전에 인덱스를 수정하거나 사용할 수 있는 기존 트랜잭션이 모두 끝나기를 기다립니다. 두 번째 스캔 이후에는 그 스캔보다 앞선 스냅샷을 가진 트랜잭션이 끝나기를 또 기다립니다. 그래서 오래 열려 있는 트랜잭션 하나가 인덱스 생성 전체를 무한정 지연시킬 수 있습니다. 인덱스를 만들기 전에 pg_stat_activity에서 장수 트랜잭션을 확인하는 습관을 들이세요.

또한 CREATE INDEX CONCURRENTLY는 트랜잭션 블록 안에서 실행할 수 없습니다. 마이그레이션 도구가 모든 마이그레이션을 하나의 트랜잭션으로 감싸는 설정이라면 이 명령은 실패합니다. 도구별로 트랜잭션을 끄는 옵션을 찾아 두어야 합니다.

실패했을 때

문제가 생기면 명령은 실패하지만 무효(invalid) 인덱스가 남습니다. 문서 표현대로 이 인덱스는 "불완전할 수 있으므로 질의에는 무시되지만, 갱신 오버헤드는 계속 발생시킵니다." 즉 비용만 내고 이득은 없는 최악의 상태입니다.

-- 무효 인덱스 찾기
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

-- 복구: 지우고 다시 만들거나
DROP INDEX CONCURRENTLY idx_orders_tenant_status_created;
-- 또는 재구축
REINDEX INDEX CONCURRENTLY idx_orders_tenant_status_created;

인덱스 생성 작업을 끝냈다고 보고하기 전에 위 조회를 한 번 돌리는 것을 절차에 넣으세요. 무효 인덱스는 조용히 남습니다.

파티션 테이블의 예외

문서는 "파티션된 테이블의 인덱스 동시 생성은 현재 지원되지 않는다"라고 못박습니다. 대신 권장하는 우회 방법이 있습니다. 각 파티션에 개별적으로 CONCURRENTLY로 인덱스를 만든 다음, 마지막에 부모 파티션 테이블에 비동시적으로 인덱스를 만들면 쓰기가 잠기는 시간을 줄일 수 있습니다. 부모에 인덱스를 만들 때 이미 이름이 맞는 인덱스가 각 파티션에 있으면 그것을 붙여서 씁니다.

6. 검증 — 정말 쓰이고 있는가

만든 인덱스가 쓰이는지 확인하는 방법은 두 층위입니다.

쿼리 단위로는 EXPLAIN을 봅니다. PostgreSQL 18에서는 ANALYZE를 쓰면 버퍼 정보가 자동으로 포함됩니다. 문서에 "Buffers information is automatically included when ANALYZE is used"라고 명시되어 있습니다. 17 이하에서는 BUFFERS를 직접 붙여야 했습니다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

 Limit  (cost=0.56..42.18 rows=50 width=20)
        (actual time=0.041..0.180 rows=50 loops=1)
   Buffers: shared hit=54
   ->  Index Only Scan Backward using idx_orders_cover on orders
         (cost=0.56..8321.44 rows=10004 width=20)
         (actual time=0.039..0.171 rows=50 loops=1)
         Index Cond: ((tenant_id = 42) AND (status = 'PAID'::text))
         Heap Fetches: 0
         Buffers: shared hit=54
 Planning Time: 0.212 ms
 Execution Time: 0.221 ms

여기서 볼 것은 세 가지입니다. Index Cond에 조건이 들어갔는지(들어가지 않고 Filter에 있으면 인덱스로 좁히지 못한 것입니다), Heap Fetches가 0인지, 그리고 rows 추정치와 실제값의 차이입니다.

경고: EXPLAIN ANALYZE는 문장을 실제로 실행합니다. 문서가 명시하듯 SELECT의 출력은 버려지지만 다른 부수 효과는 그대로 일어납니다. INSERT, UPDATE, DELETE, MERGE를 분석할 때는 BEGIN / EXPLAIN ANALYZE ... / ROLLBACK으로 감싸세요.

워크로드 단위로는 통계 뷰를 봅니다. pg_stat_user_indexes의 idx_scan은 해당 인덱스로 시작된 스캔 횟수이고, last_idx_scan은 마지막 스캔 시각입니다.

SELECT s.schemaname, s.relname, s.indexrelname,
       s.idx_scan, s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC;

이 숫자를 읽을 때 두 가지를 조심해야 합니다. 첫째, 통계는 마지막 초기화 이후 누적값입니다. 언제 초기화했는지 모르면 0의 의미도 모릅니다. 둘째, 월 1회 배치나 분기 마감 보고서만 쓰는 인덱스는 일주일 관찰로는 0으로 보입니다. 최소 한 번의 전체 업무 주기를 관찰하세요.

7. 운영 — 부풀어 오른 인덱스 다루기

PostgreSQL의 MVCC에서는 갱신이 새 행 버전을 만들고, 인덱스도 그에 맞춰 엔트리를 추가합니다. 죽은 엔트리는 VACUUM이 정리하지만, 정리된 공간이 항상 재사용 가능한 형태로 남지는 않습니다. 시간이 지나면 인덱스는 논리적 데이터량보다 커집니다. 이것이 인덱스 bloat입니다.

증상은 조용합니다. 쿼리가 갑자기 느려지지 않고 조금씩 느려집니다. 인덱스가 캐시에 덜 들어가고, 스캔이 더 많은 페이지를 읽습니다.

대응은 재구축입니다.

경고: REINDEX를 CONCURRENTLY 없이 실행하면 대상 테이블에 ACCESS EXCLUSIVE 잠금을 겁니다. 이 잠금은 모든 잠금 모드와 충돌하므로 읽기까지 전부 막힙니다. 같은 이유로 VACUUM FULL과 CLUSTER도 운영 중에 함부로 쓰면 안 됩니다. PostgreSQL 문서 자체가 "관리자는 일반 VACUUM을 쓰고 VACUUM FULL은 피하도록 노력해야 한다"라고 권고합니다. 안전한 대안은 REINDEX INDEX CONCURRENTLY 입니다. 이 명령은 SHARE UPDATE EXCLUSIVE 잠금만 취하므로 읽기와 쓰기를 막지 않습니다.

-- 안전: 읽기와 쓰기를 막지 않음
REINDEX INDEX CONCURRENTLY idx_orders_tenant_status_created;

-- 테이블의 모든 인덱스를 동시 재구축
REINDEX TABLE CONCURRENTLY orders;

REINDEX ... CONCURRENTLY 역시 실패하면 무효 인덱스를 남길 수 있습니다. 이름 끝에 _ccnew가 붙은 잔여 인덱스가 있으면 정리해야 합니다.

재구축 주기를 정할 때는 갱신 패턴을 보세요. append-only에 가까운 테이블은 인덱스가 잘 부풀지 않습니다. 같은 행을 반복해서 갱신하는 상태 테이블, 큐 테이블은 빠르게 부풉니다. 큐 테이블은 부분 인덱스와 함께 쓰면 bloat 영향이 크게 줄어듭니다.

8. 폐기 — 되돌릴 수 있게 지우기

쓰이지 않는 인덱스는 지워야 합니다. 다만 "쓰이지 않는다"는 판단이 틀릴 수 있으므로 되돌릴 수 있는 절차로 진행합니다.

먼저 중복 인덱스를 찾습니다. (a)와 (a, b)가 둘 다 있으면 앞의 것은 대개 불필요합니다. B-tree는 선행 컬럼만 쓰는 조건도 처리할 수 있기 때문입니다. 다만 (a)가 유일 인덱스거나 (a)만으로 Index Only Scan이 성립하는 경우는 예외입니다.

폐기 절차는 세 단계입니다.

1단계 — 후보 선정. 6절의 조회로 idx_scan이 0이거나 극단적으로 낮은 인덱스를 뽑습니다. 유일 제약을 뒷받침하는 인덱스와 외래 키의 참조 측 인덱스는 제외합니다. 외래 키가 걸린 자식 테이블에 인덱스가 없으면 부모 행 삭제 시 전체 스캔이 발생합니다.

2단계 — 비활성화 실험. 지우기 전에 먼저 옵티마이저가 무시하게 만듭니다. PostgreSQL에는 인덱스를 끄는 공식 명령이 없지만, 시스템 카탈로그를 직접 고쳐 유효하지 않다고 표시하는 방법이 알려져 있습니다. 다만 카탈로그 직접 수정은 위험하므로, 더 안전한 방법은 세션 단위로 enable_indexscan과 enable_bitmapscan을 끄고 대표 쿼리의 계획과 실행 시간을 비교하는 것입니다. 이 두 파라미터의 기본값은 모두 on 입니다.

-- 세션에서만 인덱스 경로를 배제해 최악의 경우를 측정
SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_indexscan;
RESET enable_bitmapscan;

3단계 — 삭제. 삭제도 동시 모드로 합니다.

DROP INDEX CONCURRENTLY idx_orders_legacy_status;

그리고 반드시 재생성 스크립트를 함께 커밋해 두세요. 인덱스 삭제의 롤백은 재생성이고, 재생성에는 시간이 걸립니다. 장애 상황에서 정의를 기억으로 복원하려 하면 실수합니다. pg_get_indexdef()로 정의를 미리 떠 두는 것이 좋습니다.

SELECT indexrelid::regclass AS index_name,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'orders'::regclass;

퀴즈: 실력을 확인해 보세요

Index Only Scan using idx_orders_cover on orders
  (actual time=0.05..820.4 rows=48000 loops=1)
  Index Cond: (tenant_id = 42)
  Heap Fetches: 47912
  Buffers: shared hit=1204 read=46180

SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_alive
  ON users (lower(email))
  WHERE deleted_at IS NULL;

CREATE INDEX idx_a ON events (tenant_id);
CREATE INDEX idx_b ON events (tenant_id, occurred_at);

마치며

인덱스 문제의 대부분은 지식 부족이 아니라 절차 부재에서 옵니다. B-tree가 무엇인지 아는 사람은 많지만, 운영 테이블에 인덱스를 만들 때 CONCURRENTLY를 붙이고 장수 트랜잭션을 먼저 확인하고 끝나고 무효 인덱스를 조회하는 절차를 가진 팀은 적습니다.

세 가지만 팀 규칙으로 만들어도 사고의 대부분이 사라집니다. 첫째, 운영 테이블의 인덱스 생성·삭제·재구축은 항상 CONCURRENTLY로 한다. 둘째, 인덱스를 만들 때 왜 만드는지와 어떤 쿼리를 위한 것인지를 마이그레이션 파일 주석에 남긴다. 셋째, 분기마다 한 번 idx_scan을 조회해 폐기 후보를 검토한다.

여기 나온 SQL은 이 사이트의 Postgres 놀이터에서 그대로 실행해 볼 수 있습니다.

참고 자료

• PostgreSQL 18, CREATE INDEX: https://www.postgresql.org/docs/18/sql-createindex.html (2026-08-15 확인)
• PostgreSQL 18, Index Types: https://www.postgresql.org/docs/18/indexes-types.html (2026-08-15 확인)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (2026-08-15 확인)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (2026-08-15 확인)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (2026-08-15 확인)
• PostgreSQL 18, The Statistics Collector: https://www.postgresql.org/docs/18/monitoring-stats.html (2026-08-15 확인)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (2026-08-15 확인)
• PostgreSQL 18, Client Connection Defaults: https://www.postgresql.org/docs/18/runtime-config-client.html (2026-08-15 확인)

이어서 읽기

• 다음 편: SQL 실행계획 완전 가이드 — 옵티마이저가 인덱스 경로를 고르는 과정
• PostgreSQL 고급 인덱싱 완전 가이드 — GIN, GiST, BRIN의 내부 구조
• 인덱스를 만들었는데 안 타는 이유 — 옵티마이저가 인덱스를 거부하는 경우들
• PostgreSQL VACUUM과 MVCC 내부 구조 — bloat가 생기는 원리
• Postgres 놀이터 — 이 글의 SQL을 직접 실행해 보기
• SQL 놀이터 — 쿼리 문법 실험