Adding a new column to a database is easy to get wrong. The wrong method locks tables, blocks queries, or triggers downtime. The right method scales with your dataset, preserves integrity, and stays invisible to users.
First, choose the correct migration strategy. In Postgres, use ALTER TABLE ADD COLUMN for small datasets when downtime is acceptable. For large tables, use online schema change tools like pg_repack, gh-ost, or pt-online-schema-change to avoid locks and keep writes flowing. In MySQL, ALTER TABLE can still cause locks—avoid this in high-traffic systems without online DDL support.
Default values slow migrations on large datasets. Add the new column as NULL first, then backfill data in batches. Once backfilled, apply constraints or defaults in a separate migration. This pattern avoids rewriting the entire table in one transaction.
Indexes follow the same caution. Do not create them inline with the new column unless they are small; instead, build them concurrently to prevent blocking writes.