새벽 2시에 페이저가 울렸다. Slack 알림이 아니라 진짜 PagerDuty 소리였다. 모니터를 켜보니 메인 대시보드 API의 p99 응답 시간이 평소 230ms에서 47초로 튀어 있었다. 우리 팀이 운영하는 e-commerce 플랫폼 — 5명짜리 팀, PostgreSQL 14.8 — 의 주문 조회 쿼리가 완전히 멈춰버린 거였다.
2025년 3월 중순의 일이다. orders 테이블 혼자 3.2TB, 연관 테이블들까지 합치면 총 10TB가 넘는 데이터베이스였다. 이후 2주를 거의 이 문제에만 매달렸고, 이 글은 그 과정에서 배운 것들을 정리한 거다.
새벽에 처음 든 오해, 그리고 실제 범인
첫 반응은 예상 가능했다. “인덱스 문제겠지.” 그래서 \d orders로 인덱스 목록을 확인하고 pg_stat_user_indexes를 훑어봤다. 인덱스는 있었다. created_at, user_id, 심지어 복합 인덱스도.
그런데 슬로우 쿼리 로그를 보니 엉뚱한 곳에서 문제가 터지고 있었다. pg_stat_statements를 켜놨기 때문에 범인을 특정하는 건 금방이었다:
SELECT
round(total_exec_time::numeric, 2) AS total_ms,
calls,
round(mean_exec_time::numeric, 2) AS mean_ms,
substring(query, 1, 80) AS query_snippet
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
평균 실행 시간이 38초짜리 쿼리가 있었다. 호출 횟수는 시간당 200번 정도. 수학이 맞아 떨어졌다.
문제의 쿼리는 이런 모양이었다:
SELECT o.*, u.email, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN ('pending', 'processing')
AND o.created_at > NOW() - INTERVAL '30 days'
GROUP BY o.id, u.email
ORDER BY o.created_at DESC;
별로 복잡해 보이지 않는다. 그런데 이 쿼리가 3.2TB 테이블에서 sequential scan을 하고 있었다.
EXPLAIN ANALYZE를 반쪽만 읽고 있었다
솔직히 말하면, 나는 몇 년 동안 EXPLAIN ANALYZE를 잘못 쓰고 있었다. Seq Scan이 보이면 “인덱스 추가하면 되겠네” 하고 넘어갔는데, 진짜 중요한 건 BUFFERS 옵션이었다.
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.email, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status IN ('pending', 'processing')
AND o.created_at > NOW() - INTERVAL '30 days'
GROUP BY o.id, u.email
ORDER BY o.created_at DESC;
BUFFERS 옵션을 넣으면 shared hit(캐시에서 읽은 블록)과 shared read(디스크에서 읽은 블록)가 같이 출력된다. 우리 케이스에서 shared read가 2.1M 블록이었다. 거의 모든 걸 디스크에서 읽고 있었다는 뜻이다.
effective_cache_size 설정도 문제였다. 기본값으로 서버 RAM의 절반도 안 되는 값이 들어가 있었다. PostgreSQL 쿼리 플래너는 이 값을 보고 인덱스 스캔이 얼마나 이득인지 판단하는데, 내가 이 설정을 방치한 게 꽤 비쌌다.
work_mem도 건드렸다. 기본값 4MB는 우리 정렬 연산에 턱없이 부족해서 temporary file이 계속 만들어지고 있었다. EXPLAIN ANALYZE 출력에서 “Sort Method: external merge Disk” 같은 문구가 보이면 이게 신호다. 전역 설정을 건드리면 연결 수가 많을 때 OOM으로 이어질 수 있어서, 특정 쿼리 실행 전에만 SET work_mem = '128MB'로 세션 단위로 조정했다.
EXPLAIN ANALYZE 출력을 읽는 게 처음엔 정말 어렵다. 노드를 안쪽에서 바깥쪽으로 읽어야 하고, actual rows와 estimated rows 차이가 클수록 통계가 오래됐다는 신호다. ANALYZE 명령어로 통계를 수동 갱신하고 나서 플래너 동작이 달라진 경우도 있었다.
인덱스를 더 쌓았더니 오히려 느려졌다
이게 진짜 예상 못 한 부분이었다. 처음에 내가 한 일은 partial index를 추가하는 거였다:
-- status 필터 + 날짜 필터 + 정렬을 한 번에 커버하려고
CREATE INDEX CONCURRENTLY idx_orders_status_created
ON orders (status, created_at DESC)
WHERE status IN ('pending', 'processing');
조건에 맞는 행만 인덱스에 들어가서 크기가 작다. 이건 잘 작동했다. 쿼리 시간이 38초에서 4초대로 줄었다.
그런데 문제는 다른 데서 났다. orders 테이블에는 이미 인덱스가 11개 있었다. 주문이 생성되거나 업데이트될 때마다 11개 인덱스를 전부 갱신해야 한다. pg_stat_user_indexes를 다시 확인해보니 그 중 3개는 idx_scan이 지난 6개월 동안 0이었다. 아무도 쓰지 않는 인덱스가 write 성능을 갉아먹고 있었던 거다.
쓰지 않는 인덱스 3개를 삭제했더니 주문 생성 API 응답 시간이 18% 줄었다. 작은 숫자처럼 보이지만, 트래픽이 몰릴 때는 체감이 됐다.
인덱스 bloat 문제도 있었다. pgstattuple 익스텐션으로 확인해보니 일부 인덱스는 dead tuple이 35%를 넘고 있었다. REINDEX CONCURRENTLY로 재구성하고 나서 인덱스 크기가 눈에 띄게 줄었다. autovacuum이 있는데 왜 이렇게 됐냐면 — 기본 autovacuum_vacuum_scale_factor가 0.2, 즉 테이블 행의 20%가 dead tuple이 돼야 청소가 시작된다는 뜻인데, 수십억 행짜리 테이블에서 20%면 어마어마한 양이다(몇억 건). 이걸 0.01로 낮추고 나서야 정상적으로 청소가 됐다.
파티셔닝에 걸었던 기대와 실제로 얻은 것
이 시점에서 “파티셔닝을 하면 근본적으로 해결되지 않을까”라는 생각이 들었다. orders 테이블을 created_at 기준 분기별 파티션으로 쪼개는 계획이었다.
마이그레이션 자체는 골치 아팠다. 3.2TB를 pg_dump 받아 파티션 테이블로 복원하는 데 거의 이틀이 걸렸다. 구조는 이렇게 만들었다:
CREATE TABLE orders (
id BIGSERIAL,
user_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (id, created_at) -- 파티션 키가 PK에 반드시 포함되어야 함
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2025_q1 PARTITION OF orders
FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');
CREATE TABLE orders_2025_q2 PARTITION OF orders
FOR VALUES FROM ('2025-04-01') TO ('2025-07-01');
그런데 여기서 예상 못 한 문제가 있었다. WHERE 절에 created_at이 있어야만 partition pruning이 작동한다. 당연한 얘기 같지만, 코드베이스 여기저기 흩어진 쿼리들 중에 created_at 필터가 없는 게 꽤 됐다. EXPLAIN을 돌려보면 여전히 모든 파티션을 스캔하는 경우가 있었다. 파티셔닝을 해도 쿼리가 파티션 키를 쓰지 않으면 그냥 다 읽는다.
더 황당했던 건 PK 설정이었다. 파티셔닝된 테이블에서 글로벌 unique constraint는 파티션 키를 포함해야 한다. 즉 id만으로는 unique를 보장할 수 없고, (id, created_at) 조합이 PK가 돼야 한다. 이걸 모르고 시작했다가 orders를 참조하던 다른 테이블들 외래 키를 전부 손댔다 — PostgreSQL 문서 어딘가에 분명히 써있는 내용인데, 직접 당해봐야 머리에 박히는 종류의 교훈이다. 스키마 마이그레이션만 며칠이 걸렸다.
파티셔닝 후 30일치 조회 쿼리는 4초에서 0.3초로 줄었다. 이건 파티셔닝 덕분이기도 하지만, 파티션별로 인덱스를 새로 만들면서 bloat도 같이 해소된 효과가 섞여 있다.
다음에 다시 한다면, 이 순서로 하겠다
그래서 뭘 먼저 해야 하냐고? 내 경험상 순서가 중요하다.
첫 번째. pg_stat_statements를 켜고 2-3일 데이터를 모아라. 추측으로 최적화하지 말고, 실제 느린 쿼리를 데이터로 확인해라. 범인이 특정되면 EXPLAIN (ANALYZE, BUFFERS)로 실행 계획을 확인한다. BUFFERS 없이 보는 건 절반만 보는 거다.
두 번째. 현재 인덱스를 감사해라. pg_stat_user_indexes에서 idx_scan = 0인 인덱스를 찾아라 — 단, 최소 2주 이상의 데이터를 기준으로.
SELECT
tablename,
indexname,
idx_scan,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
세 번째. autovacuum 설정을 테이블 크기에 맞게 조정해라. 대형 테이블은 autovacuum_vacuum_scale_factor를 낮춰야 한다.
ALTER TABLE orders SET (
autovacuum_vacuum_scale_factor = 0.01,
autovacuum_analyze_scale_factor = 0.005
);
네 번째. 파티셔닝은 정말 필요한지 먼저 따져봐라. 데이터 접근 패턴이 시간 범위 기반이고, 테이블이 충분히 크고(500GB 이상), 마이그레이션 비용을 감당할 수 있다면 고려해볼 만하다. 그렇지 않다면 partial index + vacuum 튜닝이 훨씬 간단하고 효과적이다. 우리 케이스처럼 최근 데이터 위주로 접근하는 경우엔 파티셔닝이 확실히 효과적이었다 — 다만 PK 제약 문제를 설계 단계에서 미리 고려하고 들어가야 한다.
PgBouncer도 빼놓을 수 없다. 연결 수가 많아서 connection overhead가 상당했는데, transaction pooling 모드로 붙이고 나서 DB 서버 부하가 눈에 띄게 줄었다. 코드 한 줄 안 바꾸고 인프라 레벨에서 얻는 공짜 최적화다.
한 가지 중요한 점은 — 금요일 오후에 대규모 인덱스 변경은 절대 하지 마라. REINDEX CONCURRENTLY가 “동시” 실행이라 안전하다고 방심했다가 금요일 오후에 lock 경쟁 때문에 30분짜리 장애를 냈다. CONCURRENTLY는 테이블 lock을 안 잡는다는 뜻이지, 부하가 없다는 뜻이 아니다.
최종 결과는 — 문제 쿼리 47초에서 0.3초, write 성능 18% 향상 — 세 가지 조합이었다: 정확한 진단, 인덱스 감사, 파티셔닝. 어느 하나만으로는 부족했다.