1초짜리 ALTER TABLE 이 서비스를 몇 분 세웠다

컬럼 하나 추가하는 마이그레이션을 돌렸다. 평소라면 1초면 끝나는 작업이다. 그런데 그날은 서비스 전체가 몇 분 동안 멈췄다.

더 이상한 건, 마이그레이션은 아직 시작도 못 했다는 점이다. ALTER TABLE 은 락을 기다리는 중이었고, 그동안 그 테이블을 읽는 평범한 SELECT 들이 줄줄이 멈춰 있었다.

배포 직후에 이런 일이 나면 다들 새 코드부터 의심한다. 롤백을 해도 안 풀린다. 롤백한 뒤에도 마이그레이션 세션은 여전히 락을 기다리고 있으니까.

락 큐를 들여다보기 전까지는 아무것도 설명이 안 된다. 그래서 이 장애는 원인을 못 찾은 채 "몇 분 뒤 저절로 회복됨"으로 회고에 적히는 일이 잦다.

SELECT 끼리는 안 부딪히는데 왜 멈출까

ALTER TABLE 은 ACCESS EXCLUSIVE 락을 원한다. 가장 강한 락이라 읽기까지 막는다. 앞에서 긴 조회 하나가 돌고 있으면 그게 끝날 때까지 기다린다. 여기까지는 누구나 예상하는 범위다.

진짜 문제는 그다음이다. PostgreSQL 의 락 큐는 선입선출이다. ALTER TABLE 이 큐에서 기다리기 시작하면 그 뒤에 오는 요청은 전부 ALTER TABLE 뒤에 줄을 선다. 서로 부딪힐 일 없는 평범한 SELECT 끼리도 마찬가지다. 앞사람이 못 지나가니 뒷사람도 못 지나간다.

그래서 이렇게 흘러간다. 리포트 쿼리 하나가 5분째 돌고 있다 → ALTER TABLE 이 그 뒤에서 기다린다 → 그 사이 들어온 조회 수백 개가 ALTER TABLE 뒤에 쌓인다 → 테이블이 사실상 5분 동안 접근 불가가 된다. 원인은 리포트 쿼리 하나인데 증상은 전면 장애다.

특히 고약한 조합은 유휴 트랜잭션이다. BEGIN 만 해 놓고 놀고 있는 세션이 그 테이블을 한 번이라도 건드렸다면, 트랜잭션이 끝날 때까지 락을 안 놓는다. 마이그레이션이 그런 세션을 만나면 하염없이 기다린다.

MySQL 도 이름만 다르고 구조는 같다. 메타데이터 락(MDL)이 같은 방식으로 큐를 막는다.

못 잡으면 차라리 실패하게 하기

마이그레이션 세션의 안전장치:

코드

· 못 잡으면 차라리 실패한다 — 이게 핵심이다
✓ SET lock_timeout = '2s';
✓ SET statement_timeout = '30s';

ALTER TABLE orders ADD COLUMN memo text;

· 실패하면 조용한 시간대에 재시도한다
· 짧은 타임아웃 + 재시도가
· 긴 대기 한 번보다 언제나 낫다

✕ statement_timeout 만 걸면 부족하다
✕ 락 대기는 lock_timeout 이 따로 관장한다

· 인덱스는 서비스를 멈추지 않고
✓ CREATE INDEX CONCURRENTLY idx_orders_memo ON orders(memo);
✓ -- 단, 트랜잭션 블록 안에서는 못 쓴다

✕ 문제 지점 · ✓ 해법 · 그대로 돌리기 전에 자기 환경에 맞게 고친다.

어떤 변경이 테이블을 다시 쓰나

무엇이 테이블을 통째로 다시 쓰는지 알고 있느냐가 실무에서 큰 차이를 만든다.

PostgreSQL 11 부터는 기본값이 있는 컬럼을 추가해도 테이블을 다시 쓰지 않는다. 기본값을 카탈로그에 적어 두고 읽을 때 채워 주는 방식이라, 1억 행 테이블이어도 바로 끝난다. 그전 버전에서는 전체 재작성이었다. 그러니 "컬럼 추가는 위험하다"는 통념은 버전에 따라 맞기도 하고 틀리기도 하다.

다만 기본값이 volatile 함수(예: now(), random())라면 여전히 다시 쓴다. 행마다 값이 달라야 하니 당연하다.

타입 변경은 대개 재작성이다. varchar(50) → varchar(100) 처럼 늘리는 건 다시 안 쓰지만, 줄이거나 타입 자체를 바꾸면 다시 쓴다. NOT NULL 추가도 전체를 훑어야 해서 오래 걸린다. 이건 CHECK 제약을 NOT VALID 로 먼저 붙이고 나중에 VALIDATE 하는 식으로 쪼갤 수 있다.

MySQL 은 ALGORITHM 으로 통제한다. INSTANT 는 메타데이터만 바꾸고, INPLACE 는 테이블을 재구성하되 대개 동시 DML 을 허용하고, COPY 는 전체를 복사한다. ALGORITHM=INSTANT 를 적어 두면 즉시 처리가 안 되는 작업일 때 아예 실패한다. 뜻하지 않은 긴 작업을 미리 막는 장치다.

헷갈리기 쉬운 게 하나 있다. 락을 빨리 잡았어도 재작성이 오래 걸리면 그동안 락을 계속 쥐고 있다. "락을 빨리 잡았다"와 "빨리 끝났다"는 다른 얘기다.

다음 마이그레이션 전에 걸어 둘 것

lock_timeout 은 선택이 아니다. 마이그레이션 세션에 반드시 건다. 2초 정도면 정상적인 짧은 트랜잭션은 통과하고, 긴 것에 걸리면 바로 실패한다. 그 실패는 나쁜 게 아니라 서비스를 지킨 거다.
짧은 타임아웃에 재시도를 붙이는 구조가 좋다. 실패하면 몇 초 뒤 다시 하고, 몇 번 실패하면 사람에게 알리는 식으로. 긴 대기 한 번보다 언제나 낫다.
마이그레이션 전에 오래 열린 트랜잭션이 있는지 봐야 한다. 배포 파이프라인에 "지금 5초 넘게 열린 트랜잭션이 있으면 멈춘다" 같은 게이트를 두는 것도 방법이다.
컬럼 추가와 인덱스 생성을 한 트랜잭션에 넣으면 락을 쥐는 시간이 합쳐진다. DDL 은 잘게 쪼개서 각각 따로 돌리자.
인덱스는 CREATE INDEX CONCURRENTLY 로 만든다. 시간은 더 걸려도 쓰기를 막지 않는다.
큰 테이블을 다시 써야 하는 변경이라면 온라인 도구가 답이다. pg_repack, pt-online-schema-change, gh-ost 가 그 용도다. 새 테이블을 만들어 복사하고 마지막에 이름만 바꾸는 방식이라 락 시간이 짧다.
기술로 다 못 막으면 남은 카드는 배포 시간대다. 트래픽이 낮은 때를 고른다.

pg_blocking_pids 로 범인 찾기

누가 막고 있고, 뒤에 몇이 섰나:

코드

· 막고 있는 세션을 바로 짚는다
SELECT pid, pg_blocking_pids(pid) AS blocked_by,
       state, wait_event_type,
       now() - xact_start AS xact_age,
       left(query, 60) AS q
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
✕ AND cardinality(pg_blocking_pids(pid)) > 0;

· 마이그레이션 전 게이트 — 긴 트랜잭션이 있으면 멈춘다
✓ SELECT count(*) FROM pg_stat_activity
✓ WHERE xact_start < now() - interval '5 seconds';

· 실패한 CONCURRENTLY 가 남아 있지 않은지
SELECT c.relname FROM pg_index i
  JOIN pg_class c ON c.oid = i.indexrelid
✕ WHERE NOT i.indisvalid;

핵심 한 줄DDL 은 락을 기다리는 동안 서비스를 인질로 잡는다.