Adding a new column to a production database is simple in syntax but dangerous in execution. The right approach keeps data integrity intact, avoids downtime, and makes rollback immediate if something breaks. This means planning, testing, and deploying with precision.
Define the exact data type. Decide if it can be NULL or must be NOT NULL. Add sensible defaults only when they make sense. Use ALTER TABLE in development first, then run the migration in staging with production data size and shape. Watch the execution time and lock behavior. For large datasets, use an online schema change tool like gh-ost or pt-online-schema-change.
Understand the impact on reads, writes, and indexes. Adding an index for the new column can speed queries but may slow inserts and updates. Always benchmark. If the column will store foreign keys, enforce constraints and verify joins perform well. If it will be queried often in sorted order, index it accordingly.