Zero-Downtime Schema Change on a Large Table: Expand-and-Contract

-- Dangerous: locks the 400-million-row accounts table for hours
ALTER TABLE accounts
ADD COLUMN amount_decimal NUMERIC(15,2) NOT NULL DEFAULT 0.00;
-- Table rewrite + exclusive lock = effective outage

Timeline of the naive approach:

  T=0    ALTER TABLE begins, acquires exclusive lock
  T=0    All reads and writes to accounts are blocked
  T+2hr  ALTER TABLE finishes rewriting 400M rows
  T+2hr  Lock released, traffic resumes
  Result: 2-hour outage during business hours

-- Step 1: Fast metadata-only change
ALTER TABLE accounts
ADD COLUMN amount_decimal NUMERIC(15,2) NULL;
-- Completes in milliseconds, no table rewrite

package com.bank.repository;

import java.math.BigDecimal;
import java.sql.*;

public class AccountRepository {

    private final Connection connection;

    public AccountRepository(Connection connection) {
        this.connection = connection;
    }

    public void updateBalance(String accountId, long amountPaise)
            throws SQLException {
        // Dual-write: update both old (integer paise) and new (decimal rupees)
        BigDecimal amountRupees = BigDecimal.valueOf(amountPaise, 2);
        String sql = "UPDATE accounts "
                   + "SET amount_paise = ?, amount_decimal = ? "
                   + "WHERE account_id = ?";
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setLong(1, amountPaise);
            ps.setBigDecimal(2, amountRupees);
            ps.setString(3, accountId);
            ps.executeUpdate();
        }
    }
}

-- Step 3: Backfill in batches of 5000
DO $$
DECLARE
    v_batch_size INT := 5000;
    v_updated    INT;
BEGIN
    LOOP
        UPDATE accounts
        SET amount_decimal = amount_paise / 100.0
        WHERE amount_decimal IS NULL
          AND account_id IN (
              SELECT account_id FROM accounts
              WHERE amount_decimal IS NULL
              LIMIT v_batch_size
              FOR UPDATE SKIP LOCKED
          );

        GET DIAGNOSTICS v_updated = ROW_COUNT;
        RAISE NOTICE 'Backfilled % rows', v_updated;

        IF v_updated = 0 THEN
            EXIT;
        END IF;

        COMMIT;  -- release locks between batches
        PERFORM pg_sleep(0.5);  -- throttle to protect replication
    END LOOP;
END;
$$;

-- Step 4a: Add a NOT VALID check (instant, no scan)
ALTER TABLE accounts
ADD CONSTRAINT chk_amount_decimal_not_null
CHECK (amount_decimal IS NOT NULL) NOT VALID;

-- Step 4b: Validate the constraint (scans but does not lock exclusively)
ALTER TABLE accounts
VALIDATE CONSTRAINT chk_amount_decimal_not_null;

-- Step 4c: Now safe to set NOT NULL (no scan needed)
ALTER TABLE accounts
ALTER COLUMN amount_decimal SET NOT NULL;

-- Step 4d: Build index concurrently (no lock)
CREATE INDEX CONCURRENTLY idx_amount_decimal
ON accounts (amount_decimal);

-- Step 5: Drop the old column (after 2-week bake period)
ALTER TABLE accounts DROP COLUMN amount_paise;
-- Fast metadata operation, irreversible

Flyway migration files:

  V10__expand_add_amount_decimal.sql     (Step 1)
  V11__backfill_amount_decimal.sql       (Step 3)
  V12__constrain_amount_decimal.sql      (Step 4)
  V13__contract_drop_amount_paise.sql    (Step 5)

-- V10__expand_add_amount_decimal.sql
ALTER TABLE accounts
ADD COLUMN amount_decimal NUMERIC(15,2) NULL;

-- V12__constrain_amount_decimal.sql
ALTER TABLE accounts
ADD CONSTRAINT chk_amount_decimal_nn
CHECK (amount_decimal IS NOT NULL) NOT VALID;

ALTER TABLE accounts
VALIDATE CONSTRAINT chk_amount_decimal_nn;

ALTER TABLE accounts
ALTER COLUMN amount_decimal SET NOT NULL;

Senior Checklist:
  1. Every step before CONTRACT is reversible
  2. Dual-write window ensures both old and new code work
  3. Backfill is throttled (batch size, sleep between batches)
  4. NOT NULL added via NOT VALID + VALIDATE (no exclusive lock)
  5. Indexes built CONCURRENTLY
  6. Contract only after a bake period with monitoring
  7. Each step is a separate Flyway/Liquibase migration