Zero-Downtime Database Migration Guide

-- Adding a nullable column is instant and lock-free
ALTER TABLE users ADD COLUMN preferences JSONB;

-- Adding with DEFAULT rewrites the table in PG < 11.
-- In PG 11+, this is also fast (metadata-only).
ALTER TABLE users ADD COLUMN status TEXT NOT NULL DEFAULT 'active';

-- Standard CREATE INDEX locks writes. Use CONCURRENTLY instead.
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Drop index concurrently too
DROP INDEX CONCURRENTLY idx_users_email;

-- Step 1: Add the FK as NOT VALID (no row scan, minimal lock)
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_user_id
  FOREIGN KEY (user_id) REFERENCES users(id)
  NOT VALID;

-- Step 2: Validate in a separate transaction (SHARE UPDATE EXCLUSIVE lock)
ALTER TABLE orders
  VALIDATE CONSTRAINT fk_orders_user_id;

-- Changing column type usually rewrites the table.
-- There is no built-in online tool, but you can use the expand/contract pattern:

-- Step 1: Add new column
ALTER TABLE products ADD COLUMN price_cents BIGINT;

-- Step 2: Backfill in batches
UPDATE products SET price_cents = (price * 100)::BIGINT
WHERE id BETWEEN 1 AND 10000;
-- ... repeat in batches

-- Step 3: Add trigger to keep in sync
CREATE OR REPLACE FUNCTION sync_price_cents() RETURNS TRIGGER AS $$
BEGIN
  NEW.price_cents = (NEW.price * 100)::BIGINT;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_price_cents
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION sync_price_cents();

-- Step 4: Switch application to use new column
-- Step 5: Drop old column
ALTER TABLE products DROP COLUMN price;

-- Add column online
ALTER TABLE users
  ADD COLUMN preferences JSON,
  ALGORITHM=INPLACE, LOCK=NONE;

-- Create index online
ALTER TABLE users
  ADD INDEX idx_email (email),
  ALGORITHM=INPLACE, LOCK=NONE;

-- Add foreign key online (InnoDB only)
ALTER TABLE orders
  ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id),
  ALGORITHM=INPLACE, LOCK=NONE;

# Install: apt-get install percona-toolkit  (or download from percona.com)

# Add a column to a large table
pt-online-schema-change \
  --alter "ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active'" \
  --execute \
  --max-load Threads_running=25 \
  --critical-load Threads_running=50 \
  D=mydb,t=users

# Add an index
pt-online-schema-change \
  --alter "ADD INDEX idx_email (email)" \
  --execute \
  D=mydb,t=users

# Drop a column
pt-online-schema-change \
  --alter "DROP COLUMN old_field" \
  --execute \
  D=mydb,t=users

# Download from https://github.com/github/gh-ost/releases

# Add a column
gh-ost \
  --database="mydb" \
  --table="users" \
  --alter="ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active'" \
  --user="root" \
  --password="secret" \
  --host="localhost" \
  --execute

# Throttle if replication lag > 3 seconds
gh-ost \
  --database="mydb" \
  --table="users" \
  --alter="ADD INDEX idx_email (email)" \
  --user="root" \
  --password="secret" \
  --host="localhost" \
  --max-lag-millis=3000 \
  --execute

-- SQLite: change a column type (requires recreation)
-- Step 1: Create new table
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL,
  preferences JSONB  -- new/changed column
);

-- Step 2: Copy data
INSERT INTO users_new (id, name, email, preferences)
SELECT id, name, email, NULL FROM users;

-- Step 3: Drop old table
DROP TABLE users;

-- Step 4: Rename
ALTER TABLE users_new RENAME TO users;

-- Step 5: Recreate indexes and triggers
CREATE INDEX idx_users_email ON users(email);

-- SQLite can add columns, but with restrictions:
--  - Cannot add PRIMARY KEY, UNIQUE, or FOREIGN KEY
--  - Cannot add NOT NULL without DEFAULT
ALTER TABLE users ADD COLUMN preferences TEXT;

-- Online index build (Enterprise Edition)
CREATE INDEX idx_users_email ON users(email)
WITH (ONLINE = ON);

-- Resumable index build (SQL Server 2017+)
CREATE INDEX idx_users_email ON users(email)
WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 5 MINUTES);

-- Resume later if interrupted
ALTER INDEX idx_users_email ON users RESUME;
ALTER INDEX idx_users_email ON users PAUSE;
ALTER INDEX idx_users_email ON users ABORT;

-- Adding a nullable column is metadata-only and instant
ALTER TABLE users ADD COLUMN preferences NVARCHAR(MAX) NULL;

-- Adding with DEFAULT may lock. Use this pattern for large tables:
-- 1. Add column nullable (instant)
ALTER TABLE users ADD COLUMN status NVARCHAR(20) NULL;

-- 2. Backfill in small batches
UPDATE TOP (10000) users SET status = 'active'
WHERE status IS NULL;
-- Repeat until complete

-- 3. Add DEFAULT constraint (metadata-only)
ALTER TABLE users ADD CONSTRAINT df_users_status DEFAULT 'active' FOR status;

-- 4. Optionally make NOT NULL after backfill
ALTER TABLE users ALTER COLUMN status NVARCHAR(20) NOT NULL;

-- SQL Server checks all rows by default.
-- For large tables, first create a trusted CHECK constraint,
-- then add the FK WITH NOCHECK to skip validation.

-- Step 1: Add a CHECK constraint that enforces the FK rule
ALTER TABLE orders WITH CHECK
  ADD CONSTRAINT chk_orders_user_id CHECK (user_id IN (SELECT id FROM users));

-- Step 2: Add FK with NOCHECK (no row scan)
ALTER TABLE orders WITH NOCHECK
  ADD CONSTRAINT fk_orders_user_id FOREIGN KEY (user_id) REFERENCES users(id);

-- Step 3: Make the FK trusted (optional, for query optimizer)
ALTER TABLE orders WITH CHECK CHECK CONSTRAINT fk_orders_user_id;

-- Step 1: Verify the table can be redefined
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'EMPLOYEES');
END;
/

-- Step 2: Create an interim table with the new schema
CREATE TABLE employees_new (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(100) NOT NULL,
  email VARCHAR2(100),
  status VARCHAR2(20) DEFAULT 'active'  -- new column
);

-- Step 3: Start redefinition
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname        => 'SCOTT',
    orig_table   => 'EMPLOYEES',
    int_table    => 'EMPLOYEES_NEW',
    col_mapping  => NULL,
    options_flag => DBMS_REDEFINITION.CONS_USE_PK
  );
END;
/

-- Step 4: Sync changes (run periodically during migration)
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'EMPLOYEES', 'EMPLOYEES_NEW');
END;
/

-- Step 5: Finish redefinition (atomic swap)
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMPLOYEES', 'EMPLOYEES_NEW');
END;
/

-- Step 6: Drop interim table
DROP TABLE employees_new;

-- Rebuild an index online
ALTER INDEX idx_employees_email REBUILD ONLINE;

-- Create index online
CREATE INDEX idx_employees_email ON employees(email) ONLINE;

-- Create a new edition
CREATE EDITION edition_v2;

-- Switch to it
ALTER SESSION SET EDITION = edition_v2;

-- Apply schema changes in this edition only
ALTER TABLE employees ADD COLUMN status VARCHAR2(20);

-- Hot-rollover: new sessions get edition_v2, existing sessions stay on old edition

-- Step 1: Add new column (expand)
ALTER TABLE users ADD COLUMN user_email VARCHAR(255);

-- Step 2: Backfill in batches
UPDATE users SET user_email = email WHERE user_email IS NULL LIMIT 10000;
-- Repeat until done

-- Step 3: Add trigger to keep in sync
CREATE TRIGGER trg_sync_email
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
BEGIN
  NEW.user_email = COALESCE(NEW.user_email, NEW.email);
  NEW.email = COALESCE(NEW.email, NEW.user_email);
END;

-- Step 4: Update application to read from user_email
-- Step 5: Stop writing to old 'email' column
-- Step 6: Drop old column (contract)
ALTER TABLE users DROP COLUMN email;