Job schema-add-column-with-backfill-and-tested-rollback · revised 11 October 2026
Add a column to a large table and backfill it safely, with a rehearsed rollback
One new column is added to a large PostgreSQL or MySQL table and backfilled in batches on a staging copy under write load, rows written meanwhile also get the value, and a rollback is actually run.
You might be seeing
- Earlier migrations on this table blocked writes or timed out during a release
- The team avoids touching the table because nobody is sure how long a change will take
- There is a migration written, but no one has rehearsed running it or undoing it
No passwords, keys, card details or admin invites needed to start.
What usually happened
Changing a large live table is risky because of the lock each step takes, the order of steps and the lack of a rehearsal. Some forms of ADD COLUMN rewrite the table or hold an exclusive lock, adding a NOT NULL or foreign-key constraint can scan every row, and a one-shot backfill updates every row in a single transaction. If it fails halfway, the way back is often untested. The risk sits in the choice and ordering of steps, not in the new column.
Who it’s for: An engineering manager or founder who must change the shape of a busy table and is worried about locking it or being unable to go back.
Usually starts when: A feature needs a new column, constraint or filled-in value on a table with millions of rows, and the last schema change on it caused a stall or a failed deploy.
The result: On a staging copy of realistic size, the column is added, backfilled and constrained while a synthetic write workload keeps running. Rows written during and after the backfill also receive the value, no lock wait is above the limit you set, and the rollback is run and shown to restore the previous structure. You receive the migration files and the rehearsal log.
Check whether this job fits
These questions decide whether the change can be rehearsed safely. Nothing here needs access.
Checks you can run yourself
Read the table size
On PostgreSQL, ask your engineer to run this against a copy or the primary. It only reads catalog data.
SELECT pg_size_pretty(pg_total_relation_size('orders')) AS total_size;Look for: The total size with indexes. Send the figure and an approximate row count; the number shapes the rehearsal.
What you get
- Migration files for your tool (Django, Rails, Alembic) or plain SQL scripts, split into small steps
- A backfill script with batch size and pause settings, and a resume log
- The column default or the pull request that sets the new column on each listed write path
- The rehearsal log, with step timings and any lock waits observed under the synthetic workload, and a comparison query you can re-run at any time
- A rollback script and the log of running it on staging, plus a runbook for your production window
Included
- One new column on one table in one PostgreSQL or MySQL database, filled from data already in that database by a rule you state; it adds a new value and does not replace or convert an existing column
- Choosing each step by the lock it takes on your engine and version, using the engine's documented behaviour
- A batched backfill that commits in small units and can be stopped and resumed
- Rows inserted or updated while the backfill runs, and afterwards, also receive the value: by a column default when the rule is a constant, or by a change to the application's own write paths (up to three, listed with you at intake); we add no database trigger
- A rehearsal on staging under a synthetic write workload, including running the rollback
Not included
- Running anything against production; you run the runbook yourself after a restore point you have tested
- Converting or splitting an existing column and switching the application over to the new one while the old one is kept: that is the separate job "Change one column on one large table in steps, with every row reconciled and a way back"
- Changing the type of an existing column in place, or more than one table
- Backfilling from an outside system or file; that is an import job
- Application changes beyond setting the new column on the listed write paths
- Repairing or reconciling your migration history; if your change sits behind a migration conflict, that is yours to settle first
- A promise of zero impact in production, where load and data differ from the rehearsal
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
On the staging copy, adding the column, running the backfill and adding the agreed constraint complete under the synthetic write workload with no statement waiting for a lock longer than the limit you set.
Evidence: The rehearsal log with step timings and the lock-wait record.
After the backfill, and again after the synthetic write workload has stopped, every row, including each row inserted or updated through a listed write path during the backfill, holds the value the stated rule gives, with zero mismatches in the comparison query.
Evidence: The reconciliation output taken both times: total rows (which moves with the workload's inserts), rows filled, mismatches.
The backfill can be stopped partway and resumed without processing any row twice.
Evidence: The log of a stop and resume run, with row counts per batch.
The rollback, run on staging after the backfill with the write workload stopped, returns the table structure to the starting listing and leaves the row count equal to the count taken just before the rollback, and your own test suite passes before the change and after the rollback.
Evidence: Before and after structure listings, the row counts before and after the rollback, and test output.
Sign-off. You read the rehearsal and rollback logs, repeat the comparison query if you wish, and sign off in writing. Payment follows sign-off; running the change in production stays your decision.
If it fails. If the rehearsal does not meet the agreed lock-wait limit or the rollback does not restore the starting state, you do not pay for this fixed scope and you keep the logs. If a step cannot be done without a long exclusive lock on your version, we say so and propose options in writing.
When it fits, and when we stop
It fits when
- PostgreSQL or MySQL on a version you can name; we confirm at intake which forms of the change your version can do without rewriting the table
- Your migration tool is Django, Rails or Alembic, or you accept plain SQL scripts
- A staging copy of the table at realistic size exists, with personal data removed or replaced
- The column's value can be worked out from existing data by a rule you can state in a sentence
- The application code that inserts or updates the table can be listed (up to three write paths), or the rule is a constant default
We stop and tell you if
- The table is partitioned, replicated or sharded in a way that cannot be reproduced on staging
- No restore point exists that you have restored successfully
- The backfill needs data from an outside system or a human decision per row
- The write workload cannot be described well enough to simulate
- The table is written by many applications or scripts that cannot be listed and changed
- The new column is a converted or split copy of an existing column that the application will start reading instead
What could go wrong
The rollback script undoes the steps in reverse order: constraints and indexes added, then the column. We run it on staging as part of acceptance. In production, you run it only after confirming your restore point. Values the application has written into the new column since go-live are lost when the column is dropped, and the runbook says so and says how to export them first. If application code has already started using the column, the runbook says to revert that code first.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| A step rewrites the table or holds an exclusive lock longer than staging suggested. | The runbook marks each step with the lock it takes and how long it ran on staging, and sets a lock timeout so a blocked step fails instead of queueing writes behind it. |
| The backfill overloads the primary or replication. | Batch size and pause are settings, tested under the synthetic write workload, and the runbook says what to watch. |
| Rows written while the backfill runs are left without the value, or a constraint rejects writes from a path that does not set the column. | The write paths are listed first, new writes fill the column before the backfill starts and the constraint is added last. The comparison is re-run after the write workload stops, and a write path that was missed shows up as mismatches. |
| The rollback works on staging but the application has begun using the new column. | The runbook orders the application revert before the column drop and states that values written since go-live are lost. |
A second reviewer checks the choice of each step against the engine's documented lock behaviour, reads the rehearsal logs for lock waits above the limit, confirms that every listed write path fills the column, and confirms that the rollback was run and not only written. Your engineer decides the production window.
How we deliver
We arrange the work and independent review, then show you the result against the agreed checks. You keep authority over your systems.
- Agree the table, the column, the fill rule, the write paths that insert or update the table, the lock-wait limit and the staging copy in writing
- Read the engine's documented lock and rewrite behaviour for each candidate step on your version, and choose the order
- Write the migration in small steps, the backfill as resumable batches and the change that fills the column on new writes; write the rollback alongside, not afterwards
- Run the sequence on staging under the synthetic write workload in this order: add the column, make new writes fill it, backfill existing rows, then add the constraint. The constraint comes last because on PostgreSQL a constraint added as NOT VALID still applies to new inserts and updates, so a write path that omitted the column would fail
- Stop the write workload and re-run the comparison query: every row, including those written during the backfill, must match the rule
- Stop the backfill partway and resume it, then run the rollback and compare structure and row counts with the starting state
- Independent review of the files and the logs, then hand over the runbook
This is a one-off job, not emergency cover or a subscription. We confirm eligibility, the total price, a start window and a delivery date before you accept. Work starts only after agreed inputs, secure access and any permissions are in place. Hosting and database charges are excluded unless the written quote includes them. No charge or booking is created by an enquiry.
Need to keep it working?
Discuss a project that covers several schema changes in sequence, or a monthly review of table growth.
Ongoing work is separately scoped and quoted: no monitoring, response-time guarantee or automatic subscription is included in this job.
Explore an ongoing engineering lane, or mention the responsibility you need in your enquiry.
What you can check
This is a new service. We have not delivered this job for a client yet.
Other ways to get this done
- PostgreSQL documents which ALTER TABLE forms rewrite a table, which take which lock, and how NOT VALID plus a later validation avoids a long exclusive scan. Your engineer can follow it directly. www.postgresql.org
- The MySQL 8.4 manual lists which online DDL operations allow concurrent changes, including adding a column with the INSTANT algorithm. dev.mysql.com
Questions
Can you promise zero downtime?
No. We rehearse on staging under a synthetic write load and report every lock wait. Production load differs, so the runbook names which steps take locks and sets timeouts so a blocked step fails safely.
What about rows written while the backfill is running?
They are part of the job. Before the backfill starts, new inserts and updates fill the column, through a column default or a small change on the write paths we list with you. After the write load stops we re-run the comparison, and every row, new ones included, must match.
Why rehearse the rollback?
A rollback that has never run is a guess. The job is accepted only after the rollback has been run on staging and the starting structure restored. Values written into the new column after go-live are lost when it is dropped, and the runbook says so.
How is this different from changing an existing column?
This adds a new column that holds a new value. The job that changes an existing column, for example a type change or a split, also keeps the old column current, switches the application to the new one and compares every row old against new; it is priced and tested separately.
What if my table is much bigger than 20 GB?
It is quoted after we have seen the table and write pattern. The method does not change, but the rehearsal copy and time do.
Send an enquiry
Send us
- The engine and version, the table name, its approximate row count and size, and how often it is written
- What the new column holds, the rule that fills it, and whether it must be NOT NULL or reference another table
- Which parts of the application insert into or update the table
- The migration tool you use, and the longest write stall you could tolerate
- Do not send credentials, a dump or real rows in the first enquiry
Later, once you agree
- A staging database restored from a backup, with personal data removed or replaced consistently
- Your migrations folder through an authorised company-controlled repository route
- A description of the write workload to simulate: statements, rates and peak, and the application write paths that insert or update the table
- The agreed lock-wait limit and the date of your tested restore
You own the database, the code and every credential. We work on a copy you prepare, such as a staging database restored from a backup with personal data removed or replaced, and we hand work back as a pull request or a script. We never ask for production passwords, and we do not connect to your production database. You apply any change to production yourself, after a restore point that you have tested.
Email fallback: open your mail app
If website submission is unavailable, review and send the fallback email yourself. An email fallback is not a website receipt. Or write to hello@syntheticindustry.ai with “schema-add-column-with-backfill-and-tested-rollback” as the subject.