Adding a new column to a database table is trivial until it isn’t. In small datasets, the operation is fast. In production with millions of rows, a poorly planned ALTER TABLE can lock writes, block reads, and cascade into application errors. The real challenge is introducing a new column without downtime, data loss, or unpredictable query plans.
Before adding a column, decide if it must be nullable. Adding a NOT NULL column with a default can trigger a full table rewrite in many engines. In MySQL and Postgres, this can take minutes or hours depending on table size. To avoid blocking, add the column as nullable first, backfill data in batches, then enforce constraints in a separate migration.
Know your database engine’s behavior. In Postgres, adding a nullable column is metadata-only and completes instantly. In MySQL, even a nullable column can lock the table depending on storage engine and version. In distributed SQL systems, schema changes propagate across nodes and must be carefully staged.
If the new column must be indexed, avoid creating the index in the same migration as the column. Build indexes concurrently where supported. This reduces lock times and keeps the application responsive.