다운타임 없는 DB 마이그레이션 완전 가이드: PostgreSQL & MySQL(Online DDL, gh-ost/pt-osc, Expand→Migrate→Contract)

다운타임 없는 DB 마이그레이션 완전 가이드: PostgreSQL & MySQL(Online DDL, gh-ost/pt-osc, Expand→Migrate→Contract)

웹 서비스가 커질수록 스키마 변경은 필수가 됩니다. 문제는 잘못된 DDL 한 번으로 락이 길게 잡히거나 재인덱싱으로 I/O가 폭증해 다운타임을 유발한다는 점이죠. 이 글은 PostgreSQL·MySQL 운영 환경에서 무중단에 초점을 맞춘 Expand→Migrate→Contract 3단계 전략과, Online DDL, gh-ost/pt-osc 같은 도구 활용, 그리고 배포 파이프라인과의 연계를 실전 템플릿으로 정리합니다.

---

1) 원칙: Expand → Migrate → Contract (EMC)

• Expand: 서비스 중단 없이 추가만 수행(새 컬럼/테이블/인덱스). 기존 경로와 동시 호환.
• Migrate: 데이터 백필(backfill), 인덱스 전환, 타입 변경을 온라인으로 진행.
• Contract: 더 이상 쓰지 않는 컬럼·제약 제거(최종 청소). 이때도 짧은 트랜잭션 유지.

핵심은 새 코드가 구·신 스키마를 모두 처리하도록 먼저 배포하는 것입니다. 그래서 배포→데이터 이동→최종 정리 순서를 지키면 사용자 영향 없이 점진적으로 변화를 완료할 수 있습니다.

---

2) PostgreSQL: CONCURRENTLY & Lock 최소화

2-1) 인덱스 생성/교체

-- 읽기/쓰기 동시 트래픽 중에도 동작(단, 테이블 메타락은 짧게 잡힐 수 있음)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- 기존 인덱스 교체(동시 운영 후 제거)
DROP INDEX CONCURRENTLY IF EXISTS users_email_old;

2-2) 컬럼 추가와 백필

-- Expand: 널 허용 + 디폴트 없이 추가(쓰기 지연 방지)
ALTER TABLE orders ADD COLUMN note text;

-- Migrate: 배치 백필(트랜잭션/배치 크기 조절)
UPDATE orders
   SET note = concat('ord#', id)
 WHERE note IS NULL
 LIMIT 10000;  -- 도구/프로시저로 루프 돌리기

2-3) 타입 변경(중간 컬럼 경유)

-- Expand: 새 컬럼 준비
ALTER TABLE payments ADD COLUMN amount_cents bigint;

-- Migrate: 앱 코드가 amount_cents를 쓰도록 배포 후, 백필 작업
UPDATE payments SET amount_cents = (amount * 100)::bigint WHERE amount_cents IS NULL;

-- Contract: 구 컬럼 제거
ALTER TABLE payments DROP COLUMN amount;
ALTER TABLE payments RENAME COLUMN amount_cents TO amount;

주의: 대용량 테이블에서 ALTER TABLE ... SET NOT NULL은 전체 스캔을 유발할 수 있습니다. 운영에서는 CHECK (col IS NOT NULL) 제약을 먼저 추가하고, 데이터가 100% 채워지면 SET NOT NULL로 전환하세요.

---

3) MySQL: Online DDL & 대체 도구(gh-ost/pt-osc)

3-1) InnoDB Online DDL

엔진/버전/변경 유형에 따라 온라인 여부가 달라집니다(MySQL 5.7+/8.0 권장).

-- 인덱스 추가(온라인 가능)
ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE;

-- 컬럼 추가(제약적 온라인, 상황별 테스트 필수)
ALTER TABLE orders ADD COLUMN note VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

3-2) gh-ost (GitHub, 대규모 운영 적합)

트리거가 아닌 binlog를 활용해 온라인으로 그림자 테이블을 만든 뒤 스위치합니다. 쓰기 부하가 큰 환경에서도 안정적이라 선호됩니다.

# 예시: 컬럼 추가/변경이 포함된 테이블 리라이트
gh-ost \
  --user=dbuser --password=*** --host=db.example.com \
  --database=shop --table=orders \
  --alter="ADD COLUMN note varchar(255) NULL" \
  --allow-on-master \
  --cut-over=default \
  --exact-rowcount \
  --verbose \
  --execute

3-3) pt-online-schema-change (Percona 툴킷)

트리거 기반 그림자 테이블 방식. 부하/락 영향 평가가 관건입니다.

pt-online-schema-change \
  --alter "ADD COLUMN note varchar(255) NULL" \
  --execute \
  h=127.0.0.1,P=3306,u=dbuser,p=***,D=shop,t=orders

선택 가이드: MySQL 8.0에서 간단한 인덱스/컬럼 추가는 Online DDL로 충분합니다. 대규모 테이블 구조 변경이나 파티셔닝/PK 변경 등은 gh-ost 우선 고려 후, 조건에 따라 pt-osc 대체를 검토하세요.

---

4) 백필(Backfill) 전략: 가볍고, 재시도 가능하게

• Id 범위로 슬라이스: id BETWEEN a AND b 단위 배치.
• 속도 조절: 배치 간 SLEEP 또는 큐 처리로 부하 분산.
• 멱등성: 이미 처리된 행은 건너뛰는 조건을 둡니다.

# PostgreSQL: 병렬 백필(주의: 부하 감안)
DO $$
DECLARE
  step int := 5000;
  i bigint := 1;
  max_id bigint;
BEGIN
  SELECT max(id) INTO max_id FROM orders;
  WHILE i <= max_id LOOP
    UPDATE orders
       SET note = concat('ord#', id)
     WHERE id BETWEEN i AND i + step - 1
       AND note IS NULL;
    PERFORM pg_sleep(0.1);
    i := i + step;
  END LOOP;
END $$;

---

5) 애플리케이션 연계: 배포 순서와 피처 토글

1. Expand: 새 컬럼/인덱스 추가(온라인). 코드 호환 버전 배포(구/신 컬럼 모두 읽기).
2. Migrate: 백필 + 트래픽 관찰. 읽기 경로를 점진적으로 신 컬럼으로 전환(피처 토글).
3. Contract: 구 컬럼을 더 이상 읽지 않으면 제거 스크립트 실행.

Canary 배포(예: HAProxy/Nginx 가중치 전환)와 묶으면 위험을 작게 쪼갤 수 있습니다.

---

6) 트랜잭션/락/리소스 관리

• 긴 트랜잭션 금지: 백필은 짧은 배치 트랜잭션으로.
• 락 관찰:
  • PostgreSQL: pg_locks, pg_stat_activity, pg_stat_statements
  • MySQL: show processlist, performance_schema
• I/O 제한: maintenance_work_mem, work_mem 튜닝(PSQL), InnoDB 버퍼풀/redo flush 감시(MySQL).

-- PostgreSQL: 락/실행 관찰
SELECT pid, wait_event_type, wait_event, query
  FROM pg_stat_activity
 WHERE state <> 'idle'
 ORDER BY query_start ASC;

---

7) 롤백 플랜: 언제든 한 단계 전으로

• Expand 실패 → 변경 취소(온라인 인덱스/컬럼 제거는 보통 안전).
• Migrate 중 문제 → 토글로 읽기 경로 원복, 백필 중단, 다음 저점에 재시도.
• Contract 직전 → 구 컬럼을 아직 유지하고 있으므로 즉시 복구 가능.

변경 내역은 DDL 스크립트 + 마이그레이션 도구 버전으로 명확히 기록하세요.

---

8) 체크리스트(복붙용)

• 테이블/인덱스 크기, QPS, 슬로우 쿼리 베이스라인 확보
• Expand: 온라인 가능 여부 확인(CONCURRENTLY/LOCK=NONE)
• 코드: 구/신 스키마 동시 처리(널 허용, 기본값 지연)
• 백필: 배치/재시도/멱등성 보장 스크립트
• 관찰성: p95, 에러율, 락 대기, I/O 지표 대시보드
• 롤백: 피처 토글·가중치 전환 스크립트, Contract 연기 가능

---

9) 예제 시나리오: 이메일 중복 방지 & 검색 최적화

목표

users(email)에 유니크 + prefix 인덱스(MySQL) 또는 LOWER(email) 인덱스(PSQL)를 추가.

PostgreSQL

-- Expand
CREATE UNIQUE INDEX CONCURRENTLY ux_users_email_lower
  ON users (LOWER(email));

-- 코드 배포: 항상 LOWER(email) 비교
-- Migrate: 과거 데이터 정규화(소문자 변환)
UPDATE users SET email = LOWER(email) WHERE email <> LOWER(email);

-- Contract: 과거 보조 인덱스 제거
DROP INDEX CONCURRENTLY IF EXISTS idx_users_email_old;

MySQL

-- Expand (8.0): 함수 기반 인덱스
ALTER TABLE users
  ADD UNIQUE INDEX ux_users_email_lower ((lower(email)))
  , ALGORITHM=INPLACE, LOCK=NONE;

-- (구버전) prefix + 검증 트리거/애플리케이션 로직 병행 고려

---

10) 마무리

무중단 DB 마이그레이션의 본질은 변경을 쪼개고, 호환 가능한 코드를 먼저 배포하고, 데이터 이동은 배치로 처리하는 데 있습니다. PostgreSQL에서는 CONCURRENTLY와 짧은 트랜잭션, MySQL에서는 Online DDL과 gh-ost/pt-osc 조합이 강력한 무기가 됩니다. 이 글의 템플릿을 팀 표준으로 정리해 두면, 다음 스키마 변경은 두렵지 않습니다.