Safe idempotent database migrations ghItlh

staging Daq Qapla' migration production Daq Dung Qagh: schema old version pagh new version pagh, deploy roll forward laHbe', rollback undo pagh. migrations ghItlh (re-run reH safe - idempotent) "migration partially failed" incident pagh, non-event choH.

nuq Qagh

typical migration script:

ALTER TABLE users ADD COLUMN last_login_at TIMESTAMP;
CREATE INDEX idx_users_last_login ON users (last_login_at);
UPDATE users SET last_login_at = created_at WHERE last_login_at IS NULL;

index creation large table Daq timeout (pagh deploy process killed, connection drop) vaj column added, index missing, backfill Qu'be'. script top vo' re-run vaj wa'DIch line fail: column "last_login_at" already exists - 'ach script Qaw' Daq resume poQ.

statement Hoch re-run safe

schema-change statements law' IF EXISTS / IF NOT EXISTS form ghaj. Hoch lo'.

ALTER TABLE users ADD COLUMN IF NOT EXISTS last_login_at TIMESTAMP;
CREATE INDEX IF NOT EXISTS idx_users_last_login ON users (last_login_at);
UPDATE users SET last_login_at = created_at WHERE last_login_at IS NULL;

DaH script partial failure qaSDI' re-run laH. ADD COLUMN 'ej CREATE INDEX no-ops (already applied). UPDATE natural idempotent: WHERE last_login_at IS NULL rows still need neH touch - cha'logh run extra work pagh.

generalize: UPDATE condition "still needs this change" (pagh "apply this change") idempotent by construction. compare:

-- NOT idempotent: running this twice doubles every balance
UPDATE accounts SET balance = balance * 1.05;

-- idempotent: running this any number of times has the same effect
UPDATE accounts SET balance = base_balance * 1.05 WHERE balance != base_balance * 1.05;

wa'DIch action; cha'DIch target state. migration schema change neH pagh vaj target state describe.

drops same treatment poQ

DROP TABLE IF EXISTS legacy_sessions;
ALTER TABLE orders DROP COLUMN IF EXISTS legacy_status;

additive case symmetric - IF EXISTS pagh vaj migration (drop Qapla') re-run loud fail (statement already done).

backfills resumable (idempotent neH pagh)

one-shot UPDATE huge table lock whole duration, all-or-nothing. batch: failure few minutes lose (whole job pagh):

-- repeat until this returns 0 rows affected
UPDATE users
SET last_login_at = created_at
WHERE id IN (
  SELECT id FROM users
  WHERE last_login_at IS NULL
  LIMIT 1000
);

async function backfillLastLogin(db) {
  let updated;
  do {
    const result = await db.query(`
      UPDATE users SET last_login_at = created_at
      WHERE id IN (SELECT id FROM users WHERE last_login_at IS NULL LIMIT 1000)
    `);
    updated = result.rowCount;
  } while (updated > 0);
}

batch Hoch small transaction. batch 40 qaSDI' process Hegh vaj restart last_login_at IS NULL rows Daq resume. progress table poQ pagh - WHERE clause progress tracker.

safe part destructive part pIm

ADD COLUMN retry safe. DROP COLUMN data gone vaj reversible pagh. schema change ("rename status order_status") separate deploys:

1. new column chel, old vo' backfill, code both write deploy.
2. code new column neH read deploy.
3. later separate migration (step 2 clean Qu' confirm qaSDI') old column drop.

step 3 fail vaj irreplaceable pagh lose: old column retry vaD ghaj. wej Hoch wa' migration vaj partial failure data column rolled-back app code read pagh Daq write.

migration tools part Qu'

Rails ActiveRecord migrations, Django migration system metadata table Daq migrations ran track: migration command re-run vaj fully completed migration re-execute pagh. 'ach single migration file body Daq halfway fail protect pagh - SoH defensive ghItlh (IF EXISTS/target-state patterns).

yaj

migration statement Hoch "ensure this true" ghItlh ("do this action" pagh) vaj halfway fail migration re-run (2am Daq page pagh). cost: IF EXISTS 'ej WHERE conditions; payoff: partial failure special case pagh.