실행 가능한 SQLite 문서: 부분 인덱스

Q: SQLite의 부분 인덱스(Partial Index)란 무엇인가요?

인덱스를 만들 때 지정한 WHERE 조건을 만족하는 행만 인덱싱하는 방식입니다. CREATE INDEX name ON table(col) WHERE condition 형태로 작성하면, SQLite는 조건이 참인 행만 인덱스에 저장합니다. 인덱스 크기가 줄고 쓰기 속도도 빨라지지만, 조건에 맞는 쿼리에 대해서는 일반 인덱스와 동일한 조회 성능을 냅니다.

Q: 부분 인덱스로 UNIQUE 제약도 걸 수 있나요?

가능합니다. CREATE UNIQUE INDEX ... WHERE ... 형태로 만들면 조건에 맞는 행에 대해서만 유니크 제약이 적용됩니다. 대표적인 활용이 '사용자당 활성 레코드 1개'인데, 소프트 삭제된 행은 조건에서 제외되기 때문에 같은 키를 가진 삭제 이력은 여러 개 있더라도 활성 행은 단 하나만 존재하도록 강제할 수 있습니다.

부분 인덱스는 일부 행만 인덱싱한다

일반 인덱스는 테이블의 모든 행에 대해 항목을 만든다. 반면 sqlite 부분 인덱스(partial index)는 인덱스를 만들 때 지정한 WHERE 조건에 맞는 행만 항목으로 가진다. 인덱스가 작아지니 탐색할 페이지 수도 줄고, 인덱싱된 영역을 건드리지 않는 INSERT나 UPDATE에서는 부담도 그만큼 가벼워진다.

문법은 평범한 CREATE INDEX 뒤에 WHERE 절만 붙이면 된다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

idx_orders_pending 인덱스에는 status = 'pending'인 행만 들어갑니다. 배송 완료, 취소, 환불된 주문은 아예 인덱스에 포함되지 않죠. 만약 orders 테이블의 95%가 과거 데이터이고 평소에 진행 중인 주문 위주로 조회한다면, 같은 쿼리 성능을 내면서도 인덱스 크기는 20배 작아집니다.

쿼리 플래너가 실제로 부분 인덱스를 사용하는 조건

SQLite 부분 인덱스는 쿼리가 인덱스에 포함된 행만 조회한다는 사실을 플래너가 증명할 수 있을 때 만 사용됩니다. 가장 확실한 방법은 인덱스에 적어둔 WHERE 조건을 쿼리에도 그대로 적어주는 것입니다:

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status TEXT NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

INSERT INTO orders(customer_id, status, total, created_at) VALUES
    (1, 'pending',  42.0, '2026-01-10'),
    (2, 'shipped',  99.0, '2026-01-11'),
    (3, 'pending', 120.0, '2026-01-12');

EXPLAIN QUERY PLAN
SELECT id FROM orders
WHERE status = 'pending'
ORDER BY created_at;

계획에는 USING INDEX idx_orders_pending가 찍혀야 합니다. 쿼리에서 status = 'pending'을 빼버리면 옵티마이저는 다시 풀 테이블 스캔으로 떨어집니다. 쿼리가 인덱스가 커버하는 범위 안에만 머문다는 걸 플래너 입장에선 알 길이 없으니까요.

핵심 규칙은 이겁니다: 쿼리의 WHERE가 인덱스의 WHERE를 함의해야 한다. 같은 컬럼, 같은 값으로 동등 비교하는 경우가 가장 안전하고 직관적이죠. 부등호나 OR이 섞이면 얘기가 미묘해지니, 반드시 EXPLAIN QUERY PLAN으로 확인하세요.

부분 인덱스를 쓰면 뭐가 좋은가 — 세 가지 이점

sqlite 부분 인덱스가 빛을 발하는 이유는 크게 세 가지입니다:

디스크 사용량이 작다. 조건에 맞는 행만 저장됩니다. "테이블의 1%만 뜨겁다"는 패턴이라면, 인덱스 크기도 풀 인덱스의 약 1% 수준에 그칩니다.
쓰기 비용이 줄어든다. INSERT나 UPDATE 시점에 행이 필터 조건에 맞을 때만 인덱스를 건드립니다. 위 테이블에서 status = 'shipped'로 INSERT가 들어오면 idx_orders_pending은 아예 손도 안 댑니다.
조회 속도는 그대로. B-tree 탐색은 인덱스 크기에 로그 비례합니다. 인덱스가 작아지면 조회가 살짝 빨라지긴 하지만, 진짜 이득은 주변 효과 — 캐시 미스 감소, I/O 감소 — 에서 나옵니다.

특정 컬럼 값이 심하게 편중되어 있는 경우 — 대다수 행이 한 값이고 정작 관심사는 소수의 나머지 값 — 이 패턴이 sqlite partial index의 교과서적인 사용 사례입니다.

부분 UNIQUE 인덱스 (진짜 킬러 기능)

일반 UNIQUE 제약은 모든 행에 똑같이 적용됩니다. 소프트 삭제(soft delete)를 도입하는 순간 이게 골치 아파집니다:

-- 失敗: email = 'a@x.com' の行が2つ存在します。たとえ片方が削除済みであっても。
CREATE UNIQUE INDEX idx_users_email ON users(email);

부분 unique 인덱스를 사용하면 의미 있는 행에 대해서만 유일성 제약을 걸 수 있습니다:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    deleted_at TEXT
);

CREATE UNIQUE INDEX idx_users_email_active
    ON users(email)
    WHERE deleted_at IS NULL;

INSERT INTO users(email, deleted_at) VALUES
    ('a@x.com', '2026-01-01'),  -- 소프트 삭제됨
    ('a@x.com', '2026-01-05'),  -- 다시 소프트 삭제됨
    ('a@x.com', NULL);          -- 활성 행 하나, 허용됨

SELECT email, deleted_at FROM users;

같은 이메일을 가진 행이 세 개나 있어도 제약 위반이 발생하지 않습니다. deleted_at IS NULL 조건을 만족하는 행만 유일성 검사에 포함되기 때문이죠. 여기서 같은 이메일을 가진 활성 행을 하나 더 넣으려고 하면 SQLite는 곧바로 UNIQUE constraint failed 오류를 던집니다.

이 패턴은 실무에서 정말 자주 등장합니다. 고객당 활성 구독 하나, 사용자당 기본 주소 하나, 주문당 미결제 청구서 하나 같은 요구사항이 모두 여기에 해당하죠. partial unique index는 이런 규칙을 그대로 표현해 줍니다.

NULL을 다루는 인덱스 설계

NULL은 인덱스와 묘하게 얽힙니다. 자주 마주치는 상황은 "NULL은 아예 무시하고 싶다"는 케이스입니다. 예를 들어 대부분의 행이 NULL이지만 값이 들어 있는 행끼리는 반드시 유일해야 하는 희소한 external_id 컬럼이 있다고 해 봅시다:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    external_id TEXT
);

CREATE UNIQUE INDEX idx_products_external_id
    ON products(external_id)
    WHERE external_id IS NOT NULL;

INSERT INTO products(name, external_id) VALUES
    ('Widget',  'EXT-001'),
    ('Gadget',  NULL),
    ('Doohickey', NULL),
    ('Sprocket', 'EXT-002');

SELECT name, external_id FROM products;

NULL은 두 개가 공존해도 문제없고, EXT-001과 EXT-002 행은 유일성이 보장됩니다. 게다가 NULL인 행은 아예 저장되지 않아서 인덱스 크기도 작아지죠. 덕분에 테이블이 커져도 external_id로 조회하는 속도가 빠릅니다.

필터에 쓸 수 있는 것과 없는 것

sqlite 부분 인덱스의 WHERE 절은 사용할 수 있는 표현이 꽤 제한적입니다. 다음은 참조할 수 있습니다:

인덱스가 만들어지는 테이블의 컬럼
리터럴 상수
일부 결정적(deterministic) 내장 함수

반면, 다음은 사용할 수 없습니다:

다른 테이블
서브쿼리
random()이나 CURRENT_TIMESTAMP처럼 비결정적인 함수
파라미터나 변수

이런 제약이 있는 이유는 단순합니다. SQLite는 행이 삽입되거나 갱신될 때마다 필터 조건을 평가해야 하는데, 그 결과가 항상 일정해야 하기 때문이죠. 그래서 아래 같은 형태는 잘 동작합니다:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type TEXT NOT NULL,
    priority INTEGER NOT NULL,
    created_at TEXT NOT NULL
);

CREATE INDEX idx_events_high_priority
    ON events(created_at)
    WHERE priority >= 5 AND event_type IN ('error', 'critical');

하지만 WHERE created_at > date('now')는 사용할 수 없습니다. date('now')는 시간이 흐르면 값이 바뀌기 때문에, 인덱스에 포함되는 행의 집합도 SQLite 모르게 계속 달라지게 됩니다.

점검 워크플로

부분 인덱스를 추가했다면 다음 세 가지를 차례대로 확인해 보세요:

CREATE TABLE jobs (
    id INTEGER PRIMARY KEY,
    queue TEXT NOT NULL,
    status TEXT NOT NULL,
    run_at TEXT NOT NULL
);

CREATE INDEX idx_jobs_runnable
    ON jobs(run_at)
    WHERE status = 'queued';

INSERT INTO jobs(queue, status, run_at) VALUES
    ('default', 'queued',  '2026-01-10 10:00'),
    ('default', 'done',    '2026-01-10 09:00'),
    ('email',   'queued',  '2026-01-10 11:00');

-- 1. 플래너가 인덱스를 선택하는가?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'queued' ORDER BY run_at LIMIT 10;

-- 2. 필터 범위를 벗어난 쿼리는 무시하는가?
EXPLAIN QUERY PLAN
SELECT id FROM jobs WHERE status = 'done' ORDER BY run_at;

-- 3. 필터가 아예 없는 쿼리는 건너뛰는가?
EXPLAIN QUERY PLAN
SELECT id FROM jobs ORDER BY run_at;

쿼리 1은 idx_jobs_runnable를 사용해야 합니다. 쿼리 2와 3은 풀 스캔(또는 다른 인덱스가 있다면 그쪽)으로 빠져야 정상이고요. 만약 의도하지 않은 쿼리에서 옵티마이저가 부분 인덱스를 골라잡았다면, 필터 조건을 다시 들여다보세요. 생각보다 범위가 넓을 가능성이 큽니다.

부분 인덱스를 쓰지 말아야 할 때

sqlite 부분 인덱스는 날카로운 도구입니다. 다음과 같은 상황이라면 굳이 손대지 않는 편이 낫습니다.

필터가 테이블 대부분을 포함하는 경우. "active" 상태가 전체 행의 90%라면, 부분 인덱스는 그냥 일반 인덱스에 군더더기만 붙은 꼴입니다. 그럴 땐 컬럼 자체에 인덱스를 거세요.
쿼리에 필터 조건이 그대로 들어가지 않는 경우. ORM이 WHERE status IN (?, ?, ?) 같은 식으로 쿼리를 만들거나, 필터를 동적으로 조립한다면 옵티마이저가 매칭을 인식하지 못할 때가 많습니다. 짐작하지 말고 EXPLAIN QUERY PLAN으로 직접 확인해 보세요.
핫한 서브셋이 시간이 지남에 따라 바뀌는 경우. "최근 30일치 주문"에 대한 부분 인덱스는 그럴듯해 보이지만 사실 표현 자체가 불가능합니다. 필터는 결정적(deterministic)이어야 하기 때문이죠. 이런 경우엔 인덱스를 주기적으로 다시 만들거나 스키마 자체를 바꿔야 합니다(별도의 recent_orders 테이블을 두거나, 매일 밤 토글하는 archived 불리언 컬럼을 추가하는 식으로요).

필터 조건이 안정적이고, 큰 테이블의 작은 일부만 가리킨다면 — 부분 인덱스는 SQLite에서 얻을 수 있는 가장 효과적인 튜닝 수단 중 하나입니다.

다음 글: 쿼리 플랜 읽는 법

이 글에서는 인덱스가 실제로 사용됐는지 확인하기 위해 EXPLAIN QUERY PLAN에 많이 의지했습니다. 이 도구는 별도 페이지로 다룰 만한 가치가 있어요. 출력을 어떻게 읽는지, 등장하는 키워드들이 무엇을 뜻하는지, 그리고 깔끔한 인덱스 조회와 슬그머니 끼어든 풀 스캔을 어떻게 구분하는지까지 — 다음 글에서 이어서 살펴보겠습니다.

자주 묻는 질문

SQLite의 부분 인덱스(Partial Index)란 무엇인가요?

인덱스를 만들 때 지정한 WHERE 조건을 만족하는 행만 인덱싱하는 방식입니다. CREATE INDEX name ON table(col) WHERE condition 형태로 작성하면, SQLite는 조건이 참인 행만 인덱스에 저장합니다. 인덱스 크기가 줄고 쓰기 속도도 빨라지지만, 조건에 맞는 쿼리에 대해서는 일반 인덱스와 동일한 조회 성능을 냅니다.

전체 인덱스 대신 부분 인덱스를 써야 하는 경우는 언제인가요?

큰 테이블에서 일부 행만 반복적으로 조회할 때 유리합니다. 예를 들어 처리 대기 중인 주문, 활성 사용자, 아직 실행되지 않은 작업처럼요. 해당 슬라이스만 인덱싱하면 인덱스가 작게 유지되고, 조건에 해당하지 않는 행에 대한 쓰기는 인덱스 갱신을 아예 건너뜁니다. 단, 쿼리에 인덱스의 WHERE 조건이 함께 들어 있지 않으면 옵티마이저가 인덱스를 사용하지 못한다는 점은 주의해야 합니다.

부분 인덱스로 UNIQUE 제약도 걸 수 있나요?

가능합니다. CREATE UNIQUE INDEX ... WHERE ... 형태로 만들면 조건에 맞는 행에 대해서만 유니크 제약이 적용됩니다. 대표적인 활용이 '사용자당 활성 레코드 1개'인데, 소프트 삭제된 행은 조건에서 제외되기 때문에 같은 키를 가진 삭제 이력은 여러 개 있더라도 활성 행은 단 하나만 존재하도록 강제할 수 있습니다.

SQLite 부분 인덱스(Partial Index) 완벽 정리