Adding a new column is one of the most common schema changes. Done wrong, it locks tables, stalls writes, and slows reads. Done right, it lands in production without downtime.
A new column in SQL starts simple. You use ALTER TABLE with your RDBMS syntax:
ALTER TABLE orders ADD COLUMN priority INT DEFAULT 0 NOT NULL;
That command changes the schema. But the real work happens before and after. You need to know:
- Data type: Pick the smallest type that holds possible values. Smaller types mean smaller indexes and faster queries.
- Defaults: Setting a default helps maintain invariants, but can trigger a full table rewrite if not supported by your database.
- Nullability: Adding a NOT NULL column often requires filling all existing rows. That can block writes if run in one transaction.
- Indexing: Avoid creating the index in the same migration. Create it separately to reduce lock time.
- Backfilling: For large tables, backfill in small batches. This keeps locks short and avoids saturating replicas.
Many production databases need online schema changes. MySQL and PostgreSQL have tools like gh-ost, pt-online-schema-change, and pg_online_alter. These create shadow tables, copy data, and swap in the new version. This avoids blocking long-running queries and keeps applications responsive.