Zero Downtime SQL Schema Migrations with Expand/Contract

Zero Downtime SQL Schema Migrations with Expand/Contract

id: hook

Migrating SQL schemas without blocking writes requires a careful balance of design and execution. The expand/contract pattern is your best bet: add new columns (expand) and then update existing rows (contract) in a way that avoids locks. For example, adding a status column to a users table can be done by inserting a new row with the new data and a flag, then updating the old rows in the background.

Use dual writes to keep both the old and new schema in sync. This means writing to both the original and new table during the migration. Tools like sql-formatter help keep your DDL clean, while sql-explain can help you understand query performance during the transition.

Avoid using ALTER TABLE during peak hours. Instead, use a staging environment to test the migration flow. For instance, if you're adding a preferences column to a users table, you can first expand the schema by adding the column, then contract by updating the rows in a batch process.

If you're dealing with a large dataset, consider async backfills. This means you can write new data to the new schema while old data is gradually migrated. This approach ensures that writes are never blocked, and reads can continue uninterrupted.

For a visual guide, here's a flowchart of the expand/contract pattern:

Migrating a User Table with a New Column

id: use-case

Adding a new column like is_verified to a user table in production requires careful handling to avoid downtime. Use the expand/contract pattern: first add the column to a new table, then switch to it. This avoids locking the original table during the migration.

-- Step 1: Create new table with new column
CREATE TABLE users_new (
    id INT PRIMARY KEY,
    name TEXT,
    email TEXT,
    is_verified BOOLEAN DEFAULT FALSE
);

-- Step 2: Copy data from old table to new
INSERT INTO users_new (id, name, email)
SELECT id, name, email FROM users;

-- Step 3: Rename tables
ALTER TABLE users_new RENAME TO users;

This approach ensures that existing queries continue to use the old table until the new one is fully populated. Use a tool like sql-formatter to keep your DDL clean and readable.

For the is_verified column, you’ll need to run a backfill query to update existing rows. Avoid using UPDATE on the original table, as it can lock it. Instead, run the backfill on the new table after the rename.

Q: What if the new column has a default value? A: Set a default in the new table, then run a backfill to update rows. This avoids needing to lock the original table.

Q: How do I handle indexes or constraints? A: Add them to the new table during creation. If you need to move indexes, use CREATE INDEX on the new table after the rename.

Q: What about application code that uses the old table? A: Ensure your application uses the new table name after the rename. Use a tool like sql-explain to verify query execution plans and ensure no accidental use of the old table.

Step-by-Step Schema Migration

To add a new column like is_verified to an existing table without downtime, use the expand/contract pattern. Start by adding the new column with a default value, then update the data in a batch or background job.

-- Step 1: Add new column with default value
ALTER TABLE users ADD COLUMN is_verified BOOLEAN DEFAULT FALSE;

Next, run a backfill job to update the is_verified column based on business logic. This avoids locking the table during the migration. For example, you might set is_verified = TRUE for users who have completed a verification step.

-- Step 2: Backfill data (run in a batch job)
UPDATE users SET is_verified = TRUE WHERE email_verified = TRUE;

During this time, existing queries can still read and write the users table. The new column is optional, so queries that don’t need it can ignore it. This is safe because the column has a default.

If you need to remove the column later, use the contract phase. First, ensure all data is migrated, then drop the column.

-- Step 3: Drop column after data is migrated
ALTER TABLE users DROP COLUMN is_verified;

This pattern avoids long-running locks and ensures zero downtime. Use it for new columns that require backfilling, but skip it for columns that can be safely omitted or are not needed by existing queries.

Expand/Contract Migration Flow

This flowchart illustrates the core of an expand/contract migration. You begin by adding a new column to a new table, which avoids locking the existing table. Next, you update your application logic to write to the new table. After that, you run a backfill process to populate the new column using data from the old table. Once the backfill is complete, you switch all read/write operations to the new table and drop the old one. This approach ensures zero downtime and avoids long-running locks.

The key is to keep the backfill process simple and efficient. For example, if you're adding a status column to a users table, you can backfill it with a query like:

UPDATE new_users
SET status = (SELECT status FROM old_users WHERE old_users.id = new_users.id);

This avoids complex joins and ensures the backfill runs quickly. Always test the backfill logic in a staging environment before running it in production. Also, make sure your application can handle reads from both tables during the transition phase. Tools like sql-formatter and sql-explain can help you analyze and optimize your queries during this process.

Common Gotchas in Expand/Contract

id: pitfalls

Avoid data inconsistencies by ensuring all backfill jobs complete before dropping old columns. Use a CHECKSUM or HASH to verify data integrity during the backfill. If you skip this step, you might end up with a mix of old and new data in the same table, leading to logic errors in queries.

Lock contention is a common issue when using SELECT FOR UPDATE or LOCK IN SHARE MODE. These locks can block other queries, especially in high-throughput systems. Instead, use READ UNCOMMITTED or READ COMMITTED with explicit transaction boundaries to minimize lock contention.

Incomplete backfills are a risk when using INSERT or UPDATE to migrate data. Always validate that the backfill covers all rows, including edge cases like NULL values or rows with default values. Use a COUNT(*) or SUM(CASE WHEN ...) to ensure the backfill matches the expected number of rows.

Dual writes can be tricky. If you write to both the old and new columns, ensure that the old column is not used in any new queries. Use ALTER TABLE with DROP COLUMN only after confirming that all queries have been updated to use the new column.

Backfill timing is critical. If you drop the old column before the backfill completes, you'll lose data. Always run the backfill in a separate transaction or batch process to avoid partial updates.

Use EXPLAIN to understand how your queries interact with the new schema. Tools like sql-explain can help you spot performance issues or unexpected query plans.

Avoid using RENAME unless you're certain the old column is no longer needed. It's safer to keep the old column until all queries are updated.

Never assume that a SELECT will return the same data as before the schema change. Always test with a sample dataset before applying changes to production.

Use CHECKSUM or HASH to verify that the data in the new column matches the old column. This helps catch any data loss or corruption during the backfill.

When Expand/Contract Fails

id: when-not

Expand/contract is ideal for additive changes like adding columns or indexes. It fails when you need to drop columns, change data types, or modify existing data. For example, removing a status column from a users table requires a full table rewrite, which blocks reads and writes. In such cases, use a staging table with a DELETE or UPDATE to migrate data, then rename tables. This approach avoids locks but requires careful planning.

Q: What if I need to change a column's data type, like from VARCHAR(255) to TEXT?

A: You can’t use expand/contract here. Instead, create a new column with the desired type, copy data using a UPDATE, and drop the old column. For example:

ALTER TABLE users ADD COLUMN new_status TEXT;
UPDATE users SET new_status = status;
ALTER TABLE users DROP COLUMN status;

This avoids locks but requires a backup and careful testing.

Q: How do I handle a column that needs a default value or a check constraint?

A: Add the column with a default, then update rows to apply the constraint. For example:

ALTER TABLE orders ADD COLUMN payment_status VARCHAR(20) DEFAULT 'pending';
UPDATE orders SET payment_status = 'pending' WHERE payment_status IS NULL;

This works for additive constraints but not for removing them.

Q: What if I need to split a column into multiple columns?

A: Add new columns, populate them with data, then drop the old column. For example:

ALTER TABLE products ADD COLUMN category VARCHAR(50);
ALTER TABLE products ADD COLUMN subcategory VARCHAR(50);
UPDATE products SET category = 'electronics', subcategory = 'smartphones' WHERE id = 1;
ALTER TABLE products DROP COLUMN type;

This method avoids downtime but requires a data migration plan. Always test in a staging environment before applying to production.

Quick Answers for AI Search

Q: How do I add a new column to a large table without downtime?
A: Use the expand strategy: add the new column with a default value, then update rows in batches using a background job. Avoid ALTER TABLE with DROP COLUMN unless you can afford a full table lock. SQL Formatter helps structure the migration.

Q: What if I need to change a column's data type?
A: For nullable columns, use ALTER TABLE to change the type, then run a UPDATE to convert non-null values. For non-nullable changes, use a temporary column and a CONCAT or CAST during the contract phase. SQL Explain can help analyze query performance.

Q: How do I handle backfills without blocking reads?
A: Split the backfill into smaller chunks using a WHERE clause with a progress counter. Use a background worker to process each batch, and mark the migration as complete once all rows are updated. Avoid LOCK or SELECT FOR UPDATE to prevent contention.

Zero Downtime with Expand/Contract

Expand/contract is the best way to migrate SQL schemas without downtime. You add a new column to the table, populate it with default values, and then update the old data in a batch. This avoids locking tables and keeps your app running. Use ALTER TABLE with ADD COLUMN for the new column, then run a backfill query that updates the old rows.

For complex migrations, split the work into smaller steps. For example, if you're adding a status column to a user table, first add the column with a default value, then run a backfill that updates the status based on existing data. This keeps the migration simple and avoids long-running transactions.

If you're using PostgreSQL, you can use pg_trgm or GIN indexes for faster lookups during backfills. For MySQL, consider using FULLTEXT or SPATIAL indexes where applicable. Always test your backfill queries in a staging environment before running them in production.

For managing migrations and backfills, use sql-formatter to keep your SQL clean and sql-explain to understand query execution plans. These tools help you spot performance issues early and avoid accidental downtime.

If you're not using expand/contract, you're probably using a suboptimal approach. Avoid long-running ALTER TABLE operations and always plan for backfills. If you're migrating a large table, split the work into smaller batches to prevent blocking other queries.