PostgreSQL Partial Index란?
Partial Index(부분 인덱스)는 테이블의 일부 행에만 인덱스를 생성하는 PostgreSQL 고유 기능이다. WHERE 조건을 지정하여, 조건에 해당하는 행만 인덱스에 포함시킨다. 전체 인덱스 대비 크기가 작고 유지비용이 낮으며, 쿼리 성능은 동일하거나 더 빠르다.
-- 전체 인덱스: 1000만 행 모두 인덱싱
CREATE INDEX idx_orders_status ON orders (status);
-- Partial Index: active 주문만 인덱싱 (전체의 5%)
CREATE INDEX idx_orders_active ON orders (created_at)
WHERE status = 'active';1000만 건 중 active가 50만 건이라면, Partial Index는 전체 인덱스의 1/20 크기로 동일한 조회 성능을 제공한다.
언제 Partial Index를 써야 하는가?
다음 조건을 모두 만족할 때 Partial Index가 효과적이다:
- 데이터 분포가 편향됨: 특정 조건의 행이 전체의 소수(보통 20% 이하)
- 쿼리에 항상 해당 조건이 포함됨:
WHERE status = 'active'가 거의 모든 쿼리에 붙는 경우 - 인덱스 크기/유지비용을 줄여야 함: 대용량 테이블에서 쓰기 성능이 중요한 경우
실전 패턴 5가지
1. Soft Delete: 삭제되지 않은 행만
가장 흔한 패턴이다. deleted_at IS NULL인 행이 대부분의 쿼리 대상이므로, 삭제된 행은 인덱싱할 필요가 없다.
-- Soft Delete 테이블
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
email VARCHAR(255) NOT NULL,
deleted_at TIMESTAMPTZ
);
-- ❌ 전체 인덱스: 삭제된 100만 행도 포함
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- ✅ Partial Index: 활성 사용자만 (+ Unique 보장)
CREATE UNIQUE INDEX idx_users_email_active ON users (email)
WHERE deleted_at IS NULL;보너스: Partial Unique Index를 사용하면, 삭제된 사용자의 이메일을 재사용할 수 있다. 전체 Unique Index에서는 불가능한 패턴이다.
2. 상태 기반: 처리 대기 행만
-- 주문 테이블: 99%가 completed, 1%가 pending
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';
-- 결제 재시도: failed 상태만 조회
CREATE INDEX idx_payments_failed ON payments (failed_at, retry_count)
WHERE status = 'failed' AND retry_count < 3;배치 처리나 큐 워커가 "미처리 건"만 조회하는 패턴에서 극적인 효과를 발휘한다.
3. NULL 제외: 값이 있는 행만
-- 프로필 사진이 있는 사용자만 검색
CREATE INDEX idx_users_avatar ON users (username)
WHERE avatar_url IS NOT NULL;
-- 전화번호 인증 완료 사용자만
CREATE INDEX idx_users_verified_phone ON users (phone_number)
WHERE phone_verified_at IS NOT NULL;4. 시간 기반: 최근 데이터만
-- 최근 30일 로그만 인덱싱 (주기적 재생성 필요)
CREATE INDEX idx_logs_recent ON access_logs (user_id, action)
WHERE created_at > '2026-02-14';
-- 만료되지 않은 세션만
CREATE INDEX idx_sessions_active ON sessions (user_id)
WHERE expires_at > NOW();주의: NOW()는 인덱스 생성 시점의 값으로 고정된다. 주기적으로 인덱스를 재생성(REINDEX)하거나, 파티셔닝과 조합하는 것이 더 실용적이다.
5. Boolean 플래그: true인 행만
-- 피처 플래그: 활성화된 사용자만
CREATE INDEX idx_users_beta ON users (id)
WHERE is_beta_tester = true;
-- VIP 고객만 빠르게 조회
CREATE INDEX idx_customers_vip ON customers (name, email)
WHERE is_vip = true;쿼리 매칭 조건
Partial Index가 사용되려면, 쿼리의 WHERE 절이 인덱스의 WHERE 조건을 포함(imply)해야 한다:
-- 인덱스: WHERE status = 'active'
CREATE INDEX idx_active ON orders (created_at) WHERE status = 'active';
-- ✅ 사용됨: 조건이 정확히 일치
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';
-- ✅ 사용됨: 더 좁은 조건 (인덱스 조건을 포함)
SELECT * FROM orders WHERE status = 'active' AND amount > 100;
-- ❌ 사용 안 됨: 인덱스 조건 없음
SELECT * FROM orders WHERE created_at > '2026-01-01';
-- ❌ 사용 안 됨: 다른 상태
SELECT * FROM orders WHERE status = 'pending';EXPLAIN ANALYZE로 반드시 확인하라:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'active' AND created_at > '2026-01-01';
-- Index Scan using idx_active on orders ← Partial Index 사용 확인Expression Index와 조합
Partial Index는 Expression Index와 조합할 수 있다:
-- 대소문자 무시 이메일 유니크 (활성 사용자만)
CREATE UNIQUE INDEX idx_users_email_lower ON users (LOWER(email))
WHERE deleted_at IS NULL;
-- JSONB 필드 + Partial
CREATE INDEX idx_orders_metadata ON orders USING GIN (metadata)
WHERE status IN ('pending', 'processing');성능 비교: 전체 vs Partial
| 항목 | 전체 인덱스 | Partial Index |
|---|---|---|
| 인덱스 크기 (1000만 행, 5% 대상) | ~240MB | ~12MB |
| INSERT 오버헤드 | 모든 행에 인덱스 업데이트 | 조건 해당 행만 업데이트 |
| VACUUM 부하 | 전체 인덱스 대상 | 작은 인덱스만 대상 |
| 캐시 효율 | 인덱스가 메모리에 안 올라갈 수 있음 | 작아서 메모리에 상주 가능 |
| 조회 성능 | 동일 | 동일 또는 더 빠름 (캐시 히트) |
주의사항
- ORM 호환성: 대부분의 ORM(TypeORM, Prisma, Drizzle)은 Partial Index를 Raw SQL로만 생성 가능. 마이그레이션 파일에 직접 작성하라
- 쿼리 조건 일치: 쿼리의 WHERE가 인덱스 조건을 정확히 포함해야 한다. 살짝만 달라도 인덱스가 무시된다
- 통계 갱신:
ANALYZE를 실행하여 플래너가 Partial Index의 선택도를 정확히 파악하게 하라 - MySQL 미지원: Partial Index는 PostgreSQL 고유 기능이다. MySQL은 Filtered Index를 지원하지 않는다
정리
PostgreSQL Partial Index는 "필요한 행만 인덱싱"하는 강력한 최적화 도구다. Soft Delete, 상태 기반 조회, NULL 필터링 등 편향된 데이터 분포에서 인덱스 크기를 1/10~1/20로 줄이면서 동일한 조회 성능을 유지한다. 쓰기 성능과 VACUUM 부하도 함께 개선되므로, 대용량 테이블 운영에서 반드시 검토해야 할 기법이다.