Modifying a column can be straightforward in a small database. In a large, active table, the same operation may require a lock, rebuild indexes, or compete for resources with production queries. The impact depends on the engine, its version, the type of change, and the configuration: don’t assume that a statement will be instantaneous or non-blocking.
Schema migrations on large tables separate structural changes from data transformations. The goal is to keep application versions compatible during the transition, measure the impact, and be able to stop the process. There is no universal recipe: check the engine’s capabilities and the application’s actual behavior.
Why a small change can block operations
Widening a column, adding a constraint, or changing a type can involve work proportional to the table’s size. Depending on the engine, the operation might hold locks, generate disk load, affect replicas, or wait for open transactions to finish. Even an operation considered online may briefly block or have limitations.
Assess the table’s size and growth, read and write load, long-running transactions, indexes, and available space. Consult the documentation for the specific version and test in a representative environment. Set operational limits for latency, locks, storage, and replication lag.
A conventional schema update changes structures, for example by adding a column. A data migration transforms or copies existing values, often row by row. They may be part of the same functional change, but they carry different risks and should be run and monitored separately.
Inventory readers, writers, and dependencies
Identify everything that reads and writes the data: PHP code, SQL queries, queue jobs, scheduled commands, imports, reports, and external services. Review which versions may coexist during a gradual rollout. Dynamic queries and consumers outside the repository matter too.
- Document the current and target formats, including nulls, default values, and conversion rules.
- Identify dependent indexes, foreign keys, constraints, and views.
- Check which components update fields partially and which ones write the data.
- Determine how to detect and repair invalid values.
This inventory determines the deployment order. An old version may fail if a column it still queries is removed. If you cannot identify all consumers, assume that old code could remain active for longer.
Split the change into compatible phases
A common pattern is expand and then contract: add the new structure without removing the old one, deploy compatible code, copy historical data, and switch reads. Only remove the old structure after verifying the result.
- Expand: add the new column or table without breaking versions in production. Consider creating indexes and constraints in a separate operation.
- Deploy compatibility: release readers that tolerate pending data and writers that keep both representations consistent.
- Complete the historical backfill: run it in batches and track progress.
- Switch usage: read primarily from the new structure and monitor errors, latency, and discrepancies.
- Contract: stop writing to the old structure first, then remove it in a later deployment.
Deploying code does not require exposing the functionality immediately. Confirm that each version works with the schema at every phase, even if the code needs to be rolled back.
Run a controlled backfill from PHP
Avoid loading the entire table into memory or keeping a global transaction open. A PHP console command lets you control batch size, log progress, and stop the process without tying it to the lifecycle of a web request. This PDO pattern uses optimistic concurrency. progressStore represents a progress record stored in the same database and committed in the same transaction as the batch rows.
$limit = 200;
$maxAttempts = 5;
$cursor = (int) $progressStore->load('backfill');
// Initial upper bound; this does not, by itself, cover late inserts.
$upperId = (int) $pdo->query('SELECT MAX(id) FROM records')->fetchColumn();
while ($cursor < $upperId) {
$done = false;
for ($attempt = 1; $attempt <= $maxAttempts; $attempt++) {
$select = $pdo->prepare(
'SELECT id, old_value, version FROM records
WHERE id > :cursor AND id <= :upper_id
ORDER BY id LIMIT ' . (int) $limit
);
$select->execute([':cursor' => $cursor, ':upper_id' => $upperId]);
$rows = $select->fetchAll(PDO::FETCH_ASSOC);
if (!$rows) {
$cursor = $upperId;
$done = true;
break;
}
try {
$pdo->beginTransaction();
$update = $pdo->prepare(
'UPDATE records SET new_value = :value, version = version + 1
WHERE id = :id AND version = :version'
);
foreach ($rows as $row) {
$update->execute([
':value' => transform($row['old_value']),
':id' => $row['id'], ':version' => $row['version'],
]);
if ($update->rowCount() !== 1) {
throw new VersionConflict('The row changed during the backfill');
}
}
$next = (int) end($rows)['id'];
$progressStore->saveWithinTransaction('backfill', $next);
$pdo->commit();
$cursor = $next;
$done = true;
break;
} catch (Throwable $e) {
if ($pdo->inTransaction()) $pdo->rollBack();
$retryable = $e instanceof VersionConflict
|| ($e instanceof PDOException && isRetryableDatabaseError($e));
if (!$retryable || $attempt === $maxAttempts) throw $e;
usleep(min(100000 * (2 ** ($attempt - 1)), 2000000));
// Reread the batch with the same cursor; progress has not advanced.
}
}
if (!$done) throw new RuntimeException('Batch pending; cursor has not advanced.');
}VersionConflict should be a custom exception, not a generic exception that groups transformation errors. This ensures that permanent failures in transform() stop the command for diagnosis. isRetryableDatabaseError() should recognize only transient errors classified for the engine and driver in use, such as deadlocks or timeouts. Other database failures stop the process. The attempt limit and bounded backoff prevent indefinite retries; if the attempts are exhausted, the command fails without advancing the cursor. Check how your PDO and engine combination reports rowCount().
Optimistic concurrency protection requires all writers to increment version in the same transaction in which they update the data. This contract covers PHP code, queues, imports, and external services, and must be implemented and verified before starting the backfill. If a writer does not comply, the comparison may not detect the race: update it, route it through a common mechanism, temporarily disable it, or use appropriate locks. Do not consider the protection valid until you have checked every writer against the contract.
The initial upper bound reduces the amount of work, but does not guarantee that it includes inserts committed late. When the pass finishes, run a reconciliation that explicitly searches for untransformed or discrepant rows, without relying on their IDs being greater than the cursor. Repair and verify those records again; repeat the pass until none remain, using a verifiable criterion and writers that maintain compatibility. If you cannot reliably detect and reconcile those rows, consider change capture or a maintenance window. Do not declare the historical backfill complete just because the cursor reached the initial upper bound.
The transformation should be idempotent, and progress should be committed atomically with the rows. If the cursor store does not share a transaction with the data, design resumption so that already committed rows can be safely repeated. Add pause options, batch limits, structured logs, and an exit code that reflects failures; tune batch size using measurements, not assumptions.
Avoid races with concurrent writes
Dual writes alone do not prevent every race. The backfill may read an old value; a concurrent write may update both fields; and then the backfill could overwrite the new field with a stale result. The version condition in the example prevents that update if the concurrent write incremented the version; the conflict is preserved because the cursor advances only after the entire batch is committed.
A conditional update based on the original value or appropriate locks can also be used, depending on the guarantees provided by the engine and the workload. Each alternative has different costs and semantics. Test the strategy on the specific engine with interleaved writes, deadlocks, and timeouts, and verify both the affected rows and the reconciliation before claiming that the process has completed the historical backfill correctly.
Verify before removing the old structure
The command finishing does not prove that the data is correct. Check that no rows remain pending, validate constraints, and compare results with the expected transformation. Review nulls and edge cases, and confirm that reads use the new field without degrading functional behavior or performance.
Also monitor infrequent processes, such as reports or periodic tasks. Keep the old structure while dependent readers or writers exist: its presence is a compatibility measure, not proof that the migration is complete.
Define pauses, rollback, and a maintenance window
Define signals for pausing: errors or latency above the agreed threshold, locks, excessive replica lag, resource pressure, or growing discrepancies. Determine who stops the process and how it resumes from the last committed batch. Monitor batch duration, errors, CPU, disk, and locks.
Rolling back code is not the same as rolling back data. After accepting writes only in the new format, a reverse transformation may lose information. Recovery might mean returning to a compatible version and keeping both structures, not undoing the data. A maintenance window may be preferable if consistency cannot be maintained across versions, if the required locks are unacceptable, or if you cannot verify the result while the application is active.
- Does the engine and its version allow the change with acceptable locking?
- Can old readers and writers coexist with the intermediate schema?
- Can the PHP command be paused and resumed without duplicating effects or skipping rows?
- Is there a tested strategy for concurrent writers, transient errors, and discrepancies?
- Will integrity and impact be measured before the old structure is removed?
