How do you change a live database schema safely?
A safe database schema migration keeps the running application compatible with each intermediate schema. Add the new structure, make every writer maintain compatible data, backfill and check existing records, then switch readers. Remove the old structure only after its users and rollback needs are understood. Each stage needs a stop decision; the pattern reduces risk but does not guarantee uninterrupted service.
A schema defines the tables, columns, and rules that an application uses. Changing it while several application versions, reports, or background jobs are running can break a system even when the SQL succeeds. If you are still choosing a data model, start with the relational database guide. This guide covers a later decision: how to release a change to an existing live schema.
What must the team identify before changing a schema?
List every reader and writer of the affected data, including old application instances, workers, imports, reports, integrations, and scripts. Record their owners and release schedules. A job that runs monthly may be absent from recent logs and still depend on the old column.
Write down the current rule, the proposed rule, and how existing values map to the new one. Check whether old data has nulls, duplicates, values that cannot be converted, or exceptions that a new constraint would reject. Decide which representation is authoritative at each stage and how the team will detect a mismatch.
Agree on an acceptable interruption and data-loss limit with the people who operate the system. Rehearse the migration and recovery on representative data and the actual database version. AWS guidance on schema compatibility recommends testing coexistence of versions and a rollback deployment. A successful test does not eliminate production risk; data size, access patterns, and concurrent work can differ.
Use a release record to control the transition
The following record is a planning tool for one change. Fill in the actual owners, checks, and thresholds before the release. A stage is ready only when its evidence is available; an empty field is a reason to pause.
| Stage | Decision and evidence | Stop or recovery decision |
|---|---|---|
| Prepare | Name each reader and writer, the data mapping, the database version, and the release owner. Test the restore path and intermediate versions. | Delay if an owner, mapping, or recovery path is unknown. |
| Expand | Add the new structure while old code still works. Check the operation’s lock and resource behavior. | Stop if the change blocks work beyond the agreed limit. |
| Synchronize | Make every active writer maintain the required relationship between old and new data. Define how failures are detected and repaired. | Do not backfill or switch reads while an old-only writer can create untracked differences. |
| Backfill and verify | Copy existing data in bounded, restartable batches. Check missing values, mismatches, failed batches, and writes during the run. | Pause on agreed load, error, or mismatch thresholds. Repair and recheck before switching. |
| Switch | Move readers to the new representation in a controlled release. Check business results and the ability to return to the old reader. | Return reads only if old data is still current and compatible; otherwise use the prepared repair plan. |
| Contract | Retire old readers and writers, views, scheduled jobs, and rollback dependencies. Obtain approval to remove the old structure separately. | Keep the old structure while a dependency or recovery need remains. |
This sequence separates a code rollback from a data recovery. Rolling back the application does not automatically undo committed database writes. If the new code has already created data that the old code cannot represent, the team may need a forward repair or a controlled restore and reconciliation instead.
Walk through a column change
Suppose a booking application stores one contact_name field and needs a new display_name field. The following is a hypothetical planning example, not a tested implementation or a Hapy client case. Assume both fields carry the same value during the transition. A change that splits a field or loses information needs its own mapping and recovery rules.
Expand and protect writes
Add display_name without removing contact_name. Verify that the existing application and reports still work. Then establish a synchronization mechanism that covers every writer, including old application instances. Updating only the new application to write both fields is insufficient while an old worker can still update the old field alone. A database trigger is one possible mechanism, but its behavior, permissions, failure handling, and interaction with every writer must be designed and tested for the actual system.
Choose one authority for conflicting changes. For this example, keep contact_name authoritative until the switch. Ensure an old-only write also updates the new field, or capture and reconcile that change before using the new field. Do not assume a second write succeeds just because the first one did. Define how to detect and repair a partial failure.
Backfill, compare, and switch
Once the write path is protected, copy historical values to the new field in batches that can be restarted safely. A backfill is the work of filling the new representation for existing records. Record progress and failed batches. Monitor database load, lock waits, replication delay where relevant, and application errors; pause when the agreed thresholds are reached. GitLab’s background migration guidance calls for small, repeatable jobs and a final data-state check when data cannot be lost.
Compare the two fields using a check that handles nulls correctly. Include records created or changed during the backfill, not only the original batch. Investigate mismatches rather than overwriting a newer value with an older snapshot. Test inserts, updates, deletes, retries, and failed writes while old and new application versions coexist. Only then move a small set of reads to display_name and compare the business result. Increase use when the agreed checks pass.
Keep the synchronization and old column through the rollback period. Before switching authority to the new field, establish how old readers and a returned application version will receive current values. If that cannot be guaranteed, a simple application rollback is no longer a safe recovery plan.
Remove the old column later
Inventory remaining dependencies again after the switch. Include database views, external reports, scheduled jobs, infrequent integrations, and long-running instances. Observe them for a period that covers their actual schedules; quiet logs alone do not prove that none exist. Decide who accepts any unknown dependency and when the old application version stops being a supported rollback option.
Remove the old column in a separate reviewed change only after those conditions are met. GitLab documents why its own Rails processes and views require staged column removal. Its exact release procedure is specific to GitLab, but the dependency check applies to any team’s plan.
Which database operations can still interrupt work?
An additive change can be compatible with old code and still wait for a lock or consume enough resources to affect users. In PostgreSQL 18, the lock required by ALTER TABLE depends on the subcommand; the default is ACCESS EXCLUSIVE unless the documentation states otherwise. Test the exact operation, table size, and database version. Set an appropriate lock-wait limit and decide when to stop rather than allowing an unbounded wait.
An index may also affect a busy table. PostgreSQL’s CREATE INDEX CONCURRENTLY permits writes during the build, but has its own restrictions: it takes more work, cannot run inside a transaction block, and a failed build can leave an invalid index that needs attention. It is not a guarantee of zero impact. Check the database and hosting provider’s current documentation for your version.
If a change cannot meet the agreed availability or recovery limits, choose a maintenance window, reduce its scope, or redesign it. A planned interruption with a tested recovery can be safer than calling an untested sequence zero downtime.
What evidence should close the release?
The release owner should confirm that the new application produces the expected result, data comparisons pass, errors and load remain within agreed limits, and every known reader and writer has an owner. Record unresolved exceptions and who will handle them. Keep the recovery plan and the date or event for the separate cleanup decision visible to operations.
Hapy builds business systems and integrations. If your team needs help planning a change to a live system, bring the dependency list, proposed release stages, and recovery limits to the discussion.
