2026-09-04 @이영훈
개요
1억 7천만 건이 들어 있는 MongoDB 컬렉션에서 플래그 하나로 수백 건을 걸러내는 조회를 만들었습니다. 인덱스가 없는 필드에 조건을 걸어서 요청마다 풀스캔이 나갔고,
explain을 돌리면 서버 모니터 타임아웃으로 연결이 끊기는 수준이었습니다.그런데 일반 인덱스를 걸기에는 필드 자체가 너무 치우쳐 있었습니다.
true인 문서가 수백 건이고 나머지 1억 7천만 건은 필드가 없거나 false입니다. 이 상황에서 가장 잘 맞는 도구가 partial index였고, 쓰다 보니 몰랐으면 걸릴 만한 함정도 하나 있어서 정리합니다.본문
Partial index가 뭐지?
일반 인덱스는 컬렉션의 모든 문서를 색인합니다. 필드가 없는 문서도
null 엔트리로 들어가고, 조회에 쓰이지 않을 값도 전부 들어갑니다.Partial index는 조건에 맞는 문서만 색인합니다. 조건은 인덱스를 만들 때 지정하고, 조건에 맞지 않는 문서는 인덱스에 존재하지 않습니다.
- 인덱스 크기가 조건에 맞는 문서 수에 바로 묶입니다. 1억 7천만 엔트리가 수백 엔트리로 줄어듭니다
- 쓰기 부담도 조건에 맞는 문서를 갱신할 때만 생깁니다. 그 외 문서의 insert, update는 이 인덱스를 건드리지 않습니다
- 보통
deletedAt IS NULL,status = 'PENDING',flag = true처럼 전체 중 소수만 골라내는 조건에 쓰입니다
MongoDB에서는
partialFilterExpression으로 지정합니다.JavaScript
db.item.createIndex(
{ "characteristicItem.isQualityAssured": 1, createdAt: -1 },
{ partialFilterExpression: { "characteristicItem.isQualityAssured": true }, background: true }
)PostgreSQL에서는
CREATE INDEX 문 마지막에 WHERE를 붙입니다.SQL
CREATE INDEX idx_deal_active
ON deal (seller_id, created_at DESC)
WHERE deleted_at IS NULL;다른 이름으로 부르는 곳도 있습니다. PostgreSQL 문서는 partial index, SQL Server는 filtered index라고 하고, MySQL과 Aurora MySQL에는 이 기능이 없어서 조건을 값으로 가진 generated column을 만들고 그 위에 인덱스를 걸어 흉내냅니다.
언제 쓰는게 좋은가
제 경우는 이런 상황이었습니다.
- 컬렉션 1억 6,900만 건, 1.4TB
- 필터 필드
characteristicItem.isQualityAssured가true인 문서는 수백 건
- 조회는 항상
{ isQualityAssured: true }로만 들어오고false나 누락을 조회하는 케이스가 없음
일반 인덱스를 걸었다면 1억 7천만 엔트리 중 유용한 것은 수백 개였을 겁니다. 단지 디스크만 아끼는 것이 아니라, 이 컬렉션은 상품 갱신이 많아서 인덱스 하나가 늘어날 때마다 쓰기 비용이 전체 문서에 붙습니다. Partial로 만들면 수백 건만 인덱스에 있고, 나머지 문서의 쓰기는 이 인덱스를 모릅니다.
조건에 맞는 문서 바율이 낮을수록 이득이 큽니다. 전체의 절반이 조건에 맞는다면 일반 인덱스와 차이가 나지 않고, 아래에서 설명하는 제약만 남습니다.
제약: 쿼리가 인덱스 조건을 함의해야 한다
Partial index의 제약은 하나로 요약됩니다. 쿼리 조건이 인덱스 조건을 논리적으로 포함해야만 플래너가 그 인덱스를 후보로 봅니다. 인덱스에 없는 문서를 쿼리가 필요로 할 가능성이 조금이라도 있으면 플래너는 그 인덱스를 버립니다. 그러면 다시 풀스캔입니다.
인덱스 조건이
{ qa: true }일 때 쿼리별로 어떻게 되는지 정리하면 다음과 같습니다.쿼리 조건 | 인덱스 사용 | 이유 |
{ qa: true } | 사용 | 인덱스 조건과 같다 |
{ qa: true, deletedAt: null } | 사용 | 쿼리가 더 좁다. 인덱스 안에서 다 찾을 수 있다 |
{ qa: { $ne: false } } | 미사용 | null과 누락도 매칭되는데 인덱스에 없다 |
{ qa: { $exists: true } } | 미사용 | false도 매칭되는데 인덱스에 없다 |
{ qa: { $in: [true, false] } } | 미사용 | 같은 이유 |
규칙은 단순합니다. 인덱스 조건은 넓게, 쿼리 조건은 그보다 같거나 좁게. 넓은 조건의 인덱스는 좁은 쿼리를 덮지만, 좁은 조건의 인덱스는 넓은 쿼리를 덮지 못합니다.
제가 걸린 함정: 삭제 필터
처음 목록 쿼리는 미삭제 상품만 보여주는 것이었고, 인덱스 조건도 그에 맞춰 삭제 조건을 넣었습니다.
JavaScript
// 쿼리
{ "characteristicItem.isQualityAssured": true, deletedAt: null }
// 인덱스 조건
{ "characteristicItem.isQualityAssured": true, deletedAt: null }그 뒤 요구사항이 바뀜어서 삭제된 상품도 목록에 보여주기로 했습니다. 쿼리에서
deletedAt: null을 뻔는데, 인덱스 조건을 그대로 두면 위 표의 세 번째 줄과 같은 상태가 됩니다. 삭제된 상품은 인덱스에 없는데 쿼리는 그것도 원하니 플래너가 인덱스를 버립니다. 인덱스는 그대로 있는데 풀스캔으로 돌아가는 것이어서, 변경 직후에 explain을 보지 않으면 눈치채기 어렵습니다.쿼리 조건을 넓히는 변경(조건 제거,
$ne, $exists, $in 도입)은 partial index 조건도 같이 넓혀야 합니다. 이 둘은 항상 함께 움직이는 쌍이라고 생각하는 게 안전합니다.결국 인덱스 조건에서 삭제 필터를 뻔고
{ isQualityAssured: true }만 남깔습니다. 미삭제만 보는 쿼리가 나중에 다시 생기더라도 그 쿼리는 더 좁으니 같은 인덱스를 탈 수 있습니다.배열 필드와 $exists
배열 필드에 인덱스를 걸면 멀티키 인덱스가 되고 원소마다 엔트리가 하나씩 생깁니다. 상품 속성을
attributes: ["ALFRESH", "QUALITY_ASSURED"]처럼 문자열 배열로 저장하기 시작했는데, 이 필드는 전체 문서 중 속성이 있는 수백 건에만 존재합니다.JavaScript
db.item.createIndex(
{ attributes: 1 },
{ partialFilterExpression: { attributes: { $exists: true } }, background: true }
)$exists: true 조건이 없으면 필드가 없는 1억 7천만 건이 null 엔트리로 들어갑니다. 그리고 { attributes: "QUALITY_ASSURED" }처럼 등호로 조회하면 "필드가 존재한다"를 함의하기 때문에 플래너가 이 인덱스를 쓰지만, { attributes: { $exists: false } }로 "속성 없는 상품"을 세려고 하면 인덱스 밖이라 풀스캔입니다. 백필이나 대사 스크립트를 짤 때 무심코 쓰기 쉬운 형태라 적어둡니다.PostgreSQL에서 다른 점
개념과 제약은 같습니다.
WHERE deleted_at IS NULL로 만든 인덱스는 WHERE deleted_at IS NULL을 포함한 쿼리에만 쓰이고, 삭제 포함 조회는 타지 못합니다.한 가지 더 신경 쓸 것은 바인드 파라미터입니다.
WHERE flag = $1처럼 값이 실행 시점에 결정되면 플래너가 인덱스 조건 flag = true를 함의하는지 확신하지 못해 부분 인덱스를 포기할 수 있습니다. generic plan이 선택되는 상황이 그렇습니다. ORM이 조건을 전부 파라미터로 넘기는 환경이라면 EXPLAIN ANALYZE로 실제 계획을 확인하고, 안 타면 조건을 리터럴로 박거나 일반 인덱스로 가는 게 안전합니다.만들고 나서 확인하기
인덱스를 만들고 끝내지 않고 쿼리가 실제로 타는지 봅니다. Partial index는 "있는데 안 쓰이는" 상태가 조용히 생기기 때문입니다.
JavaScript
db.item.find({ "characteristicItem.isQualityAssured": true })
.sort({ createdAt: -1, _id: -1 }).limit(20)
.explain("executionStats")winningPlan에 IXSCAN과 인덱스 이름이 보이고 totalDocsExamined가 결과 건수 근처면 정상입니다. COLLSCAN이면 쿼리 조건이 인덱스 조건을 함의하지 못하는 것이라 둘을 나란히 놓고 봅니다. 제 경우 인덱스가 있는 필드는 145건을 1ms에 읽었고, 없는 필드는 explain 자체가 타임아웃으로 끊겼습니다. 다른 것은 인덱스 하나뿐이었습니다.정리
- Partial index는 조건에 맞는 문서만 색인합니다. 전체 중 소수만 골라내는 조회에 맞고, 문서 수가 크고 쓰기가 많을수록 이득이 큽니다
- 쿼리 조건이 인덱스 조건을 함의해야 쓰입니다. 인덱스 조건은 넓게, 쿼리는 같거나 좁게
- 쿼리를 넓히는 변경은 인덱스 조건도 같이 바꿔야 합니다. 삭제 필터 제거가 대표적입니다
- MongoDB는
partialFilterExpression, PostgreSQL은CREATE INDEX ... WHERE. MySQL은 없어 generated column으로 흉내냅니다
- 만든 뒤 explain으로 IXSCAN을 확인합니다

