The query was slow. You look at the schema. The table is huge, the indexes are tight, but the columns no longer fit the evolving shape of your data. You need a new column.
Adding a new column sounds trivial, but the consequences ripple. In production, schema changes touch migrations, data integrity, API contracts, and deployment pipelines. A mistimed ALTER TABLE can lock writes for seconds or minutes. On massive datasets, it can stall the app entirely.
The first step is defining exactly what the column will store. Decide on type, nullability, default values, and constraints up front. This keeps migrations clean and reduces downstream fixes. Use VARCHAR only if you must; prefer fixed-size types for performance. If data must always exist, set NOT NULL during creation. If it’s optional, plan for how missing entries will be handled.
Plan the migration path. For small tables, a direct ALTER TABLE ADD COLUMN is safe. For large or critical ones, run a phased migration. Create the column with NULL values, backfill data in batches, then apply constraints in a final step. This minimizes lock times and avoids outages.