Large-Table ALTER Strategies: Changing Schema Without Locking Production

Changing the shape of a table with a hundred million rows is a different problem than changing a table with a hundred. Large-table ALTER strategies exist because a naive ALTER TABLE ... ADD COLUMN or ALTER TABLE ... ALTER COLUMN TYPE can hold a lock for the entire duration of a rewrite, and on a table that size, that duration is measured in minutes or hours, not milliseconds. This post covers what actually blocks production traffic, what each major dialect offers natively, and how to structure the change so it never becomes an incident.

Why ALTER TABLE Locks Get Dangerous at Scale

Most relational databases implement a schema change by either updating metadata in place or rewriting the underlying table storage. Metadata-only changes (like adding a nullable column with no default in recent PostgreSQL or MySQL) are near-instant regardless of row count. But a change that requires touching every row — adding a column with a non-constant default, changing a column's type, adding a NOT NULL constraint without a prior check, or rebuilding an index — forces a full table rewrite. If that rewrite happens under an exclusive lock, every read and write queues behind it until it finishes.

What Each Dialect Gives You for Free

The starting point differs a lot by engine, so knowing your dialect's defaults matters before you reach for a third-party tool:

• MySQL / MariaDB — InnoDB's online DDL supports ALGORITHM=INSTANT for adding columns at the end of a table (MySQL 8.0.12+) and ALGORITHM=INPLACE for many index and column operations, both avoiding a full table copy in the common case. Rebuilding operations still take a metadata lock at the start and end.
• PostgreSQL — Adding a column with a constant default or no default is metadata-only from PG 11 onward. Type changes, adding a volatile default, or adding NOT NULL still trigger a full rewrite and hold ACCESS EXCLUSIVE unless you split the operation (see below).
• SQL Server — Enterprise and Azure SQL support WITH (ONLINE = ON) for index rebuilds and some column operations, keeping the table readable and writable during the rebuild.
• Oracle — DBMS_REDEFINITION lets you redefine a table online by building a shadow copy and synchronizing changes before a brief final swap.

Split the Change Instead of Fighting the Lock

The most reliable pattern for a genuinely large table isn't a clever flag — it's the expand/contract pattern: add the new column or constraint as optional, backfill it in the background, then tighten it in a second, much cheaper migration once every row already qualifies. A NOT NULL constraint added after 100% of rows are already non-null, for example, can often use a fast validation path instead of a full rewrite on PostgreSQL, because the engine can skip re-checking rows it already knows satisfy the constraint.

Batch the Backfill

Never backfill a wide table in a single UPDATE statement. A single-statement update against millions of rows holds row locks for the whole transaction, bloats the write-ahead log, and risks replica lag or lock-wait timeouts for unrelated queries hitting the same table. Instead, loop in batches keyed by primary key range or ID cursor — a few thousand rows per batch with a short pause between batches — so the transaction footprint per batch stays small and other traffic gets a chance to interleave.

Reach for a Tool When Native Online DDL Isn't Enough

For MySQL/MariaDB specifically, pt-online-schema-change and gh-ost exist because InnoDB's built-in online DDL doesn't cover every case (notably, changing a primary key or a column involved in a foreign key still forces a full copy). Both tools work by creating a shadow table, copying rows in batches, capturing ongoing writes via triggers or binlog streaming, and doing an atomic rename at the end — trading a longer overall migration for near-zero blocking.

Confirm the Change Before and After

Whichever path you take, verify the resulting schema matches what you intended before you call the migration done — a batched backfill or a third-party rewrite tool is one more place for the actual DDL to drift from what was reviewed. Comparing schemas and generating the migration SQL up front, and diffing again after the change lands, catches a missed index or a default that didn't carry over before it becomes a production surprise. Wiring that same comparison into your CI/CD pipeline means the next large-table change gets the same scrutiny automatically.

FoxSchema generates the migration SQL for exactly these situations across ten dialects, so you can review the plan before running anything against a production-sized table. Self-host it in Docker or check the install page to get started.