Changing a column on a large table: add, backfill, reconcile, switch, and only then drop

What a single ALTER may do to a big table

PostgreSQL's documentation says ALTER TABLE takes an ACCESS EXCLUSIVE lock unless a particular form is listed as weaker, and that changing a column's type normally rewrites the whole table and its indexes, which can temporarily need up to twice the disk space. MySQL's online-DDL reference says adding a column is done in place and instantly by default in 8.4, while changing a column's data type is not done in place, rebuilds the table and does not permit concurrent writes. Neither statement tells you how long your table will take. Only a rehearsal on a copy of your table's size does.

• A change that is instant on a small table is not evidence for a large one.
• Read the page for your exact engine version before you plan.

Look for a cheaper form of the same change

Some changes have a lighter route. PostgreSQL's documentation says adding a column with a non-volatile default does not rewrite the table, and that setting a column to not-null scans the table unless a valid check constraint already proves there are no nulls. A constraint can be added as not valid, which skips the scan, and validated afterwards with a weaker lock that lets writes continue. Check whether one of these forms does what you need before building a longer procedure.

• A cheaper form may exist for part of the change, not all of it.
• Record the lock named in the documentation for each statement you will run.

The stepwise pattern

When no single statement is acceptable, the change is split into steps that can each be checked and reversed. Add the new column. Keep it current for new writes, in the application or with a database-side rule. Backfill existing rows in small batches, with a lock timeout on each statement and a pause between batches so other work gets through. Reconcile the old and new columns. Switch reads to the new column. Leave the old column in place until you have lived with the result, and drop it as a separate, later step.

• Before any production step, take a verified backup and prove that it restores; it is the restore point until you have signed off.
• Every writer to the column must keep the new column current, including scheduled scripts.
• Choose batch size and pause from the rehearsal, not from a guess.
• Do not drop the old column in the same change as the switch.

Reconcile every row, not a sample

The backfill and the live writes can disagree for rows changed while it runs. Compare every row, old against new, under the conversion rule you agreed, and list the exceptions, including nulls and values that do not convert. Run the comparison once after the backfill and again after the write load finishes on the rehearsal copy. A count of rows alone does not prove that values match.

• Decide before you start what happens to values that do not convert.
• Keep the comparison query: it is also useful after production steps.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

-- example form for an integer-to-bigint change on PostgreSQL
SELECT COUNT(*) FROM your_table
WHERE new_column IS DISTINCT FROM old_column::bigint;

What does not fit, and how the fixed job is accepted

This pattern is wrong for tables that many unlisted tools write to, for keys referenced by other large tables, or where replication needs its own plan. The fixed one-table job covers one column on 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; it keeps the new column current through the application's own write paths and adds no database trigger. It is accepted when the row-by-row comparison on the production-sized copy shows no unexplained difference, the longest lock wait under a scripted write load stays under the limit you agreed, the application passes with reads switched, a backup of the copy restores into a separate instance with the same row counts, and the way back is rehearsed at each step. Your database owner runs production, starting with a verified backup. Prices are untested proposals; payment follows the agreed checks.