Schema Changes You Can Undo

Summary

Reversibility is not a property of code but a combination of code, data, and time — the same change can be undone if nobody has used it yet, and cannot be undone after a million rows have piled up.

Why this is a problem

This is why "there is a down script, so it is safe" is a dangerous thing to say. A down that reverses DROP COLUMN restores only the column structure and cannot restore the values that were in it. The schema goes back and the data does not.

So when you make a rollback plan, the question to ask is not "is there a script to undo it" but "what do we lose after undoing it." If you ask this question before deployment, you will usually choose to go in steps: expand → migrate → contract.

Reversibility is not a property of code

The accurate answer to "is this change rollback-able?" is this.

Reversibility is a combination of code, data, and time. The same code change is reversible if nobody has used it yet, and irreversible once a million rows have piled up.

You added a column. Can you undo it? If you deployed yesterday and nobody used it, yes. If a million rows filled that column with values over a week, a DROP throws those million rows away. The schema goes back but the data does not.

This is where the illusion that "there is a down script, so it is safe" comes from. A down that reverses DROP COLUMN restores only the column structure, not the values. That is why people say — a down migration is usually a lie.

A list of dangerous DDL

DDL	Why it is dangerous
Adding NOT NULL	A long lock while checking every row. Fails if existing NULLs are present
Renaming a column	Looks atomic, but is effectively a drop plus an add. Old-version code breaks immediately
Changing a type	Depending on the DBMS, rewrites the table. Takes very long for large tables
Creating an index	Locks or load on large tables. You need to check the online option
Bulk backfill UPDATE	A huge transaction → locks and replication lag. The read replica collapses
DROP COLUMN	Data disappears. Cannot be undone

You must be especially careful with bulk backfills. If you UPDATE 5 million rows in a single transaction, the transaction log explodes and replication lags. If your configuration sends read traffic to replicas, at this moment the lookup service shows stale data. So always split a backfill into batches.

-- 나쁜 예
UPDATE ORDERS SET DLVR_STS = '01' WHERE DLVR_STS IS NULL;

-- 좋은 예: 1000건씩, 사이에 잠깐 쉬면서
UPDATE ORDERS SET DLVR_STS = '01'
 WHERE ORD_NO IN (SELECT ORD_NO FROM ORDERS WHERE DLVR_STS IS NULL LIMIT 1000);
-- 영향 행이 0이 될 때까지 반복

Expand → migrate → contract (Expand / Migrate / Contract)

This is the standard pattern for reversible schema changes. The key rule: do not put two stages in a single deployment.

[1차 배포 — 확장]
  새 컬럼 추가 (NULL 허용, 기본값 있음)
  애플리케이션: 새 컬럼과 옛 컬럼에 모두 쓰고, 읽기는 옛 컬럼
  → 이 시점에서 롤백하면? 새 컬럼을 아무도 안 읽으니 안전

[2차 — 이관]
  기존 데이터 백필 (배치로 쪼개서)
  검증: 두 컬럼 값이 일치하는가

[3차 배포 — 전환]
  애플리케이션: 읽기를 새 컬럼으로
  → 롤백하면 옛 컬럼을 읽는데, 계속 써 왔으니 값이 있다. 안전

[4차 배포 — 축소]
  애플리케이션: 옛 컬럼 쓰기 중단
  충분한 관찰 기간 후 옛 컬럼 DROP
  → 이 시점에서야 비가역이 된다

It looks slow, but the whole point of this pattern is that every stage can be reversed. Renaming a column in one go takes 30 seconds, and if it goes wrong the service stops. This pattern takes 2 weeks, and at no point does anything stop.

The change management ledger

In an SI project, DDL is not something a developer fires off at will. You keep a change management ledger, and production changes go through approval.

Item	Why it is needed
Change ID / date	The unit of tracking
Target object	The starting point for understanding the impact scope
DDL script file	What will actually be executed
Rollback script file	No approval without it
Estimated duration	For estimating service downtime
Affected systems	Who must be notified among integration counterparts
Approver	Accountability

Requiring a rollback script is this ledger's core value. As you write the rollback, you realize "this cannot be undone" before deployment. That realization makes you change the design to expand-migrate-contract.

The order of schema changes and application deployment

Mistakes are frequent here too.

• Adding a column: DB first → application later. (Because the app must not use a column that does not exist)
• Dropping a column: application first → DB later. (Make the app stop using it, then drop it)

In one sentence: for additions the DB goes first; for removals the app goes first.

And during a rolling deployment, the old and new versions run at the same time. So the schema must always be in a state where both versions work. This is the fundamental reason expand-migrate-contract is necessary.

Blue-green does not solve the schema

A point you must make when discussing zero-downtime deployment strategies.

Compute is easy to replicate, but the database is usually shared. So blue-green makes application rollback a matter of seconds, but does not solve schema problems at all.

If blue and green look at the same DB, the schema must be compatible with both application versions. In the end it comes back to the same story.

Migration verification

Once applied, you must check. Look at it in 4 layers.

1. 스키마   컬럼·타입·제약·인덱스가 의도대로인가
2. 데이터   행 수 · 합계 · NULL 개수 · 체크섬이 보존됐는가
3. 성능     주요 쿼리의 실행계획과 응답시간이 나빠지지 않았는가
4. 앱       핵심 기능 스모크 테스트

The checksum in item 2 is especially useful. Row counts and totals alone cannot catch the situation where "one row went missing and another doubled."

-- 이관 전 기준선을 만들어 둔다
CREATE TABLE MIG_BASELINE AS
SELECT COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT,
       COUNT(DISTINCT CUST_ID) AS CUSTS FROM ORDERS;
-- 이관 후 같은 쿼리로 비교

The trick is to create a baseline before the work. If you find yourself asking "how many were there originally?" after the work, it is already too late.

What it looks like in the field

Schema changes turn into incidents mostly by two paths.

The first is locks. Adding NOT NULL locks the table while every row is checked, and all requests using that table wait in the meantime. What took 0.2 seconds on the development DB becomes several minutes on a production table with ten million rows, and those several minutes are indistinguishable from an outage.

The second is order. If you deploy the application first, it dies reading a column that does not exist yet, and if you change the schema first, the old application violates the new constraint. So "deploy both at the same time" is not a plan — the plan is to make things tolerate either order.

I also often see attempts to solve this with blue-green, but the fact that the two colors look at the same DB does not change. The application changes with zero downtime, but the schema is still a single copy.