A schema migration can fail even if the code change has passed testing. In production, an application does not usually change all at once: web processes, queue workers, scheduled tasks, and replicas running different versions may coexist. If a new version removes a column that an old worker still reads, or if a column becomes mandatory before all writers populate it, the deployment is no longer compatible.
Zero-downtime database migrations in PHP treat the schema and data as components of an operational contract. The goal is not only to execute a correct DDL statement, but to keep reads and writes available while old and new versions coexist, and to retain a realistic recovery path.
Why the schema can break already tested code
Local tests usually start from a database created from scratch or updated instantly. That scenario omits the transition: incomplete historical data, millions of rows, locks, persistent connections, and asynchronous consumers. An apparently minor change can cause errors or degradation.
- Renaming or removing a column breaks queries, ORM mappers, reports, and processes that still use the previous name.
- Adding a
NOT NULLconstraint fails if old rows have no value or if a writer does not yet know about the new field. - Changing a type can truncate values, alter comparisons, invalidate indexes, or trigger costly conversions.
- Creating an index or rewriting a large table can hold locks and increase the latency of normal operations.
- A bulk update in a single transaction can exhaust the transaction log, compete for resources, or make replication more difficult.
The relevant question is: which code versions can read and write each representation of a piece of data throughout the entire deployment window? The answer must include executables that do not restart automatically, not just HTTP requests.
Temporary compatibility between code, data, and processes
During a gradual deployment, at least three states must be compatible: old code, new code, and data in old, new, or partially transformed formats. Compatibility does not necessarily mean that every consumer understands every format forever; it means defining a bounded window in which predictable combinations work.
For example, to replace full_name with first_name and last_name, it is not advisable to delete the original field at the outset. The new version can write both formats and read the new fields first when they are complete, with an explicit fallback to the old value. The previous version continues to operate with full_name. Once the historical data has been transformed and old consumers have been retired, reads can depend only on the new structure.
Do not let temporary compatibility be scattered across controllers. Centralize reading, writing, and normalization in a domain service or repository. This makes it possible to audit which version of the format is produced, which value takes priority, and when to remove the transitional logic. A migration template does not replace this compatibility model: the template executes changes; the model defines how the application behaves during the transition.
The expand, migrate, and retire pattern
1. Expand without invalidating current consumers
The first phase adds capabilities without removing existing ones: a nullable column, a new table, an additional index, or a parallel structure. It must avoid destructive changes and, when required by the engine, plan the creation method to reduce locking. Adding a column does not mean it is safe to immediately impose a default value, recalculate every row, or declare it mandatory.
Before executing the operation, review the table size, the most frequent queries, foreign keys, available space, replication load, and database engine-specific behavior. Rehearse on a representative copy or an environment with comparable volume and concurrency. Also define observable limits: duration, acceptable latency, error rate, and cancellation condition.
2. Deploy compatible writers and readers
Next, deploy code that understands both representations. New writers can perform dual writes if cost and consistency allow it. Readers must establish unambiguous precedence: read the new value if it is validated; otherwise, use the old one. Do not use an exception as a fallback mechanism, because it hides data defects and adds unnecessary work to the critical path.
Dual writes require explicit decisions. If an update affects both structures, determine whether it must be performed in the same transaction. If that is not possible, design idempotent reconciliation and metrics to detect divergences. Events, caches, APIs, and exports are consumers too: modifying only the PHP repository does not guarantee end-to-end compatibility.
3. Migrate historical data in a resumable way
After enabling compatible code, transform existing records in small batches. Each batch must be repeatable without duplicating effects or corrupting data. Use a stable key or persistent cursor, size limits, progress logging, and controlled retries. Avoid paginating with offsets over changing sets, as this can skip or reprocess rows.
$lastId = 0; // For a positive, increasing primary key.
while (true) {
$rows = $repository->findPendingAfterId($lastId, 500);
if ($rows === []) {
break;
}
foreach ($rows as $row) {
$repository->migrateIfNeeded($row);
$lastId = $row->id;
}
}This pattern requires findPendingAfterId() to return rows sorted in ascending order by the same key used as the cursor. The cursor starts at a value preceding the first valid identifier and advances only after processing each row; termination depends on the query returning no batch. In a resumed run, the confirmed value of $lastId must be persisted. migrateIfNeeded() must verify the current state and produce the same result if run again.
Measure pending rows, transformed rows, validation errors, and differences between formats. Do not declare the phase complete merely because the table has been traversed: also verify referential integrity, uniqueness, business totals, and samples of critical records.
4. Switch reads, observe, and retire
When historical data is complete and old processes have stopped running, switch reads to use only the new structure. This activation can be gradual through controlled configuration, but it must not be confused with deployment: deploying makes code available; activating changes which path traffic uses.
Observe query errors, unexpected null fields, functional discrepancies, response times, and worker health. Only after a defined observation window should you remove dual writes, transitional dependencies, and finally the old column, index, or table. Keeping obsolete structures indefinitely increases ambiguity and cost; removing them too early eliminates straightforward recovery.
Nulls, types, constraints, and indexes without stopping operations
A new column usually starts as nullable because historical records do not yet have it. The application must treat its absence as an expected state, not as an impossible case. After completing and validating the backfill, a constraint can be imposed, provided that all active writers supply a valid value.
For type changes, create a new column and convert values explicitly. This makes it possible to detect non-convertible values, apply rounding or normalization rules, and compare both results before replacing the previous column. Changing the type directly may be appropriate in limited cases, but it must be justified by engine behavior, volume, and query compatibility.
Indexes require an equivalent analysis. A new index can improve reads, but its construction consumes resources and an unsuitable creation strategy can block writes. Validate the execution plan of the query that needs it; do not add indexes based on intuition. If the engine offers lower-locking creation modes, understand their requirements and limitations before incorporating them into the plan.
Rollback: code rollback does not always imply data rollback
An operational rollback must be separated into decisions. While the old structure and dual writes exist, it is usually possible to return to the previous code. But if the new format has accepted information that the old model cannot represent, undoing the schema does not semantically recover that data.
- Reversible: disable a new read and return to the fallback, while keeping both structures.
- Compensable: correct or rebuild data from a defined source, using an audited process.
- Irreversible: delete a structure or accept transformations that lose precision without preserving the original.
Document the point of no return, the person responsible for authorizing it, the required backups or exports, and the procedure for pausing workers. A down() method in a migration tool is not, by itself, a rollback plan: it can revert DDL, but it does not guarantee the validity of data written during the transition.
Testing, evidence, and checklist
Test a compatibility matrix: old code with the expanded schema, new code with data not yet migrated, new code with transformed data, and asynchronous processes on mixed versions. Include interrupted and resumed migrations, invalid records, write concurrency, and restoration of an earlier version where applicable.
- Inventory affected tables, queries, workers, integrations, and reports.
- Define the temporary read and write contract, including null values and priorities.
- Separate expansion, compatible deployment, backfill, activation, and retirement into independent steps.
- Estimate the impact of DDL, indexes, and batches with representative data.
- Make the data process idempotent, resumable, and measurable.
- Establish integrity validations and post-change observation thresholds.
- Document rollback, compensations, and the point of no return.
- Retire old compatibility and structure only with evidence that no consumers remain.
Applied with discipline, this pattern turns a high-risk database change into a verifiable sequence. The key is to design coexistence as part of the product and operations, not as a hidden detail inside a migration.
