Adding a new column to a database table is simple in theory. In production, it can be the trigger for downtime, data loss, or performance collapse. Schema changes must be fast, safe, and reversible. That means thinking beyond a basic ALTER TABLE and planning for the full lifecycle of the column: creation, backfill, validation, and deployment.
When introducing a new column, define the exact data type and constraints up front. Avoid implicit conversions. Always set a default when possible, but consider the cost of writing that default to millions of rows. For large datasets, add the column as nullable, then run an online backfill in controlled batches. This prevents write locks that can stall application queries.
Concurrent reads and writes require careful coordination. Deploy application changes that can handle the column existing but empty before backfilling data. Only enforce NOT NULL and foreign key constraints after confirming every row meets the requirement. Indexing should be deferred until after the backfill to prevent index bloat and slowdowns.