Schema Evolution Planner

Schema Evolution Plan — users.name → users.full_name, varchar(255) → text

Engine: Postgres 15 · Table size: ~50M rows · Strategy: expand → migrate → contract

Dependencies

Phased Deploy Plan

Phase 1 — Add the new column

ALTER TABLE users ADD COLUMN full_name text;

Metadata only; no row rewrite. Sub-second lock.

Phase 2 — Dual-write from the application

// Drizzle insert/update
await db.update(users).set({ name: payload.name, fullName: payload.name }).where(...);

Ship v2 to all replicas before starting the backfill. The backfill is idempotent only when every new write is captured.

Phase 3 — Chunked backfill

-- Run in 50k-row batches, sleep 100ms between, monitor replication lag
DO $$
DECLARE batch_size int := 50000;
BEGIN
  LOOP
    WITH cte AS (
      SELECT id FROM users WHERE full_name IS NULL ORDER BY id LIMIT batch_size
    )
    UPDATE users SET full_name = name FROM cte WHERE users.id = cte.id;
    EXIT WHEN NOT FOUND;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

ShareUpdateExclusive is held per-batch only — does not block writes. Expected wall-clock: ~30–45 min for 50M rows on managed Postgres at medium load.

Phase 6 — Promote to NOT NULL via NOT VALID

ALTER TABLE users
  ADD CONSTRAINT users_full_name_not_null
  CHECK (full_name IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT users_full_name_not_null;

NOT VALID is metadata-only; VALIDATE takes ShareUpdateExclusive and does not block writes. Then convert the check into a true NOT NULL on Postgres 12+:

ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_full_name_not_null;

Phase 7 — Drop name

ALTER TABLE users DROP COLUMN name;

AccessExclusiveLock but metadata-only — sub-second on a healthy 50M-row table. Schedule outside peak hours.

Rollback per Phase

Before / After Summary

Plan is illustrative against a hypothetical users.name → users.full_name evolution. Re-run timing estimates against representative staging data before scheduling production cutover.

This sample illustrates the skill's output format. Names, metrics, and operational details are illustrative unless the artifact explicitly analyzes public information.