Adding a new column looks simple. It rarely is. The database schema must change without breaking existing queries. The code must handle both the old and new structures during rollout. Production data must remain intact.
Start by defining the exact type, constraints, and default values. Use explicit names that describe the data. Avoid nullable columns unless absolutely necessary. Every choice here will ripple through indexing, performance, and storage.
For large tables, choose an online migration strategy. ALTER TABLE can lock writes for minutes or hours. Use tools or frameworks that support batched migrations. Stage the deployment: add the column, backfill data asynchronously, then update application code to read from it.
If the column needs to be indexed, create the index in a separate step. Building indexes on massive datasets is costly. Doing it after backfill prevents expensive operations from blocking critical workloads.