ダウンタイム無しの Postgres スキーママイグレーション

-- ✅ 高速 (Postgres 11+)
-- テーブル書き換え無し、ロック時間は最小
ALTER TABLE users ADD COLUMN status text DEFAULT 'active';

-- ❌ 低速 (テーブル書き換えが必要)
-- 定数ではないデフォルト値が必要なときだけ使う
ALTER TABLE users ADD COLUMN created_at timestamp DEFAULT now();

-- ステップ 1: 既存データを検証せずに制約を追加
-- 高速 — カタログ更新のみで一瞬しかブロックしない
ALTER TABLE users ADD CONSTRAINT users_age_positive
  CHECK (age > 0) NOT VALID;

-- ステップ 2: 既存データを検証 (時間はかかるが書き込みをブロックしない)
-- SHARE UPDATE EXCLUSIVE ロックを使うため読み書きは可能
ALTER TABLE users VALIDATE CONSTRAINT users_age_positive;

-- ステップ 1: 検証無しで外部キーを追加
-- 高速 — カタログ更新のみ、既存データは検証しない
ALTER TABLE orders ADD CONSTRAINT orders_user_id_fk
  FOREIGN KEY (user_id) REFERENCES users(id) NOT VALID;

-- ステップ 2: 既存リレーションを検証
-- 時間はかかるが並行する操作を許可する
ALTER TABLE orders VALIDATE CONSTRAINT orders_user_id_fk;

-- ステップ 1: CHECK 制約を追加 (NOT VALID で高速)
ALTER TABLE users ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- ステップ 2: 制約を検証 (並行する操作を許可)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- ステップ 3: NOT NULL を設定 (制約が NULL 無しを保証するので高速)
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- ステップ 4: 冗長になった CHECK 制約を削除
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

-- Postgres 18+ — 簡略化されたアプローチ
ALTER TABLE users ALTER COLUMN email SET NOT NULL NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- ❌ 索引作成中、すべての書き込みをブロックする
CREATE INDEX idx_users_email ON users(email);

-- ✅ 索引作成中も書き込みを続行できる
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- セッション 1: 長時間クエリ (ACCESS SHARE を保持)
SELECT COUNT(*) FROM large_table; -- 5 分かかる

-- セッション 2: ACCESS EXCLUSIVE を待つ DDL
ALTER TABLE large_table ALTER COLUMN description TYPE text; -- SELECT の後ろで待つ

-- セッション 3: 新しいアプリケーションクエリ
SELECT * FROM large_table WHERE id = 123; -- 待ち状態の DDL によりブロック！

-- オプション 1: セッションレベル (一時的)
SET lock_timeout = '5s';

-- オプション 2: ロールレベル (推奨 — セッションをまたいで永続)
CREATE ROLE ddl_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE ddl_user SET lock_timeout = '5s';

-- オプション 3: データベースレベル (すべての接続に適用)
ALTER DATABASE mydb SET lock_timeout = '5s';

-- スキーマ移行には専用 DDL ユーザーを使う
-- ddl_user として接続し、次を実行:
ALTER TABLE users ALTER COLUMN description TYPE text;

-- リトライ付き DDL スクリプトの例
DO $$
DECLARE
    max_attempts INTEGER := 10;
    attempt INTEGER := 1;
    success BOOLEAN := FALSE;
BEGIN
    WHILE attempt <= max_attempts AND NOT success LOOP
        BEGIN
            SET lock_timeout = '2s';
            ALTER TABLE users ALTER COLUMN description TYPE text;
            success := TRUE;
            RAISE NOTICE 'DDL succeeded on attempt %', attempt;
        EXCEPTION
            WHEN lock_not_available THEN
                RAISE NOTICE 'Attempt % failed, retrying in 30 seconds...', attempt;
                PERFORM pg_sleep(30);
                attempt := attempt + 1;
        END;
    END LOOP;

    IF NOT success THEN
        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
    END IF;
END $$;