On a laptop, renaming a column is one statement and takes a second. In production it can cause an outage, not because the database struggles, but because of everything talking to it. Take an illustrative users table with 200 million rows, read and written by a service running on 10 instances behind a load balancer. The team wants to rename name to full_name. The moment the rename runs, every instance still running old code starts failing with “column does not exist”. Deploy the new code first instead, and it fails because full_name does not exist yet. There is no single instant at which you can switch the database and all ten servers together.
The way out is to stop thinking of a schema change as one step. Expand-and-contract (also called parallel change) breaks it into a sequence where, at every point, both the old and the new version of the application work against the current schema.
The underlying constraint
During a rolling deployment, instances are replaced a few at a time, so for several minutes old and new code run side by side. If a deployment goes wrong you also want to roll back, which means old code may come back after new code has run. So every schema state must be compatible with two consecutive versions of the code. Expand-and-contract is simply a disciplined way of honouring that rule.
The steps, in order
Step 1: Expand the schema. Add full_name as a new, nullable column. Old code does not know it exists and keeps working. On PostgreSQL, adding a nullable column without a default only changes the catalogue and is near-instant regardless of table size; since PostgreSQL 11, a constant default is handled the same way. MySQL 8.0 can add many columns with its instant algorithm. Even so, the statement needs a brief metadata lock, so set a short lock timeout and retry rather than letting it queue behind a long transaction and block everyone behind it.
Step 2: Write to both. Deploy code that writes the value to both name and full_name on every insert and update, but still reads from name. During the rollout, old instances write only name; that is fine, because the backfill in the next step and a final check will catch those rows. Some teams use a database trigger to copy the value instead of changing application code; it works, but adds hidden logic that must be removed later.
Step 3: Backfill existing rows. Copy name into full_name for all rows where full_name is empty. Never do this in one giant update: on 200 million rows it would run for a long time, generate enormous transaction logs, cause replica lag, and in MVCC databases leave a huge amount of cleanup work. Instead, walk the primary key in batches, perhaps 10,000 rows at a time, committing after each batch and pausing briefly if replica lag rises. At 10,000 rows per batch that is 20,000 batches; at around 200 milliseconds each, roughly 67 minutes of work, spread gently over a longer period.
Step 4: Verify. Check that no rows have full_name empty while name is filled, and spot-check that the values match. Rows written by old instances during step 2 are caught here. Only proceed when the columns agree.
Step 5: Switch reads. Deploy code that reads from full_name and still writes to both. If something is wrong, rolling back is safe, because name is still being kept up to date.
Step 6: Stop writing the old column. Once you are confident, deploy code that only uses full_name. If name has a NOT NULL constraint, drop that constraint first, otherwise inserts from the new code fail.
Step 7: Contract. After a safe interval, and after confirming nothing else reads name, drop it. “Nothing else” is the part people forget: reporting queries, ETL jobs, a data warehouse sync, a support team’s saved queries, another service reading the table directly. Search for the column name across repositories and ask the data team before the drop.
Why so many deployments for one rename?
Each step is individually safe and reversible, and that is the whole point. If the backfill causes replica lag, you pause it. If reading from the new column reveals a bug, you roll back the code and nothing is lost. Compare that with a maintenance window, where the rollback plan is often “restore from backup”, and a failure halfway leaves you in an unknown state. Seven boring deployments are cheaper than one exciting night.
It is also worth asking whether the rename is needed. Many ORMs let you map a property called fullName to a column called name. If the motivation is only that the column name reads oddly in code, an annotation might solve it with zero migration risk.
The same pattern, applied elsewhere
- Changing a column’s type, say from integer to bigint before ids run out, follows the same path: new column, dual write, backfill, switch, drop. Changing the type in place usually rewrites the whole table under a heavy lock.
- Adding a NOT NULL constraint is safest after the data is already clean. PostgreSQL lets you add a check constraint as NOT VALID and validate it later with a weaker lock.
- Adding an index on a large live table should use the non-blocking build your database offers, such as
CONCURRENTLYin PostgreSQL. - Splitting a table or moving data to a new service is expand-and-contract at a larger scale: write to both, backfill, compare, switch reads, then retire the old path.
- API fields work the same way. Add the new field, have clients move over, then remove the old one once logs show nobody uses it.
Tools that do some of the work
For MySQL, tools such as gh-ost and Percona’s pt-online-schema-change perform heavy table changes by building a shadow copy of the table, keeping it in sync, and swapping it in at the end. They solve the “table rewrite locks everything” problem. They do not solve the application compatibility problem: if old code needs the old column name, a perfectly executed rename still breaks it. Migration frameworks such as Flyway and Liquibase track which scripts ran where, but the sequencing across deployments is still a design decision your team makes.
The habit to build is to treat every schema change as a question: will the currently running code and the previous version both work with this schema? If the answer is no, split the change until it is yes.
Written by the RP Strativa Team. Table size, batch sizes and timings in the examples are illustrative.
Leave a Reply