🗄️ PHASE 1 - MIGRATION SCOPE ANALYSIS Change Classification Risk Increase email length to 320 🟢 Additive/compatible Low Normalize existing emails 🟡 Compatibility-sensitive Medium Fill 1.2M NULL values 🟠 High-risk High Remove duplicates 🔴 Destructive Critical Make email NOT NULL 🟠 High-risk High Add UNIQUE constraint 🟠 High-risk High Add unique index 🟠 High-risk High Dependency chain Clean data ↓ Resolve duplicates ↓ Populate NULL strategy ↓ Deploy compatible application ↓ Backfill ↓ Validate ↓ Create unique index ↓ Enforce NOT NULL The duplicate cleanup must happen before the UNIQUE constraint can succeed. --- 🧩 PHASE 2 - SCHEMA CHANGE RISK AUDIT VARCHAR(255) → VARCHAR(320) Risk: 🟢 Low The change expands the permitted size, so existing values are not truncated. However, application validation should also accept the new length. NULL → NOT NULL Risk: 🟠 High There are currently 1.2 million NULL values. A direct constraint addition would fail unless those rows are resolved first. More importantly, simply replacing NULL with a fake value could create incorrect customer data. UNIQUE email Risk: 🔴 Critical There are 83,000 duplicate email groups. A UNIQUE constraint cannot safely be introduced until duplicates are resolved. Creating the constraint immediately could fail and potentially create significant locking/operational impact. Recommended approach Do not execute: ALTER TABLE users ... NOT NULL + UNIQUE as one large production operation. Use an expand/migrate/verify/contract strategy. --- 🗃️ PHASE 3 - DATA INTEGRITY ANALYSIS Three major data problems exist: 1. NULL emails 1.2 million users have no email. Do NOT automatically generate fake emails. First determine: Are these legitimate users? Are emails optional for certain account types? Can another verified contact field be used? Can those users remain without email? If email must become mandatory, define a valid business rule before migration. 2. Duplicate emails 83,000 duplicate groups require investigation. Possible reasons: Shared family email Historical duplicate accounts Data-quality bug Case differences Whitespace differences Example: User@Example.com user@example.com user@example.com These may represent the same normalized email. Do not blindly delete rows. 3. Data loss risk Deleting duplicate customer records is destructive. The migration should instead: Identify duplicate groups Select canonical records Preserve historical records Reassign dependent records if required Mark duplicate accounts rather than immediately deleting them Maintain an audit trail --- 🔒 PHASE 4 - LOCKING & CONCURRENCY The users table is a hot production table with 48 million rows. A direct schema change may require locks that interfere with: User reads User updates Authentication Account management Background workers The dangerous operation is not necessarily the SQL length. A one-line ALTER TABLE can still have significant production impact depending on PostgreSQL version, operation, existing data, indexes, and concurrent activity. Safer strategy Use: Small batches Short transactions Online/concurrent index creation where supported Lock monitoring Low-traffic deployment window Retryable operations Throttled backfill Avoid a single transaction updating millions of rows. --- ⏱️ PHASE 5 - DOWNTIME ASSESSMENT Current assessment: 🟠 Significant availability risk The exact migration duration cannot be reliably estimated from the supplied information. Required benchmark data: Production-like row count Storage performance CPU Disk I/O Current write rate Index creation benchmark Backfill throughput Replica replay speed Expected downtime Target should be: Near-zero application downtime rather than attempting a large blocking schema migration. The maximum allowed downtime is only 2 minutes, so any operation requiring an unpredictable table lock should be treated as a NO-GO until tested. --- 🔗 PHASE 6 - COMPATIBILITY AUDIT The old application allows: email = NULL The new application expects: email != NULL Therefore: Old App + New DB Potentially incompatible. If the database enforces NOT NULL while an old application still writes NULL, requests can fail. New App + Old DB Potentially compatible if the new application continues handling the old schema during the transition. Old App + New App Needs careful rolling-deployment testing. Safe compatibility design Use: EXPAND Add support first. Then: MIGRATE Backfill and normalize data. Then: VERIFY Then: SWITCH Deploy application behavior requiring the new invariant. Finally: CONTRACT Apply the strict NOT NULL/UNIQUE enforcement after all consumers are compatible. --- 🔄 PHASE 7 - SAFE DEPLOYMENT SEQUENCE Stage 1 - EXPAND Do not enforce NOT NULL or UNIQUE yet. Deploy application code capable of handling both old and new data states. Gate: All API services and workers successfully deployed. --- Stage 2 - DATA CLEANUP Normalize email values. Example normalization concept: LOWER(TRIM(email)) But only after confirming that business rules permit this normalization. Gate: No unexpected normalization changes. --- Stage 3 - DUPLICATE RESOLUTION Generate duplicate report. For each duplicate group: Identify canonical user Identify dependent records Reassign references if required Preserve audit information Mark duplicate records Do not physically delete records initially Gate: Duplicate count reaches zero for the uniqueness definition. --- Stage 4 - NULL STRATEGY Resolve NULL emails according to an approved business rule. If email is legitimately optional for some users, the requirement for global NOT NULL must be reconsidered. Gate: NULL count = 0 OR business-approved exception model exists. --- Stage 5 - BACKFILL Run in small batches. Example: 5,000 rows per batch Commit each batch independently. Monitor: CPU Disk I/O Lock waits Query latency Replica lag Pause/throttle if production load increases. --- Stage 6 - VERIFY Check: NULL count Duplicate count Invalid email count Row counts Referential integrity Application errors Only continue if all gates pass. --- Stage 7 - CREATE UNIQUE INDEX Prefer a production-safe concurrent index strategy where appropriate. Monitor index build and replica behavior. Gate: Index successfully created and validated. --- Stage 8 - SWITCH APPLICATION Deploy the application version that requires the new invariant. Rolling deployment is allowed only if old and new versions remain compatible during coexistence. --- Stage 9 - CONTRACT Only after all consumers have migrated: Enforce NOT NULL Remove temporary compatibility logic Remove obsolete fields/indexes if any --- 📦 PHASE 8 - BACKFILL STRATEGY Recommended initial configuration: Batch: 5,000 rows Transaction: one batch per transaction Commit frequently Process by indexed primary-key ranges Record progress Make operation idempotent Retry failed batches Throttle during peak load Example conceptual flow: SELECT next 5,000 eligible IDs ↓ Transform ↓ UPDATE ↓ COMMIT ↓ Validate batch ↓ Record progress ↓ Continue Important Do not use: UPDATE users SET ... against all 48 million rows in one transaction. That can generate excessive: WAL Locks I/O Vacuum pressure Replica lag Transaction duration --- ⚡ PHASE 9 - PERFORMANCE & CAPACITY Main risks CPU: Medium Normalization/backfill consumes CPU. Disk I/O: High 48 million-row processing can create substantial I/O. WAL: High Large UPDATE workloads can generate significant WAL. Replication lag: High possibility Both read replicas may fall behind during the backfill. Query latency: Medium to High The hot users table is actively used. Recommendation Set operational thresholds before starting. Example: Replica lag > 60 seconds → throttle Replica lag > 5 minutes → STOP API p95 latency > 2x baseline → throttle/STOP Database CPU sustained > 80% → throttle Unexpected lock wait → STOP Exact thresholds should be aligned with the production SLOs. --- 🧪 PHASE 10 - PRE-MIGRATION VALIDATION Database [ ] Backup completed [ ] Backup restore tested [ ] PITR verified [ ] Schema snapshot captured [ ] Row counts recorded [ ] Indexes recorded [ ] Constraints recorded [ ] Replica health verified [ ] Replication lag baseline recorded Data [ ] NULL count confirmed [ ] Duplicate count confirmed [ ] Invalid email count confirmed [ ] Normalization impact measured [ ] Duplicate resolution rules approved [ ] NULL handling rules approved Application [ ] Old application compatibility tested [ ] New application tested [ ] Workers tested [ ] Admin tools tested [ ] Reporting queries tested [ ] Rolling deployment tested Operations [ ] Monitoring enabled [ ] Alerts configured [ ] DBA available [ ] Application owner available [ ] Rollback/recovery procedure tested [ ] Incident communication ready Critical rule If backup restoration has not been tested, production execution should not proceed. --- 🚨 PHASE 11 - FAILURE MODE ANALYSIS Failure Detection Immediate Action Recovery Data Check Migration times out Migration error Stop operation Retry smaller batch Compare affected rows Production queries blocked Lock wait spike Abort/terminate migration operation Resume later Check transaction state Backfill fails halfway Job failure Stop worker Resume from checkpoint Validate processed batch Unique index fails Duplicate error Do not enforce constraint Re-run duplicate analysis Duplicate count Replica falls behind Replication lag Throttle/stop backfill Allow replica recovery Compare replication state Application deployment fails Error rate rises Halt rollout Roll back application Verify DB writes Unexpected transformation Validation mismatch Stop backfill Restore affected values if possible Row-level validation Connection pool saturation Connection metrics Throttle migration Recover pool capacity Check application errors --- 🔄 PHASE 12 - ROLLBACK & RECOVERY Schema expansion Classification: 🟢 Fully reversible The expanded column size can generally be retained without data loss. Normalization Classification: 🟡 Reversible with conditions Only reversible if original values are preserved or transformation is provably reversible. Therefore, create an audit/shadow mechanism before destructive transformation. Duplicate resolution Classification: 🔴 Destructive if records are deleted Do not physically delete duplicate users during the first migration stage. Prefer: duplicate -> canonical mapping -> preserve original -> migrate references -> validate -> later cleanup NOT NULL Classification: 🟡 Reversible with conditions The constraint can potentially be removed, but data written under the new invariant may not restore the old state automatically. UNIQUE Classification: 🟡 Reversible with conditions The index/constraint can be removed, but duplicate data cannot be recreated unless it was preserved. Recovery hierarchy 1. Stop migration 2. Prevent further writes if integrity is threatened 3. Preserve current database state 4. Inspect migration checkpoint 5. Roll back application deployment if necessary 6. Restore affected data from shadow/audit information 7. Use PITR only when logical recovery is insufficient 8. Validate data integrity 9. Resume migration only after root cause is resolved --- 🔍 PHASE 13 - POST-MIGRATION VERIFICATION Schema Expected: email supports 320 characters UNIQUE index exists NOT NULL enforcement exists Expected indexes are valid No unexpected constraints Data Verify: NULL emails = 0 Duplicate normalized emails = 0 Row count preserved No unexpected deleted users No orphan records Transformation accuracy verified Application Monitor: HTTP 5xx Authentication errors User update failures API latency Database query latency Worker failures Job retries Infrastructure Monitor: Replica lag CPU Memory Disk Connections WAL generation Storage growth Success gate Migration is successful only when: Schema correct AND Data correct AND Application healthy AND Replication healthy --- 📊 PHASE 14 - MIGRATION RISK REGISTER Risk Component Trigger Impact Likelihood Severity Mitigation Validation Duplicate emails Data UNIQUE creation Migration failure High P0 Resolve before constraint Duplicate count = 0 NULL emails Data NOT NULL Write failures High P0 Approved NULL strategy NULL count = 0 Long table locks DB Schema operation Availability loss Medium P0 Online strategy + testing Lock monitoring Replica lag Replication Backfill HA/read issues High P1 Throttle batches Lag threshold Data transformation error Data Normalization Corruption Medium P0 Shadow/audit data Row validation Application incompatibility App Rolling deployment API failures Medium P0 Expand/contract Compatibility test WAL/storage growth Infrastructure Backfill Capacity issue Medium P1 Throttling Disk/WAL monitoring Recovery failure DR Migration failure Extended outage Low/Medium P0 Test restore/PITR Recovery drill --- 🛠️ PHASE 15 - SAFE MIGRATION RUNBOOK Step 1 - Freeze migration inputs Owner: DBA Action: Capture schema, row counts, duplicate counts, NULL counts and replication state. Precondition: Monitoring active. Expected result: Baseline recorded. Abort: Unexpected database health issue. --- Step 2 - Verify recovery Owner: DBA/Infrastructure Action: Verify backup and PITR recovery capability. Expected result: Recovery successfully demonstrated. Abort: Recovery cannot be verified. Recovery: Do not begin migration. --- Step 3 - Deploy compatibility code Owner: Backend team Action: Deploy application capable of handling both old and new states. Expected result: No increase in error rate. Abort: API/worker errors exceed baseline. --- Step 4 - Normalize data Owner: DBA/Data team Action: Run controlled batches. Expected result: Expected number of rows transformed. Monitor: Latency, locks, CPU, WAL and replicas. Abort: Unexpected data changes or performance degradation. --- Step 5 - Resolve duplicates Owner: Data + Backend Action: Create canonical mapping and preserve affected records. Expected result: Duplicate groups resolved without customer-record loss. Abort: Any ambiguous customer merge. --- Step 6 - Resolve NULL strategy Owner: Product + Backend + Data Action: Apply approved business rule. Expected result: All records satisfy new invariant. Abort: Any unresolved business exception. --- Step 7 - Verify Owner: DBA Action: Run complete integrity checks. Expected result: All validation gates pass. Abort: Any P0 validation failure. --- Step 8 - Build unique index Owner: DBA Action: Use an online/concurrent approach appropriate for PostgreSQL production. Monitor: Locks, CPU, I/O, replication. Abort: Unexpected blocking or resource exhaustion. --- Step 9 - Deploy new application Owner: Backend Action: Rolling deployment. Expected result: Application uses new invariant successfully. Abort: Error rate or latency exceeds agreed threshold. --- Step 10 - Enforce NOT NULL Owner: DBA Action: Apply final constraint after application compatibility is confirmed. Expected result: Database enforces invariant. Abort: Constraint validation failure or unexpected locking. --- Step 11 - Final validation Owner: DBA + Backend Action: Run schema, data, application and infrastructure checks. Expected result: All success gates pass. --- 🛑 STOP CONDITIONS Immediately stop or throttle migration if: Unexpected production lock occurs API error rate materially increases Database latency materially increases Replica lag exceeds approved threshold Disk capacity becomes unsafe Connection pool saturation occurs Unexpected data transformation appears Duplicate count increases NULL count behaves unexpectedly Referential integrity fails Backup/recovery capability becomes unavailable --- 🧾 PHASE 16 - FINAL PRODUCTION MIGRATION BLUEPRINT 🗄️ MIGRATION SUMMARY Current: users.email VARCHAR(255) NULL with: 1.2M NULL values 83K duplicate groups 48M users High production traffic Target: users.email VARCHAR(320) NOT NULL UNIQUE The target state is achievable, but not through a single destructive ALTER operation. --- 🚨 TOP RISKS 1. P0 - Duplicate customer emails 2. P0 - 1.2M NULL emails 3. P0 - Old/new application incompatibility 4. P0 - Data loss during duplicate cleanup 5. P0 - Production locking 6. P1 - Replica lag 7. P1 - Backfill resource consumption 8. P1 - Recovery not yet tested --- 🔒 LOCKING & DOWNTIME Classification: 🟠 Significant availability risk A zero/near-zero downtime migration is possible in principle, but the exact operational behavior must be benchmarked against production-like data and workload. The 2-minute downtime requirement means a blocking migration should not be accepted without testing. --- 🔗 COMPATIBILITY Combination Result Old App + Old DB 🟢 Safe New App + Old DB 🟡 Depends on implementation Old App + New DB 🔴 Unsafe if NOT NULL enforced too early New App + New DB 🟢 Target state Old + New App during rollout 🟡 Requires compatibility testing --- 🔄 SAFE MIGRATION SEQUENCE EXPAND ↓ Deploy compatibility application ↓ Normalize ↓ Resolve duplicates ↓ Resolve NULLs ↓ Batched backfill ↓ VERIFY ↓ Create unique index safely ↓ Deploy new application ↓ VERIFY ↓ Enforce NOT NULL ↓ Final validation ↓ CONTRACT --- 🎯 GO / NO-GO DECISION 🔴 NO-GO CURRENTLY The migration should not be executed in production yet. Reasons 1.2 million NULL values exist 83,000 duplicate groups exist Duplicate cleanup strategy is not yet proven NULL handling business rule is not explicitly established Old/new application compatibility is not fully established Rollback migration is not tested Production backfill duration is unknown Lock behavior has not been benchmarked Recovery procedure has not been demonstrated Change to GO WITH CONDITIONS when all are satisfied [ ] Duplicate-resolution strategy approved [ ] No customer records are deleted without recovery [ ] NULL handling strategy approved [ ] Compatibility deployment tested [ ] Production-like migration benchmark completed [ ] Lock behavior validated [ ] Backfill is restartable [ ] Backup restoration tested [ ] PITR tested [ ] Replica lag thresholds established [ ] Abort conditions configured [ ] Monitoring active [ ] On-call owners confirmed [ ] Pre-migration data validation passes [ ] Post-migration validation queries are ready Final recommendation: DELAY & MITIGATE first. Once the above gates pass, the migration can move toward 🟡 GO WITH CONDITIONS, using the EXPAND → MIGRATE → VERIFY → SWITCH → CONTRACT strategy. ::: Sample test takeaway: The key lesson is that a migration can look simple at the SQL level but still be P0-risk at the production level because of existing data, application compatibility, locks, replication, and irreversible data changes.