From harness-claude
> Add new structure, migrate data, remove old structure -- the three-phase pattern for safe column renames, type changes, and table restructuring.
npx claudepluginhub intense-visions/harness-engineering --plugin harness-claudeThis skill uses the workspace's default tool permissions.
> Add new structure, migrate data, remove old structure -- the three-phase pattern for safe column renames, type changes, and table restructuring.
Generates zero-downtime database migrations with forward SQL, rollback SQL, and deployment sequences for schema changes like adding columns, renaming tables, or dropping columns.
Guides zero-downtime PostgreSQL schema changes like adding/dropping columns, concurrent indexing, type alterations, and pg_repack on large production tables without locks or outages.
Guides safe schema changes, data migrations, rollbacks, and zero-downtime deployments for PostgreSQL, MySQL, and ORMs including Prisma, Drizzle, Django, TypeORM, golang-migrate. Use for table modifications, column additions, indexes, backfills.
Share bugs, ideas, or general feedback.
Add new structure, migrate data, remove old structure -- the three-phase pattern for safe column renames, type changes, and table restructuring.
1. The Three Phases
Each phase is a separate deployment. The application must work correctly at every intermediate state.
2. Column Rename Pattern
You cannot safely rename a column in a single step because the old application code still references the old name.
-- Phase 1: EXPAND -- add new column
ALTER TABLE users ADD COLUMN full_name TEXT;
-- Phase 2: MIGRATE -- backfill and dual-write
UPDATE users SET full_name = name WHERE full_name IS NULL;
-- Application code writes to BOTH name and full_name
-- Phase 3: CONTRACT -- drop old column
-- Only after all application instances use full_name
ALTER TABLE users DROP COLUMN name;
3. Dual-Write Strategy
During the migrate phase, the application writes to both old and new columns. This ensures either version of the application can read correctly.
-- Application INSERT during migrate phase:
INSERT INTO users (name, full_name, email)
VALUES ('Alice', 'Alice', 'alice@example.com');
-- Trigger-based alternative (keeps sync automatic):
CREATE OR REPLACE FUNCTION sync_name_to_full_name()
RETURNS TRIGGER AS $$
BEGIN
IF NEW.full_name IS NULL THEN
NEW.full_name := NEW.name;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_name_to_full_name();
4. Type Change Pattern
Changing a column type (e.g., INT to BIGINT) requires a full table rewrite if done directly. Expand-contract avoids this:
-- Phase 1: EXPAND
ALTER TABLE orders ADD COLUMN order_number_v2 BIGINT;
-- Phase 2: MIGRATE
-- Backfill in batches (see db-zero-downtime-migration for batch pattern)
UPDATE orders SET order_number_v2 = order_number
WHERE id BETWEEN 1 AND 10000 AND order_number_v2 IS NULL;
-- ... repeat in batches
-- Application starts reading from order_number_v2,
-- writing to both columns
-- Phase 3: CONTRACT
ALTER TABLE orders DROP COLUMN order_number;
ALTER TABLE orders RENAME COLUMN order_number_v2 TO order_number;
Scenario: a multi-service system needs to rename the users.name column to users.display_name while three services query the table.
-- Deploy 1: EXPAND
ALTER TABLE users ADD COLUMN display_name TEXT;
CREATE OR REPLACE FUNCTION sync_user_name() RETURNS TRIGGER AS $$
BEGIN
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
IF NEW.display_name IS NULL THEN
NEW.display_name := NEW.name;
ELSIF NEW.name IS NULL OR NEW.name != NEW.display_name THEN
NEW.name := NEW.display_name;
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_sync_user_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION sync_user_name();
-- Deploy 2: BACKFILL
-- Run during off-peak in 5K-row batches
UPDATE users SET display_name = name
WHERE display_name IS NULL
AND id BETWEEN $1 AND $2;
-- Deploy 3-5: Update each service to read/write display_name
-- Each service deploys independently
-- Deploy 6: CONTRACT
-- Verify: SELECT count(*) FROM users WHERE display_name IS NULL;
-- Must return 0
DROP TRIGGER trg_sync_user_name ON users;
DROP FUNCTION sync_user_name();
ALTER TABLE users DROP COLUMN name;
Timeline: 2-4 weeks, depending on service deployment cadence. The database is safe at every intermediate state.
Renaming a column directly with ALTER TABLE RENAME COLUMN. This breaks all application code referencing the old name instantly. Expand-contract avoids this by maintaining both names during transition.
Skipping the dual-write phase. If old application instances still write to the old column while new instances read from the new column, data is silently lost. Both columns must stay synchronized.
Never completing the contract phase. Dead columns accumulate, confuse developers, waste storage, and slow queries. Set a deadline for the contract phase and track it as tech debt.
Backfilling in a single transaction. A single UPDATE users SET display_name = name locks millions of rows. Batch the backfill with explicit bounds and commits between batches.
Using database views as a permanent compatibility layer. Views add indirection and query complexity. They are a reasonable temporary bridge during migration but should not persist after the contract phase.
ALTER TABLE ... ADD COLUMN is instant (no table rewrite) in PostgreSQL 11+ even with a default value.DROP COLUMN does not physically remove data; it marks the column as dropped. Use VACUUM FULL or pg_repack to reclaim space afterward if needed.Table Splits: When splitting a wide table into two narrower tables (vertical split), expand-contract applies at the table level:
Feature Flags for Contract Phase: Use application-level feature flags to control which column the application reads from. This makes the contract phase reversible: if issues arise, flip the flag back to the old column.
Automated Expand-Contract: Tools like reshape (Rust-based schema migration) automate the expand-contract pattern. They generate the triggers and handle the dual-write automatically, reducing manual error.
MySQL lacks transactional DDL. Each ALTER TABLE commits implicitly, so a multi-step expand operation cannot be rolled back atomically. This makes careful planning even more critical: if the expand step partially succeeds, manual cleanup is required.
MySQL pt-online-schema-change and gh-ost can automate the expand-contract pattern for column type changes by creating a shadow table, copying data, and swapping. They effectively implement expand-contract internally.
GitHub migrated their repositories.id column from INT to BIGINT on a table with billions of rows. The expand-contract approach took several weeks: they added a new_id BIGINT column, set up dual-write synchronization via application code, backfilled the new column in background batches, updated all queries to use the new column, and finally dropped the old column. At no point was the service interrupted. The migration was invisible to users.