PostgreSQL 고급 가이드 | 인덱스·쿼리 최적화·파티셔닝·복제·백업 전략
이 글의 핵심
쿼리가 느려지거나 테이블이 계속 커질 때 인덱스부터 추가하는 것이 늘 정답은 아닙니다. 읽기가 쓰기를 막지 않는 MVCC 구조와 그 대가로 필요한 VACUUM을 먼저 이해하고, 실행 계획을 읽어 병목을 찾는 법, 파티셔닝과 복제를 도입할 시점, 논리·물리 백업을 고르는 기준을 정리합니다.
이 글의 핵심
이 글은 PostgreSQL을 운영하면서 성능과 용량 문제를 만났을 때 필요한 내부 동작(MVCC, VACUUM, WAL)을 먼저 설명하고, 그 위에서 인덱스, 쿼리 튜닝, 파티셔닝, 복제, 백업을 다룹니다. 내부 동작을 먼저 다루는 이유는 PostgreSQL의 운영 문제 대부분이 “UPDATE가 행을 덮어쓰지 않고 새 버전을 만든다”는 한 가지 설계에서 파생되기 때문입니다. 테이블이 계속 커지는 현상, 오래 열린 트랜잭션이 전체를 느리게 만드는 현상, autovacuum 튜닝이 필요한 이유가 모두 여기에 연결됩니다. PostgreSQL이 무엇인지, 이름은 왜 이렇게 생겼는지부터 보려면 PostgreSQL 뜻과 특징을 먼저 읽는 것이 좋습니다.
PostgreSQL의 역사와 아키텍처 설계 철학
POSTGRES의 탄생: Michael Stonebraker와 Berkeley (1986)
PostgreSQL의 역사는 INGRES(1970년대)까지 거슬러 올라갑니다. UC Berkeley의 Michael Stonebraker 교수는 INGRES를 상용화한 후, “관계형 데이터베이스의 근본적 한계를 극복하자”는 목표로 POSTGRES 프로젝트를 시작했습니다.
당시 관계형 DB의 한계:
1980년대 RDBMS 문제:
- 복잡한 타입 지원 없음 (JSON, 배열, GIS)
- 규칙(Rule) 시스템 없음 (트리거 미지원)
- 시간 여행(Time Travel) 불가능
- 확장성 제로 (사용자 정의 타입/함수 불가)Stonebraker의 혁신적 아이디어:
- 객체-관계형 모델: 테이블뿐 아니라 복잡한 타입 지원
- 규칙 시스템: 데이터베이스 레벨에서 비즈니스 로직 실행
- MVCC(Multi-Version Concurrency Control): 읽기-쓰기 충돌 없이 동시성 극대화
- 확장성: C 함수, 사용자 정의 연산자 추가 가능
MVCC의 혁명: “읽기는 쓰기를 블록하지 않는다”
MVCC가 없는 잠금 기반 동시성 제어 (예: 초기 RDBMS, MySQL의 MyISAM 엔진, SQL Server의 기본 READ COMMITTED 잠금 모드):
문제 상황:
┌─────────────┐ ┌─────────────┐
│ Transaction │ │ Transaction │
│ 1 │ │ 2 │
└──────┬──────┘ └──────┬──────┘
│ │
│ UPDATE row A │
│ (Write Lock 획득) │
│ │
│ │ SELECT row A
│ │ ← 대기! (Lock 때문에)
│ COMMIT │
│ (Lock 해제) │
│ │
│ │ 이제 읽기 가능PostgreSQL MVCC 해결책:
┌─────────────┐ ┌─────────────┐
│ Transaction │ │ Transaction │
│ 1 (ID=100) │ │ 2 (ID=101) │
└──────┬──────┘ └──────┬──────┘
│ │
│ UPDATE row A │
│ (버전 100 생성) │
│ │
│ │ SELECT row A
│ │ → 버전 100 읽기!
│ │ (즉시 응답, 대기 없음)
│ COMMIT │
│ │오해하기 쉬운 부분이 있습니다. MVCC는 PostgreSQL만의 기능이 아니라, Oracle과 MySQL InnoDB도 MVCC를 써서 읽기가 쓰기를 기다리지 않습니다. 차이는 옛 버전을 어디에 두는가입니다. Oracle과 InnoDB는 행을 제자리에서 고치고 옛 값을 별도의 undo 영역에 보관하는 반면, PostgreSQL은 새 버전을 테이블 안에 새 행으로 추가하고 옛 행을 그대로 남겨 둡니다. 이 방식은 롤백이 즉시 끝나고 undo 영역이 넘칠 걱정이 없다는 장점이 있지만, 옛 행이 테이블 파일 안에 쌓이므로 이를 치우는 VACUUM이 반드시 필요해집니다. 뒤에서 다룰 bloat, autovacuum, HOT 업데이트 같은 PostgreSQL 특유의 운영 이슈가 모두 이 선택에서 나옵니다.
내부 메커니즘 (xmin/xmax):
PostgreSQL의 각 행은 시스템 컬럼을 가지고 있습니다. 테이블 정의에 적지 않아도 모든 행에 존재하며, 이름을 명시하면 조회할 수 있습니다.
-- 시스템 컬럼 조회 (CREATE TABLE에 직접 선언하는 것이 아님)
SELECT xmin, xmax, ctid, id, name FROM users;
-- xmin: 이 행 버전을 만든 트랜잭션 ID (xid 타입)
-- xmax: 이 행 버전을 삭제/수정한 트랜잭션 ID (0이면 아직 유효)
-- ctid: 물리적 위치 (페이지 번호, 페이지 내 순번)UPDATE 시 실제 동작:
-- 초기 상태
INSERT INTO users VALUES (1, 'Alice');
-- → xmin=100, xmax=0 (아직 삭제 안 됨)
-- UPDATE 실행
BEGIN; -- Transaction ID = 200
UPDATE users SET name = 'Alice2' WHERE id = 1;
-- 실제로 일어나는 일:
-- 1. 기존 행: xmin=100, xmax=200 (삭제 표시)
-- 2. 새 행: xmin=200, xmax=0 (생성)
-- 동시에 다른 트랜잭션이 읽으면:
BEGIN; -- Transaction ID = 201
SELECT * FROM users WHERE id = 1;
-- → 트랜잭션 200이 아직 커밋되지 않았으므로
-- xmax=200인 기존 행이 여전히 보이고, xmin=200인 새 행은 안 보임 (Alice)
COMMIT; -- Transaction 200 커밋
-- READ COMMITTED라면 201의 다음 SELECT부터 새 행이 보임 (Alice2)
-- REPEATABLE READ라면 201이 끝날 때까지 계속 Alice가 보임어떤 행 버전이 보이는지는 xmax가 0인지로 정해지는 것이 아니라, 쿼리 시작 시점에 찍은 스냅샷(그 시점에 커밋된 트랜잭션 목록)과 각 행의 xmin·xmax를 비교해 결정됩니다. “xmin이 스냅샷 기준으로 커밋됐고, xmax가 없거나 아직 커밋되지 않았으면 보인다”가 기본 규칙입니다. 트랜잭션 ID가 32비트라서 약 21억 개를 쓰면 한 바퀴 돌아 옛 행이 미래의 것처럼 보이게 되는데, 이를 막기 위해 VACUUM이 오래된 행을 “모두에게 보이는 행”으로 표시(freeze)합니다. autovacuum을 오래 끄거나 막아 두면 PostgreSQL이 이 wraparound를 막으려고 database is not accepting commands to avoid wraparound data loss 에러와 함께 쓰기를 거부하는 상황까지 갈 수 있습니다.
MVCC의 트레이드오프: Dead Tuples
문제:
UPDATE/DELETE 시 실제로는 삭제하지 않고 표시만 함
→ 데이터 파일 크기 계속 증가
→ "테이블 비대화" (Bloat)
예시:
모든 행을 한 번씩 UPDATE하면 VACUUM 전까지
테이블 파일에 옛 버전과 새 버전이 함께 존재해 크기가 최대 약 2배가 됨이 비용을 줄이는 PostgreSQL의 장치가 HOT(Heap-Only Tuple) 업데이트입니다. 인덱스에 포함되지 않은 컬럼만 바뀌고 같은 페이지에 빈 공간이 있으면, 새 버전을 같은 페이지에 두고 인덱스는 갱신하지 않습니다. 그래서 자주 바뀌는 컬럼(조회수, 상태값)에 인덱스를 거는 것은 조회 이득보다 쓰기 비용이 더 클 수 있고, 업데이트가 잦은 테이블은 fillfactor를 90 정도로 낮춰 페이지마다 여유 공간을 남겨 두면 HOT 비율이 올라갑니다. pg_stat_user_tables의 n_tup_hot_upd와 n_tup_upd를 비교하면 현재 HOT 비율을 확인할 수 있습니다.
VACUUM: 가비지 컬렉션의 아키텍처
VACUUM의 역할:
┌────────────────────────────────────┐
│ PostgreSQL 데이터 파일 │
├────────────────────────────────────┤
│ [Live Row 1][Dead][Live Row 2] │
│ [Dead][Dead][Live Row 3][Dead] │
│ ... │
└────────────────────────────────────┘
↓ VACUUM 실행
┌────────────────────────────────────┐
│ [Live Row 1][Live Row 2] │
│ [Live Row 3][...Free Space...] │
└────────────────────────────────────┘VACUUM vs VACUUM FULL:
-- VACUUM (온라인, 빠름)
VACUUM users;
-- 동작:
-- 1. Dead Tuples 표시 제거
-- 2. Free Space Map 업데이트 (재사용 가능 표시)
-- 3. 파일 크기 줄이지 않음
-- 4. 다른 세션 블록 안 함
-- VACUUM FULL (오프라인, 느림)
VACUUM FULL users;
-- 동작:
-- 1. 새 파일에 Live Rows만 복사
-- 2. 기존 파일 삭제
-- 3. 파일 크기 실제 축소
-- 4. 테이블 Lock (다른 세션 블록!)
-- 5. 디스크 여유 공간 필요 (원본 + 새 파일)Autovacuum 튜닝 (실전):
-- 테이블별 Autovacuum 설정
ALTER TABLE high_write_table SET (
autovacuum_vacuum_scale_factor = 0.05, -- 5% 변경 시 VACUUM
autovacuum_vacuum_threshold = 1000, -- 최소 1000개 변경
autovacuum_analyze_scale_factor = 0.02 -- 2% 변경 시 ANALYZE
);
-- 글로벌 설정 (postgresql.conf)
autovacuum = on
autovacuum_max_workers = 4 -- 동시 VACUUM 워커 수
autovacuum_naptime = 10s -- 체크 주기
autovacuum_vacuum_cost_delay = 2ms -- CPU 제한 (낮을수록 빠름)흔한 장애 유형:
문제:
- 대량 배치 작업 후 autovacuum이 따라가지 못함
- 테이블과 인덱스가 실제 데이터보다 몇 배로 비대화
- 인덱스 스캔이 느려짐 (죽은 행까지 방문해야 함)
해결:
1. VACUUM VERBOSE로 현재 상태 확인
2. pg_stat_user_tables에서 n_dead_tup, last_autovacuum 모니터링
3. 공간을 실제로 돌려받아야 하면 pg_repack(온라인) 또는 점검 시간에 VACUUM FULL
4. 해당 테이블의 autovacuum 임계값 강화기본값 autovacuum_vacuum_scale_factor = 0.2는 “테이블의 20%가 죽은 행이 되면 VACUUM”이라는 뜻입니다. 행이 1억 개인 테이블이라면 2천만 개가 쌓여야 시작되므로, 큰 테이블일수록 이 비율을 낮추는 것이 핵심입니다. 반대로 autovacuum_vacuum_cost_delay를 너무 공격적으로 줄이면 VACUUM이 디스크 I/O를 크게 써서 서비스 쿼리가 느려질 수 있으므로, 변경 후에는 디스크 사용률을 함께 봐야 합니다. VACUUM FULL은 테이블 전체에 ACCESS EXCLUSIVE 락을 걸어 읽기까지 막고 원본 크기만큼 추가 디스크가 필요하므로, 운영 중에는 락을 짧게만 잡는 pg_repack 확장이 일반적인 대안입니다.
Write-Ahead Log (WAL)의 원리
WAL의 철학: “먼저 로그에 쓰며, 나중에 데이터 파일에 쓴다”
트랜잭션 실행 흐름:
┌──────────────────────────────────────┐
│ 1. BEGIN │
│ 2. UPDATE users SET name = 'Bob' │
│ → 메모리 버퍼에 변경 기록 │
│ │
│ 3. COMMIT │
│ → WAL 파일에 쓰기 (fsync) │ ← 디스크 동기화 (느림)
│ → 트랜잭션 완료! │
│ │
│ 4. Checkpoint (주기적) │
│ → 버퍼의 더티 페이지를 │
│ 데이터 파일에 쓰기 │
└──────────────────────────────────────┘왜 이렇게 복잡하게?
직접 데이터 파일 쓰기:
- 랜덤 I/O (느림)
- 1000개 행 수정 = 1000번 디스크 쓰기
WAL 쓰기:
- 순차 I/O (빠름)
- 한 트랜잭션의 변경을 로그 끝에 이어 쓰고 커밋 시 한 번 fsync
- 데이터 페이지는 나중에 Checkpoint에서 모아서 쓰기
→ 커밋 경로에서 랜덤 쓰기가 사라짐WAL의 또 다른 목적은 크래시 복구입니다. 데이터 파일은 체크포인트 이후의 변경이 반영되지 않은 상태일 수 있지만, WAL에는 커밋된 모든 변경이 남아 있으므로 재시작 시 마지막 체크포인트부터 WAL을 다시 적용해 일관된 상태로 돌아옵니다. 이 같은 WAL을 다른 서버로 흘려보내 적용하는 것이 아래 복제 절의 스트리밍 복제이고, 보관해 두었다가 특정 시점까지 재생하는 것이 백업 절의 PITR(시점 복구)입니다. 체크포인트 간격(max_wal_size, checkpoint_timeout)을 너무 짧게 잡으면 체크포인트 직후 페이지를 처음 수정할 때마다 페이지 전체를 WAL에 쓰는 full-page write가 늘어나 WAL 양이 크게 증가하고, 너무 길게 잡으면 크래시 복구 시간이 늘어납니다.
synchronous_commit 트레이드오프:
-- 기본값 (안전, 느림)
SET synchronous_commit = on;
-- COMMIT 시 WAL이 디스크에 fsync 될 때까지 대기
-- 크래시 시 커밋된 데이터 손실 없음
-- 커밋 지연 = 디스크 fsync 시간 (저장 장치에 따라 크게 다름)
-- 비동기 (빠름, 위험)
SET synchronous_commit = off;
-- COMMIT 즉시 반환, WAL은 WAL writer가 곧 기록
-- 크래시 시 최근 커밋 일부 손실 가능 (최대 wal_writer_delay의 약 3배 구간)
-- 데이터 파일이 깨지지는 않음 (일관성은 유지, 최근 커밋만 사라짐)
-- 실전 적용:
-- - 금융 거래: synchronous_commit = on
-- - 로그 수집: synchronous_commit = offB-Tree vs GiST vs GIN vs BRIN: 인덱스 내부 구조
B-Tree (균형 다분기 트리): 이진 트리가 아니라 한 노드(8KB 페이지)에 수백 개의 키를 담는 트리라서, 수억 행도 보통 3~4단계 안에 찾습니다. 아래 그림은 구조를 단순화한 것입니다.
인덱스 구조:
[50]
/ \
[25] [75]
/ \ / \
[10] [30] [60] [90]
| | | |
데이터 포인터
특징:
- 탐색: O(log N)
- 범위 쿼리 최적화 (WHERE age BETWEEN 20 AND 30)
- 정렬 유지 (ORDER BY 빠름)GIN (Generalized Inverted Index):
용도: JSONB, 배열, 전문 검색
예시: 태그 배열
행 1: tags = ['docker', 'kubernetes']
행 2: tags = ['docker', 'postgres']
행 3: tags = ['kubernetes', 'helm']
GIN 인덱스:
┌──────────────┬────────────┐
│ 태그 │ 행 ID │
├──────────────┼────────────┤
│ docker │ 1, 2 │
│ helm │ 3 │
│ kubernetes │ 1, 3 │
│ postgres │ 2 │
└──────────────┴────────────┘
쿼리: WHERE 'docker' = ANY(tags)
→ GIN에서 'docker' 찾기 → [1, 2] 행 반환 (즉시!)BRIN (Block Range Index):
용도: 초대용량 테이블, 시계열 데이터
개념: 블록 범위 요약
┌────────────────────────────────┐
│ Block 1-100: created_at │
│ Min: 2026-01-01 │
│ Max: 2026-01-05 │
├────────────────────────────────┤
│ Block 101-200: created_at │
│ Min: 2026-01-06 │
│ Max: 2026-01-10 │
└────────────────────────────────┘
쿼리: WHERE created_at = '2026-01-07'
→ Block 101-200만 스캔 (나머지 블록 skip!)
장점:
- 블록 범위마다 요약값만 저장하므로 B-Tree보다 훨씬 작음
- 인덱스 유지 비용이 매우 낮음
단점:
- 범위만 알려 주므로 해당 블록을 다시 읽어 걸러야 함
- 값이 물리적 저장 순서와 상관관계가 있을 때만 효과가 있음BRIN이 효과를 보려면 값의 순서와 행이 디스크에 놓인 순서가 비슷해야 합니다. 로그 테이블처럼 시간순으로 계속 INSERT만 하는 경우 created_at은 이 조건을 잘 만족하지만, UPDATE가 잦아 행이 여기저기 새 위치로 옮겨지거나, 과거 날짜 데이터를 나중에 몰아서 넣으면 블록마다 최솟값과 최댓값의 범위가 넓어져 거의 모든 블록을 읽게 됩니다. pg_stats의 correlation 값이 1이나 -1에 가까운지 확인해 보고 도입하는 것이 안전합니다. GiST는 그림에 없지만 좌표, 범위 타입(tsrange), 최근접 검색처럼 “겹침”이나 “거리”를 다루는 데 쓰이며, PostGIS의 공간 인덱스가 대표적인 예입니다.
PostgreSQL vs MySQL: 설계 철학의 근본적 차이
PostgreSQL:
- 기본 격리 수준: READ COMMITTED
- SERIALIZABLE: 잠금 대신 충돌 감지(SSI)로 구현, 충돌 시 재시도 필요
- 타입: 엄격 (text 값과 정수를 암묵적으로 섞지 않음)
- 확장성: 사용자 정의 타입·연산자·인덱스 방식, 풍부한 확장 생태계
- 옛 행 버전: 테이블 안에 보관 → VACUUM 필요MySQL (InnoDB):
- 기본 격리 수준: REPEATABLE READ
- 범위 조건 쓰기에 gap lock/next-key lock 사용
- 타입: 느슨한 편 (문자열과 숫자를 자동 변환, sql_mode에 따라 달라짐)
- 옛 행 버전: undo 로그에 보관 → purge 스레드가 정리구체적 차이:
-- PostgreSQL: 타입이 정해진 text 값은 숫자와 섞이지 않음
SELECT '1'::text + 1;
-- ERROR: operator does not exist: text + integer
-- (따옴표 리터럴 '1' + 1 자체는 타입 미정 리터럴이라 2로 계산됨)
-- MySQL: 문자열을 숫자로 자동 변환
SELECT '1' + 1; -- 2
SELECT 'abc' + 1; -- 1 (경고만 발생)
-- MySQL InnoDB (REPEATABLE READ): 범위 UPDATE 시 gap lock으로
-- 그 범위에 대한 다른 트랜잭션의 INSERT가 대기할 수 있음어느 쪽이 “더 정확하다”기보다는 기본값의 방향이 다릅니다. PostgreSQL의 엄격한 타입은 애플리케이션 버그를 DB 단계에서 에러로 드러내 주는 대신 ORM이나 쿼리에서 캐스팅을 명시해야 하는 경우가 많고, MySQL의 자동 변환은 편하지만 WHERE varchar_col = 123처럼 쓰면 인덱스를 못 타는 함정이 있습니다. 마이그레이션할 때 가장 많이 부딪히는 차이는 기본 격리 수준입니다. MySQL에서 REPEATABLE READ를 전제로 짠 “읽고 판단한 뒤 쓰는” 로직을 PostgreSQL의 READ COMMITTED로 옮기면, 같은 트랜잭션 안에서도 두 번 읽은 값이 달라질 수 있습니다.
이후 절에서 다루는 문제들
운영 중에 만나는 문제는 대개 세 가지 형태로 나타납니다. 특정 쿼리가 느려지는 경우(인덱스 전략과 쿼리 최적화 절), 테이블이 너무 커져 삭제와 VACUUM이 감당되지 않는 경우(파티셔닝 절), 장애나 실수에서 데이터를 되살려야 하는 경우(복제와 백업 절)입니다. 어느 해결책이 얼마나 효과가 있는지는 데이터 분포와 쿼리 패턴에 따라 크게 달라지므로, 각 절의 방법을 적용할 때는 전후를 EXPLAIN (ANALYZE, BUFFERS)와 pg_stat_statements로 직접 측정해 판단해야 합니다.
B-Tree와 GIN 인덱스 전략
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));복합 인덱스 (user_id, created_at)은 왼쪽부터 쓰입니다. WHERE user_id = 1 AND created_at > ...와 WHERE user_id = 1 ORDER BY created_at DESC에는 잘 맞지만, WHERE created_at > ...만 있는 쿼리에는 거의 도움이 되지 않습니다. 컬럼 순서는 “등호 조건 컬럼을 앞에, 범위나 정렬 컬럼을 뒤에” 두는 것이 기본입니다. 표현식 인덱스는 쿼리가 같은 표현식을 쓸 때만 사용되므로, LOWER(email)로 만들었다면 쿼리도 WHERE LOWER(email) = ...여야 합니다. 부분 인덱스도 마찬가지로 쿼리의 조건이 인덱스의 WHERE is_active = true를 포함해야 선택됩니다.
운영 중인 큰 테이블에 인덱스를 만들 때는 CREATE INDEX CONCURRENTLY를 써야 합니다. 일반 CREATE INDEX는 만드는 동안 테이블의 쓰기를 막기 때문입니다. CONCURRENTLY는 트랜잭션 블록 안에서 쓸 수 없고, 도중에 실패하면 INVALID 상태의 인덱스가 남으므로 \d 테이블명으로 확인해 지우고 다시 만들어야 합니다. 사용되지 않는 인덱스는 쓰기 비용만 늘리므로, pg_stat_user_indexes의 idx_scan이 계속 0인 인덱스는 정리 대상입니다.
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;jsonb에 대한 기본 GIN 인덱스(jsonb_ops)는 @>(포함), ?(키 존재) 연산자를 가속하지만 metadata->>'type' = 'click' 같은 비교에는 쓰이지 않습니다. 이런 쿼리는 WHERE metadata @> '{"type": "click"}'으로 바꾸거나, 특정 키만 자주 조회한다면 CREATE INDEX ON articles ((metadata->>'type')) 같은 표현식 B-Tree 인덱스가 더 작고 빠릅니다. 전문 검색 인덱스도 쿼리의 to_tsvector('english', title || ' ' || content)가 인덱스 정의와 글자 하나까지 같아야 사용됩니다. 매번 이 식을 반복하기 번거롭다면 PostgreSQL 12의 생성 컬럼(GENERATED ALWAYS AS (...) STORED)에 tsvector를 저장하고 그 컬럼에 인덱스를 거는 편이 실수가 적습니다. 참고로 'english' 구성은 영어 형태소 처리용이라, 한국어 문서에는 simple 구성이나 별도 형태소 분석 확장, pg_trgm 기반 검색을 검토해야 합니다.
EXPLAIN ANALYZE와 쿼리 최적화
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: 실제 실행 시간 (ms,시작..종료형태)
Seq Scan이 항상 나쁜 것은 아닙니다. 테이블의 상당 부분을 읽어야 하는 쿼리라면 인덱스를 따라 페이지를 여기저기 읽는 것보다 처음부터 순서대로 읽는 편이 빠르고, 플래너는 이를 계산해 일부러 순차 스캔을 고릅니다. 실행 계획에서 먼저 볼 것은 스캔 종류가 아니라 rows(추정)와 actual ... rows(실제)의 차이입니다. 추정이 10행인데 실제가 10만 행이라면 플래너가 잘못된 조인 방식(Nested Loop)을 골랐을 가능성이 높고, 대개 통계가 낡았거나 컬럼 간 상관관계를 모르기 때문입니다(아래 “느려졌을 때 보는 순서” 절 참고). EXPLAIN ANALYZE는 쿼리를 실제로 실행하므로, UPDATE나 DELETE를 분석할 때는 BEGIN; EXPLAIN ANALYZE ...; ROLLBACK;으로 감싸야 데이터가 바뀌지 않습니다.
쿼리 최적화 예제
-- ❌ 인덱스를 못 타는 쿼리
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2026;
-- created_at 인덱스가 있어도 함수를 씌운 값은 인덱스 키와 달라 Seq Scan
-- ✅ 인덱스를 타는 쿼리
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';
-- 컬럼을 그대로 두고 범위로 비교하면 Index Scan 가능created_at이 timestamptz라면 '2026-01-01'은 세션의 시간대 기준 자정으로 해석됩니다. 애플리케이션 서버와 DB 콘솔의 TimeZone 설정이 다르면 같은 쿼리가 몇 시간 어긋난 결과를 돌려주므로, 경계값에는 '2026-01-01 00:00:00+09'처럼 시간대를 명시하는 것이 안전합니다.
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;MATERIALIZED는 “더 빠르게”가 아니라 “최적화 경계를 세우라”는 지시입니다. PostgreSQL 11까지는 모든 CTE가 이렇게 따로 계산되어, 바깥 쿼리의 조건이 CTE 안으로 내려가지 못해 오히려 느린 경우가 많았습니다. PostgreSQL 12부터는 한 번만 참조되고 부수 효과가 없는 CTE를 서브쿼리처럼 인라인해서 바깥 조건과 함께 최적화합니다. 그래서 MATERIALIZED를 붙이면 이 최적화를 끄게 되고, 위 쿼리에 WHERE ro.order_count > 10을 추가해도 CTE 전체를 먼저 계산합니다. MATERIALIZED가 유리한 경우는 같은 CTE를 여러 번 참조하는 무거운 계산이거나, 플래너가 인라인 후 잘못된 계획을 고를 때 이를 강제로 막고 싶을 때입니다. 반대로 12 이전 버전에서 옮겨 온 쿼리의 동작을 그대로 유지하려면 MATERIALIZED, 인라인을 강제하려면 NOT MATERIALIZED를 명시합니다.
Range 파티셔닝과 pg_partman
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');
-- 부모에 만든 인덱스는 각 파티션에 자동 생성 (PostgreSQL 11+)
CREATE INDEX idx_events_user_id ON events(user_id);파티셔닝의 가장 큰 실무 이점은 조회 속도보다 오래된 데이터 삭제입니다. 1년 지난 이벤트를 DELETE로 지우면 수억 개의 죽은 행이 생기고 VACUUM이 그만큼 일해야 하지만, 월별 파티션이라면 DROP TABLE events_2025_01;이나 ALTER TABLE events DETACH PARTITION ...으로 한 번에 끝납니다. 대신 제약이 따릅니다. 파티션 테이블의 기본 키와 유니크 제약에는 파티션 키가 반드시 포함되어야 하므로, id만으로 PRIMARY KEY를 만들면 “unique constraint on partitioned table must include all partitioning columns” 에러가 납니다. PRIMARY KEY (id, created_at)처럼 만들어야 하고, 이는 id만으로는 전역 유일성이 보장되지 않는다는 뜻이기도 합니다. 또 어느 파티션에도 속하지 않는 값을 넣으면 no partition of relation "events" found for row 에러로 INSERT가 실패하므로, 미래 파티션을 미리 만들어 두거나 DEFAULT 파티션을 둬야 합니다.
자동 파티션 생성 (pg_partman)
-- pg_partman 확장 설치 (보통 전용 스키마에 설치)
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;
-- 자동 파티션 관리 (pg_partman 5.x 문법)
SELECT partman.create_parent(
p_parent_table := 'public.events',
p_control := 'created_at',
p_interval := '1 month',
p_premake := 3 -- 3개월 치를 미리 생성
);
-- 새 파티션 생성/오래된 파티션 정리는 주기적으로 실행해야 함
CALL partman.run_maintenance_proc();pg_partman은 4.x와 5.x 사이에 create_parent의 인자가 바뀌었습니다. 예전 자료에 있는 'native', 'monthly' 같은 인자는 5.x에서 더 이상 쓰이지 않아 함수 시그니처 불일치 에러가 나므로, 설치된 버전(SELECT extversion FROM pg_extension WHERE extname = 'pg_partman';)의 문서를 확인해야 합니다. 또 pg_partman은 파티션을 만들어 두기만 할 뿐, run_maintenance_proc()를 주기적으로 호출하지 않으면 미래 파티션이 추가되지 않습니다. pg_cron이나 pg_partman의 백그라운드 워커로 이 호출을 예약해 두지 않으면, 몇 달 뒤 미리 만든 파티션이 떨어지는 날 INSERT가 일제히 실패하는 사고가 납니다.
파티션 조회
-- 특정 월 데이터만 스캔 (빠름)
SELECT * FROM events
WHERE created_at >= '2026-03-01' AND created_at < '2026-04-01';
-- Scan only events_2026_03 partition스트리밍 복제와 논리 복제
스트리밍 복제 설정
Primary 서버 (postgresql.conf):
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GBReplica 서버 (postgresql.conf):
hot_standby = on복제 시작:
# Replica 서버에서
pg_basebackup -h primary-host -D /var/lib/postgresql/data -U replicator -P -v -R이 명령이 동작하려면 Primary 쪽에 두 가지가 먼저 준비돼 있어야 합니다. 복제용 계정에 REPLICATION 권한이 있어야 하고(CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD '...';), pg_hba.conf에 데이터베이스 이름 자리에 replication을 적은 줄이 있어야 합니다. 이 줄이 없으면 no pg_hba.conf entry for replication connection from host ... 에러가 납니다. -R 옵션은 Replica가 Primary에 접속할 정보를 postgresql.auto.conf에 쓰고 standby.signal 파일을 만들어 주므로, 이후 Replica를 시작하면 바로 복제를 받습니다.
wal_keep_size는 Replica가 잠시 끊겼을 때를 대비해 Primary가 WAL을 얼마나 남겨 둘지 정합니다. Replica가 이보다 오래 끊겨 있으면 필요한 WAL이 지워져 requested WAL segment ... has already been removed 에러와 함께 복제가 멈추고, Replica를 처음부터 다시 만들어야 합니다. 복제 슬롯(pg_create_physical_replication_slot)을 쓰면 Replica가 받아 가기 전까지 WAL을 지우지 않아 이 문제가 없지만, 반대로 Replica가 영영 돌아오지 않으면 Primary의 디스크가 WAL로 가득 찹니다. 슬롯을 쓴다면 max_slot_wal_keep_size로 상한을 두고 pg_replication_slots를 모니터링해야 합니다. 스트리밍 복제는 기본적으로 비동기라서 Primary가 갑자기 죽으면 Replica에 아직 도착하지 않은 최근 커밋이 사라질 수 있다는 점도 고가용성 설계에서 고려해야 합니다.
논리 복제 (Logical Replication)
-- 발행 서버 (wal_level = logical 필요)
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- 구독 서버 (같은 이름·구조의 테이블을 미리 만들어 둬야 함)
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=primary-host dbname=mydb user=replicator'
PUBLICATION my_pub;논리 복제는 WAL을 “어떤 테이블의 어떤 행이 어떻게 바뀌었다”는 변경 내용으로 풀어서 보내므로, 메이저 버전이 다른 서버 사이에도 복제할 수 있습니다. 그래서 무중단에 가까운 메이저 버전 업그레이드에 자주 쓰입니다. 대신 물리 복제와 달리 복제되지 않는 것이 있습니다. DDL(테이블 구조 변경)은 전달되지 않으므로 컬럼을 추가하려면 구독 쪽에 먼저 같은 변경을 적용해야 하고, 시퀀스 값도 복제되지 않아 구독 서버로 전환할 때 setval로 맞춰 주지 않으면 기본 키 충돌이 납니다. UPDATE와 DELETE를 복제하려면 테이블에 기본 키(또는 REPLICA IDENTITY)가 있어야 하며, 없으면 발행 서버에서 cannot update table "..." because it does not have a replica identity and publishes updates 에러로 쓰기 자체가 실패합니다.
pg_dump, pg_basebackup 백업 전략
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.dumppg_dump는 하나의 스냅샷으로 일관된 백업을 만들므로 백업 중에도 서비스가 계속 쓰기를 할 수 있습니다. -F c(custom 형식)로 받으면 압축되고, pg_restore -t로 특정 테이블만 골라 복원하거나 -j 4로 병렬 복원할 수 있어 평문 SQL보다 다루기 편합니다. 다만 pg_dump는 데이터베이스 하나만 백업하고 역할(role)과 테이블스페이스 같은 클러스터 전체 객체는 포함하지 않으므로, pg_dumpall --globals-only를 함께 받아 두어야 새 서버에서 소유자 역할이 없다는 에러 없이 복원됩니다. 데이터가 수백 GB를 넘어가면 덤프와 복원 모두 몇 시간 단위가 되므로 물리 백업으로 넘어가는 것이 일반적입니다.
pg_basebackup (물리 백업)
# 전체 물리 백업
pg_basebackup -h localhost -D /backup/pgdata -U postgres -P -v
# 연속 아카이빙 (WAL 아카이빙) - postgresql.conf
archive_mode = on
# 같은 이름 파일이 이미 있으면 실패하게 해서 덮어쓰기를 막음
archive_command = 'test ! -f /backup/wal/%f && cp %p /backup/wal/%f'물리 백업(pg_basebackup)과 WAL 아카이브를 함께 두면 특정 시점 복구(PITR)가 가능해집니다. 베이스 백업을 복원한 뒤 recovery_target_time = '2026-03-15 14:29:00+09'처럼 목표 시각을 정하고 아카이브된 WAL을 그 시각까지 재생하면, 누군가 실수로 DELETE를 실행하기 직전 상태로 되돌릴 수 있습니다. 복제는 실수로 지운 데이터도 즉시 Replica에 복제하므로 이런 사고에서는 백업을 대신하지 못합니다. archive_command가 실패하면 PostgreSQL은 WAL을 지우지 않고 계속 재시도하므로, 백업 디스크가 가득 차거나 권한이 틀어지면 이번에는 DB 서버의 디스크가 WAL로 가득 찹니다. pg_stat_archiver의 failed_count를 모니터링해야 하는 이유입니다. 실무에서는 cp 대신 압축, 원격 저장, 무결성 검사까지 처리하는 pgBackRest나 WAL-G 같은 도구를 쓰는 경우가 많고, PostgreSQL 17부터는 pg_basebackup --incremental로 변경된 블록만 받는 진짜 증분 백업도 지원합니다.
자동 백업 스크립트
#!/bin/bash
# backup.sh
set -euo pipefail # pg_dump가 실패하면 즉시 중단 (오래된 백업 삭제 방지)
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.shset -euo pipefail이 없던 원래 스크립트는 pg_dump가 인증 실패나 디스크 부족으로 실패해도 다음 줄로 넘어가 7일 지난 백업을 지우고 “백업 완료”를 출력합니다. 이 상태가 일주일 이어지면 쓸 수 있는 백업이 하나도 남지 않는데, 로그에는 매일 성공 메시지가 찍혀 있어 아무도 알아채지 못합니다. 백업 운영에서 가장 흔한 실패는 “백업은 매일 돌았는데 복원이 안 된다”는 것이므로, 정기적으로 백업 파일을 별도 서버에 실제로 복원해 보는 검증까지 일정에 넣어 두는 것이 좋습니다. cron은 로그인 셸의 환경 변수를 읽지 않으므로 pg_dump의 전체 경로와 인증 정보(~/.pgpass)가 cron 환경에서도 유효한지도 확인해야 합니다.
설정 튜닝과 VACUUM·ANALYZE
설정 최적화
# 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이 값들은 RAM 16GB 서버를 가정한 출발점이지 정답이 아닙니다. 가장 조심해야 할 것은 work_mem입니다. 이 값은 커넥션당이 아니라 정렬·해시 작업 하나당 할당되므로, 복잡한 쿼리 하나가 정렬과 해시 조인을 여러 개 쓰고 동시 커넥션이 100개라면 64MB × 작업 수 × 100까지 메모리를 쓸 수 있습니다. 전역 값은 보수적으로 두고, 큰 집계를 하는 배치 세션에서만 SET work_mem = '256MB'로 올리는 편이 안전합니다. 정렬이 work_mem을 넘으면 실행 계획에 Sort Method: external merge Disk: ...로 표시되므로 이 값을 올릴지 판단하는 근거가 됩니다. shared_buffers를 RAM의 25%로 두는 것은 PostgreSQL이 OS 페이지 캐시에도 크게 의존하기 때문이며, 그보다 크게 잡는다고 비례해서 빨라지지는 않습니다. random_page_cost = 1.1은 SSD에서 랜덤 읽기가 순차 읽기와 비용이 비슷하다는 것을 플래너에 알려 인덱스 스캔을 더 적극적으로 고르게 합니다.
VACUUM 및 ANALYZE
-- 통계 업데이트
ANALYZE users;
-- 죽은 행 정리 + 통계 갱신 (운영 중 실행 가능)
VACUUM (VERBOSE, ANALYZE) users;
-- 공간을 OS에 돌려줘야 할 때만, 점검 시간에 (테이블 전체 잠금)
-- VACUUM FULL users;
-- 자동 VACUUM 설정
ALTER TABLE users SET (
autovacuum_vacuum_scale_factor = 0.1,
autovacuum_analyze_scale_factor = 0.05
);대용량 로그 테이블 설계 예제
다음 SQL 쿼리를 실행합니다.
-- 파티션 테이블
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;느려졌을 때 보는 순서와 자주 만나는 원인
운영 중인 PostgreSQL이 느려지면 설정 파일부터 열고 싶어지지만, 원인의 대부분은 쿼리와 통계 쪽에 있습니다. 보는 순서를 정해 두면 헤매는 시간이 줄어듭니다. 먼저 pg_stat_statements로 총 실행 시간이 큰 쿼리를 추리고, 그 쿼리를 EXPLAIN (ANALYZE, BUFFERS)로 찍어 예상 행 수(rows)와 실제 행 수가 크게 어긋나는지, 버퍼를 얼마나 읽는지 확인합니다. 예상이 10배 이상 틀리면 통계가 낡았거나(ANALYZE), 컬럼 간 상관관계를 플래너가 모르는 경우라 CREATE STATISTICS로 확장 통계를 만들어 줍니다. shared_buffers나 work_mem 같은 설정은 이 단계를 거친 다음에 만져도 늦지 않습니다.
SELECT query, calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;
-- user_id와 region처럼 서로 연관된 컬럼의 결합 분포를 플래너에 알려 주기
CREATE STATISTICS orders_user_region (dependencies) ON user_id, region FROM orders;
ANALYZE orders;자주 겪는 사고 유형 하나는 오래 열린 트랜잭션이 VACUUM을 막는 경우입니다. 이벤트 로그처럼 쓰기가 몰리는 테이블에서, 인덱스는 잘 타는데 어느 날부터 조회가 몇 초씩 걸리기 시작합니다. EXPLAIN을 보면 실제로 읽는 행과 버퍼가 비정상적으로 많고, 원인을 따라가 보면 몇 시간째 열려 있는 배치 작업이나 idle in transaction 상태의 커넥션이 있습니다. MVCC에서 VACUUM은 가장 오래된 활성 트랜잭션보다 이후에 죽은 행만 정리할 수 있기 때문에, 트랜잭션 하나가 스냅샷을 붙잡고 있으면 그동안 쌓인 옛 버전 행이 전부 남아 테이블과 인덱스가 부풉니다(bloat). 이럴 때는 pg_stat_activity에서 오래된 트랜잭션을 찾아 정리하고, 배치는 짧은 트랜잭션 여러 개로 쪼개며, idle_in_transaction_session_timeout으로 방치된 세션을 자동으로 끊게 합니다. 쓰기가 몰리는 테이블은 위의 파티셔닝으로 나눠 두면 autovacuum이 작은 단위로 따라갈 수 있습니다.
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start LIMIT 5;커넥션 수도 함께 봐야 합니다. PostgreSQL은 커넥션마다 프로세스를 하나씩 띄우기 때문에, 서버리스 함수나 오토스케일되는 앱 서버가 커넥션을 수백 개씩 열면 메모리와 컨텍스트 스위칭 비용으로 전체가 느려집니다. 앱 앞단에 PgBouncer(트랜잭션 풀링 모드)를 두면 실제 DB 커넥션 수를 수십 개로 묶어 둘 수 있습니다. 다만 트랜잭션 풀링에서는 세션 단위 기능(세션 변수, 일부 prepared statement 사용 방식, advisory lock)이 기대대로 동작하지 않으니 앱 드라이버 설정을 함께 확인해야 합니다.
PostgreSQL 운영 요약
- 인덱스: B-Tree, GIN, GiST 등 상황별 선택
- 쿼리 최적화: EXPLAIN ANALYZE로 병목 지점 파악
- 파티셔닝: 대용량 테이블을 월/년 단위로 분할
- 복제: 스트리밍 복제로 고가용성 확보
- 백업: pg_dump + WAL 아카이빙
- 성능 튜닝: shared_buffers, work_mem 등 설정 최적화
프로덕션 체크리스트
- 적절한 인덱스 생성
- EXPLAIN ANALYZE로 쿼리 분석
- 파티셔닝 전략 수립 (필요 시)
- 복제 서버 구성
- 백업 자동화 스크립트
- 모니터링 설정 (pg_stat_statements)
- 정기 VACUUM 및 ANALYZE
같이 보면 좋은 글
자주 묻는 질문 (FAQ)
Q. 인덱스를 많이 만들면 성능이 나빠지나요?
A. 네, 인덱스는 INSERT/UPDATE/DELETE 성능을 저하시킵니다. 자주 조회하는 컬럼에만 인덱스를 만들고, 사용하지 않는 인덱스는 삭제하세요.
Q. 파티셔닝은 언제 사용하나요?
A. 테이블이 수억 건 이상이거나, 시계열 데이터로 오래된 데이터를 주기적으로 삭제해야 할 때 사용합니다.
Q. 복제 서버는 몇 대가 적절한가요?
A. 목적에 따라 다릅니다. 고가용성이 목적이라면 Primary 장애 시 승격할 Standby가 최소 1대 필요하고, 자동 페일오버 도구(Patroni 등)는 두 대가 동시에 Primary라고 주장하는 상황을 막기 위해 별도의 합의 저장소(etcd 등)를 요구합니다. 읽기 부하 분산이 목적이라면 대수는 읽기 트래픽으로 정하되, 비동기 복제라 Replica가 약간 늦을 수 있어 “방금 쓴 데이터를 바로 읽는” 요청은 Primary로 보내야 합니다.
Q. 백업은 얼마나 자주 해야 하나요?
A. “얼마나 잃어도 되는가(RPO)“와 “얼마나 빨리 복구해야 하는가(RTO)“로 정합니다. WAL 아카이빙을 켜 두면 마지막 아카이브 시점까지 복구할 수 있어 RPO가 분 단위로 줄고, 베이스 백업 주기는 복구 시 재생해야 할 WAL 양, 즉 복구 시간을 결정합니다. 주기보다 더 중요한 것은 복원 테스트를 정기적으로 해 보는 것입니다.