Antigravity で実装するゼロダウンタイム DB マイグレーション：Expand-Contract パターンと AI 駆動の安全策

-- ロックが取れなければ 3 秒で諦める。待ち行列を作らない
SET lock_timeout = '3s';
-- 文の実行時間にも上限をかける（バックフィル以外のDDL向け）
SET statement_timeout = '60s';

// scripts/run-ddl.ts
import { sql } from "kysely";
import { db } from "@/lib/db";
 
export async function runDdl(statement: string, maxAttempts = 8) {
  for (let attempt = 1; attempt <= maxAttempts; attempt++) {
    try {
      await db.transaction().execute(async (trx) => {
        await sql`SET LOCAL lock_timeout = '3s'`.execute(trx);
        await sql.raw(statement).execute(trx);
      });
      console.log(`OK (attempt ${attempt})`);
      return;
    } catch (err: any) {
      // 55P03 = lock_not_available
      if (err?.code !== "55P03") throw err;
      const wait = Math.min(1000 * 2 ** attempt, 30_000);
      console.warn(`lock busy, retry in ${wait}ms (attempt ${attempt})`);
      await new Promise((r) => setTimeout(r, wait));
    }
  }
  throw new Error("could not acquire lock within the retry budget");
}

-- migration_001_expand.sql
SET lock_timeout = '3s';
 
-- デフォルトを付けずに追加する（揮発性デフォルトは全行を書き直すため）
ALTER TABLE users ADD COLUMN id_new uuid;
 
-- デフォルトの設定はメタデータのみ。以後に挿入される行にだけ効く
ALTER TABLE users ALTER COLUMN id_new SET DEFAULT gen_random_uuid();

-- migration_002_index.sql（トランザクションなしで実行）
CREATE UNIQUE INDEX CONCURRENTLY users_id_new_key ON users (id_new);

SELECT c.relname, i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'users_id_new_key';
-- indisvalid が false なら DROP INDEX CONCURRENTLY して作り直す

// app/server/users/create.ts
import { randomUUID } from "node:crypto";
import { db } from "@/lib/db";
 
interface CreateUserInput {
  email: string;
  name: string;
}
 
export async function createUser(input: CreateUserInput) {
  // Phase 1: 旧 id は serial が採番。新しい id_new をアプリ側でも明示する
  const [user] = await db
    .insertInto("users")
    .values({
      email: input.email,
      name: input.name,
      id_new: randomUUID(),
    })
    .returning(["id", "id_new", "email", "name"])
    .execute();
 
  return user;
}

// scripts/backfill_user_uuid.ts
import { sql } from "kysely";
import { db } from "@/lib/db";
 
const CHUNK_SIZE = 1000;
const SLEEP_MS = 200;
 
async function backfillUuid(startFrom = 0) {
  let lastId = startFrom;
  let processed = 0;
 
  while (true) {
    const updated = await db
      .updateTable("users")
      .set({ id_new: sql<string>`gen_random_uuid()` })
      .where("id", "in",
        db.selectFrom("users")
          .select("id")
          .where("id", ">", lastId)
          .where("id_new", "is", null)
          .orderBy("id", "asc")
          .limit(CHUNK_SIZE)
      )
      .returning(["id"])
      .execute();
 
    if (updated.length === 0) break;
 
    lastId = Math.max(...updated.map((u) => Number(u.id)));
    processed += updated.length;
    console.log(`processed=${processed} lastId=${lastId}`);
 
    await new Promise((r) => setTimeout(r, SLEEP_MS));
  }
}
 
backfillUuid(Number(process.argv[2] ?? 0)).catch((err) => {
  console.error("backfill failed:", err);
  process.exit(1);
});

SELECT count(*) AS remaining FROM users WHERE id_new IS NULL;
-- 0 になるまで。Phase 1 のデプロイ後に挿入された行は、すでに埋まっています

// app/server/users/get.ts
import { db } from "@/lib/db";
import { isFeatureEnabled } from "@/lib/feature-flags";
 
export async function getUserById(idOrUuid: string | number) {
  const useUuid = await isFeatureEnabled("users.read.use_uuid_id");
 
  if (useUuid && typeof idOrUuid === "string") {
    return db.selectFrom("users").selectAll()
      .where("id_new", "=", idOrUuid)
      .executeTakeFirst();
  }
 
  const numericId = typeof idOrUuid === "string" ? Number(idOrUuid) : idOrUuid;
  return db.selectFrom("users").selectAll()
    .where("id", "=", numericId)
    .executeTakeFirst();
}

-- 新旧の両方が埋まっていて、重複もない状態を確認
SELECT
  count(*)                       AS total,
  count(*) FILTER (WHERE id_new IS NULL) AS missing_new,
  count(*) - count(DISTINCT id_new)      AS duplicated_new
FROM users;

-- migration_003_notnull.sql
SET lock_timeout = '3s';
 
-- まず NOT VALID で追加（既存行を見ないので瞬時）
ALTER TABLE users
  ADD CONSTRAINT users_id_new_not_null CHECK (id_new IS NOT NULL) NOT VALID;
 
-- 別の文で検証（読み書きを止めずに全行を走査）
ALTER TABLE users VALIDATE CONSTRAINT users_id_new_not_null;
 
-- 検証済み CHECK があれば、SET NOT NULL は全行走査を省略する（PostgreSQL 12 以降）
ALTER TABLE users ALTER COLUMN id_new SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_id_new_not_null;

-- migration_004_swap_pk.sql
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE users DROP CONSTRAINT users_pkey;
ALTER TABLE users
  ADD CONSTRAINT users_pkey PRIMARY KEY USING INDEX users_id_new_key;
COMMIT;

-- migration_005_rename.sql
SET lock_timeout = '3s';
BEGIN;
ALTER TABLE users RENAME COLUMN id TO id_old_deprecated;
ALTER TABLE users RENAME COLUMN id_new TO id;
COMMIT;

-- migration_006_drop.sql（1〜2 週間の観察後）
SET lock_timeout = '3s';
ALTER TABLE users DROP COLUMN id_old_deprecated;

SELECT pid, now() - xact_start AS duration, state, left(query, 80) AS query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY duration DESC
LIMIT 10;