Changing the shape of a table that is being written to right now

You need to rename users.email to users.email_address and make it non-nullable. Ninety million rows, written continuously. In staging the migration took under a second. In production it held a lock for forty seconds: every query against users queued behind it, the pool filled, and the whole site errored for a minute — including pages that never touch that column.

The schema change was not the problem. The lock queue was.

Predict

A Postgres ALTER TABLE that takes milliseconds to execute took the site down for a minute. What happened?

Choose one answer

The statement rewrote the whole table, and ninety million rows takes about that long.It waited for a lock behind a long-running query, and while it waited every new query queued behind it.The replica could not keep up with the schema change, so reads failed while it caught up.The rename invalidated cached query plans, and the resulting recompilation storm overloaded the database.

Lock in answer

The lock queue, which is the whole lesson

In Postgres, ALTER TABLE takes an ACCESS EXCLUSIVE lock — the strongest one, conflicting with everything including plain SELECT. Most forms of ALTER TABLE hold it only briefly. But locks are granted in order, so if a reporting query has been running for two minutes holding ACCESS SHARE, your ALTER waits, and every SELECT that arrives while it waits is also made to wait, because it would otherwise jump the queue ahead of a conflicting request.

The defence is two lines, and it is not optional. The timeout values here are illustrative; pick them from what your callers tolerate:

SET lock_timeout = '2s';
SET statement_timeout = '30s';
ALTER TABLE users ALTER COLUMN email_address SET NOT NULL;

With lock_timeout set, the ALTER gives up rather than queueing, and the retry loop belongs in your migration tool rather than in your incident channel. Run it in a loop with backoff and it will land in a quiet moment. Without it, every migration on a busy table is a bet on nothing slow being in flight.

MySQL has the same shape with different names. DDL takes a metadata lock, an open transaction holding one blocks the DDL, and the blocked DDL then blocks everything behind it. lock_wait_timeout is the parameter, SHOW PROCESSLIST and performance_schema.metadata_locks show you who is holding. The additional MySQL hazard is that DDL there is not transactional: a multi-step migration that fails halfway leaves half a schema and no rollback, where Postgres can wrap most DDL in a transaction and have a failure undo cleanly.

Expand, migrate, contract

A rename cannot be done in one step on a live system for a reason that has nothing to do with locks: during any deploy, two versions of your code are running. Whatever the schema is at that moment, it has to be correct for both. So the change becomes three deploys, each one separately deployable and separately revertible.

Expand. Add the new shape without removing the old. ALTER TABLE users ADD COLUMN email_address text; — nullable, no default, metadata-only, and compatible with the code already running because nothing reads it yet. If you need a default, note that Postgres 11 and later store a constant default as metadata instead of rewriting the table, so a default is cheap on a modern version and was a table rewrite before that.

Migrate. Backfill in batches, and dual-write from the application so new rows get both columns. Batches matter: one UPDATE users SET email_address = email on ninety million rows is a single transaction holding row locks on every row it has touched, generating write-ahead log at a rate that can outpace your replicas and your disk. Loop over primary-key ranges, commit each batch, sleep briefly between them, and watch replica lag while it runs.

Contract. Only once the backfill is verified and no running code reads the old column: add the constraint, then drop the old column. In Postgres DROP COLUMN is metadata-only — the space comes back later, through vacuum — so the drop itself is cheap, and it is also the irreversible step, which is why it is its own deploy days later rather than the tail of the same one.

Each step is independently safe, which is the property that matters. If anything goes wrong you stop, and the system is in a state both code versions understand.

The statements that rewrite, and the ones that do not

The step people get wrong in the contract phase is SET NOT NULL, because it is not a rewrite but it does scan the whole table under ACCESS EXCLUSIVE to prove no nulls exist. On a table this size that is a long hold of the worst lock. The way around it is two statements that take weaker locks:

ALTER TABLE users
  ADD CONSTRAINT email_address_not_null
  CHECK (email_address IS NOT NULL) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT email_address_not_null;

ALTER TABLE users ALTER COLUMN email_address SET NOT NULL;

NOT VALID adds the constraint without checking existing rows, so it is instant and applies to new writes immediately. VALIDATE CONSTRAINT does the scan under SHARE UPDATE EXCLUSIVE, which does not block reads or writes. Then SET NOT NULL can use the validated constraint as proof and skip its own scan. The same NOT VALID then VALIDATE pattern is how you add a foreign key to a large table without a long lock.

In MySQL the equivalent question is which algorithm the engine picks. ALGORITHM=INSTANT covers adding and dropping a column in place; INPLACE with LOCK=NONE covers many index operations while allowing concurrent reads and writes; COPY rebuilds the table and blocks writes for the duration. State the algorithm explicitly rather than letting the server choose, so the statement fails fast instead of silently copying a large table: ALTER TABLE users ADD COLUMN email_address varchar(320), ALGORITHM=INSTANT; When only COPY is available — most column type changes — that is the point at which gh-ost or pt-online-schema-change earn their complexity, building a shadow table from the binary log and swapping it in.

What would you do

Postgres, one primary and two replicas. The users table is about ninety million rows, taking writes continuously, and is joined by most read paths in the product. Deploys go out roughly four times a day. Replica lag at peak is a fraction of a second.

Forty engineers across six services read this column. There is no maintenance window the business will agree to, and the change is wanted because an upcoming integration needs the longer name. Nobody is on fire.

Optimising for

The column is renamed and non-nullable with no single statement blocking reads or writes on the users table for more than a couple of seconds, no step that cannot be reverted by deploying the previous version, and no window in which two running code versions disagree about where the email lives.

You cannot know

• Whether all six services read the column through one shared library or directly
• How long the longest-running query against users typically holds its lock
• Whether any service writes this column outside the main application, such as an admin tool
• Whether the backfill can run during the working day without pushing replica lag past its budget

Choose an option

One migration: ALTER TABLE users RENAME COLUMN email TO email_address, deployed together with the code that uses the new name.Expand, migrate, contract across three deploys, with lock_timeout and a retry on every DDL step and a batched backfill in between.Keep the physical column and add a database view exposing it under the new name, so no data moves at all.Build the new shape with gh-ost-style shadow-table copying: new table, replicate changes into it, swap.

Check

You add an index to a busy table with CREATE INDEX CONCURRENTLY and set lock_timeout to two seconds. The command fails. What is in your database now?

Choose one answer

Nothing. The statement failed, so it rolled back.An invalid index that is maintained by every write and used by no query.A valid but incomplete index, which will finish building in the background.A valid index. Concurrent builds are resumable, so the retry continues from where it stopped.

Check answer

When this is all overhead

Three deploys, a dual-write path and a batched backfill are the right cost for a table that is large and continuously written. They are the wrong cost for most tables, and the crossing is concrete: if the table is small enough that the rewriting form of the statement completes inside the lock timeout you are willing to set, then one transaction containing the whole change — rename, constraint, backfill — is simpler, atomic and safer than a three-deploy sequence with a dual-write you might forget to remove.

In practice that means a table of some thousands of rows, where a full rewrite is a few hundred milliseconds. Below that line, wrap it in a transaction, set lock_timeout, retry on failure, and stop reading this lesson. The thing that moves the line is not only row count but write traffic: a small table that every request reads on its hot path can still produce an outage from a two-second lock, so set the timeout from what your callers tolerate rather than from the size of the table.

And keep lock_timeout either way. It is the one piece of this lesson that costs nothing and prevents the outage in the opening paragraph.

›The rename you should probably not doOptional

Everything above is the correct way to rename a column on a live system, and it is worth asking what the rename buys before paying for it.

A column name is an internal identifier. If the reason is clarity, a view, an alias in the data-access layer, or a comment on the column achieves most of it for none of the risk. If the reason is that an external integration expects a particular name, that mapping belongs at the boundary where the integration lives, not in the physical schema that six services share.

The renames worth doing are the ones where the old name is actively wrong — email holding a username, created_at holding the time of last edit, a boolean named for the opposite of what it means. Those cause incidents, because somebody will eventually trust the name. Cosmetic renames cause exactly the migration this lesson is about and nothing else.

Adding a column, backfilling it and dropping an old one is the same sequence and usually has a clearer justification, which is why the sequence is worth knowing even if this particular rename does not survive the question.

Worth remembering

• An ungranted exclusive lock request blocks every query that arrives behind it.
• Set lock_timeout on every DDL statement and retry; that alone prevents the outage.
• In MySQL the same hazard is a metadata lock held open by an idle transaction.
• Postgres DDL is transactional; MySQL DDL is not, so a half-failed migration stays half-applied.
• During any deploy two code versions run, so the schema must be correct for both.
• Expand, migrate, contract are three separate deploys, each revertible on its own.
• Backfill in primary-key batches and watch replica lag, never one UPDATE over everything.
• SET NOT NULL scans the table under the worst lock unless a validated CHECK proves it.
• NOT VALID then VALIDATE CONSTRAINT does the scan without blocking reads or writes.
• A failed CREATE INDEX CONCURRENTLY leaves an invalid index that is maintained and unused.
• State the MySQL algorithm explicitly so a COPY rebuild fails fast instead of running.