PostgreSQL 인덱스 전략 — 느린 쿼리 잡는 법

느린 쿼리, 어디서부터 시작해야 할까?

서비스가 성장하면서 가장 먼저 맞닥뜨리는 성능 병목은 대부분 데이터베이스 쿼리입니다. 페이지 로딩이 2초, 3초로 늘어나고, 특정 API 엔드포인트만 유독 느릴 때 — 인덱스 문제일 가능성이 90%입니다. PostgreSQL은 강력한 인덱스 시스템을 제공하지만, 올바르게 설계하지 않으면 오히려 성능을 해치기도 합니다. 이 글에서는 EXPLAIN ANALYZE로 문제를 진단하는 법부터 복합 인덱스, 부분 인덱스, GIN 인덱스를 실전에 적용하는 패턴까지 단계별로 설명합니다.

EXPLAIN ANALYZE로 쿼리 실행 계획 읽기

느린 쿼리를 최적화하려면 먼저 PostgreSQL이 그 쿼리를 어떻게 실행하는지 파악해야 합니다. EXPLAIN ANALYZE는 실제로 쿼리를 실행하면서 각 단계별 소요 시간과 처리 행 수를 보여줍니다. 아래는 기본 사용법입니다.

-- 기본 실행 계획 확인
EXPLAIN ANALYZE
SELECT u.id, u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
WHERE u.created_at > '2025-01-01'
ORDER BY p.created_at DESC
LIMIT 20;

-- 결과 예시
-- Limit  (cost=1842.50..1842.55 rows=20 width=96) (actual time=234.512..234.518 rows=20 loops=1)
--   ->  Sort  (cost=1842.50..1854.00 rows=4600 width=96) (actual time=234.510..234.513 rows=20 loops=1)
--         Sort Key: p.created_at DESC
--         Sort Method: top-N heapsort  Memory: 26kB
--         ->  Hash Join  (cost=312.00..1726.00 rows=4600 width=96) (actual time=12.301..230.445 rows=4600 loops=1)
--               Hash Cond: (p.user_id = u.id)
--               ->  Seq Scan on posts p  (cost=0.00..1320.00 rows=50000 width=72) (actual time=0.012..180.234 rows=50000 loops=1)
--               ->  Hash  (cost=290.00..290.00 rows=1760 width=24) (actual time=12.100..12.100 rows=1760 loops=1)
--                     Buckets: 2048  Batches: 1  Memory Usage: 105kB
--                     ->  Seq Scan on users u  (cost=0.00..290.00 rows=1760 width=24) (actual time=0.010..11.800 rows=1760 loops=1)
--                           Filter: (created_at > '2025-01-01'::date)

-- 핵심 포인트: "Seq Scan" 이 보이면 인덱스가 없거나 사용 안 됨
-- actual time이 크면 해당 단계가 병목

실행 계획에서 주목해야 할 키워드는 Seq Scan(순차 탐색, 인덱스 미사용), Index Scan(인덱스 사용), Bitmap Index Scan(여러 조건 조합)입니다. 대용량 테이블에서 Seq Scan이 나오면 즉시 인덱스 추가를 검토해야 합니다. 또한 actual time의 앞 숫자는 첫 행을 가져올 때까지 걸린 시간, 뒷 숫자는 전체 완료 시간입니다.

B-Tree 인덱스 — 가장 기본, 가장 중요

PostgreSQL의 기본 인덱스 타입은 B-Tree입니다. 등호(=), 범위(<, >, BETWEEN), ORDER BY, LIKE ‘prefix%’ 패턴에 효과적입니다. 가장 흔한 실수는 카디널리티(값의 다양성)가 낮은 컬럼에 인덱스를 거는 것입니다. 예를 들어 boolean 컬럼이나 상태 값이 2~3개뿐인 컬럼에는 인덱스 효과가 미미합니다.

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_posts_user_id ON posts(user_id);

-- 복합 인덱스 (순서가 매우 중요!)
-- WHERE user_id = ? AND status = ? ORDER BY created_at DESC 패턴에 최적화
CREATE INDEX idx_posts_user_status_created 
ON posts(user_id, status, created_at DESC);

-- 쿼리 예시 — 위 복합 인덱스가 효율적으로 동작함
SELECT * FROM posts
WHERE user_id = 123 AND status = 'published'
ORDER BY created_at DESC
LIMIT 10;

-- 인덱스 사용 여부 확인
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'posts'
ORDER BY idx_scan DESC;

-- 사용되지 않는 인덱스 찾기 (idx_scan = 0인 것들)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND schemaname = 'public';

복합 인덱스의 컬럼 순서는 쿼리 패턴에 따라 달라집니다. 일반 원칙은 선택도(selectivity)가 높은 컬럼을 앞에 배치하는 것입니다. 단, PostgreSQL은 “Index Skip Scan”을 지원하지 않으므로 앞 컬럼 조건 없이 중간/뒤 컬럼만 사용하는 쿼리에는 복합 인덱스가 동작하지 않습니다. 이 경우 별도 인덱스가 필요합니다.

부분 인덱스 — 필요한 데이터만 인덱싱

부분 인덱스(Partial Index)는 WHERE 조건을 붙여 특정 행만 인덱싱하는 방식입니다. 테이블 데이터 중 특정 상태(예: 처리 대기 중인 건)만 자주 조회한다면, 전체 데이터 대신 해당 부분만 인덱싱해 크기를 줄이고 성능을 높일 수 있습니다.

-- 부분 인덱스 예시: 활성 사용자만 인덱싱
CREATE INDEX idx_users_active_email 
ON users(email) 
WHERE is_active = true;

-- 처리 대기 중인 작업만 인덱싱 (전체의 0.1%일 수 있음)
CREATE INDEX idx_jobs_pending 
ON background_jobs(created_at, priority) 
WHERE status = 'pending';

-- 위 인덱스는 아래 쿼리에서만 동작
SELECT * FROM background_jobs
WHERE status = 'pending'
ORDER BY priority DESC, created_at ASC
LIMIT 100;

-- NULL이 아닌 값만 인덱싱 (NULL이 많은 컬럼에 유용)
CREATE INDEX idx_orders_payment_id
ON orders(payment_id)
WHERE payment_id IS NOT NULL;

-- 인덱스 크기 비교
SELECT 
    indexrelname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename = 'background_jobs';

부분 인덱스는 소프트 딜리트 패턴에서도 매우 유용합니다. deleted_at IS NULL 조건의 인덱스를 만들면, 논리 삭제된 수백만 건의 데이터를 인덱스에서 제외해 크기를 대폭 줄일 수 있습니다.

GIN 인덱스 — JSONB와 전문 검색

JSONB 컬럼 내부 키/값 검색이나 배열 포함 여부, 전문(Full-Text) 검색에는 GIN(Generalized Inverted Index) 인덱스가 적합합니다. B-Tree로는 처리할 수 없는 복잡한 구조의 데이터를 효율적으로 인덱싱합니다.

-- JSONB 컬럼에 GIN 인덱스
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    metadata JSONB
);

CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- JSONB 포함 검색 (@> 연산자)
SELECT * FROM products
WHERE metadata @> '{"category": "electronics", "in_stock": true}';

-- JSONB 특정 키 값 검색
SELECT * FROM products
WHERE metadata ->> 'brand' = 'Samsung';

-- 위 쿼리를 위한 표현식 인덱스
CREATE INDEX idx_products_brand 
ON products ((metadata ->> 'brand'));

-- 전문 검색 GIN 인덱스
CREATE INDEX idx_posts_fts 
ON posts USING GIN (to_tsvector('korean', title || ' ' || content));

-- 한국어 전문 검색 쿼리
SELECT id, title
FROM posts
WHERE to_tsvector('korean', title || ' ' || content) 
      @@ plainto_tsquery('korean', '인덱스 최적화');

-- 배열 GIN 인덱스
CREATE INDEX idx_articles_tags ON articles USING GIN (tags);

-- 배열 포함 검색
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'performance'];

인덱스 관리와 VACUUM 전략

인덱스를 추가하기만 하고 관리하지 않으면 오히려 성능이 저하됩니다. INSERT/UPDATE/DELETE가 잦은 테이블에서는 인덱스 팽창(bloat)이 발생하고, VACUUM을 통한 정리가 필요합니다. 또한 불필요한 인덱스는 쓰기 성능을 떨어뜨리므로 주기적으로 사용 현황을 확인하고 정리해야 합니다.

-- 인덱스 bloat 확인
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- REINDEX로 인덱스 재구축 (bloat 제거)
-- PostgreSQL 12+에서는 CONCURRENTLY 옵션으로 무중단 재구축 가능
REINDEX INDEX CONCURRENTLY idx_posts_user_status_created;

-- 인덱스 동시 생성 (프로덕션 환경에서 테이블 락 방지)
CREATE INDEX CONCURRENTLY idx_new_column ON large_table(new_column);

-- 느린 쿼리 로그 설정 (postgresql.conf 또는 ALTER SYSTEM)
ALTER SYSTEM SET log_min_duration_statement = '1000';  -- 1초 이상 쿼리 로깅
ALTER SYSTEM SET log_statement = 'none';
SELECT pg_reload_conf();

-- pg_stat_statements로 느린 쿼리 상위 10개 확인
SELECT 
    round(total_exec_time::numeric, 2) AS total_time_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS avg_time_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS percentage,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

실전 체크리스트

인덱스 전략을 실제 프로젝트에 적용할 때 확인해야 할 사항들을 정리했습니다.

• EXPLAIN ANALYZE 먼저 — 개선 전후를 항상 실행 계획으로 검증
• Seq Scan != 무조건 나쁨 — 소규모 테이블(수천 행)은 인덱스보다 Seq Scan이 빠를 수 있음
• 복합 인덱스 순서 — 등호 조건 먼저, 범위 조건 나중, ORDER BY 컬럼 마지막
• CONCURRENTLY 사용 — 프로덕션에서 인덱스 추가/삭제 시 락 방지
• 부분 인덱스 활용 — 특정 상태 값으로 자주 필터링한다면 반드시 검토
• pg_stat_statements 설치 — 느린 쿼리 지속 모니터링의 기본 도구
• 불필요한 인덱스 삭제 — idx_scan = 0인 인덱스는 쓰기 성능만 낮춤

코드벤터는 글로벌 협력 네트워크를 기반으로 다양한 규모의 서비스를 개발하면서 쌓아온 PostgreSQL 최적화 경험을 이 블로그에 지속적으로 공유하고 있습니다. 인덱스 설계는 한 번으로 끝나지 않습니다. 서비스가 성장하고 쿼리 패턴이 바뀔 때마다 실행 계획을 다시 확인하고, 불필요해진 인덱스를 제거하며, 새로운 병목에 맞는 인덱스를 추가하는 — 이 반복적인 과정이 결국 빠르고 안정적인 데이터베이스를 만드는 핵심입니다.