Currently Available: Need a skilled Software Developer for your next project?
Categories
Databases

How to Add, Rename, and Backfill Database Columns Safely

When you change a database column in a production service, the service's code and the database rarely switch at the same instant. A rolling deployment replaces servers gradually, so old and new code run at the same time. If a migration removes or renames a column before old code stops using it, the old code sends queries for a column that no longer exists, and those queries fail.

A safer approach uses stages that let old and new code work with the database during the change: add the new column, make sure new writes keep it current, backfill existing records, switch reads, and remove the old column only after nothing depends on it. A backfill is a job that fills a new field for records already stored in the database. These stages also give you time to check the data before the new column becomes essential.

Add the column before code depends on it

Add the new column so existing versions of the service can use it. If the column needs values based on existing records, add it as nullable, meaning it can have no value. Then deploy code that handles empty values and fill the column before switching reads to it.

Even a quick column addition can wait to get a database lock. A lock controls which other operations can access a table while the database changes it. PostgreSQL documents that ALTER TABLE takes an ACCESS EXCLUSIVE lock unless a subcommand specifies another lock level. A long-running transaction can delay the lock. While it waits, requests can pile up and put more load on the database. Check the exact statement and database version. If it fits your deployment strategy, set a short lock timeout and retry. GitLab describes this practice in its own migration guidance, but its specific conventions are not universal.

Defaults need careful handling. PostgreSQL 11 and later can add a column with a default that does not change from one call to the next without rewriting every row during the schema change. A volatile expression, whose result can change each time it runs, such as clock_timestamp(), behaves differently and can require a table rewrite. A default sets values for later inserts; changing a default does not fill existing rows. See PostgreSQL’s ALTER TABLE documentation and table modification guidance.

MySQL's rules depend on the version and operation. For example, MySQL documents support for ALGORITHM=INSTANT when adding columns beginning with MySQL 8.0.12. “Instant” describes a supported database structure change. It does not guarantee that the statement will never wait for a metadata lock, which controls access to information about the table's structure. Check the deployed version, storage engine, and exact table definition against the MySQL online DDL documentation. If the database lets you require a particular DDL algorithm, doing so makes an unsupported operation fail instead of silently using a more disruptive method.

Rename in stages when deployments overlap

A direct rename changes the database’s column name immediately. A running service can fail if it still queries the old name. The same risk applies to a background worker, report, or scheduled job that still queries it. A direct rename is reasonable when you can coordinate a full stop or when every consumer changes together. During a rolling deployment, keeping both names available gives old and new code time to coexist.

For a staged rename, add the replacement column and deploy code that writes to both columns. Once no old code writes only to the original column, backfill the replacement and compare the values or reads before switching service reads to the new column. Keep the old column while your rollback plan depends on it, then remove it in a later deployment after every user of the column has stopped using it.

When code writes to both columns, decide which value takes priority and what to do if one write succeeds but the other fails. A trigger or another way to keep the columns in sync can serve the same purpose, but each option needs a clear rule for handling conflicts. Stripe describes a staged data migration that used dual writes, a backfill, read comparisons, and a gradual switch. Its online migration example shows the sequence, not one synchronization design for every service.

Backfill in batches and verify the result

For a large table, run the backfill separately from the schema change. Updating every row in one long transaction can hold locks, create heavy database load, and make recovery harder. A batch job that commits bounded groups of rows can pause, resume, and report progress without restarting from the beginning.

For example, a PostgreSQL job could update a range of account IDs at a time:

UPDATE accounts
SET display_name = legacy_name
WHERE id > :lower_id
  AND id <= :upper_id
  AND display_name IS NULL;

The ID range limits the work in each batch. The IS NULL condition prevents the job from replacing a value another writer has already set. The backfill can still race with service writes. Decide which write takes priority, and make sure the backfill cannot overwrite newer data from the service. Also confirm that NULL means “not yet filled” for this column. If NULL is a valid final value, use another way to track records that still need an update.

Keep transactions short and pace the job to match production load. Monitor database latency, lock waits, and replication lag, which measures how far a replica trails changes on the primary database. Record enough progress to resume after an interruption. Also define how to check that the job is done, such as counting records that still meet the conditions for an update. No batch size is safe for every database. Choose a size in an environment that represents production, then adjust it based on the load you observe. For more detail on this pattern, see backfilling a PostgreSQL column in batches.

Before switching reads, verify both completeness and correctness. Count missing values, compare old and new values, and check edge cases in the transformation. Stripe’s migration example used comparisons between old and new read paths before moving traffic. That kind of check can reveal errors that a successful job status cannot.

Require values only after existing rows are ready

A NOT NULL constraint tells the database to reject rows with no value in a column. If existing rows have no value when you add the constraint, the migration can fail or the database may need to check every row. On PostgreSQL, you can add a constraint first and check existing rows later. Check which constraint options your database version supports and how they affect locks. The PostgreSQL constraint guidance covers this staged approach.

Add the column, make new writes supply a value, backfill existing rows, check for rows that violate the requirement, and then enforce the constraint. Keep the service able to handle missing values until the backfill and checks finish. This keeps a deployment from depending on data that is not yet filled in.

Remove the old column after its last user is gone

Treat cleanup as a separate change from introducing the replacement. Before dropping the old column, check application code, background workers, scheduled jobs, reporting queries, and other database clients. Confirm that the old application version has stopped running and that your rollback plan no longer needs the old data.

A successful backfill does not prove that every consumer has switched. Once the new column serves reads and writes, keep the old one through the period when rollback requires it. Then remove the old code dependency and drop the column in a later migration. Before running that DDL, check its lock and rewrite behavior for the exact database version and table.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com

Email updates

Usually a new article and a few links I found interesting.

No spam. Unsubscribe with one click.

Leave a Reply

Your email address will not be published. Required fields are marked *