▎ IDENTITY 컬럼 재설계와 스키마 분리를 서비스 중단 없이 처리하는 Ghost Table 패턴
배경 — 왜 이 전략이 필요한가
운영 중인 items 테이블에 구조적 변경이 필요했다.
-
문제 1. 하나의 테이블에 성격이 다른 데이터(일반 아이템 / 가챠 아이템)가 혼재
-
문제 2. IDENTITY 컬럼 재설정, 신규 컬럼 추가, 제약조건 변경이 필요
-
문제 3. 해당 테이블은 게임 메인 BM 매출의 95% 이상을 담당하는 핵심 테이블 — 단 몇 분의 중단도 직접적인 매출 손실로 이어지므로 무중단이 절대 조건이었다
MSSQL Server에서 다음과 같은 작업들은 모두 테이블 전체 락(Sch-M Lock)을 유발한다.
┌────────────────────────────────┬────────────────┬───────────┐
│ 방법 │ 서비스 중단 │ 롤백 가능 │
├────────────────────────────────┼────────────────┼───────────┤
│ DROP → RECREATE │ ❌ 중단 발생 │ ❌ 불가 │
├────────────────────────────────┼────────────────┼───────────┤
│ ALTER TABLE (IDENTITY 변경) │ ❌ 전체 락 │ ❌ 어려움│
├────────────────────────────────┼────────────────┼───────────┤
│ ALTER COLUMN NOT NULL → NULL│ ❌ 전체 락 │ ❌ 어려움│
├────────────────────────────────┼────────────────┼───────────┤
│ Ghost Table 패턴 │ ✅ 중단 없음 │ ✅ 가능 │
└────────────────────────────────┴────────────────┴───────────┘특히 NOT NULL → NULL과 같은 제약조건 변경은 단순해 보이지만, SQL Server는 내부적으로 테이블 메타데이터 재구성을 위해 Sch-M Lock을 획득한다. 이 락은 모든 읽기/쓰기를 차단하므로 대용량 운영 테이블에서는 사실상 사용 불가다.
사전 작업 — 마이그레이션 전 준비
본 마이그레이션에 앞서 두 가지 선행 작업이 완료되어 있어야 했다.
-
신규 컬럼 추가 요청 반영
가챠 테이블 분리 시 attribute_01 ~ attribute_06 컬럼을 추가하는 요구사항이 있었다. Ghost Table 패턴 특성상 새 테이블 생성 시점에 원하는 스키마를 자유롭게 정의할 수 있어 이 요구사항을 자연스럽게 포함할 수 있었다.
-
정규화 코드 테이블 선행 적용
Step 3 후속에서 문자열 값을 코드로 치환하는 작업에 item_types, item_categories 테이블이 사용된다. 이 코드 테이블들은 레거시 이슈가 있어서 정규화 작업을 하고 난 뒤 메인 마이그레이션보다 먼저 생성되고 데이터가 정비되어 있어야 한다. 코드 테이블 누락 상태에서 치환
UPDATE를 실행하면 전체 데이터가 오염될 수 있다.
사전 작업 순서:
1. item_types 생성 및 데이터 입력
2. item_categories 생성 및 데이터 입력
3. 데이터 검증 완료
↓
본 마이그레이션 진행
전략의 핵심 아이디어
▎ 원본은 건드리지 않는다 → 복사본에서 작업한다 → 원자적으로 교체한다
원본 items 테이블은 마이그레이션 내내 살아있다. 새 테이블을 옆에서 조용히 준비한 뒤, sp_rename의 원자성을 이용해 순간적으로 교체한다.
단계별 실행 흐름
Step 1 — Ghost 테이블 생성 (IDENTITY 없이)
새 테이블을 IDENTITY 컬럼 없이 먼저 만든다. IDENTITY가 있으면 기존 id 값을 그대로 INSERT할 수 없기 때문이다. 이 시점에 원본 items 테이블은 정상 운영 중이다.
-- IDENTITY 없이 생성 → 기존 id 값을 자유롭게 INSERT 가능
CREATE TABLE [dbo].[items_new]
(
[id] [bigint] NOT NULL, -- IDENTITY(1,1) 없음
[type] [nvarchar](20) NOT NULL,
...
CONSTRAINT [PK_items_new] PRIMARY KEY CLUSTERED ([id] ASC)
)Step 2 — 데이터 이동 (타입 기준 분리)
원본 items에서 조건을 걸어 각 Ghost 테이블로 복사한다. WITH (NOLOCK)으로 읽기 잠금을 최소화하고, 데이터 변환도 이 시점에 함께 처리한다.
-- 유료 아이템 → items_new
INSERT INTO [dbo].[items_new] (id, [type], name, price, ...)
SELECT
id, [type], name,
TRY_CAST(price AS int) AS price, -- 타입 변환
...
FROM [dbo].[items] WITH (NOLOCK)
WHERE type = N'유료 아이템';
-- 가챠 아이템 → gacha_items (컬럼 구조가 다름)
INSERT INTO [dbo].[gacha_items] (id, [type], name, img, ...)
SELECT id, [type], name, img, ...
FROM [dbo].[items] WITH (NOLOCK)
WHERE type = N'트레져 체스트';실패해도 원본은 그대로이므로 안전하다.
Step 3 — IDENTITY 복원
데이터가 채워진 Ghost 테이블을 _bak으로 이름을 바꾸고, IDENTITY가 포함된 최종 테이블을 새로 만든 뒤 SET IDENTITY_INSERT ON으로 기존 id를 보존하며 다시 INSERT한다.
-- ① Ghost 테이블을 bak으로 보존 (원자적 이름 변경)
EXEC sp_rename 'items_new', 'items_new_bak'
ALTER TABLE [items_new_bak] DROP CONSTRAINT [PK_items_new]
-- ② IDENTITY 포함 최종 테이블 생성 (+ 신규 컬럼 attribute_01~06 추가)
CREATE TABLE [dbo].[items_new]
(
[id] [bigint] IDENTITY(1,1) NOT NULL,
...
)
-- ③ 기존 id 값 보존하며 INSERT
SET IDENTITY_INSERT [dbo].[items_new] ON
INSERT INTO [dbo].[items_new] (id, type, name, ...)
SELECT id, type, name, ...
FROM [dbo].[items_new_bak]
SET IDENTITY_INSERT [dbo].[items_new] OFF
-- ④ IDENTITY 시드를 현재 최댓값으로 재설정
DBCC CHECKIDENT ('items_new', RESEED)Step 4 후속 — 레거시 문자열 코드 치환
마지막으로 category, duration, usage 컬럼의 한글 문자열 값을 코드 테이블 기준으로 일괄 치환한다.
-- "지속시간" 같은 레거시 문자열 → 코드값으로 치환
UPDATE A
SET A.duration = B.code
FROM items_new AS A WITH (NOLOCK)
JOIN item_categories AS B WITH (NOLOCK) ON A.duration = B.name
WHERE B.code_parent = 'duration'전체 흐름 요약
[사전 작업]
item_types, item_categories 생성 및 검증
↓
[items] 원본 테이블 (서비스 중)
│
├─ Step 1: Ghost 테이블 생성 (IDENTITY 없음)
│
├─ Step 2: 데이터 복사 (NOLOCK, 타입 변환 포함)
│ items → items_new (아이템)
│ items → gacha_items (가챠)
│
└─ Step 3: IDENTITY 복원
sp_rename → _bak 으로 보존 ← 롤백 보험
CREATE 최종 테이블 (IDENTITY 포함, 신규 컬럼 포함)
IDENTITY_INSERT ON → INSERT from bak
DBCC CHECKIDENT (RESEED)
UPDATE: 문자열 → 코드 치환
(DROP bak은 검증 후 수동 실행)핵심 포인트 3가지
-
sp_rename의 원자성
카탈로그 메타데이터만 변경하므로 거의 즉각적이며 테이블 잠금이 최소화된다. Ghost 테이블이 준비된 순간 rename 한 번으로 교체가 완료된다.
-
SET IDENTITY_INSERT ON으로 id 보존
IDENTITY 컬럼이 있는 테이블에 id 값을 직접 지정해 INSERT할 수 있는 유일한 방법이다. 세션 단위로 동작하며, 작업 후 반드시 OFF로 되돌려야 한다.
-
DBCC CHECKIDENT (RESEED)
IDENTITY_INSERT로 기존 id를 삽입하면 IDENTITY 카운터가 갱신되지 않는다. 반드시 재설정해야 이후 INSERT에서 중복 키 오류가 발생하지 않는다.
롤백 전략
┌─────────────┬─────────────────────────────────────────────────────────────┐
│ 실패 시점 │ 롤백 방법 │
├─────────────┼─────────────────────────────────────────────────────────────┤
│ Step 2 실패 │ Ghost 테이블만 DROP. 원본 items 무결 │
├─────────────┼─────────────────────────────────────────────────────────────┤
│ Step 3 실패 │ items_new_bak을 items_new로 rename하면 Step 2 상태로 복원 │
└─────────────┴─────────────────────────────────────────────────────────────┘주의사항
DML 발생 구간
이 패턴은 복사 기간 동안 원본에 DML이 없거나 무시 가능할 때 안전하다. Step 2 ~ Step 3 사이에 원본에 INSERT/UPDATE가 발생하면 해당 변경분은 반영되지 않는다. 실시간 DML이 지속되는 환경에서는 점검 시간을 짧게 잡거나 CDC(Change Data Capture)와 병행해야 한다.
NOT NULL → NULL 제약조건 변경
단순해 보이는 ALTER COLUMN NOT NULL → NULL 변경도 SQL Server에서는 Sch-M Lock(스키마 수정 락)을 획득한다. 이 락은 해당 테이블의 모든 읽기/쓰기를 차단하며, 대용량 테이블에서는 수 분~수십 분이 소요될 수 있다. 매출 95% 이상을 담당하는 테이블에서 이 방법은 선택지가 될 수 없었고, Ghost Table 패턴으로 새 테이블 정의 시점에 원하는 제약조건을 처음부터 올바르게 지정하는 것으로 해결했다.
사전 작업 미완료 시 데이터 오염
Step 3 후속의 코드 치환 UPDATE는 item_types, item_categories가 정확히 준비된 상태를 전제한다.(운영 미리 적용) 코드 테이블에 누락된 값이 있으면 해당 행의 컬럼이 NULL로 치환되거나 JOIN 미매칭으로 업데이트가 누락된다. 반드시 사전 작업 검증 후 본 마이그레이션을 진행해야 한다.
