대용량 테이블 온라인 스키마 변경 — gh-ost, pt-online-schema-change, 네이티브 Online DDL

+-------------------------------------------------------------+
|  직접 ALTER TABLE의 위험 경로                                 |
|                                                             |
|   ALTER 시작                                                 |
|      |                                                      |
|      v                                                      |
|   [메타데이터/쓰기 락 획득]                                    |
|      |                                                      |
|      +--> 쓰기 트랜잭션 대기열에 적체                          |
|      |        |                                             |
|      |        v                                             |
|      |     커넥션 풀 고갈 --> 읽기까지 타임아웃                 |
|      |                                                      |
|      +--> 복제본에 동일 DDL 직렬 적용 --> 복제 지연 폭증        |
|      |                                                      |
|      +--> 대량 I/O/CPU --> 동일 서버 다른 쿼리 성능 저하        |
|                                                             |
|   => 한 테이블의 변경이 DB 전체 장애로 확산                     |
+-------------------------------------------------------------+

-- INSTANT: 8.0+, 컬럼 추가가 즉시 끝남 (메타데이터만 변경)
ALTER TABLE orders
  ADD COLUMN coupon_code VARCHAR(32) NULL,
  ALGORITHM=INSTANT;

-- INPLACE + LOCK=NONE: 테이블 복사 없이, 읽기/쓰기 허용하며 인덱스 생성
ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

+----------------------------------------------------------+
|  MySQL 네이티브 Online DDL 알고리즘 선택                    |
|                                                          |
|   변경 종류                  추천 알고리즘     테이블 복사  |
|   ----------------------     ------------     ----------  |
|   컬럼 추가(끝)               INSTANT          없음        |
|   컬럼 기본값 변경             INSTANT          없음        |
|   세컨더리 인덱스 추가         INPLACE          없음        |
|   컬럼 타입 변경               COPY             있음        |
|   PRIMARY KEY 변경            COPY             있음        |
|   문자셋 변경                 COPY             있음        |
+----------------------------------------------------------+

+--------------------------------------------------------------+
|  pt-online-schema-change 동작 (트리거 기반)                   |
|                                                              |
|   1) 새 테이블 _orders_new 생성 + 원하는 ALTER 적용            |
|                                                              |
|   2) 원본 orders 에 트리거 설치                                |
|        INSERT/UPDATE/DELETE --> _orders_new 로 전파           |
|                                                              |
|       애플리케이션 쓰기                                        |
|            |                                                  |
|            v                                                 |
|         [ orders ] --(트리거)--> [ _orders_new ]              |
|            ^                                                 |
|            |  3) 기존 행을 청크 단위로 복사                     |
|            +--- 백필: 1000행씩 INSERT ... SELECT             |
|                                                              |
|   4) 원자적 RENAME: orders <-> _orders_new 맞바꿈             |
|                                                              |
|   5) 트리거 제거 + 옛 테이블 _orders_old 삭제                  |
+--------------------------------------------------------------+

pt-online-schema-change \
  --alter "ADD COLUMN coupon_code VARCHAR(32) NULL" \
  --host=db-primary.internal \
  --user=migrator \
  --ask-pass \
  --chunk-size=1000 \
  --max-load "Threads_running=50" \
  --critical-load "Threads_running=120" \
  --max-lag=2 \
  --check-slave-lag=db-replica.internal \
  D=shop,t=orders \
  --execute

+--------------------------------------------------------------+
|  gh-ost 동작 (바이너리 로그 기반, 트리거 없음)                 |
|                                                              |
|   애플리케이션 쓰기                                            |
|        |                                                     |
|        v                                                     |
|    [ orders ] ---> MySQL binlog                              |
|        ^                |                                    |
|        |                v                                    |
|        |          gh-ost (복제본인 척 binlog 구독)            |
|        |                |                                    |
|        |        +-------+-------+                            |
|        |        |               |                            |
|        |   binlog 이벤트 적용   기존 행 백필 (청크)            |
|        |        |               |                            |
|        |        v               v                            |
|        |     [ _orders_gho 고스트 테이블 ]                    |
|        |                                                     |
|        +--- 4) 컷오버: orders <-> _orders_gho 맞바꿈          |
+--------------------------------------------------------------+

gh-ost \
  --host=db-primary.internal \
  --user=migrator \
  --password=secret \
  --database=shop \
  --table=orders \
  --alter="ADD COLUMN coupon_code VARCHAR(32) NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running=50" \
  --critical-load="Threads_running=120" \
  --max-lag-millis=1500 \
  --throttle-control-replicas="db-replica.internal" \
  --allow-on-master \
  --postpone-cut-over-flag-file=/tmp/ghost.postpone \
  --execute

# 실행 중 스로틀 강제 (백필 일시 정지)
echo "throttle" | nc -U /tmp/gh-ost.shop.orders.sock

# 스로틀 해제
echo "no-throttle" | nc -U /tmp/gh-ost.shop.orders.sock

# 청크 사이즈 실시간 변경
echo "chunk-size=200" | nc -U /tmp/gh-ost.shop.orders.sock

-- 쓰기를 막지 않고 인덱스 생성 (트랜잭션 블록 밖에서 실행)
CREATE INDEX CONCURRENTLY idx_orders_customer_created
  ON orders (customer_id, created_at);

-- 실패해 INVALID 상태로 남은 인덱스가 있는지 확인
SELECT indexrelid::regclass AS index_name
FROM pg_index
WHERE indisvalid = false;

-- INVALID 인덱스는 동일하게 CONCURRENTLY로 제거
DROP INDEX CONCURRENTLY idx_orders_customer_created;

-- 락을 3초 안에 못 얻으면 이 DDL은 실패시킨다
SET lock_timeout = '3s';

-- 11+ 에서 NOT NULL 없는 컬럼 추가는 기본적으로 즉시 (테이블 재작성 없음)
ALTER TABLE orders ADD COLUMN coupon_code VARCHAR(32);

SET lock_timeout = '3s';
-- DEFAULT 가 있는 컬럼 추가도 11+ 에서는 메타데이터만 변경되어 빠름
ALTER TABLE orders ADD COLUMN status SMALLINT NOT NULL DEFAULT 0;

-- 1단계: 기존 행 검증을 건너뛰고 제약 추가 (짧은 락)
ALTER TABLE order_items
  ADD CONSTRAINT fk_order
  FOREIGN KEY (order_id) REFERENCES orders (id)
  NOT VALID;

-- 2단계: 한가할 때 기존 행 검증 (쓰기를 막지 않는 약한 락)
ALTER TABLE order_items
  VALIDATE CONSTRAINT fk_order;

-- 잘못된 예: 수억 행을 한 트랜잭션으로 (절대 금지)
-- UPDATE orders SET coupon_code = 'NONE' WHERE coupon_code IS NULL;

-- 올바른 예: PK 범위로 청크를 끊어 반복
-- (애플리케이션/스크립트 루프에서 batch_start 를 1000씩 증가)
UPDATE orders
SET coupon_code = 'NONE'
WHERE id >= 1000000 AND id < 1001000
  AND coupon_code IS NULL;

// 의사 코드 수준의 청크 백필 루프 (복제 지연 감시 포함)
func backfill(db *sql.DB, maxID, batch int) error {
    for start := 0; start < maxID; start += batch {
        for replicationLagSeconds(db) > 2 {
            time.Sleep(500 * time.Millisecond) // 지연이 회복될 때까지 대기
        }
        _, err := db.Exec(
            "UPDATE orders SET coupon_code = 'NONE' "+
                "WHERE id >= ? AND id < ? AND coupon_code IS NULL",
            start, start+batch,
        )
        if err != nil {
            return err
        }
        time.Sleep(50 * time.Millisecond) // 청크 사이 짧은 휴식
    }
    return nil
}

+--------------------------------------------------------------+
|  컷오버 타임라인 (이상적인 경우)                               |
|                                                              |
|   백필 진행 ===============================>  (낮 시간, 스로틀) |
|                                            |                 |
|                                  [컷오버 대기, 플래그 파일]     |
|                                            |                 |
|   트래픽 적은 시간 -----------------------> [컷오버 실행]       |
|                                            |  짧은 락         |
|                                            v                 |
|                                  orders <-> ghost 맞바꿈      |
|                                            |                 |
|                                  옛 테이블 정리                |
+--------------------------------------------------------------+

+--------------------------------------------------------------+
|  확장-수축(expand-contract) 패턴                              |
|                                                              |
|   1) 확장: 새 컬럼 추가 (옛 컬럼 그대로 유지)                   |
|   2) 이중 쓰기: 앱이 옛+새 컬럼 둘 다 기록                      |
|   3) 백필: 기존 행의 새 컬럼을 청크로 채움                      |
|   4) 읽기 전환: 앱이 새 컬럼을 읽도록 배포                      |
|   5) 수축: 안정화 후 옛 컬럼 제거                              |
|                                                              |
|   => 각 단계가 독립 배포 + 단계별 롤백 가능                     |
+--------------------------------------------------------------+

[ ] 변경을 가벼운 변경(INSTANT/즉시)과 무거운 변경(재작성)으로 분류했는가
[ ] 가벼운 변경은 네이티브 DDL/마이그레이션 도구로 직접 처리 가능한가
[ ] 무거운 변경에 gh-ost 또는 pt-osc 중 무엇을 쓸지 정했는가
[ ] 원본 크기만큼의 추가 디스크 여유가 확보되었는가
[ ] 외래 키/트리거가 걸린 테이블인지 확인하고 옵션을 준비했는가
[ ] 스로틀 임계치(부하/복제 지연)를 보수적으로 설정했는가
[ ] 복제본 지연 감시 대상을 도구에 지정했는가
[ ] 컷오버를 트래픽 적은 시간대로 계획했는가(연기 플래그 활용)
[ ] 컷오버 직전 긴 트랜잭션/미커밋 트랜잭션이 없음을 확인했는가
[ ] 롤백 절차(컷오버 전/후)를 문서화했는가
[ ] 동일 규모 스테이징에서 리허설을 마쳤는가
[ ] PostgreSQL이라면 lock_timeout 설정과 INVALID 인덱스 점검 계획이 있는가
[ ] 확장-수축으로 애플리케이션 배포와 스키마 변경을 분리했는가