How Does ALTER TABLE Change Schema?

ALTER TABLE changes the structure of an existing table: add a column, drop a column, rename a column, change a column's type, add or drop a constraint, add or drop an index. The operations and their costs vary enormously, and the difference matters on a large table.

CREATE TABLE tickets (
  id INTEGER PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'open'
);

INSERT INTO tickets (id, title) VALUES (1, 'Login page times out');

-- Add a column with a default.
ALTER TABLE tickets ADD COLUMN priority INTEGER NOT NULL DEFAULT 1;

-- Add a nullable column with no default.
ALTER TABLE tickets ADD COLUMN assignee_id INTEGER;

-- Rename a column.
ALTER TABLE tickets RENAME COLUMN title TO subject;

-- Drop a column.
ALTER TABLE tickets DROP COLUMN assignee_id;

SELECT id, subject, status, priority FROM tickets ORDER BY id;

One row comes back: ticket 1 with subject 'Login page times out', status 'open' and priority 1. Every one of the four alterations succeeded, and the table still holds its data.

The costs are the interesting part. Adding a column with a constant default is cheap on every modern engine, because the value is stored in the table's metadata rather than written into each row — that is how SQLite, PostgreSQL 11 and later, and MySQL 8 handle it. Adding a column with a volatile default, or changing a column's type, is a different matter: on some engines it rewrites every row and takes an exclusive lock for the duration. Dropping a column is metadata-only on most engines, but the space is not reclaimed until the table is vacuumed. Renaming a column is metadata-only everywhere, which is why it is the cheapest structural change there is.

SQLite's own limits are worth knowing since it is the engine these examples use. It supports ADD COLUMN, RENAME COLUMN, RENAME TABLE and DROP COLUMN from version 3.35.0 onwards, but it does not support ALTER COLUMN to change a column's type, and it does not support adding a table-level constraint after the fact — the constraint has to be in the CREATE TABLE statement. SQLite also will not let you drop a column that is indexed or referenced by a foreign key or a view.

Use the expand-and-contract pattern, and treat the migration as a sequence of backwards-compatible steps rather than one atomic change. The rule is that every intermediate state must be a state the currently deployed code can run against.

The sequence for adding a column and then using it looks like this. First, add the new column as nullable, or with a default, so the old code still works — it does not know the column exists, and an INSERT that does not mention it gets the default. Second, deploy the code that writes to the new column and reads the old one, or reads the new one with a fallback to the old. Third, backfill the existing rows, in batches, so no single UPDATE holds a lock for long. Fourth, deploy the code that reads only the new column. Fifth, once nothing reads the old one any more, add the constraint that the old column was supposed to have — NOT NULL, UNIQUE, the foreign key. Sixth, drop the old column, again in a separate deployment.

Each step is separately reversible, and none of them breaks the running application. The temptation to squash them into one migration is what causes downtime, because a single ALTER that rewrites the table locks out every reader for the duration.

Two details that catch people out. If you rename a column, the old code that still names the old column will fail immediately — there is no way to make a rename backwards compatible, which is why renaming is always a deploy-sequenced operation with the rename happening after both versions of the code agree on the new name. And the backfill has to be batched: a single UPDATE over fifty million rows holds locks and bloats the transaction log, whereas batches of a few thousand keep each transaction short and give you a resumable progress marker.

Finally, make the whole thing idempotent, so a migration that fails halfway can be re-run safely. That is covered on the migrations page, and it is the difference between a deploy that retries and a deploy that needs a human to unpick it.