Check a migration for locking risk

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

문장별 평가

중요한 점

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 경로로 처리하는 것이 안전합니다.