DB 인덱스와 쿼리 최적화의 실제 판단법
대량 테이블에서 인덱스가 왜 필요한지부터 B+Tree 구조, EXPLAIN ANALYZE 해석, 조인 플래너 판단, TypeORM의 N+1 문제까지 연결해 설명한다. 인덱스는 읽기를 빠르게 하지만 쓰기와 운영 비용을 만들기 때문에, 실제 슬로우 쿼리와 실행 계획을 보고 정확히 추가해야 한다.
Script Companion
오디오와 함께 스크립트 보기
- 01
수십만 건 이상의 테이블에서 인덱스 없이 조건 조회를 하면 PostgreSQL은 모든 행을 순서대로 읽는 Seq Scan을 수행한다. 반환되는 행이 아주 적어도 테이블 전체를 훑어야 하므로, 데이터가 늘수록 응답 시간은 선형으로 늘어난다. BackOps 환경의 대량 배치 처리, 정산, 통계 조회에서는 이 차이가 API 지연을 넘어 서비스 안정성과 연결된다. 그래서 갑자기 API가 느려졌을 때는 감으로 인덱스를 추가하는 것이 아니라 EXPLAIN ANALYZE로 실제 실행 계획을 읽을 수 있어야 한다.
- 02
B+Tree 인덱스가 등장한 이유는 탐색 공간을 미리 정렬된 구조로 줄이기 위해서다. 아무 준비가 없으면 WHERE id 조건처럼 결과가 0개나 1개인 조회도 테이블을 끝까지 확인해야 하지만, 인덱스가 있으면 검색 트리의 몇 단계만 내려가 후보 행을 찾는다. 이때 핵심 메커니즘은 전체 테이블 순차 스캔 병목을 정렬된 B+Tree 인덱스로 바꾸어 O(log N) 탐색으로 줄이는 것이다. 다만 인덱스는 공짜가 아니어서, 테이블 변경과 인덱스를 동기화하는 DML 오버헤드와 저장공간 비용이 함께 생긴다.
- 03
PostgreSQL이 기본 인덱스로 사용하는 것은 엄밀히 말하면 B+Tree다. 루트, 브랜치, 리프 구조를 지나 목표 위치에 도달하고, 리프 노드는 연결 리스트처럼 이어져 범위 검색에 강하다. 데이터 1억 건도 3단계에서 4단계 정도면 도달할 수 있다는 설명은 이 균형 트리 구조를 전제로 한다. leaf page가 전체 페이지의 대부분을 차지하고, leaf page가 꽉 차면 page split이 상위 레벨로 전파될 수 있기 때문에 PostgreSQL은 HOT Updates, bottom-up deletion, deduplication 같은 최적화도 함께 사용한다.
- 04
이 원리는 PostgreSQL에만 갇혀 있지 않다. MySQL InnoDB의 Clustered Index는 PK 자체가 B+Tree이며 데이터 행이 리프 노드에 직접 저장되고, 보조 인덱스 리프에는 실제 행 위치가 아니라 PK 값이 저장되어 추가 탐색이 발생한다. Elasticsearch와 Lucene의 역색인은 텍스트 토큰에서 문서 ID 목록을 찾는 구조로, 조건에 해당하는 대상만 찾는다는 목적은 유사하지만 불변 세그먼트와 병합 기반 갱신을 사용한다. 파일시스템 ext4와 APFS의 디렉터리 엔트리에서도 B-Tree 변형을 볼 수 있으며, 시스템마다 갱신 전략과 데이터 위치가 다를 뿐 탐색 공간을 줄인다는 큰 원리는 이어진다.
- 05
인덱스 종류를 구분하면 쿼리 최적화 판단이 더 선명해진다. Clustered Index는 테이블 데이터 자체가 인덱스 순서로 물리적으로 정렬되는 방식이지만, PostgreSQL에서는 InnoDB처럼 자동 적용되는 것이 아니라 CLUSTER 명령으로 수동 생성하는 성격에 가깝다. Non-Clustered Index는 별도 구조에 키 값과 힙 주소를 저장하며, 일반 CREATE INDEX가 여기에 해당한다. 복합 인덱스는 왼쪽 컬럼부터 순서대로 사용해야 하는 Leftmost Prefix Rule을 따르고, Covering Index는 필요한 컬럼을 인덱스 안에 담아 힙 페이지를 읽지 않는 Index Only Scan을 가능하게 한다.
- 06
Hash Index와 B-Tree의 차이도 경계 조건으로 기억해야 한다. Hash Index는 동등 검색에서는 O(1)로 설명되지만, 범위 검색과 정렬에는 사용할 수 없다. 반대로 B-Tree는 동등 검색, 범위 검색, ORDER BY에 모두 대응할 수 있어 실무 기본 선택지가 된다. 그래서 문서의 판단은 단순하다. 대부분은 B-Tree를 기본으로 두고, Hash는 오직 동등 검색만 필요한 특수한 경우에만 검토한다. 인덱스 설계에서 중요한 것은 빠른 자료구조 이름을 고르는 일이 아니라, 실제 쿼리의 WHERE, JOIN ON, ORDER BY, GROUP BY 조건과 맞는 구조를 고르는 일이다.
- 07
EXPLAIN ANALYZE를 읽을 때는 먼저 cost, rows, actual time, loops를 본다. cost는 planner가 예상한 비용이며 비교용 숫자이고, rows는 예상 결과 행 수다. actual time은 실제 실행 시간이고, loops는 해당 노드가 몇 번 실행됐는지를 나타낸다. 특히 rows 예상과 실제 rows가 크게 다르면 통계 갱신이 필요할 수 있고, Nested Loop에서 loops가 커지면 반복 조회가 폭발할 수 있다. Rows Removed by Filter가 크다면 인덱스 없이 많은 행을 필터로 버리고 있다는 뜻이므로 인덱스 후보를 의심한다.
- 08
스캔 타입은 결과 행 수와 접근 방식에 따라 달라진다. Seq Scan은 전체 테이블을 순차로 읽으며, 인덱스가 없거나 결과가 전체의 약 10퍼센트 이상일 때 선택될 수 있다. Index Scan은 인덱스를 먼저 읽고 힙 페이지를 Random I/O로 다시 읽기 때문에 소수 행을 정확히 찾을 때 적합하다. Index Only Scan은 Covering Index가 있을 때 인덱스만 읽고, Bitmap Index Scan은 중간 정도의 행 수를 대상으로 인덱스로 비트맵을 만든 뒤 힙을 읽는다. EXPLAIN ANALYZE의 목표는 무조건 Seq Scan을 없애는 것이 아니라, 결과 비율과 I/O 비용에 맞는 선택인지 확인하는 것이다.
- 09
실무에서는 EXPLAIN ANALYZE에 BUFFERS 옵션을 붙여 I/O 병목을 함께 본다. shared hit는 공유 버퍼, 즉 메모리에서 읽은 페이지 수이고, shared read는 디스크에서 읽은 페이지 수다. shared read가 shared hit보다 크면 cold cache, 인덱스 누락, shared_buffers 부족 같은 가능성을 검토한다. shared dirtied는 변경된 더티 페이지이고, shared written은 디스크에 쓰인 더티 페이지라서 UPDATE, DELETE 작업량이나 checkpoint 비용을 볼 때 중요하다. 실행 시간만 보고 판단하면 캐시 상태나 디스크 읽기 원인을 놓칠 수 있다.
- 10
시니어 수준의 실행 계획 읽기는 조인 노드가 왜 그 알고리즘으로 선택됐는지를 보는 데서 시작한다. PostgreSQL은 cost-based optimizer라 Nested Loop, Hash Join, Merge Join 중 예상 cost가 가장 낮은 것을 고른다. Nested Loop는 outer 한 row마다 inner index lookup을 수행하므로 outer가 작고 inner index가 잘 탈 때 강하지만, outer가 커지면 폭발한다. Hash Join은 작은 쪽으로 hash table을 만들고 큰 쪽으로 probe하며, work_mem 안에 들어갈 때 유리하다. Merge Join은 양쪽이 정렬된 상태에서 zipper merge를 하며, 정렬 비용이 이미 낮거나 index sort order를 활용할 수 있을 때 힘을 낸다.
- 11
Planner의 선택은 통계와 설정값에 크게 의존한다. seq_page_cost 기본값은 1.0이고 random_page_cost 기본값은 4.0이며, SSD 환경에서는 random_page_cost를 1.1에서 1.5로 조정해야 index scan이 과소평가되지 않는다는 판단이 나온다. cpu_tuple_cost, cpu_index_tuple_cost, work_mem도 sort와 hash 비용 계산에 영향을 준다. 하지만 몇 번의 실험만으로 cost 상수를 바꾸는 것은 위험하다. 먼저 EXPLAIN ANALYZE와 BUFFERS로 쿼리 단위 병목을 확인하고, ANALYZE로 stale한 통계를 제거한 뒤에 조정해야 한다.
- 12
통계가 오래되면 planner의 모든 결정이 어긋날 수 있다. Plan rows와 Actual rows가 10배 이상 차이 나면 pg_statistic이 stale한 신호로 보고 즉시 ANALYZE를 검토한다. autovacuum_analyze_scale_factor 기본값은 0.1이라서, 1억 행 테이블에서는 약 1,000만 행이 바뀌기 전까지 자동 ANALYZE가 늦을 수 있다. Hash Join에서 Batches가 1보다 크면 hash table이 work_mem을 넘어 디스크 spill이 난 것이고, Sort Method가 external merge Disk라면 정렬이 메모리를 넘은 것이다. work_mem은 세션당, 노드당 사용되므로 전역으로 크게 올리면 동시 세션 수만큼 RAM 위험이 커진다.
- 13
인덱스 추가 판단은 정량적으로 접근해야 한다. WHERE 절에 자주 등장하는 컬럼, JOIN ON 조건 컬럼, ORDER BY와 GROUP BY 컬럼은 후보가 되며, PostgreSQL은 FK 컬럼 인덱스를 자동 생성하지 않는다는 점도 놓치면 안 된다. 결과가 전체 행의 약 10퍼센트 미만이면 인덱스 효과가 커질 수 있지만, status처럼 값 종류가 3개에서 5개이거나 is_deleted처럼 true와 false만 있는 단독 컬럼은 선택도가 낮다. 읽기가 압도적으로 많은 조회 API는 인덱스 이득이 크지만, 초당 수만 건 INSERT가 들어오는 테이블은 인덱스마다 쓰기 오버헤드가 누적된다.
- 14
실제 쿼리에서는 단독 컬럼보다 조합과 순서가 중요하다. 예를 들어 최근 30일 데이터가 전체의 3퍼센트이고 status 값이 전체의 70퍼센트를 차지한다면, status를 앞에 둔 인덱스는 선택도가 낮아 Seq Scan이 유지될 수 있다. 반대로 created_at 조건이 먼저 후보를 크게 줄이면 Bitmap Index Scan이나 Index Scan으로 바뀔 가능성이 있다. 인덱스를 추가한 뒤 기대할 변화는 Seq Scan이 Index Scan 또는 Bitmap Index Scan으로 바뀌고, Rows Removed by Filter와 Buffers shared read가 줄어드는 것이다. 출력이 그대로라면 인덱스가 틀렸다고 단정하기보다 먼저 ANALYZE 후 같은 plan을 다시 본다.
- 15
TypeORM을 사용하는 코드에서는 N+1 쿼리도 반드시 확인해야 한다. 관계 없이 find를 반복 호출하면 N이 100일 때 101번, N이 1,000일 때 1,001번 쿼리가 나갈 수 있고, Scout APM은 이 패턴을 Rails, Django, Elixir 같은 ORM 공통 문제로 설명한다. 단순 관계는 find relations로 한 번에 가져오고, 복잡한 조건은 QueryBuilder의 leftJoinAndSelect로 제어한다. 항상 함께 쓰이는 관계라면 Eager Loading도 가능하지만, eager true는 모든 find에 적용되어 불필요한 JOIN을 만들 수 있으므로 신중해야 한다. GraphQL과 동적 관계에서는 DataLoader가 배치 전략으로 등장한다.
- 16
운영 중 인덱스 추가는 성능 개선 작업이면서 장애 작업이 될 수 있다. PostgreSQL의 일반 CREATE INDEX는 테이블에 ShareLock을 걸어 INSERT, UPDATE, DELETE를 막을 수 있으므로 큰 테이블에서는 CREATE INDEX CONCURRENTLY를 검토한다. 다만 CONCURRENTLY는 트랜잭션 안에서 실행할 수 없고, 빌드 중 에러가 나면 INVALID 상태 인덱스가 남을 수 있다. 이 인덱스는 쿼리에는 무시되지만 업데이트 오버헤드는 계속 만들기 때문에, 생성 직후 indisvalid가 false인지 확인해야 한다. 실패했다면 같은 트랜잭션에서 재시도하지 말고 DROP INDEX CONCURRENTLY로 정리한 뒤 단독 명령으로 다시 생성한다.
같은 레이어