PostgreSQL 성능 최적화: 인덱스부터 쿼리 튜닝까지 - 코드픽 블로그
CodePick
로그인
PostgreSQL 성능 최적화: 인덱스부터 쿼리 튜닝까지
기술 가이드

PostgreSQL 성능 최적화: 인덱스부터 쿼리 튜닝까지

2026년 3월 1일 55 views by 코드벤터
#PostgreSQL#DB최적화#백엔드

PostgreSQL 성능 최적화: 인덱스부터 쿼리 튜닝까지

서비스가 성장할수록 데이터베이스 성능은 점점 더 중요해집니다. 처음에는 아무 문제 없던 쿼리가 데이터가 수백만 건으로 늘어나면 갑자기 수 초씩 걸리기 시작하죠. 이 글에서는 PostgreSQL에서 성능 문제를 진단하고 해결하는 실전 방법을 단계별로 알아봅니다.

성능 문제 진단: EXPLAIN ANALYZE

최적화의 첫 번째 단계는 병목 지점을 정확히 파악하는 것입니다. PostgreSQL은 EXPLAIN ANALYZE 명령으로 쿼리 실행 계획과 실제 소요 시간을 함께 보여줍니다.

sql 
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 1234 AND status = 'pending'
ORDER BY created_at DESC;

출력 결과에서 주목해야 할 핵심 키워드:

  • Seq Scan: 테이블 전체를 순차 스캔 - 인덱스가 없거나 사용되지 않는 신호
  • Index Scan: 인덱스를 활용한 효율적 조회
  • Hash Join / Nested Loop: 조인 방식 - 데이터 크기에 따라 효율이 달라짐
  • actual time: 실제 소요 시간 (ms 단위)
  • rows: 실제로 처리된 행 수 vs. 예상 행 수
code 
Seq Scan on orders  (cost=0.00..45200.00 rows=1250 width=128)
                    (actual time=0.012..3241.432 rows=1250 loops=1)
  Filter: ((user_id = 1234) AND (status = pending))
  Rows Removed by Filter: 4998750
Planning Time: 0.234 ms
Execution Time: 3241.532 ms

위 결과는 500만 건을 전부 읽어서 1,250건만 남긴 전형적인 Full Scan입니다. 인덱스 추가가 필요한 상황이죠.

인덱스 유형과 선택 기준

PostgreSQL은 다양한 인덱스 유형을 지원합니다. 상황에 맞는 유형을 선택하는 것이 중요합니다.

인덱스 유형적합한 상황특징
B-Tree등가 검색, 범위 검색, 정렬기본 인덱스, 대부분의 상황에 적합
Hash등가 검색만매우 빠르지만 범위 검색 불가
GIN배열, JSONB, 전문 검색복잡한 데이터 타입에 특화
BRIN시계열 등 순차 데이터매우 작은 크기, 대용량 테이블에 유리
Partial특정 조건의 데이터만 조회인덱스 크기 최소화
Covering인덱스만으로 쿼리 완결테이블 접근 없이 결과 반환

B-Tree 인덱스 - 기본 중의 기본

sql 
-- 단일 컬럼 인덱스
CREATE INDEX idx_orders_user_id ON orders (user_id);

-- 자주 함께 조회되는 컬럼은 복합 인덱스로
CREATE INDEX idx_orders_user_status ON orders (user_id, status);

복합 인덱스는 순서가 중요합니다. (user_id, status) 인덱스는 user_id만 조건으로 쓸 때도 사용되지만, status만 조건으로 쓰면 사용되지 않습니다.

부분 인덱스 (Partial Index)

특정 조건의 데이터만 자주 조회할 때 매우 효과적입니다.

sql 
-- 처리 대기 중인 주문만 인덱싱 (전체 데이터의 1%라면 99% 크기 절약)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- 삭제되지 않은 사용자만 인덱싱
CREATE INDEX idx_users_active_email ON users (email)
WHERE deleted_at IS NULL;

커버링 인덱스 (Covering Index)

쿼리에 필요한 모든 컬럼을 인덱스에 포함시켜 테이블 접근 자체를 없앱니다.

sql 
-- product_id로 검색하되 name, price만 필요한 경우
CREATE INDEX idx_products_covering ON products (product_id)
INCLUDE (name, price);

이렇게 하면 Index Only Scan이 실행되어 실제 테이블을 전혀 읽지 않습니다.

실전 쿼리 튜닝 기법

1. SELECT * 피하기

sql 
-- 나쁜 예시
SELECT * FROM users WHERE id = 1;

-- 좋은 예시
SELECT id, name, email FROM users WHERE id = 1;

필요한 컬럼만 선택하면 네트워크 전송량과 메모리 사용량이 줄어들고, 커버링 인덱스를 활용할 가능성이 높아집니다.

2. N+1 쿼리 문제 해결

sql 
-- 좋은 예시: JOIN으로 한 번에 처리
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at >= '2024-01-01'
GROUP BY u.id, u.name;

3. 서브쿼리 대신 CTE 사용

sql 
-- 나쁜 예시: 상관 서브쿼리 (행마다 실행됨)
SELECT name,
       (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as cnt
FROM users u;

-- 좋은 예시: CTE로 한 번만 집계
WITH order_counts AS (
    SELECT user_id, COUNT(*) as cnt
    FROM orders
    GROUP BY user_id
)
SELECT u.name, COALESCE(oc.cnt, 0) as cnt
FROM users u
LEFT JOIN order_counts oc ON u.id = oc.user_id;

4. LIKE 쿼리 최적화

sql 
-- 인덱스 사용 가능 (전방 일치)
SELECT * FROM products WHERE name LIKE '노트북%';

-- 전문 검색이 필요하면 GIN 인덱스 + pg_trgm 확장 사용
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);

핵심 설정 파라미터 튜닝

postgresql.conf에서 조정할 수 있는 주요 파라미터들입니다.

ini 
# 시스템 메모리의 25~40% 권장
shared_buffers = 4GB

# 복잡한 쿼리 정렬/해시 작업 메모리 (세션당)
work_mem = 64MB

# 유지보수 작업 메모리 (VACUUM, CREATE INDEX)
maintenance_work_mem = 512MB

# SSD 사용 시 낮춰서 인덱스 스캔 선호하게 함
random_page_cost = 1.1

# OS 캐시 포함 예상 가용 메모리
effective_cache_size = 12GB

# 병렬 쿼리 워커 수
max_parallel_workers_per_gather = 4

autovacuum 설정

테이블 블로트(bloat)를 방지하는 autovacuum도 적극적으로 조정해야 합니다.

sql 
-- 특정 테이블에 autovacuum 설정 적용
ALTER TABLE orders SET (
    autovacuum_vacuum_scale_factor = 0.01,
    autovacuum_analyze_scale_factor = 0.005
);

기본값(0.2)보다 낮게 설정해 대용량 테이블에서 더 자주 vacuum이 실행되도록 합니다.

인덱스 모니터링과 유지보수

sql 
-- 사용되지 않는 인덱스 찾기
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

-- 인덱스 재구성 (온라인, 잠금 없이)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

파티셔닝으로 대용량 테이블 관리

수억 건이 넘는 테이블은 파티셔닝이 효과적입니다.

sql 
-- 날짜 기반 범위 파티셔닝
CREATE TABLE logs (
    id BIGSERIAL,
    created_at TIMESTAMP NOT NULL,
    user_id INT,
    action TEXT
) PARTITION BY RANGE (created_at);

-- 월별 파티션 생성
CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

-- 각 파티션에 인덱스 생성
CREATE INDEX ON logs_2024_01 (user_id, created_at);

파티션 프루닝(Partition Pruning)으로 쿼리 시 관련 파티션만 스캔합니다.

연결 풀링 - 성능의 숨겨진 열쇠

애플리케이션에서 DB 연결을 매번 새로 맺으면 오버헤드가 큽니다. PgBouncer 같은 연결 풀러를 사용하세요.

ini 
# pgbouncer.ini 예시
[databases]
mydb = host=localhost dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

트랜잭션 모드를 사용하면 수천 개의 클라이언트 연결을 수십 개의 실제 DB 연결로 처리할 수 있습니다.

최적화 전후 성능 비교 요약

실전 적용 결과를 정리하면:

  • 단순 인덱스 추가: Full Scan 3,241ms -> Index Scan 2ms (약 1,600배 향상)
  • 복합 인덱스 + ORDER BY 최적화: 550ms -> 0.09ms (약 6,000배 향상)
  • N+1 쿼리 제거: 100회 쿼리 200ms -> 1회 쿼리 15ms (13배 향상)

PostgreSQL 성능 최적화는 마법이 아닙니다. EXPLAIN ANALYZE로 병목을 찾고, 적절한 인덱스를 추가하고, 쿼리를 개선하는 반복 작업입니다. 작은 변화 하나가 서비스 응답 속도를 수십 배 개선하는 경험을 직접 해보시길 바랍니다.

코드벤터는 백엔드 성능 최적화부터 인프라 설계까지, 기술적으로 탄탄한 서비스를 함께 만들어 나갑니다. 데이터베이스 튜닝이 필요하거나 성능 이슈로 고민 중이라면 언제든지 함께 해결해 나갈 수 있습니다.

개발 의뢰 상담

AI 서비스나 플랫폼 개발을
고민 중이신가요?

CodePick에서는
기획 → 개발 → 운영까지 함께합니다.
아이디어만 있어도 상담 가능합니다.

무료 개발 상담 신청 개발 비용 확인하기

CodeVenter 개발팀이 직접 담당 · 1~2 영업일 내 회신

✓ 스타트업 MVP 개발✓ AI 서비스 개발✓ 웹 플랫폼 개발✓ 기업 시스템 구축✓ 모바일 앱 개발
블로그 목록 프로젝트 문의

AI Development Studio

코드픽 by 코드벤터

  • 대표: 윤승환 · 사업자등록번호: 121-57-64983
  • 대구광역시 중구 국채보상로 586, 16층 · info@codeventer.com
회사소개 무료 상담 →

© 2025 코드벤터. All rights reserved.