PostgreSQL 마이그레이션 테이블 락: ALTER TABLE별로 어떤 락을 잡는지, 그리고 안전하게 배포하는 방법

NULL을 허용하는 컬럼 하나를 추가하는 마이그레이션은 1밀리초도 안 걸려 끝납니다. 그래도 애플리케이션을 멈추게 할 수 있습니다. 실행하기 전에 락을 기다려야 하고, 기다리는 동안 그 테이블의 다른 모든 쿼리가 그 뒤에서 기다리기 때문입니다. 이 글은 세 개의 psql 세션에서 락 대기열이 생기는 모습을 보여 주고, 흔한 ALTER TABLE이 각각 어떤 락을 잡는지와 테이블을 다시 쓰는지를 정리한 뒤, 마이그레이션을 안전하게 만드는 패턴을 차례로 살펴봅니다. 재시도와 함께 쓰는 lock_timeout, NOT VALID 후 VALIDATE, 그리고 CREATE INDEX CONCURRENTLY입니다. 아래의 모든 문장과 출력은 Relyt One이 사용하는 메이저 버전인 PostgreSQL 17.11에서 실행했습니다(PostgreSQL 18에 대한 한 가지 내용만 18.6에서 확인했습니다).

네 가지만 기억한다면 다음과 같습니다.

1. 대부분의 ALTER TABLE 형태는 ACCESS EXCLUSIVE 락을 잡으며, 이 락은 쓰기뿐 아니라 읽기도 막습니다. 변경 자체는 즉시 끝나더라도 문장은 먼저 이 락을 얻기 위해 대기열에 섭니다.
2. 대기하는 동안 같은 테이블에 들어오는 새 쿼리는 그 뒤에 줄을 섭니다. 긴 트랜잭션 하나와 마이그레이션 하나만으로 그 테이블에 닿는 모든 요청이 멈출 수 있습니다.
3. 모든 마이그레이션에 lock_timeout을 지정해서 트래픽을 무기한 막는 대신 몇 초 뒤에 포기하게 하고, 다시 시도하세요.
4. 비용이 큰 검사를 락과 분리하세요. 제약 조건은 NOT VALID로 추가하고 나중에 VALIDATE하며, 인덱스는 CONCURRENTLY로 빌드하고, 큰 테이블을 다시 쓰는 문장은 피하세요.

이 글의 나머지 부분은 각 항목을 직접 재현할 수 있는 출력과 함께 보여 줍니다.

테이블은 두 개입니다. 계정 100만 개와 주문 200만 개입니다. 버려도 되는 데이터베이스에 psql로 접속해 실행하세요.

CREATE TABLE accounts (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  plan       varchar(20),
  score      integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO accounts (id, email, plan, score)
SELECT g, 'user' || g || '@example.com',
       (ARRAY['free', 'pro', 'team'])[1 + g % 3], g % 1000
FROM generate_series(1, 1000000) AS g;

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  total      numeric(10, 2) NOT NULL
);
INSERT INTO orders
SELECT g, 1 + g % 1000000, (g % 10000) / 100.0
FROM generate_series(1, 2000000) AS g;
ANALYZE;

psql 세션을 세 개 엽니다. 첫 번째 세션에서는 테이블을 읽고 열린 채로 남아 있는 트랜잭션을 시작합니다. 실제 시스템에서는 느린 리포트, 배치 작업, 또는 커밋을 잊은 애플리케이션이 idle in transaction 상태로 남겨 둔 연결이 이에 해당합니다.

-- 세션 A
SET application_name = 'a_report';
BEGIN;
SELECT count(*) FROM accounts;
-- 트랜잭션을 열어 둡니다

두 번째 세션에서는 마이그레이션을 실행합니다. NULL을 허용하는 컬럼 추가는 교과서적인 "안전한" 마이그레이션입니다.

-- 세션 B
SET application_name = 'b_migration';
ALTER TABLE accounts ADD COLUMN note text;

멈춰 버립니다. 세 번째 세션에서는 애플리케이션이 1분에 수천 번 실행하는 종류의 쿼리를 실행합니다.

-- 세션 C
SET application_name = 'c_app';
SELECT email FROM accounts WHERE id = 42;

이 쿼리도 멈춥니다. 한 행만 읽고, 세션 A도 읽기만 하는데도 그렇습니다. 네 번째 세션에서 누가 누구를 기다리는지 Postgres에 물어봅니다.

SELECT pid, application_name, state, wait_event_type AS wait_type,
       pg_blocking_pids(pid) AS blocked_by, query
FROM pg_stat_activity
WHERE application_name IN ('a_report', 'b_migration', 'c_app')
ORDER BY application_name;

 pid | application_name |        state        | wait_type | blocked_by |                   query
-----+------------------+---------------------+-----------+------------+--------------------------------------------
 478 | a_report         | idle in transaction | Client    | {}         | SELECT count(*) FROM accounts;
 488 | b_migration      | active              | Lock      | {478}      | ALTER TABLE accounts ADD COLUMN note text;
 495 | c_app            | active              | Lock      | {488}      | SELECT email FROM accounts WHERE id = 42;
(3 rows)

pid는 환경마다 다릅니다. 상황은 blocked_by 컬럼이 설명해 줍니다. C를 막고 있는 것은 A가 아닙니다. A는 ACCESS SHARE 락을 잡고 있고 C도 같은 모드를 원하므로 둘은 충돌하지 않습니다. C를 막고 있는 것은 B입니다. B는 모든 락과 충돌하는 ACCESS EXCLUSIVE를 원하므로 A를 기다립니다. 그리고 새 락 요청은 이미 부여된 요청뿐 아니라 이미 대기 중인 요청과 충돌해도 기다려야 합니다. 이 규칙은 읽기가 끊임없이 이어져 마이그레이션이 영원히 실행되지 못하는 일을 막기 위해 있지만, 그 부작용으로 대기 중인 ALTER TABLE은 테이블 앞에 벽이 됩니다.

저희가 실행했을 때 세션 A는 시작 후 8초 뒤에 커밋했고, B는 그 즉시 끝났으며, C의 한 행 조회는 6.2초가 걸렸습니다. 실제 트래픽에서는 그 시간 동안 accounts에 닿는 모든 요청이 쌓이고, 연결 풀이 바닥나며, 장애는 한 줄짜리 마이그레이션 때문에 일어난 것처럼 보입니다. 실제로 그렇지만, 마이그레이션이 느렸기 때문은 아닙니다.

lock_timeout은 락을 기다린 시간이 한도를 넘은 문장을 취소합니다. 같은 실험을 하되 세션 B에 2초를 줍니다.

-- 세션 B
SET application_name = 'b_migration';
SET lock_timeout = '2s';
ALTER TABLE accounts ADD COLUMN note text;

ERROR:  canceling statement due to lock timeout

이번에는 C가 최대 2초만 기다렸습니다(저희 실행에서는 6.2초 대신 1.8초). B가 한도에 도달해 대기열을 떠날 때까지만 기다렸기 때문입니다. 마이그레이션은 실패했고, 바로 그것이 핵심입니다. 테이블을 멈추게 하는 대신 적은 비용으로 실패한 것입니다. 이제 다시 시도하면 됩니다.

실제로 이 방법이 잘 작동하려면 두 가지가 필요합니다.

마이그레이션의 트랜잭션 안에서 SET LOCAL을 사용하세요. SET LOCAL은 현재 트랜잭션이 끝날 때까지만 유지되고 그 뒤 원래대로 돌아가므로, 그 연결에서 다음에 실행되는 작업으로 새어 나가지 않습니다.

BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

마이그레이션이 트랜잭션 모드의 풀러를 거친다면 이 점이 중요합니다. 트랜잭션마다 다른 서버 연결에 배정될 수 있으므로, 이전 트랜잭션에서 실행한 일반 SET이 ALTER TABLE에 적용된다는 보장이 없습니다. Relyt One의 풀링 연결 문자열은 트랜잭션 수준 풀링을 사용하는 PgBouncer를 거칩니다. 마이그레이션은 직접 연결 문자열로 실행하고, 그래도 설정은 트랜잭션 안에 두세요.

백오프를 두고 재시도하되, 락 타임아웃일 때만 재시도하세요. 많은 마이그레이션 도구가 재시도를 지원합니다. 사용하는 도구가 지원하지 않는다면 작은 래퍼로 충분합니다. 아래 스크립트는 마이그레이션이 락 대기를 포기했을 때만 재시도하고, 처음 성공하면 멈추며, 다섯 번 시도한 뒤에는 실패합니다. SQL 오타 같은 다른 오류는 즉시 실패합니다. 영어 오류 메시지를 비교하므로 마이그레이션 세션의 lc_messages는 영어로 유지하세요.

#!/usr/bin/env bash
# 사용법: ./migrate.sh migration.sql
# 마이그레이션이 락 대기를 포기했을 때만 재시도합니다.
set -u
for attempt in 1 2 3 4 5; do
  if output=$(psql "$DATABASE_URL" -X -v ON_ERROR_STOP=1 -f "$1" 2>&1); then
    echo "$output"
    exit 0
  fi
  echo "$output" >&2
  if ! grep -q "canceling statement due to lock timeout" <<<"$output"; then
    exit 1  # 그 밖의 오류: 재시도하지 말고 고치세요
  fi
  if [ "$attempt" -lt 5 ]; then
    echo "attempt $attempt hit the lock timeout; retrying in $((attempt * 5))s" >&2
    sleep $((attempt * 5))
  fi
done
echo "giving up after 5 attempts" >&2
exit 1

위의 외래 키 마이그레이션을 0042_orders_account_fk.sql로 저장하고, 다른 세션이 accounts의 한 행을 업데이트한 트랜잭션을 열어 둔 상태에서 래퍼를 실행했습니다. 첫 번째 시도는 타임아웃에 걸렸고, 그 트랜잭션이 커밋된 뒤 두 번째 시도가 성공했습니다.

BEGIN
SET
psql:0042_orders_account_fk.sql:5: ERROR:  canceling statement due to lock timeout
attempt 1 hit the lock timeout; retrying in 5s
BEGIN
SET
ALTER TABLE
COMMIT

파일 전체가 하나의 트랜잭션으로 감싸져 있으므로 실패한 시도는 완전히 롤백되고, 재시도는 깨끗한 상태에서 시작합니다. 이런 파일만 재시도하세요. CREATE INDEX CONCURRENTLY처럼 트랜잭션 안에서 실행할 수 없는 문장은 실패할 때 무언가를 남길 수 있으므로(아래에서 자세히 다룹니다), 다음 시도가 거기에 걸려 넘어집니다.

바쁜 테이블에서 마이그레이션을 하기 전에는 그것을 막을 트랜잭션을 미리 찾아보는 것도 좋습니다.

SELECT pid, state, now() - xact_start AS xact_age, left(query, 50) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY xact_start
LIMIT 5;

몇 분 동안 열려 있는 트랜잭션, 특히 idle in transaction 상태인 트랜잭션은 모든 시도를 타임아웃시킵니다. idle_in_transaction_session_timeout(기본값은 꺼짐)을 사용하면 이런 세션을 자동으로 종료합니다.

직접 측정할 수 있습니다. 문장을 트랜잭션 안에서 실행하고, 커밋하기 전에 pg_locks를 읽은 뒤 롤백합니다. Postgres가 테이블을 다시 썼는지 확인하려면 전후의 파일 노드를 비교합니다. 다시 쓰기는 테이블의 새 복사본을 새 파일에 씁니다.

CREATE VIEW my_locks AS
SELECT relation::regclass AS table_name, mode
FROM pg_locks
WHERE pid = pg_backend_pid()
  AND locktype = 'relation'
  AND relation IN ('accounts'::regclass, 'orders'::regclass)
ORDER BY 1, 2;

BEGIN;
SELECT pg_relation_filenode('accounts') AS before \gset
ALTER TABLE accounts ALTER COLUMN score TYPE bigint;
SELECT * FROM my_locks;
SELECT pg_relation_filenode('accounts') <> :before AS rewritten;
ROLLBACK;

 table_name |        mode
------------+---------------------
 accounts   | AccessExclusiveLock
 accounts   | ShareLock
(2 rows)

 rewritten
-----------
 t
(1 row)

(\gset은 결과를 변수에 저장하는 psql 명령입니다.) 아래 표의 모든 문장을 100만 행짜리 accounts 테이블과 200만 행짜리 orders 테이블에서 이 방식으로 실행했습니다. "막음" 컬럼은 각 락을 열린 트랜잭션에서 잡아 둔 상태로, 다른 세션에서 lock_timeout을 500 ms로 두고 한 행짜리 SELECT와 한 행짜리 UPDATE를 시도해서 확인했습니다. 나머지는 PostgreSQL 충돌 표의 락 모드에서 결과를 도출했습니다.

외래 키는 두 테이블에 락을 잡으므로 별도의 표로 정리했습니다. orders가 참조하는 테이블이고 accounts가 참조되는 테이블입니다.

시간은 로컬 컨테이너에서 측정했으며 테이블 크기와 하드웨어에 따라 달라지므로, 절대값이 아니라 상대값으로 읽으세요. 이 표에는 사람들이 놀라는 점이 몇 가지 있습니다.

• 여기서 컬럼을 추가, 삭제, 이름 변경 또는 타입 변경하는 모든 문장은 ACCESS EXCLUSIVE를 잡습니다. 즉시 끝나는 문장도 마찬가지입니다. "즉시"는 락을 얼마나 오래 잡는지를 말할 뿐, 락이 필요한지와는 관계가 없습니다. 이 문장들은 모두 락 대기열을 만들 수 있습니다.
• 기본값이 있다고 해서 다시 쓰기가 일어나는 것은 아닙니다. PostgreSQL 11부터는 volatile이 아닌 기본값을 카탈로그에 한 번만 저장하고, 기존 행은 읽힐 때 그 값을 가져옵니다. 여기서는 now()도 volatile이 아닌 것으로 취급됩니다. ALTER TABLE이 실행될 때 한 번 평가되며, 기존의 모든 행이 같은 타임스탬프를 갖게 됩니다. gen_random_uuid()나 clock_timestamp() 같은 volatile 기본값은 행마다 다른 값이 필요하므로 테이블을 다시 씁니다.
• varchar를 늘리는 것은 비용이 없지만 줄이는 것은 그렇지 않습니다. varchar(20)에서 varchar(50)이나 text로 바꾸면 카탈로그만 바뀝니다. varchar(10)으로 바꾸면 PostgreSQL 17에서 테이블을 다시 썼습니다.
• integer에서 bigint로의 변경은 항상 다시 쓰기를 일으킵니다. 값의 디스크상 크기가 바뀌기 때문입니다. 큰 테이블에서는 이것이 가장 계획이 필요한 마이그레이션입니다. 새 bigint 컬럼을 추가하고, 배치로 나누어 채운 뒤, 짧은 트랜잭션에서 전환하세요.
• 외래 키는 그것이 가리키는 테이블의 쓰기를 막습니다. 변경하는 테이블만이 아닙니다. orders에 외래 키를 추가하자 accounts의 UPDATE도 멈췄습니다.
• CREATE INDEX는 읽기는 허용하지만 쓰기는 막습니다. 빌드가 진행되는 내내 그렇습니다.

ADD CONSTRAINT는 보통 락을 잡은 채로 기존의 모든 행을 검사합니다. NOT VALID는 이 검사를 건너뜁니다. 제약 조건은 새로 추가되거나 업데이트되는 행에 즉시 적용되고, 기존 행은 나중에 훨씬 약한 락을 잡는 VALIDATE CONSTRAINT로 검사합니다. 위의 표에서 외래 키를 바로 추가하면 두 테이블의 쓰기가 1.8초 동안 막혔습니다. 두 단계로 나누자 쓰기를 막는 부분은 3 ms였고, 1.2초가 걸린 검증은 읽기와 쓰기를 모두 허용했습니다.

마이그레이션 1은 해결 1의 파일입니다. 거기서 이미 실행했다면 마이그레이션 2만 남았습니다.

-- 마이그레이션 1: 빠르지만 여전히 락이 필요하므로 lock_timeout을 유지합니다
BEGIN;
SET LOCAL lock_timeout = '2s';
ALTER TABLE orders
  ADD CONSTRAINT orders_account_fk
  FOREIGN KEY (account_id) REFERENCES accounts (id) NOT VALID;
COMMIT;

-- 마이그레이션 2: 읽기와 쓰기를 막지 않고 테이블을 스캔합니다
ALTER TABLE orders VALIDATE CONSTRAINT orders_account_fk;

VALIDATE가 제약 조건을 위반하는 행을 찾으면 실패하고, 제약 조건은 NOT VALID 상태로 남지만 새로운 쓰기에는 계속 적용됩니다. 데이터를 고친 뒤 다시 검증하세요.

PostgreSQL 17에서 SET NOT NULL에는 NOT VALID 형태가 없으며, ACCESS EXCLUSIVE를 잡은 채로 테이블 전체를 스캔합니다. 하지만 검증된 CHECK 제약 조건이 이미 그 컬럼에 NULL이 없음을 보장한다면 스캔을 건너뜁니다. client_min_messages = debug1로 이 판단을 확인할 수 있습니다.

ALTER TABLE accounts ADD CONSTRAINT plan_not_null CHECK (plan IS NOT NULL) NOT VALID;
ALTER TABLE accounts VALIDATE CONSTRAINT plan_not_null;

SET client_min_messages = debug1;
ALTER TABLE accounts ALTER COLUMN plan SET NOT NULL;
RESET client_min_messages;

ALTER TABLE accounts DROP CONSTRAINT plan_not_null;

DEBUG:  existing constraints on column "accounts.plan" are sufficient to prove that it does not contain nulls

CHECK 제약 조건이 없으면 같은 문장은 대신 DEBUG: verifying table "accounts"를 출력하며, 이것이 전체 스캔입니다. 컬럼이 NOT NULL이 되면 CHECK 제약 조건은 불필요하므로 삭제해도 됩니다.

PostgreSQL 18에서는 이 과정이 더 짧아집니다. ALTER TABLE accounts ADD CONSTRAINT plan_nn NOT NULL plan NOT VALID를 받아들이며, 이후의 VALIDATE CONSTRAINT가 SHARE UPDATE EXCLUSIVE를 잡는다는 것을 18.6에서 확인했습니다. PostgreSQL 17은 이 문장을 구문 오류로 거부하므로, Relyt One에서는 위의 CHECK 방식을 사용하세요.

CREATE INDEX CONCURRENTLY는 SHARE 대신 SHARE UPDATE EXCLUSIVE를 잡으므로 빌드하는 동안에도 쓰기가 계속됩니다. 그 대가로, 문서에 설명된 대로 테이블을 두 번 스캔하고 인덱스를 사용하거나 수정할 수 있는 기존 트랜잭션을 기다리므로, 작업량이 많고 시간이 더 걸립니다. 또한 고유한 규칙이 두 가지 있습니다.

트랜잭션 블록 안에서는 실행할 수 없습니다.

BEGIN;
CREATE INDEX CONCURRENTLY accounts_email_idx ON accounts (email);
ROLLBACK;

ERROR:  CREATE INDEX CONCURRENTLY cannot run inside a transaction block

많은 마이그레이션 도구가 각 파일을 트랜잭션으로 감싸므로, 사용하는 도구에서 마이그레이션 하나에 대해 이를 끄는 방법을 확인하세요. 이 문장도 여전히 락을 기다리므로 타임아웃을 지정하세요. SET LOCAL은 트랜잭션 밖에서는 아무 효과가 없으므로(Postgres는 WARNING: SET LOCAL can only be used in transaction blocks로 응답합니다), 직접 연결에서 바로 앞에 세션 수준의 SET lock_timeout = '2s';를 사용하세요.

그리고 실패하면 유효하지 않은 인덱스를 남깁니다. 중복 값이 있는 컬럼에 유니크 인덱스를 만들어 보면 쉽게 확인할 수 있습니다.

CREATE UNIQUE INDEX CONCURRENTLY accounts_plan_key ON accounts (plan);
SELECT indexrelid::regclass AS index_name, indisvalid
FROM pg_index WHERE indrelid = 'accounts'::regclass;

ERROR:  could not create unique index "accounts_plan_key"
DETAIL:  Key (plan)=(free) is duplicated.

    index_name     | indisvalid
-------------------+------------
 accounts_pkey     | t
 accounts_plan_key | f
(2 rows)

(DETAIL 줄의 중복 값은 실행할 때마다 다를 수 있습니다.) 유효하지 않은 인덱스는 쿼리에 절대 사용되지 않습니다. 빌드의 어느 단계에서 실패했는지에 따라 쓰기 작업이 여전히 그 인덱스를 유지해야 할 수도 있습니다(같은 카탈로그의 indisready를 확인하세요). lock_timeout으로 포기한 동시 빌드도 유효하지 않은 인덱스를 남깁니다. 오래 실행되는 쓰기 트랜잭션이 있는 테이블에서 실행했더니 타임아웃이 나고 indisvalid = f가 남았습니다. 원인이 무엇이든 DROP INDEX CONCURRENTLY accounts_plan_key;로 인덱스를 삭제하고 다시 시도하세요.

1. 위의 표에서 문장을 찾아보세요. ACCESS EXCLUSIVE나 쓰기를 막는 락(SHARE, SHARE ROW EXCLUSIVE)을 잡는다면, 아무리 빠르더라도 락 타임아웃이 필요합니다.
2. 마이그레이션의 트랜잭션을 SET LOCAL lock_timeout = '2s'(또는 애플리케이션이 멈춤으로 견딜 수 있는 시간)로 시작하고, 타임아웃이 나면 재시도하세요.
3. 시작하기 전에 pg_stat_activity에서 오래 실행 중인 세션과 idle in transaction 세션을 확인하세요.
4. 외래 키와 CHECK 제약 조건은 NOT VALID로 추가하고, 별도 단계에서 VALIDATE하세요.
5. PostgreSQL 17에서는 검증된 CHECK 제약 조건을 거쳐 NOT NULL을 추가하세요.
6. 인덱스는 트랜잭션 밖에서 세션 수준의 SET lock_timeout과 함께 CONCURRENTLY로 빌드하고 삭제하며, 끝난 뒤 indisvalid = false인 인덱스가 있는지 확인하세요.
7. 테이블을 다시 쓰는 작업(integer에서 bigint로, volatile 기본값, 타입 축소)은 마이그레이션이 아니라 하나의 프로젝트로 다루세요.
8. 마이그레이션은 트랜잭션 풀러가 아니라 직접 연결로 실행하세요.

위의 표는 문장이 어떤 락을 잡는지 알려 줍니다. 하지만 여러분의 데이터에서 스캔이나 다시 쓰기가 얼마나 걸리는지, 데이터가 VALIDATE 단계를 통과하는지는 알려 주지 못합니다. 그러려면 운영 환경 규모의 데이터가 필요하고, 보통은 그 복사본을 마련하는 것이 어려운 부분입니다.

Relyt One에서는 모든 브랜치가 전체 데이터를 가진 부모 브랜치의 copy-on-write 포크이며, 자체 컴퓨트를 가집니다. 운영 브랜치에서 브랜치를 만들고, 거기서 \timing on으로 마이그레이션 파일을 실행하고, 위의 파일 노드 확인으로 테이블을 다시 쓰는지 살펴보고, 실제로 실행하기 전에 VALIDATE가 기존 행에서 실패하는지 확인하세요. 그런 다음 브랜치를 삭제합니다. 운영 환경은 실험을 전혀 보지 못합니다.

브랜치로 리허설할 수 없는 것이 하나 있는데, 바로 락 대기열 자체입니다. 그곳에서는 다른 아무것도 실행되지 않기 때문입니다. 브랜치는 마이그레이션이 락을 얻은 뒤 얼마나 오래 잡는지를 알려 줍니다. 운영 환경에서 락을 기다리는 문제로부터 보호해 주는 것은 여전히 lock_timeout과 재시도입니다. 그리고 절대 시간이 아니라 상대 시간을 비교하세요. 브랜치의 컴퓨트는 운영 환경과 크기가 다를 수 있습니다.

무료로 시작하기