Adding a new column seems simple. In production, it can trigger downtime, lock tables, and create deadlocks if done wrong. The process must be deliberate. You start with a schema change plan, version-controlled in your repository. Define the new column with the exact data type, nullability, and default value. Avoid implicit defaults on large tables; they can rewrite every row and lock writes.
For large datasets, use an online schema change tool. Split the operation into stages: first, add a nullable column with no default. Second, backfill data in small batches to avoid I/O spikes. Third, add constraints or defaults once the table is ready. Always verify changes in a staging environment seeded with real data volumes.
Indexing the new column is another trap. Create the index after the column is populated, and consider partial or conditional indexes to reduce size and improve query performance. Use database statistics commands to ensure the query planner optimizes for the new structure.