Adding Columns Without Stopping the Website:…

Adding a column to the users table sounds like a five-minute job. The problem arises when the table contains millions of rows, is accessed by many visitors, or is read by multiple versions of the application simultaneously. The seemingly trivial ALTER TABLE command can wait for a metadata lock, rebuild the table, or cause old application code to fail to read the new structure.

Therefore, database migration should be treated as part of deployment, not a manual task performed after the code is complete. The most useful principle is expand and contract: first expand the database structure, gradually move the application, and then remove the old parts once they are no longer in use.

Why can schema changes be risky?

A database is not just a place to store data. It is also serving queries from applications, background processes, reports, and possibly API integrations. When the table structure is changed, the database may require locks or need to copy data, depending on the type of operation and MySQL version.

MySQL supports several forms of online DDL on InnoDB tables. Some operations can run without stopping data changes, while others still require table rebuilding or heavier locks. MySQL documentation also emphasizes that this support depends on the type of change, engine, server version, and options like ALGORITHM and LOCK. Refer to the online DDL support table in the MySQL documentation before running migrations in production.

Another risk is the assumption that all changes can be rolled back with transactions. In MySQL, many DDL commands like ALTER TABLE, CREATE TABLE, and DROP TABLE can cause implicit commits. This means that ROLLBACK does not always revert to the state before the schema change. A list of commands that trigger implicit commits needs to be understood before mixing DDL with data changes.

The expand and contract pattern in practice

1. Expand: add compatible structure

Suppose the old application stores the full name in the name column, but the new version wants to separate the data into first_name and last_name. Do not immediately delete name or make it non-nullable. First, add the new columns, with rules that are still acceptable to the old application.

ALTER TABLE users
ADD COLUMN first_name VARCHAR(100) NULL,
ADD COLUMN last_name VARCHAR(100) NULL;

The columns are created as NULL at the initial stage to keep the old rows valid. If the MySQL version and table conditions support it, you can evaluate using ALGORITHM=INSTANT or ALGORITHM=INPLACE. Do not blindly copy these options; if the operation is not supported, MySQL may reject the command or use a more expensive strategy.

2. Backfill: gradually fill in old data

Once the columns are available, fill in the old data from name. For large tables, avoid a single massive UPDATE that runs for a long time and burdens the undo log. Work in batches based on the primary key or time range.

UPDATE users
SET first_name = ... , last_name = ...
WHERE id > 100000
  AND id <= 110000
  AND first_name IS NULL;

The batch size needs to be tested. Ten thousand rows may be safe on a testing server, but too large on a server with many active transactions. Monitor query duration, CPU usage, locks, and their impact on application latency.

3. Use the application to read both

In the transition phase, PHP code should not assume that new data is always available. When reading profiles, use the new columns if they exist; if not, use the old value as a fallback. This makes deployment more flexible when the backfill process is not yet complete.

$displayName = $user['first_name']
    ? trim($user['first_name'] . ' ' . $user['last_name'])
    : $user['name'];

For writing data, the new application can use the dual write pattern: storing values in the new columns while still updating the old columns temporarily. This pattern adds complexity, so use it only during the transition period and set a deadline for its removal.

4. Contract: remove old parts once safe

The old columns should not be deleted in the same deployment. Ensure that all endpoints, jobs, dashboards, and administrative scripts have stopped reading them. Search for column name usage throughout the repository, check raw queries, and observe application logs.

Once you are sure there are no old consumers, only then proceed with the next migration to remove unnecessary columns or constraints. It is advisable to carry out this step in a separate release to easily identify if issues arise.

What is the role of PHP transactions?

Transactions remain important for data changes that must succeed or fail as a unit, such as creating an order and order details. With PDO, the general pattern is beginTransaction(), executing queries, then commit() or rollBack(). PDO documentation explains the limitations and behavior of transactions.

However, transactions are not an undo button for all types of migrations. Do not assume the following code will always roll back schema changes:

$pdo->beginTransaction();
$pdo->exec('UPDATE users SET status = 1');
$pdo->exec('ALTER TABLE users ADD COLUMN verified_at DATETIME NULL');
$pdo->rollBack();

In MySQL, DDL commands can cause implicit commits. Separate schema migrations from data transactions, and create realistic recovery procedures: backups, reverse migrations if possible, or tested data correction steps.

Checklist before production migration

Test migrations on a copy of the database that closely resembles production size.
Check if operations require table rebuilds or metadata locks.
Add timeouts and a shutdown plan if locks take too long.
Ensure the old application remains compatible with the schema after the expand phase.
Run backfills in batches and measure their impact.
Store migration scripts in the repository, not just in personal notes.
Prepare backups and recovery procedures that have been thoroughly tested.
Remove old columns only after ensuring all readers and writers have migrated.

What does this mean for us?

Safe migration does not mean every change has to be complicated. The essence is to break changes into several small, compatible steps. This way, the application code and database do not need to change drastically at the same moment.

For small projects, this pattern may seem excessive. However, once a website starts receiving real transactions, has background processes, or is worked on by more than one person, phased migration becomes a cheap form of insurance. Five extra minutes to consider compatibility can save the team from downtime, unsynchronized data, and panic rollbacks during peak hours.

Sources & further reading

– Rio Yotto @rioyotto