Job migrate-one-table-column-change-live · revised 11 October 2026
Change one column on one large table in steps, with every row reconciled and a way back
Change one column of a large PostgreSQL or MySQL table in steps while the app runs: new column added, backfilled, reconciled row by row, then switched, with the old column kept until sign-off.
You might be seeing
- A rehearsal of the single ALTER on a copy shows a long lock or a full table rewrite
- The change has been postponed because nobody trusts a one-shot migration
- A column's current type will soon be too small for the values it must hold
No passwords, keys, card details or admin invites needed to start.
What usually happened
On a large table a column change can rewrite every row while holding a lock that blocks other work. The PostgreSQL documentation says ALTER TABLE takes its strictest lock by default unless a form is listed as weaker, and that changing a column's type normally rewrites the whole table and its indexes. MySQL's online-DDL table says that changing a column's data type is not done in place and does not allow concurrent writes. The usual alternative is to add a new column, fill it in batches and switch the application over later, but each stage can go wrong: the batches can miss rows written meanwhile, the converted values can differ from the old ones, and the switch can happen before the data is right.
Who it’s for: An engineering lead who must change a column on a table that is too large or too busy for a single blocking ALTER, and who needs proof that no row was changed wrongly.
Usually starts when: An integer key is running out of range, a text column must be split, a date column needs a time zone, or a rehearsed ALTER on a copy took far longer than the maintenance window allows.
The result: One agreed column change on one table is complete in separate steps. A new column holds a converted value for every row, equal to the old column under a comparison you agreed, rows written during the change are covered, the application reads the new column on staging, and the old column is untouched until you sign off. A verified backup is the restore point before any production step, and each step has a rehearsed way back.
Check whether this job fits
Five short questions. Your answers stay on this page unless you choose to email them.
Checks you can run yourself
Estimate the table's size (PostgreSQL)
Ask your database owner to run this read-only query and send the two numbers. Replace the table name.
psql -c "SELECT reltuples::bigint AS row_estimate, pg_size_pretty(pg_total_relation_size('YOUR_TABLE')) AS total_size FROM pg_class WHERE oid = 'YOUR_TABLE'::regclass"Look for: A row estimate and a size. The fixed scope covers up to 50 million rows.
Estimate the table's size (MySQL)
Ask your database owner to run this read-only query and send the two numbers. Replace the database and table names.
mysql -e "SELECT table_rows, ROUND((data_length + index_length) / 1073741824, 1) AS gigabytes FROM information_schema.tables WHERE table_schema = 'YOUR_DB' AND table_name = 'YOUR_TABLE'"Look for: A row estimate and a size in gigabytes. MySQL's estimate is approximate for InnoDB.
What you get
- The migration scripts and the application change that keeps the new column current, as a pull request
- The rehearsal log: lock waits, batch timings, the restore test and the final row-by-row comparison
- A written runbook for your database owner, whose step zero is a verified backup and a go/no-go check, followed by the order of steps, checks between them and the way back
- The reconciliation query that you can re-run at any time
Included
- One table of up to 50 million rows on PostgreSQL 13 or newer (13 is out of support and accepted as a starting version only), or MySQL 8.0 or 8.4 with InnoDB, in a database you control
- One agreed change to one column: a type change, or splitting one column into two, with the conversion rule written down and signed off before work starts
- The migration split into steps: a verified backup as the restore point, then add the new column, keep it current for new writes through the application's own write paths (we add no database trigger), backfill existing rows in batches, reconcile, switch reads, and leave the old column in place
- Lock settings and batch sizes chosen from a rehearsal on a copy of production size, with the longest lock wait recorded
- A comparison of every row, old against new, a restore test of the backup on the rehearsal environment, and a rehearsed way back for each step
Not included
- Running the steps on production, which your database owner does under your accounts
- Dropping the old column, which is a later step you choose after sign-off
- A change of database engine or major version
- Performance tuning or schema redesign beyond the one column
- Tables that already have triggers, replication or change-capture tools, which need their own plan; no database trigger is added to keep the columns in step
How we know it’s done
Agreed with you before work starts. Each check produces evidence you keep.
On the production-sized copy, after the backfill, the count of rows where the new column differs from the old column under the agreed conversion is zero, with any listed exceptions accepted by you in writing
Evidence: The reconciliation query and its output, plus the exceptions list; the command shown is the form for an integer-to-bigint change on PostgreSQL
SELECT COUNT(*) FROM your_table WHERE new_column IS DISTINCT FROM old_column::bigint;While a scripted write load runs on the copy, the longest lock wait recorded across all steps stays under the limit you agreed before work started, and rows written during the backfill also match under the same comparison
Evidence: The lock-wait log and a second reconciliation run after the load finished
With reads switched to the new column on staging, the application's tests and the agreed flows pass, and switching reads back restores the same results
Evidence: Test output for both settings
The way back for each step has been run on the copy and leaves the old column and its data unchanged
Evidence: The rehearsal log of each way-back run with before and after row counts
Restore test: the backup in step zero of the runbook, taken from the production-sized copy, is restored into a separate instance on the rehearsal environment, the table there has the same row count as the copy it was taken from, and the reconciliation query returns the same result on both
Evidence: The backup and restore log with row counts and reconciliation results from both, and the completed go/no-go list from the runbook
Sign-off. You review the conversion rule, the reconciliation, the restore test and the lock-wait evidence and agree the runbook with your database owner. You sign off when the checks pass on the copy; running the steps on production, starting with the step-zero backup, is your owner's decision and responsibility, and the backup is kept until you sign off.
If it fails. If the agreed acceptance checks do not pass, you do not pay and you keep the scripts, the rehearsal logs and the findings. Your production database is not touched by us.
When it fits, and when we stop
It fits when
- The database is PostgreSQL 13 or newer, or MySQL 8.0 or 8.4 with InnoDB; the PostgreSQL project no longer supports 13, so it is accepted as a starting version only
- A copy of the schema and a production-sized synthetic or masked table can be created for rehearsal
- The conversion rule can be stated in plain terms, including what happens to nulls and values that do not convert
- The application's write paths to the column can be listed and changed
- A database owner on your side can run the steps and the way back
We stop and tell you if
- The table is written by many applications or scripts that cannot be listed and changed
- The column is part of a key that is referenced by foreign keys on other large tables, which needs its own plan
- A rehearsal copy of production size cannot be made without real customer data
- The conversion rule cannot be stated, or values exist that nobody can say how to convert
- Logical replication or change capture reads this table and the owner cannot rehearse the change with it
What could go wrong
Step zero of the runbook, before any production step, is a verified backup of the database (or of the table) as the agreed restore point: it is restored into a separate instance with matching row counts, checked in the go/no-go list, and your database owner keeps it untouched until you sign off. Each later step has a way back that was rehearsed on the copy. Until you sign off, the old column is untouched and still current, so switching reads back restores the old behaviour. The new column can be dropped without affecting the old one. Dropping the old column is a separate later step with its own backup and checks.
Scroll the table sideways to read it all.
| Risk | How we handle it |
|---|---|
| The backfill misses rows written while it runs | New writes are kept current from the first step, and the reconciliation compares every row, so a missed row is reported, not assumed away. |
| A step takes a lock longer than the application tolerates | Lock timeouts are set for each statement, batch sizes come from the rehearsal, and the longest lock wait is recorded and compared with the limit you agree. |
| Converted values differ from the old ones in edge cases | The conversion rule is signed off by the data owner and every row is compared, including nulls and out-of-range values, with exceptions listed. |
| Reads are switched before the data is right | Switching reads is a separate step after reconciliation passes, with a rehearsed switch-back. |
A second reviewer reads every script for locks and for writes the backfill could miss, re-runs the row comparison on the copy and checks the runbook's step zero, restore test and way back at each step.
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 column, the conversion rule and the definition of done, then create a production-sized masked copy and record the table's size, indexes and writers
- Write the runbook's step zero: the backup that is the restore point, how it is restored, and a go/no-go check listing the verified backup, the free disk space and the lock timeouts; take that backup of the copy, restore it into a separate instance and compare row counts
- Time the single-statement change on the copy with a scripted write load and record the lock wait, to confirm the step-by-step route is needed
- Write the step scripts: add the new column, keep it current for new writes in the application's own write paths, and backfill in batches with a lock timeout and a pause between batches
- Run the whole sequence on the copy with the write load, record the longest lock wait and batch timings, and compare every row old against new
- Switch reads to the new column on staging and run the application's tests and the agreed flows, then rehearse the way back at each step
- Independent review of the scripts, the comparison and the runbook, then hand over the pull request and the rehearsal evidence; your database owner runs production
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, any licences and necessary permissions are in place. Hosting, platform and supplier 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 follow-on to drop the old column once you are satisfied, or a monthly service that keeps the database and application dependencies on supported versions.
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
- For a small table, a single ALTER in a quiet window may be fine. The PostgreSQL and MySQL documentation describe which forms rewrite the table or block writes, so a rehearsal on a copy tells you whether you need steps at all. www.postgresql.org
- MySQL's online DDL reference lists which operations run in place and which block writes. Adding a column is instant by default in 8.4 but changing a column's data type is not done in place. dev.mysql.com
Questions
Can't I just run the ALTER?
Often yes, on a small table. On a large one, the database documentation says some forms rewrite the whole table or block writes. We time it on a copy first, and only use steps if the single statement is not acceptable.
Do you run this on our production database?
No. We rehearse on a copy and hand your database owner a runbook. Your owner runs the steps under your accounts.
How do you know the backup works?
The runbook starts with a backup of the database. The rehearsal restores a backup of the copy into a separate instance and compares row counts, and your database owner repeats that check before production and keeps the backup until you sign off.
What if some values cannot be converted?
They are listed as exceptions in the comparison. You decide what happens to each kind before the new column is used.
Send an enquiry
Send us
- The database engine and version, and the table's approximate row count and size
- The column, its current type and the type or split you want, in words
- How the application writes to the column, and any other writers such as scheduled jobs
- Why now, and any maintenance window you work within
Later, once you agree
- A schema-only export, and a synthetic or masked copy of the table at production size, through the agreed company-controlled secure handoff
- A controlled copy of the application source with secrets removed
- The agreed conversion rule, signed off by the owner of the data
- A database owner available for the rehearsal review and the production steps
- How you take and restore a backup of this database today, or agreement to the backup procedure in our runbook
- A company-controlled secure handoff agreed before access: no live passwords, keys, private code or customer records by ordinary email.
You own the database, the data and every credential. We rehearse on a copy with synthetic or masked data in an isolated environment, and your database owner runs the steps on production. We never ask for database passwords, connection strings or customer records in the first enquiry.
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 “migrate-one-table-column-change-live” as the subject.