ALTER TABLE

Definition

ALTER TABLE modifies the structure of an existing table without recreating it from scratch: adding/dropping columns, changing data types, adding/dropping constraints, or renaming columns.

sql
ALTER TABLE employees ADD COLUMN email VARCHAR(150);

ALTER TABLE employees ALTER COLUMN salary TYPE NUMERIC(12,2);

ALTER TABLE employees ADD CONSTRAINT chk_email CHECK (email LIKE '%@%');

ALTER TABLE employees DROP COLUMN legacy_flag;

ALTER TABLE employees RENAME COLUMN salary TO base_salary;

How It Works

Depending on the change, the engine either updates catalog metadata only (cheap — e.g. adding a nullable column with no default in PostgreSQL 11+) or physically rewrites every row of the table (expensive — e.g. changing a column's data type, or adding a column with a non-null default on older engine versions). Rewrites require exclusive locks that can block reads and writes for the duration.

Worked example — safely adding a required column to a huge, live table:

sql
-- Step 1: add nullable first (fast, metadata-only on modern Postgres)
ALTER TABLE employees ADD COLUMN status VARCHAR(20);

-- Step 2: backfill in batches (avoids one giant lock/transaction)
UPDATE employees SET status = 'active' WHERE status IS NULL AND id BETWEEN 1 AND 100000;
-- ... repeat in chunks ...

-- Step 3: only now enforce NOT NULL, once every row is populated
ALTER TABLE employees ALTER COLUMN status SET NOT NULL;

This "add nullable, backfill, then constrain" sequence avoids one long table-locking operation and is the standard technique for zero/low-downtime schema changes (also central to the "Schema Evolution" topic).

Edge Cases and Pitfalls

• Locking on huge live tables: a type change or full-table rewrite can hold an exclusive lock for minutes or hours on a large table, blocking application traffic — a top cause of production incidents.
• Adding NOT NULL without a default on a populated table fails outright if any existing row would violate it.
• Engine differences: MySQL's ALGORITHM=INPLACE, LOCK=NONE and tools like pt-online-schema-change/gh-ost exist specifically to avoid blocking locks for large-table changes; PostgreSQL made many ADD COLUMN operations metadata-only since v11 but type changes still typically rewrite.
• Dropping a column is often irreversible in practice — the data is gone unless you have a backup, even though the table itself remains.
• Adding a constraint (like a new CHECK or FOREIGN KEY) forces a validation scan of existing rows by default, which is itself a locking operation on large tables (Postgres offers NOT VALID + separate VALIDATE CONSTRAINT to split this into two lighter steps).

Key Takeaways / Q&A

Q: Why is "add nullable, backfill, then constrain" preferred over "add NOT NULL column directly" on a huge live table? A: Adding a NOT NULL column directly either fails (no default) or requires rewriting every row at once with a default value while holding a lock — batching the backfill avoids one long blocking operation.

Q: Is ALTER TABLE always cheap? A: No — it ranges from an instant metadata update to a full-table rewrite, depending on the exact change and the engine/version.