PostgreSQL 마이그레이션

핵심. 각 마이그레이션은 up/down 파일 쌍으로 구성되며, down은 up을 완전히 되돌립니다. 마이그레이션은 자체 트랜잭션 내에서 실행됩니다. 단 하나의 예외는 이미 데이터가 있고 쓰기가 발생하는 테이블의 인덱스입니다. 이 경우 CREATE INDEX CONCURRENTLY를 트랜잭션 없이 별도 마이그레이션 파일에 단일 명령어로 작성합니다.

확인 사항은 CHECKS.md에 있습니다.

파일 형식

migrations/
├── 20250524120000_create_users.up.sql
├── 20250524120000_create_users.down.sql
├── 20250525090000_add_users_phone.up.sql
└── 20250525090000_add_users_phone.down.sql

접두사는 UTC 시간 YYYYMMDDHHmmss이고, 이름은 snake_case로 간략한 설명을 씁니다.
*.up.sql은 마이그레이션을 적용하고, *.down.sql은 되돌립니다. 두 파일 모두 필수입니다.
이미 적용된 마이그레이션은 수정하지 말고 새로 작성하세요.

타입과 제약 조건

대신	사용	이유
`bigserial`	`bigint GENERATED ALWAYS AS IDENTITY`	SQL 표준; 자체 id를 삽입해야 하는 경우(예: 가져오기) `BY DEFAULT` 사용
`varchar(n)`	`text NOT NULL CHECK (length(x) <= n)`	제한은 데이터 규칙이며 변경하기 쉽습니다
`timestamp`	`timestamptz`	'벽시계 시간'이 아닌 시간 순간을 저장합니다
`integer` 플래그	`boolean`
`json`	`jsonb`	이진 형식, 인덱스 및 연산자 지원
확장의 uuid	`uuid DEFAULT gen_random_uuid()`	PostgreSQL 13부터 내장

텍스트 컬럼의 길이는 명백히 긴 텍스트(설명, 댓글)가 아니라면 항상 CHECK로 제한하세요. 최대 길이가 명확하지 않으면 사용자에게 물어보세요.

데이터	제약 조건
이메일	`CHECK (length(email) <= 320)`
이름	`CHECK (length(name) <= 255)`
전화번호	`CHECK (length(phone) <= 50)`
slug, 코드	`CHECK (length(code) <= 100)`
URL	`CHECK (length(url) <= 2048)`
설명	제한 없음 또는 `CHECK (length(bio) <= 10000)`

테이블 템플릿:

CREATE TABLE users (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text        NOT NULL CHECK (length(name) <= 255),
    email      text        NOT NULL CHECK (length(email) <= 320),
    is_active  boolean     NOT NULL DEFAULT true,
    metadata   jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX ix_users_email ON users (email);

되돌리기

up	down
`CREATE TABLE t`	`DROP TABLE IF EXISTS t`
`ALTER TABLE t ADD COLUMN c`	`ALTER TABLE t DROP COLUMN IF EXISTS c`
`CREATE INDEX ix`	`DROP INDEX IF EXISTS ix`
`CREATE INDEX CONCURRENTLY ix`	`DROP INDEX CONCURRENTLY IF EXISTS ix` — 역시 트랜잭션 없이
`CREATE TYPE t AS ENUM (...)`	`DROP TYPE IF EXISTS t`
`ALTER TYPE t ADD VALUE 'v'`	PostgreSQL에 `DROP VALUE` 없음 — 타입 재생성 또는 빈 down
`ALTER TABLE t ALTER COLUMN c SET NOT NULL`	`ALTER TABLE t ALTER COLUMN c DROP NOT NULL`
`ALTER TABLE t ADD CONSTRAINT c`	`ALTER TABLE t DROP CONSTRAINT IF EXISTS c`

down에 IF EXISTS를 사용하면 되돌리기가 멱등성을 가집니다.

일반적인 변경 사항

기본값이 있는 컬럼. 상수 DEFAULT를 사용하면 테이블이 다시 쓰이지 않습니다(PostgreSQL 11+). 명령은 즉시 실행됩니다. 변동 가능한 기본값(gen_random_uuid(), clock_timestamp())을 사용하면 테이블이 독점 잠금 하에 전체 다시 쓰여집니다. 대규모 라이브 테이블의 경우 기본값 없이 컬럼을 추가하고 배치로 채우세요.

ALTER TABLE users ADD COLUMN role text NOT NULL DEFAULT 'user' CHECK (length(role) <= 50);

외래 키 — 참조하는 컬럼에 인덱스를 즉시 생성하세요. PostgreSQL이 자동으로 생성하지 않으며, 인덱스가 없으면 부모 삭제와 조인이 전체 스캔으로 수행됩니다.

CREATE TABLE orders (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    user_id bigint NOT NULL REFERENCES users (id),
    total   numeric NOT NULL
);
CREATE INDEX ix_orders_user_id ON orders (user_id);

Enum 값. ALTER TYPE ... ADD VALUE는 트랜잭션 내에서 작동하지만, 새 값은 커밋 전까지 사용할 수 없습니다(55P04 unsafe use of new value). 마이그레이터가 트랜잭션 내에서 마이그레이션을 실행하는 경우, 값 추가와 그 사용(UPDATE, DEFAULT, CHECK)을 다른 마이그레이션에서 수행하세요.

ALTER TYPE payment_method ADD VALUE 'bank_transfer';

down: DROP VALUE가 없습니다. 값이 무해하면 주석과 함께 down을 비워 두세요. 그렇지 않으면 타입을 다시 생성하십시오(새 타입, ALTER COLUMN ... TYPE ... USING, 이전 타입 삭제).

라이브 테이블의 인덱스

테이블에 이미 데이터가 있고 쓰기가 발생하는 경우 — CREATE INDEX CONCURRENTLY(CIC)를 통해 인덱스를 생성하세요. 일반 CREATE INDEX는 빌드 전체 시간 동안 쓰기를 차단합니다. 새 테이블과 함께 동일 마이그레이션에서 생성하는 인덱스는 일반 CREATE INDEX를 사용합니다. 데이터베이스 크기는 문제를 막지 못합니다: 대기 중인 트랜잭션은 어떤 크기에서든 새 인덱스를 망가뜨립니다(아래 참조).

up:

CREATE INDEX CONCURRENTLY ix_orders_customer_id ON orders (customer_id);

down:

DROP INDEX CONCURRENTLY IF EXISTS ix_orders_customer_id;

트랜잭션 없이 별도 마이그레이션, 파일에 단일 명령어. 트랜잭션 블록 내에서 CIC는 금지됩니다("cannot run inside a transaction block"). 단일 요청으로 여러 명령어를 보내면 PostgreSQL이 암시적 트랜잭션으로 실행하여 CIC가 동일한 오류로 실패합니다.
마이그레이터가 트랜잭션 없는 마이그레이션을 지원하지 않으면 CIC를 일반 마이그레이션에 넣지 마세요. 사용자에게 이 모드가 필요하다고 알리거나(아래 '마이그레이터 요구사항' 섹션), CIC를 별도로 실행하고 마이그레이션을 수동으로 기록하세요.
CIC는 이전 스냅샷을 가진 모든 트랜잭션을 기다립니다. 다른 테이블을 읽는 트랜잭션도 포함됩니다. 마이그레이션이 오래 걸릴 수 있습니다.
적용 후 인덱스를 확인하세요. CIC는 트랜잭션이 아닙니다: 실패 시 INVALID 인덱스가 남고 마이그레이션 자체는 이를 표시하지 않습니다.

SELECT c.relname, i.indisvalid, i.indcheckxmin
FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'ix_orders_customer_id';

indisvalid = false — DROP INDEX CONCURRENTLY IF EXISTS 후 다시 생성하거나 REINDEX INDEX CONCURRENTLY를 실행하세요.
실패한 CIC 마이그레이션 재시도. IF NOT EXISTS 없이 재시도하면 INVALID 인덱스에 대해 "relation already exists" 오류가 발생합니다. IF NOT EXISTS를 사용하면 더 나쁩니다: 명령이 INVALID 인덱스를 조용히 건너뛰고 마이그레이션이 "성공"한 것으로 간주됩니다. 따라서 CIC에는 IF NOT EXISTS를 사용하지 마세요: 먼저 유효하지 않은 인덱스를 제거한 후 재시도하세요.

인덱스가 있지만 계획이 Seq Scan

인덱스는 유효하지만 옵티마이저가 이를 선택하지 않는 경우 — indcheckxmin을 확인하세요(위 쿼리). true인 경우: 인덱스 생성 전에 시작된 트랜잭션이 아직 살아 있는 한 쿼리가 인덱스를 사용하지 않습니다. 원인은 UPDATE: 인덱스가 참조하지 않는 HOT 버전 체인이 테이블에 남아 있습니다. INSERT만으로는 이런 현상이 발생하지 않습니다. 인덱스를 다시 만들거나 통계에서 원인을 찾지 마세요. 오래된 트랜잭션을 찾으세요:

SELECT pid, xact_start, backend_xmin, state, left(query, 60) AS query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;

완료될 때까지 기다리거나(pg_terminate_backend(pid)로 종료) — 사용자와 협의한 후에만 수행하세요. 릴리스 전에 재현하려면: 한 세션에서 repeatable read 트랜잭션을 열고 테이블을 읽고, 다른 세션에서 UPDATE 행과 CREATE INDEX를 실행하면 indcheckxmin이 true가 됩니다.

마이그레이터 요구사항

각 마이그레이션은 자체 트랜잭션이며, 적용 기록도 함께 기록합니다: 실패한 마이그레이션은 전체가 롤백되고 이미 적용된 것은 유지됩니다.
병렬 실행 방지(예: advisory lock): 함께 시작된 복제본이 동일한 작업을 두 번 적용하지 않도록 합니다.
별도 마이그레이션을 위한 트랜잭션 없는 모드 — 파일에 표시(예: 첫 줄 -- migrate:no-transaction). 이러한 파일은 트랜잭션 없이 단일 명령어로 전송됩니다. 대가: 적용과 기록이 더 이상 원자적이지 않습니다. 그 사이에 프로세스가 중단되면 재시도 시 "already exists" 오류가 발생합니다. indisvalid를 확인하고 유효한 인덱스가 있으면 마이그레이션을 수동으로 기록하세요.
데이터베이스 연결 없이 파일 쌍 생성.

scary-production/claude-plugins

claude-plugins란 무엇인가요?

즐겨 사용하는 AI에게 물어보기

문서