ALTER TABLE
BeginnerALTER TABLE modifies an existing table's structure — adding, dropping, or modifying columns, and adding or dropping constraints without recreating the table.
Overview
ALTER TABLE is the DDL statement for evolving a live schema. In PostgreSQL, adding a nullable column with no default is instantaneous (only a catalog update). Adding a NOT NULL column or setting a DEFAULT triggers a table rewrite in older versions, locking the table. PostgreSQL 11+ allows adding NOT NULL columns with a constant default without a rewrite. MySQL's Online DDL (using ALGORITHM=INPLACE) reduces locking. Production schema migrations should always use a tool like Flyway or Liquibase to version, review, and roll back changes safely.
Common ALTER TABLE Operations
Each database dialect has slightly different syntax. PostgreSQL uses ALTER COLUMN; MySQL uses MODIFY COLUMN or CHANGE COLUMN. Know the operations that cause table rewrites and table locks before running them in production.
-- Add a nullable column (instant in PostgreSQL — no table rewrite)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Add a NOT NULL column with a default (PostgreSQL 11+: instant for constant defaults)
ALTER TABLE users ADD COLUMN tier VARCHAR(20) NOT NULL DEFAULT 'free';
-- Drop a column
ALTER TABLE users DROP COLUMN phone;
-- Rename a column (PostgreSQL)
ALTER TABLE users RENAME COLUMN username TO login_name;
-- Change data type (may require a CAST and locks the table)
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(14,2);
-- Add a constraint after the fact
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
-- Drop a constraint
ALTER TABLE orders DROP CONSTRAINT fk_orders_user;
-- Add a NOT NULL constraint (validates existing data — may be slow on large tables)
ALTER TABLE employees ALTER COLUMN department_id SET NOT NULL;Zero-Downtime Migrations Pattern
Large tables require care. The expand-contract pattern avoids long locks: add the new column as nullable, backfill in batches, add the NOT NULL constraint, then drop the old column in a separate deploy.
-- Step 1 (deploy 1): add nullable column — instant, no lock
ALTER TABLE orders ADD COLUMN status_v2 VARCHAR(30);
-- Step 2: backfill in batches to avoid long-running transaction locks
UPDATE orders SET status_v2 = status WHERE id BETWEEN 1 AND 100000;
UPDATE orders SET status_v2 = status WHERE id BETWEEN 100001 AND 200000;
-- ... repeat until all rows are filled
-- Step 3 (deploy 2): set NOT NULL after backfill is complete
-- PostgreSQL: use NOT VALID to skip re-scanning existing rows
ALTER TABLE orders ADD CONSTRAINT orders_status_v2_not_null
CHECK (status_v2 IS NOT NULL) NOT VALID;
-- Then validate in a separate step (takes ShareUpdateExclusiveLock, not full lock)
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_v2_not_null;
-- Step 4 (deploy 3): drop old column
ALTER TABLE orders DROP COLUMN status;Key Points to Remember
- 1Adding a nullable column with no DEFAULT is a metadata-only change in PostgreSQL — instant.
- 2Adding a NOT NULL column with a volatile DEFAULT triggers a full table rewrite (pre-PG11).
- 3Use the expand-contract pattern for large table migrations to avoid long locks.
- 4Always wrap schema changes in a migration tool (Flyway/Liquibase) for versioning and rollback.
- 5PostgreSQL NOT VALID + VALIDATE CONSTRAINT splits constraint addition into two low-impact steps.
- 6MySQL ALGORITHM=INPLACE avoids full table copies for many DDL operations.
Interview Questions
Sign in to ask AriaWhich ALTER TABLE operations cause table-level locks in PostgreSQL?
Describe the expand-contract pattern for zero-downtime schema migrations.
How does Flyway handle migration versioning and rollback?
What is the difference between ALTER COLUMN and MODIFY COLUMN?
Ask Aria about ALTER TABLE
Your personal AI tutor — ask anything about this concept
Revision Status
Personal Notes
Sign in to save personal notes for this topic.
Discussion
Sign in to join the discussion.