The Expand and Contract Pattern for Zero-Downtime Migrations

Source: Dev.to

Steps

1. Expand – Add the new structure alongside the old one.
2. Migrate – Introduce dual‑write logic in the application and backfill existing data.
3. Contract – Remove the old structure once no instance depends on it.

Each step is performed in a separate deployment.

Example: Renaming a Column

Rename users.name to users.full_name.

Expand

ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

Dual‑write Deployment

func (r *UserRepo) Create(ctx context.Context, user *User) error {
    _, err := r.db.ExecContext(ctx, `
        INSERT INTO users (name, full_name, email)
        VALUES ($1, $1, $2)
    `, user.FullName, user.Email)
    return err
}

func (r *UserRepo) UpdateName(ctx context.Context, id int, name string) error {
    _, err := r.db.ExecContext(ctx, `
        UPDATE users SET name = $1, full_name = $1 WHERE id = $2
    `, name, id)
    return err
}

Read with Fallback

func (r *UserRepo) GetByID(ctx context.Context, id int) (*User, error) {
    row := r.db.QueryRowContext(ctx, `
        SELECT id, COALESCE(full_name, name), email FROM users WHERE id = $1
    `, id)

    var user User
    err := row.Scan(&user.ID, &user.FullName, &user.Email)
    return &user, err
}

Backfill Existing Rows

UPDATE users SET full_name = name WHERE full_name IS NULL;

Contract

ALTER TABLE users DROP COLUMN name;

Remove Dual‑write Logic

func (r *UserRepo) Create(ctx context.Context, user *User) error {
    _, err := r.db.ExecContext(ctx, `
        INSERT INTO users (full_name, email) VALUES ($1, $2)
    `, user.FullName, user.Email)
    return err
}

Extracting Address Columns into a Separate Table

Expand

CREATE TABLE addresses (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id) UNIQUE,
    street VARCHAR(255),
    city VARCHAR(100),
    postal_code VARCHAR(20)
);

Dual‑write Deployment

func (r *UserRepo) UpdateAddress(ctx context.Context, userID int, addr Address) error {
    tx, err := r.db.BeginTx(ctx, nil)
    if err != nil {
        return err
    }
    defer tx.Rollback()

    // Write to old columns (for v1 instances)
    _, err = tx.ExecContext(ctx, `
        UPDATE users
        SET address_street = $1, address_city = $2, address_postal_code = $3
        WHERE id = $4
    `, addr.Street, addr.City, addr.PostalCode, userID)
    if err != nil {
        return err
    }

    // Write to new table (for v2 instances)
    _, err = tx.ExecContext(ctx, `
        INSERT INTO addresses (user_id, street, city, postal_code)
        VALUES ($1, $2, $3, $4)
        ON CONFLICT (user_id) DO UPDATE SET
            street = EXCLUDED.street,
            city = EXCLUDED.city,
            postal_code = EXCLUDED.postal_code
    `, userID, addr.Street, addr.City, addr.PostalCode)
    if err != nil {
        return err
    }

    return tx.Commit()
}

Read with Fallback

func (r *UserRepo) GetAddress(ctx context.Context, userID int) (*Address, error) {
    // Try new table first
    row := r.db.QueryRowContext(ctx, `
        SELECT street, city, postal_code FROM addresses WHERE user_id = $1
    `, userID)

    var addr Address
    err := row.Scan(&addr.Street, &addr.City, &addr.PostalCode)
    if err == nil {
        return &addr, nil
    }
    if err != sql.ErrNoRows {
        return nil, err
    }

    // Fall back to old columns
    row = r.db.QueryRowContext(ctx, `
        SELECT address_street, address_city, address_postal_code
        FROM users WHERE id = $1
    `, userID)
    err = row.Scan(&addr.Street, &addr.City, &addr.PostalCode)
    return &addr, err
}

Backfill

INSERT INTO addresses (user_id, street, city, postal_code)
SELECT id, address_street, address_city, address_postal_code
FROM users
WHERE address_street IS NOT NULL
ON CONFLICT (user_id) DO NOTHING;

Contract

ALTER TABLE users
    DROP COLUMN address_street,
    DROP COLUMN address_city,
    DROP COLUMN address_postal_code;

After all instances run the new code, remove the dual‑write and fallback logic.

Changing Price from INTEGER (cents) to DECIMAL (dollars)

Expand

ALTER TABLE products ADD COLUMN price_decimal DECIMAL(10,2);

Dual‑write Deployment

func (r *ProductRepo) UpdatePrice(ctx context.Context, id int, cents int) error {
    dollars := float64(cents) / 100.0
    _, err := r.db.ExecContext(ctx, `
        UPDATE products SET price = $1, price_decimal = $2 WHERE id = $3
    `, cents, dollars, id)
    return err
}

Backfill

UPDATE products SET price_decimal = price / 100.0 WHERE price_decimal IS NULL;

Contract

ALTER TABLE products DROP COLUMN price;
ALTER TABLE products RENAME COLUMN price_decimal TO price;

References

• Deployment Strategies Visualized – visual guide to rolling, blue‑green, canary, and progressive rollouts.