마이그레이션 잠금 위험 점검하는 프롬프트

전제: PostgreSQL 14, RDS, orders에 지속적으로 읽기/쓰기 발생. 정확한 시간은 인스턴스의 스토리지 성능과 트랜잭션 대기 상태에 따라 달라지므로 아래 시간은 보수적인 운영 추정치입니다.

문장별 평가

문장	락과 영향	테이블 크기 의존성	테이블 rewrite	12M행 기준 예상	온라인 실행
ADD COLUMN refund_status VARCHAR(20) NOT NULL DEFAULT 'NONE'	ACCESS EXCLUSIVE. 락을 보유하는 동안 읽기·쓰기 모두 차단	PostgreSQL 11+의 상수 기본값 fast path라 보통 상수 시간. 단, 락 대기는 무제한일 수 있음	PostgreSQL 14에서는 rewrite하지 않음	보통 수 ms~수 초. 대기 중인 트랜잭션이 있으면 훨씬 길어짐	가능하지만 짧은 락을 보장해야 함. lock_timeout 권장
ADD COLUMN refund_requested_at TIMESTAMP	ACCESS EXCLUSIVE. 짧게 읽기·쓰기 차단	상수 시간	하지 않음	보통 수 ms~수 초	가능
CREATE INDEX idx_orders_refund_status ...	일반 CREATE INDEX는 SHARE 락. 읽기는 허용하지만 INSERT/UPDATE/DELETE 차단	테이블 스캔·인덱스 생성 시간이 행 수와 데이터 크기에 비례	테이블 자체는 rewrite하지 않음. 새 인덱스를 생성	18GB 기준 대략 수 분~수십 분. RDS 부하·스토리지 성능에 따라 더 길 수 있음	현재 문장 그대로는 비온라인. CREATE INDEX CONCURRENTLY 사용
ALTER COLUMN user_phone TYPE VARCHAR(20)	ACCESS EXCLUSIVE. 읽기·쓰기 모두 차단	기존 타입과 데이터에 따라 다름. text/varchar 계열이라도 길이 검증 때문에 전체 스캔 가능	text/varchar 간 binary-coercible 변경은 보통 rewrite하지 않지만, 다른 타입이거나 USING 변환이 필요하면 rewrite 가능	전체 검증이 발생하면 수 분 이상. rewrite가 발생하면 18GB 기준 수 분~수십 분 이상	현재 문장 그대로는 위험. 사전 검증과 별도 배포 필요

중요한 점

ACCESS EXCLUSIVE 요청은 락을 즉시 얻지 못할 수 있습니다. 오래 열린 트랜잭션 하나가 있으면 DDL이 기다리고, 그 뒤에 들어온 쿼리까지 락 큐에서 지연될 수 있습니다. 따라서 “실행 자체는 빠른 DDL”도 운영 장애가 될 수 있습니다.

실행 전에는 다음을 확인하는 것이 좋습니다.

SELECT id, user_phone
FROM orders
WHERE user_phone IS NOT NULL
  AND char_length(user_phone) > 20
LIMIT 100;

현재 타입도 확인합니다.

SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'orders'
  AND column_name = 'user_phone';

권장 배포 순서

배포 1: nullable 컬럼만 추가

SET lock_timeout = '3s';
ALTER TABLE orders
  ADD COLUMN refund_status varchar(20);

ALTER TABLE orders
  ADD COLUMN refund_requested_at timestamp;

두 문장은 각각 별도 트랜잭션 또는 별도 실행으로 두는 편이 안전합니다.

애플리케이션은 다음을 먼저 배포합니다.

• 새 컬럼에 값을 쓸 수 있어야 함
• 조회 시 refund_status가 NULL인 기존 행을 일단 'NONE'으로 해석
• 신규 주문은 애플리케이션에서 명시적으로 'NONE' 기록
• 구버전 애플리케이션도 컬럼 추가로 깨지지 않는지 확인

롤백:

ALTER TABLE orders DROP COLUMN refund_requested_at;
ALTER TABLE orders DROP COLUMN refund_status;

단, 애플리케이션이 해당 컬럼을 읽거나 쓴 뒤에는 먼저 애플리케이션을 이전 버전 호환 상태로 되돌려야 합니다. 컬럼 삭제는 데이터가 사라지는 작업이므로 별도 승인 후 실행해야 합니다.

배포 2: 기존 데이터 배치 백필

한 번에 1,200만 행을 갱신하지 말고 기본키 범위 또는 ctid 기반으로 작은 배치로 처리합니다.

예:

UPDATE orders
SET refund_status = 'NONE'
WHERE id > :last_id
  AND id <= :next_id
  AND refund_status IS NULL;

배치 크기는 처음에는 1,000~10,000행 정도로 시작하고, 각 배치 사이에 짧은 간격을 둡니다. id가 없거나 적절한 인덱스가 없다면 별도 방식이 필요합니다.

롤백:

UPDATE orders
SET refund_status = NULL
WHERE ...;

하지만 애플리케이션이 이미 해당 값을 사용하거나 변경했다면 안전한 전체 롤백은 어렵습니다. 백필은 사실상 가역성이 제한되는 단계로, 실행 전에 명시해야 합니다.

배포 3: 기본값 추가

백필이 끝난 뒤:

SET lock_timeout = '3s';

ALTER TABLE orders
  ALTER COLUMN refund_status SET DEFAULT 'NONE';

이 작업은 짧은 ACCESS EXCLUSIVE 락만 필요합니다.

롤백:

ALTER TABLE orders
  ALTER COLUMN refund_status DROP DEFAULT;

이것은 기존 행의 값을 되돌리지 않습니다.

배포 4: NULL 방지

운영 중 신규 쓰기가 계속되므로 먼저 새 쓰기에 대해 애플리케이션이 항상 값을 기록하는지 확인합니다.

그다음 안전한 방식은 CHECK 제약을 사용하는 것입니다.

ALTER TABLE orders
  ADD CONSTRAINT orders_refund_status_not_null
  CHECK (refund_status IS NOT NULL) NOT VALID;

NOT VALID 추가 자체는 짧은 락입니다. 이후 별도로 검증합니다.

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_refund_status_not_null;

검증은 기존 테이블을 스캔하므로 테이블 크기에 비례하지만, 일반적으로 읽기·쓰기를 허용하는 SHARE UPDATE EXCLUSIVE 락을 사용합니다. 다만 다른 DDL과 충돌할 수 있고, 실행 중 CPU·I/O를 사용합니다.

PostgreSQL 14에서 SET NOT NULL을 꼭 사용해야 한다면, 검증된 제약을 활용할 수 있는지 사전 확인 후 별도 실행합니다.

SET lock_timeout = '3s';

ALTER TABLE orders
  ALTER COLUMN refund_status SET NOT NULL;

이 단계는 ACCESS EXCLUSIVE를 요구하므로 별도 배포로 두어야 합니다. 버전과 제약 형태에 따라 기존 검증을 재활용하지 못하고 다시 전체 스캔할 가능성이 있으므로, 운영에서는 검증된 CHECK 제약을 유지하는 방식도 고려할 수 있습니다.

롤백:

ALTER TABLE orders
  ALTER COLUMN refund_status DROP NOT NULL;

ALTER TABLE orders
  DROP CONSTRAINT orders_refund_status_not_null;

애플리케이션이 이미 NULL을 처리하지 않도록 변경됐다면 애플리케이션 롤백도 선행해야 합니다.

배포 5: 인덱스 생성

현재 문장 대신 다음을 사용합니다.

CREATE INDEX CONCURRENTLY idx_orders_refund_status
ON orders (refund_status);

CREATE INDEX CONCURRENTLY는 일반 인덱스 생성보다 오래 걸리고 테이블을 여러 번 스캔하지만, 일반적인 읽기·쓰기 작업을 차단하지 않습니다.

이 문장은 반드시 단독으로 실행해야 합니다.

• 트랜잭션 블록 안에서 실행할 수 없음
• 다른 DDL과 같은 트랜잭션에 넣지 않음
• 실패하면 INVALID 인덱스가 남을 수 있음

실패 시:

DROP INDEX CONCURRENTLY IF EXISTS idx_orders_refund_status;

그 후 원인을 해결하고 다시 생성합니다. 인덱스 생성 중에는 디스크 여유 공간, I/O, CPU, replication lag를 확인해야 합니다.

배포 6: user_phone 길이 제한

먼저 애플리케이션이 20자를 넘는 값을 새로 쓰지 않도록 배포합니다. 기존 데이터 검증이 통과하면:

ALTER TABLE orders
  ADD CONSTRAINT orders_user_phone_max_len
  CHECK (user_phone IS NULL OR char_length(user_phone) <= 20)
  NOT VALID;

별도 실행으로:

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_user_phone_max_len;

이 방식은 기존 컬럼 타입을 그대로 유지하면서 온라인으로 길이 제한을 검증할 수 있습니다. 반드시 타입 자체를 varchar(20)으로 바꿔야 한다면, 검증 완료 후 다음을 별도 실행합니다.

SET lock_timeout = '3s';

ALTER TABLE orders
  ALTER COLUMN user_phone TYPE varchar(20);

그래도 ACCESS EXCLUSIVE 락이 필요하므로 트래픽이 낮고 오래 열린 트랜잭션이 없는 시점에 실행해야 합니다.

롤백:

ALTER TABLE orders
  ALTER COLUMN user_phone TYPE <기존 타입>;

기존 타입이 무엇인지 확정해야 합니다. PostgreSQL의 타입 변경은 값을 자동으로 잘라내지 않으므로, 길이 초과 데이터가 있으면 실패합니다. 다만 애플리케이션이 이미 20자 제한에 의존하게 된 뒤에는 애플리케이션 롤백도 필요합니다.

반드시 별도 실행할 문장

다음은 다른 작업과 같은 트랜잭션에 넣지 않는 것이 좋습니다.

CREATE INDEX CONCURRENTLY ...

그리고 다음도 각각 별도 배포 또는 별도 트랜잭션으로 분리합니다.

• ALTER COLUMN user_phone TYPE ...
• ALTER COLUMN refund_status SET NOT NULL
• 대규모 백필 작업
• VALIDATE CONSTRAINT

특히 원래의 일반 CREATE INDEX는 쓰기를 차단하므로 사용하지 않는 것이 좋습니다.

실행 중 모니터링과 중단 조건

확인할 항목:

• pg_stat_activity: 대기 중인 DDL과 장기 트랜잭션
• pg_locks: ACCESS EXCLUSIVE 대기 여부
• CPU, I/O latency, storage queue
• RDS free storage
• replica lag
• 애플리케이션 p95/p99 latency와 오류율
• lock timeout 및 deadlock 발생 여부
• 백필의 초당 처리량과 WAL 증가량

중단 기준은 사전에 수치로 정해야 합니다. 예를 들면:

• DDL 락 대기 3초 초과
• API p99 latency가 평상시의 2배 이상으로 1~2분 지속
• DB CPU 80~90% 이상 지속
• 스토리지 latency 또는 queue 급증
• replica lag이 허용 범위를 초과
• deadlock, timeout, connection pool 고갈 발생

결론적으로 첫 두 개의 컬럼 추가는 PostgreSQL 14에서 비교적 안전하지만 락 대기를 관리해야 합니다. 가장 위험한 것은 일반 CREATE INDEX와 user_phone 타입 변경입니다. 인덱스는 CONCURRENTLY, 전화번호 길이는 CHECK NOT VALID → VALIDATE 경로로 처리하는 것이 안전합니다.