PostgreSQL 고급 가이드 | 인덱스·쿼리 최적화·파티셔닝·복제·백업 전략

이 글의 핵심

PostgreSQL의 고급 기능을 실전 예제로 완벽 정리합니다. 인덱스 전략, 쿼리 최적화, 파티셔닝, 복제, 백업, 성능 튜닝까지 실무에 바로 적용할 수 있는 가이드입니다.

실무 경험 공유: 일 1억 건의 이벤트를 처리하는 데이터베이스를 운영하면서, 파티셔닝으로 쿼리 속도를 10배 향상시키고 인덱스 최적화로 디스크 사용량을 40% 절감한 경험을 공유합니다.

들어가며: “쿼리가 너무 느려요”

실무 문제 시나리오

시나리오 1: 쿼리가 10초 걸려요
1억 건 테이블에서 SELECT가 10초 걸립니다. 인덱스로 0.1초로 단축됩니다.

시나리오 2: 테이블이 너무 커요
10억 건 로그 테이블이 1TB입니다. 파티셔닝으로 관리가 쉬워집니다.

시나리오 3: 백업이 6시간 걸려요
전체 백업이 6시간 걸립니다. 증분 백업으로 30분으로 단축됩니다.

flowchart LR
    subgraph Before["최적화 전"]
        A1[쿼리: 10초]
        A2[테이블: 1TB]
        A3[백업: 6시간]
    end
    subgraph After["최적화 후"]
        B1[쿼리: 0.1초]
        B2[파티션: 관리 용이]
        B3[백업: 30분]
    end
    Before --> After

---

1. 인덱스 전략

B-Tree 인덱스 (기본)

-- 단일 컬럼 인덱스
CREATE INDEX idx_users_email ON users(email);

-- 복합 인덱스
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- 부분 인덱스
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- 표현식 인덱스
CREATE INDEX idx_users_lower_email ON users(LOWER(email));

GIN 인덱스 (전문 검색)

-- JSONB 인덱스
CREATE INDEX idx_metadata ON events USING GIN(metadata);

-- 배열 인덱스
CREATE INDEX idx_tags ON posts USING GIN(tags);

-- 전문 검색
CREATE INDEX idx_content_search ON articles USING GIN(to_tsvector('english', content));

실전 예제: 검색 최적화

-- 테이블 생성
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  tags TEXT[],
  metadata JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 인덱스 생성
CREATE INDEX idx_articles_tags ON articles USING GIN(tags);
CREATE INDEX idx_articles_metadata ON articles USING GIN(metadata);
CREATE INDEX idx_articles_search ON articles USING GIN(
  to_tsvector('english', title || ' ' || content)
);

-- 검색 쿼리
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'postgresql & performance')
ORDER BY created_at DESC
LIMIT 10;

---

2. 쿼리 최적화

EXPLAIN ANALYZE

-- 실행 계획 확인
EXPLAIN ANALYZE
SELECT 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 > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 10;

출력 해석:

• Seq Scan: 전체 테이블 스캔 (느림)
• Index Scan: 인덱스 사용 (빠름)
• cost: 예상 비용
• actual time: 실제 실행 시간

쿼리 최적화 예제

-- ❌ 느린 쿼리
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2026;
-- Seq Scan: 10초

-- ✅ 빠른 쿼리
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- Index Scan: 0.1초

CTE vs Subquery

-- CTE (Common Table Expression)
WITH recent_orders AS (
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, ro.order_count
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id
WHERE ro.order_count > 10;

-- MATERIALIZED CTE (더 빠름)
WITH recent_orders AS MATERIALIZED (
  -- 결과를 메모리에 캐싱
  SELECT user_id, COUNT(*) as order_count
  FROM orders
  WHERE created_at > NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, ro.order_count
FROM users u
JOIN recent_orders ro ON u.id = ro.user_id;

---

3. 파티셔닝

Range 파티셔닝

-- 부모 테이블
CREATE TABLE events (
  id BIGSERIAL,
  user_id INTEGER NOT NULL,
  event_type TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL,
  data JSONB
) PARTITION BY RANGE (created_at);

-- 파티션 생성
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE events_2026_03 PARTITION OF events
FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 인덱스는 각 파티션에 자동 생성
CREATE INDEX idx_events_user_id ON events(user_id);

자동 파티션 생성 (pg_partman)

-- pg_partman 확장 설치
CREATE EXTENSION pg_partman;

-- 자동 파티션 관리
SELECT create_parent(
  'public.events',
  'created_at',
  'native',
  'monthly',
  p_premake := 3,  -- 3개월 미리 생성
  p_start_partition := '2026-01-01'
);

파티션 조회

-- 특정 월 데이터만 스캔 (빠름)
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';
-- Scan only events_2026_03 partition

---

4. 복제 (Replication)

스트리밍 복제 설정

Primary 서버 (postgresql.conf):

wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB

Replica 서버 (postgresql.conf):

hot_standby = on

복제 시작:

# Replica 서버에서
pg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -P -v -R

논리 복제 (Logical Replication)

-- Primary 서버
CREATE PUBLICATION my_pub FOR TABLE users, orders;

-- Replica 서버
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary-host dbname=mydb user=replicator'
PUBLICATION my_pub;

---

5. 백업 전략

pg_dump (논리 백업)

# 전체 백업
pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

# 특정 테이블만
pg_dump -U postgres -d mydb -t users -t orders -F c -f tables_backup.dump

# 복원
pg_restore -U postgres -d mydb -v mydb_backup.dump

pg_basebackup (물리 백업)

# 전체 물리 백업
pg_basebackup -h localhost -D /backup/pgdata -U postgres -P -v

# 증분 백업 (WAL 아카이빙)
# postgresql.conf
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'

자동 백업 스크립트

#!/bin/bash
# backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backup"
DB_NAME="mydb"

# 백업 실행
pg_dump -U postgres -d $DB_NAME -F c -f "$BACKUP_DIR/${DB_NAME}_${DATE}.dump"

# 7일 이상 된 백업 삭제
find $BACKUP_DIR -name "*.dump" -mtime +7 -delete

echo "백업 완료: ${DB_NAME}_${DATE}.dump"

# cron 등록 (매일 새벽 2시)
0 2 * * * /path/to/backup.sh

---

6. 성능 튜닝

설정 최적화

# postgresql.conf

# 메모리
shared_buffers = 4GB          # RAM의 25%
effective_cache_size = 12GB   # RAM의 75%
work_mem = 64MB               # 정렬/해시 작업용
maintenance_work_mem = 1GB    # VACUUM, CREATE INDEX용

# 쿼리 플래너
random_page_cost = 1.1        # SSD 사용 시
effective_io_concurrency = 200

# WAL
wal_buffers = 16MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

VACUUM 및 ANALYZE

-- 통계 업데이트
ANALYZE users;

-- 불필요한 공간 회수
VACUUM FULL users;

-- 자동 VACUUM 설정
ALTER TABLE users SET (
  autovacuum_vacuum_scale_factor = 0.1,
  autovacuum_analyze_scale_factor = 0.05
);

---

7. 실전 예제: 대용량 로그 시스템

-- 파티션 테이블
CREATE TABLE logs (
  id BIGSERIAL,
  user_id INTEGER NOT NULL,
  action TEXT NOT NULL,
  ip_address INET,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);

-- 월별 파티션 (자동 생성 스크립트)
DO $$
DECLARE
  start_date DATE := '2026-01-01';
  end_date DATE := '2027-01-01';
  partition_date DATE;
BEGIN
  partition_date := start_date;
  WHILE partition_date < end_date LOOP
    EXECUTE format(
      'CREATE TABLE IF NOT EXISTS logs_%s PARTITION OF logs
       FOR VALUES FROM (%L) TO (%L)',
      to_char(partition_date, 'YYYY_MM'),
      partition_date,
      partition_date + INTERVAL '1 month'
    );
    partition_date := partition_date + INTERVAL '1 month';
  END LOOP;
END $$;

-- 인덱스
CREATE INDEX idx_logs_user_id ON logs(user_id);
CREATE INDEX idx_logs_action ON logs(action);
CREATE INDEX idx_logs_metadata ON logs USING GIN(metadata);

-- 쿼리 (특정 월만 스캔)
SELECT action, COUNT(*) as count
FROM logs
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01'
  AND user_id = 12345
GROUP BY action;

---

정리 및 체크리스트

핵심 요약

• 인덱스: B-Tree, GIN, GiST 등 상황별 선택
• 쿼리 최적화: EXPLAIN ANALYZE로 병목 지점 파악
• 파티셔닝: 대용량 테이블을 월/년 단위로 분할
• 복제: 스트리밍 복제로 고가용성 확보
• 백업: pg_dump + WAL 아카이빙
• 성능 튜닝: shared_buffers, work_mem 등 설정 최적화

프로덕션 체크리스트

• 적절한 인덱스 생성
• EXPLAIN ANALYZE로 쿼리 분석
• 파티셔닝 전략 수립 (필요 시)
• 복제 서버 구성
• 백업 자동화 스크립트
• 모니터링 설정 (pg_stat_statements)
• 정기 VACUUM 및 ANALYZE

---

같이 보면 좋은 글

• 데이터베이스 인덱싱 완벽 가이드
• SQL 쿼리 최적화 실전 가이드
• Redis 고급 활용 가이드

---

이 글에서 다루는 키워드

PostgreSQL, 데이터베이스, 인덱스, 쿼리 최적화, 파티셔닝, 복제, 백업, 성능 튜닝

자주 묻는 질문 (FAQ)

Q. 인덱스를 많이 만들면 성능이 나빠지나요?

A. 네, 인덱스는 INSERT/UPDATE/DELETE 성능을 저하시킵니다. 자주 조회하는 컬럼에만 인덱스를 만들고, 사용하지 않는 인덱스는 삭제하세요.

Q. 파티셔닝은 언제 사용하나요?

A. 테이블이 수억 건 이상이거나, 시계열 데이터로 오래된 데이터를 주기적으로 삭제해야 할 때 사용합니다.

Q. 복제 서버는 몇 대가 적절한가요?

A. 읽기 부하 분산이 목적이면 2-3대, 고가용성이 목적이면 최소 1대의 Standby를 권장합니다.

Q. 백업은 얼마나 자주 해야 하나요?

A. 전체 백업은 주 1회, WAL 아카이빙은 실시간으로 권장합니다. 중요도에 따라 일 1회 전체 백업도 고려하세요.