One line in a migration script, one ALTER TABLE, and your database’s shape is no longer the same. If you handle it well, it unlocks features, speeds up queries, and cleans your logic. If you handle it poorly, it locks rows, breaks deployments, and wakes you at 3 a.m.
Adding a new column sounds simple. It is not. You must think about schema design, backfills, defaults, indexes, and application compatibility. For relational databases like PostgreSQL and MySQL, creating a new column can be instant or can stall production, depending on data size and constraints.
Plan the migration. Always check if the column can be nullable or if it requires a default. In PostgreSQL, adding a nullable column is fast—no table rewrite. Adding a column with a default value on large tables may trigger a rewrite, blocking writes for minutes or hours. Use separate steps: first add the column as nullable, then backfill in small batches, then set the default.
Test performance. If the new column will be queried often, choose the right data type and index strategy from the start. Avoid indexing immediately on creation if the table is large—create indexes concurrently to reduce lock times.