변경 관리: DDL, 인덱스, 파라미터 변경의 위험도와 롤백 플랜

운영 중 변경이 가장 위험한 이유

새벽 두 시, ALTER TABLE orders ADD COLUMN status_code TINYINT 한 줄을 실행했다. 그런데 이 테이블에 미처 종료하지 못한 긴 트랜잭션이 있었다. ALTER는 Metadata Lock을 얻지 못하고 대기한다. 이후 들어오는 모든 SELECT와 INSERT도 그 뒤에서 줄을 선다. 수십 초가 지나면서 커넥션이 쌓이고, max_connections에 도달한다. 서비스 전체가 멈춘다.

데이터베이스 변경은 코드 배포와 달리 대부분 즉시 롤백이 불가능하고, 실행 중에 테이블 전체에 영향을 줄 수 있다. 그래서 변경의 위험도를 사전에 분류하고, 롤백 계획을 함께 작성하는 것이 운영의 기본이다.

변경 유형과 위험도 분류

DDL 위험의 핵심: Metadata Lock

MySQL Metadata Lock (MDL)

MySQL에서 DDL은 Metadata Lock(MDL)을 독점으로 획득해야 실행된다. 문제는 어떤 트랜잭션이 테이블을 열고 있으면 MDL을 줄 수 없다는 것이다.

트랜잭션 A: BEGIN; SELECT * FROM orders; (아직 COMMIT 안 함)
트랜잭션 B: ALTER TABLE orders ADD COLUMN ... ← MDL 대기
트랜잭션 C: SELECT * FROM orders; ← 트랜잭션 B 뒤에서 대기
트랜잭션 D: INSERT INTO orders ...; ← 역시 대기
...
커넥션이 쌓이고 max_connections 도달 → 서비스 중단

MDL 대기 상태 확인:

-- MySQL: MDL 대기 확인
SELECT r.trx_id             AS waiting_trx_id,
       r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query          AS waiting_query,
       b.trx_id             AS blocking_trx_id,
       b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query          AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- performance_schema에서 메타데이터 잠금 확인 (MySQL 5.7+)
SELECT object_type, object_schema, object_name,
       lock_type, lock_duration, lock_status,
       processlist_id
FROM performance_schema.metadata_locks
WHERE object_type = 'TABLE'
  AND object_schema = 'your_db'
  AND object_name   = 'orders';

PostgreSQL 잠금 레벨

PostgreSQL DDL은 트랜잭션 내에서 실행 가능하다는 MySQL과의 큰 차이점이 있다. 하지만 잠금 수준은 MySQL과 비슷하게 위험하다.

-- PostgreSQL: 대형 테이블 ALTER TABLE은 AccessExclusiveLock 획득
-- 기다리는 세션 확인
SELECT pid, state, wait_event_type, wait_event,
       left(query, 80) AS query
FROM pg_stat_activity
WHERE wait_event_type = 'Lock';

-- 잠금 보유 세션 확인
SELECT pid, usename, pg_blocking_pids(pid) AS blocked_by,
       query
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

MySQL Online DDL 전략

ALGORITHM 선택

MySQL InnoDB는 ALTER TABLE에 세 가지 알고리즘을 제공한다.

-- INSTANT (가장 안전, MySQL 8.0+)
ALTER TABLE orders
  ADD COLUMN status_code TINYINT,
  ALGORITHM=INSTANT;

-- INPLACE with LOCK=NONE (잠금 없이 인덱스 추가)
ALTER TABLE orders
  ADD INDEX idx_user_created (user_id, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 지원 여부 확인: 지원 안 되면 에러로 알 수 있음
-- ALGORITHM=COPY 강제 실행은 피한다

gh-ost: 트리거 없는 온라인 스키마 변경

대형 테이블에서 INPLACE가 불가능하거나 COPY가 너무 위험한 경우, GitHub에서 만든 gh-ost를 사용한다.

# gh-ost 실행 예시
gh-ost \
  --host=db-primary \
  --database=myapp \
  --table=orders \
  --alter="ADD COLUMN status_code TINYINT DEFAULT 0" \
  --chunk-size=1000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=80 \
  --ok-to-drop-table \
  --execute

--max-load와 --critical-load로 DB 부하에 따라 자동으로 속도를 조절하거나 중단한다. --execute 없이 실행하면 dry-run으로 검증만 한다.

PostgreSQL DDL 전략

트랜잭션 DDL의 장점

PostgreSQL은 DDL을 트랜잭션 안에 넣을 수 있다. 실패 시 자동 롤백된다.

-- DDL 롤백 테스트 (트랜잭션 내에서 검증 후 롤백)
BEGIN;
ALTER TABLE orders ADD COLUMN status_code SMALLINT;
-- 여기서 검증 쿼리 실행
SELECT column_name FROM information_schema.columns
WHERE table_name = 'orders' AND column_name = 'status_code';
ROLLBACK;  -- 개발·스테이징에서 안전하게 검증 후 COMMIT

CREATE INDEX CONCURRENTLY

일반 CREATE INDEX는 쓰기를 차단한다. CONCURRENTLY를 붙이면 백그라운드에서 인덱스를 구축하고 기존 트랜잭션이 끝날 때까지 기다린다.

-- 일반 (운영 중 절대 금지 — 대형 테이블)
CREATE INDEX idx_orders_user ON orders(user_id);   -- 쓰기 차단

-- 권장 (CONCURRENTLY)
CREATE INDEX CONCURRENTLY idx_orders_user
  ON orders(user_id);                              -- 쓰기 허용, 시간 더 걸림

주의: CONCURRENTLY가 실패하면 invalid 상태의 인덱스가 남는다. 반드시 확인하고 삭제해야 한다.

-- invalid 인덱스 확인
SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE indexname IN (
    SELECT indexrelid::regclass::text
    FROM pg_index WHERE NOT indisvalid
);

-- invalid 인덱스 삭제 (CONCURRENTLY로 안전하게)
DROP INDEX CONCURRENTLY idx_orders_user;

pg_repack: bloat 정리

대형 테이블의 bloat(죽은 공간)을 정리할 때 VACUUM FULL은 AccessExclusiveLock을 오래 잡는다. pg_repack은 잠금을 최소화하면서 테이블을 재구성한다.

# pg_repack으로 테이블 재구성 (잠금 없이 복사 후 교체)
pg_repack --host=db01 --dbname=myapp --table=orders

# 테이블스페이스 이동을 겸하는 경우
pg_repack --host=db01 --dbname=myapp --table=orders \
          --tablespace=fast_ssd

파라미터 변경 위험도

DB 파라미터 변경은 DDL보다 덜 주목받지만, 잘못된 변경은 재시작 없이도 즉각적인 장애를 낼 수 있다.

동적 파라미터 vs 정적 파라미터

위험한 동적 파라미터 변경 사례

-- MySQL: innodb_buffer_pool_size 동적 변경 (MySQL 5.7.5+)
-- 수 GB 크기 변경 시 수 초~수십 초 동안 백그라운드 작업 발생
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;

-- PostgreSQL: work_mem 전역 변경 주의
-- 연결당 해시/정렬에 work_mem이 여러 번 사용될 수 있음
-- 500 conn × 64MB work_mem × 5 연산 = 160 GB (메모리 폭발 가능)
ALTER SYSTEM SET work_mem = '16MB';  -- 적절한 값 설정
SELECT pg_reload_conf();             -- 재시작 없이 적용

-- 현재 값 확인 후 변경
SHOW work_mem;
SHOW shared_buffers;

파라미터 변경 안전 절차

1. 현재 값과 목표 값 기록
2. Replica 또는 스테이징에 먼저 적용
3. 적용 후 30분~1시간 모니터링 (CPU, 메모리, 에러율)
4. 이상 없으면 Primary에 적용
5. 재시작 필요 파라미터: maintenance window에서만 적용
6. 롤백: 이전 값으로 SET GLOBAL 또는 설정 파일 복원 후 재시작

롤백 플랜 작성 방법

변경 전에 롤백 SQL을 항상 함께 작성한다.

변경 계획서 템플릿

제목   : orders 테이블 status_code 컬럼 추가
변경   : ALTER TABLE orders ADD COLUMN status_code TINYINT DEFAULT 0
도구   : gh-ost (INSTANT 불가 확인 후 결정)
영향   : orders 테이블 쓰기 트래픽, 예상 소요 30분
롤백   : gh-ost --postpone-cut-over-flag-file 활용 → 교체 전 중단 가능
         교체 완료 후 롤백: ALTER TABLE orders DROP COLUMN status_code
검증   : DESCRIBE orders; 앱 배포 후 에러율, p99 응답시간 확인

MySQL DDL 롤백 시나리오

-- 상황 1: gh-ost 실행 중 롤백 (교체 전)
-- gh-ost는 --postpone-cut-over-flag-file 파일이 존재하면 교체를 연기함
-- 파일을 지우면 교체 진행 / 프로세스를 종료하면 고스트 테이블만 남고 원본 유지

-- 상황 2: INSTANT 완료 후 롤백 (컬럼 추가)
ALTER TABLE orders DROP COLUMN status_code, ALGORITHM=INSTANT;

-- 상황 3: 인덱스 추가 롤백
DROP INDEX idx_orders_status ON orders;

PostgreSQL DDL 롤백

-- 트랜잭션 DDL이라 COMMIT 전이면 자동 롤백
-- COMMIT 후 롤백
ALTER TABLE orders DROP COLUMN IF EXISTS status_code;

-- 인덱스 추가 롤백
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

대형 테이블 변경 체크리스트

변경 전 필수 확인 사항

• [ ] 테이블 크기 확인 (information_schema.tables / pg_total_relation_size)
• [ ] 현재 진행 중인 긴 트랜잭션 없는지 확인 (information_schema.innodb_trx / pg_stat_activity)
• [ ] 복제 지연 0인지 확인 (replica가 있을 경우)
• [ ] 롤백 SQL 작성 및 검증
• [ ] 실행 예상 시간 추정 (스테이징 환경에서 사전 측정)
• [ ] 모니터링 대시보드 준비 (에러율, p99, 연결 수)
• [ ] 야간 또는 트래픽 저점 시간대에 실행 계획

기억할 문장

DDL은 코드 배포가 아니다. 단 한 줄의 ALTER TABLE이 실서비스를 멈출 수 있고, 실행한 순간부터 COPY 방식은 롤백이 없다. 변경의 위험도를 분류하고, 올바른 도구(INSTANT / INPLACE / gh-ost / CONCURRENTLY)를 선택하고, 롤백 플랜을 미리 작성하는 것이 DBA의 기본 습관이다.

다음 장에서는 데이터베이스 사고 대응 런북(탐지, 완화, 원인분석, 재발방지)을 다룬다.

References

• Percona Blog, Online DDL Tools and Metadata Locks — https://www.percona.com/blog/online-ddl-tools-and-metadata-locks/
• GitHub Blog, gh-ost: GitHub's online schema migration tool for MySQL — https://github.blog/news-insights/company-news/gh-ost-github-s-online-migration-tool-for-mysql/
• OneUptime Blog, How to Handle Metadata Lock Issues During ALTER TABLE in MySQL — https://oneuptime.com/blog/post/2026-03-31-mysql-metadata-lock-issues-alter-table/view
• OneUptime Blog, What Is pt-online-schema-change for MySQL — https://oneuptime.com/blog/post/2026-03-31-mysql-pt-online-schema-change/view
• PingCAP Blog, MySQL Online DDL: Zero-Downtime Schema Changes — https://www.pingcap.com/blog/effective-online-ddl-database-schema-changes-zero-downtime/
• DCHost Blog, Zero-Downtime MySQL Schema Migrations with gh-ost and pt-osc — https://www.dchost.com/blog/en/zero-downtime-mysql-schema-migrations-the-blue-green-dance-with-gh-ost-and-pt-online-schema-change/
• Bytebase Blog, gh-ost vs pt-online-schema-change — https://www.bytebase.com/blog/gh-ost-vs-pt-online-schema-change/
• Citus Data Blog, When Postgres blocks: 7 tips for dealing with locks — https://www.citusdata.com/blog/2018/02/22/seven-tips-for-dealing-with-postgres-locks/
• pg_repack Documentation — https://reorg.github.io/pg_repack/
• PostgreSQL Documentation, Explicit Locking — https://www.postgresql.org/docs/current/explicit-locking.html
• Bytebase, How to ALTER large table in PostgreSQL — https://www.bytebase.com/reference/postgres/how-to/how-to-alter-large-table-postgres/
• DEV Community, The Complete Guide to PostgreSQL Lock Types for Schema Changes — https://dev.to/mickelsamuel/the-complete-guide-to-postgresql-lock-types-for-schema-changes-4b70