Schema Versioning and Migration Strategies for Scalable Databases

# gh-ost: alter a large orders table with no downtime
gh-ost \
  --host=mysql-primary \
  --user=ghuser \
  --password=secret \
  --database=myapp \
  --table=orders \
  --alter="ADD COLUMN fulfillment_status ENUM('pending','shipped','delivered') DEFAULT 'pending'" \
  --execute \
  --chunk-size=2000 \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=50 \
  --switch-to-rbr \
  --allow-master-master  # if using Group Replication

# Key flags:
# --chunk-size: rows per sync iteration (lower = less lag on replica)
# --max-load: throttle if MySQL load exceeds this
# --critical-load: abort if load exceeds this
# --postpone-cut-over-flag-file: pause before final cutover (manual approval)

# pt-osc: alternative for environments where gh-ost can't connect to binlog
pt-online-schema-change \
  --host=mysql-primary \
  --user=ptuser \
  --password=secret \
  D=myapp,t=orders \
  --alter "ADD INDEX idx_status (fulfillment_status)" \
  --execute \
  --chunk-size=5000 \
  --max-load="Threads_running:30" \
  --no-drop-old-table  # keep _orders_old until you verify

-- Standard CREATE INDEX blocks all writes during build
CREATE INDEX orders_user_idx ON orders(user_id);  -- DON'T do this on large tables in prod

-- CREATE INDEX CONCURRENTLY: builds without write lock (takes 2-3x longer but safe)
CREATE INDEX CONCURRENTLY orders_user_idx ON orders(user_id);

-- If a concurrent build fails (it leaves an INVALID index):
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';
-- Drop the invalid index and retry:
DROP INDEX CONCURRENTLY orders_user_idx;
CREATE INDEX CONCURRENTLY orders_user_idx ON orders(user_id);

# pg_repack: reclaim bloat or change table storage without an exclusive lock
# (VACUUM FULL requires a full table lock; pg_repack does not)

# Install
apt-get install postgresql-15-repack  # or your PG version

# Repack a bloated table
pg_repack -h localhost -d mydb -t orders

# Repack with index change (equivalent to CLUSTER but online)
pg_repack -h localhost -d mydb -t orders --order-by created_at

-- Migration files: V{version}__{description}.sql
-- Example: V1__create_users.sql
CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- V2__add_display_name.sql
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);

-- V3__index_users_email.sql  (PostgreSQL)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Run via CLI
flyway -url=jdbc:postgresql://localhost/mydb -user=admin -password=secret migrate

-- Or Java/Spring (auto-migrates on startup):
# application.properties
spring.flyway.enabled=true
spring.flyway.locations=classpath:db/migration

-- changelog.xml (Liquibase uses XML, YAML, or SQL formats)


  
    
      
        
      
      
        
      
    
  

  
    
      
    
  


# Run via CLI
liquibase --url=jdbc:postgresql://localhost/mydb \
  --username=admin --password=secret \
  --changeLogFile=changelog.xml update

-- Safe to roll back:
-- ADD COLUMN: rollback = DROP COLUMN
ALTER TABLE orders ADD COLUMN discount_code VARCHAR(50);
-- Rollback: ALTER TABLE orders DROP COLUMN discount_code;

-- ADD INDEX: rollback = DROP INDEX
CREATE INDEX CONCURRENTLY orders_discount_idx ON orders(discount_code);
-- Rollback: DROP INDEX CONCURRENTLY orders_discount_idx;

-- Harder to roll back:
-- DROP COLUMN: rollback requires a backup restore (data is gone)
-- Mitigation: rename instead of drop, keep data around for 1-2 sprints

-- RENAME COLUMN: both app versions must handle old and new name during rollout
-- Mitigation: use expand-contract (add new column, keep old, switch apps, drop old)

-- DATA MIGRATIONS: backfilling data is usually irreversible
-- Mitigation: write data to new column only; don't modify old column until you're sure

-- Blue/Green deployment + schema migrations:
-- Run migration BEFORE deploying new app code
-- New schema must be backward-compatible with old app code
-- Rollback = point load balancer back to old app stack (schema change stays)

-- Estimate migration time before running
-- MySQL: check table size
SELECT
  table_name,
  ROUND(data_length / 1024 / 1024, 0) AS data_mb,
  ROUND(index_length / 1024 / 1024, 0) AS index_mb,
  table_rows
FROM information_schema.tables
WHERE table_schema = 'myapp'
  AND table_name = 'orders';

-- PostgreSQL: table size
SELECT
  pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
  pg_size_pretty(pg_relation_size('orders')) AS table_size,
  (SELECT reltuples::BIGINT FROM pg_class WHERE relname = 'orders') AS est_rows;

-- Estimate gh-ost time:
-- gh-ost processes ~2,000 rows/chunk at 1 chunk/second by default
-- 50M rows ÷ 2,000 = 25,000 iterations ≈ 7 hours
-- Increase --chunk-size to 5,000 and it drops to ~3 hours (watch replica lag)

-- Run a dry-run first (gh-ost --test-on-replica)
gh-ost --test-on-replica --host=replica-host --table=orders \
  --alter="ADD COLUMN ..." --execute

-- 1. Adding a new table: safe, deploy anytime
CREATE TABLE audit_log (
  id BIGSERIAL PRIMARY KEY,
  entity_type VARCHAR(50) NOT NULL,
  entity_id BIGINT NOT NULL,
  action VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT now()
);
CREATE INDEX CONCURRENTLY audit_log_entity_idx ON audit_log(entity_type, entity_id);

-- 2. Adding a nullable column: safe, no lock (PostgreSQL 11+, MySQL 8+)
ALTER TABLE orders ADD COLUMN notes TEXT;  -- no table rewrite for nullable

-- 3. Adding a NOT NULL column WITH DEFAULT: safe in PostgreSQL 11+
ALTER TABLE orders ADD COLUMN priority SMALLINT NOT NULL DEFAULT 0;
-- PostgreSQL 11+ stores the default in catalog, no table rewrite
-- MySQL: use gh-ost or add nullable first, backfill, add constraint separately

-- 4. Adding a column with a computed default (requires backfill)
-- Step 1: add nullable
ALTER TABLE orders ADD COLUMN priority_label VARCHAR(20);
-- Step 2: backfill in chunks
UPDATE orders SET priority_label = CASE
  WHEN priority >= 8 THEN 'high'
  WHEN priority >= 4 THEN 'medium'
  ELSE 'low'
END WHERE priority_label IS NULL LIMIT 10000;
-- Step 3 (optional): add NOT NULL constraint after backfill
ALTER TABLE orders ALTER COLUMN priority_label SET NOT NULL;

-- 5. Dropping a column: two-phase
-- Phase 1: make app stop reading/writing it (deploy new code)
-- Phase 2 (next sprint): drop the column
ALTER TABLE orders DROP COLUMN legacy_tracking_id;