Adding a new column is simple in theory, but the real challenge is doing it without downtime, data loss, or breaking existing queries. The wrong approach leads to migrations that lock tables, slow the system, and force ugly rollbacks. The right approach makes the change fast, safe, and easy to test.
Start with your migration script. Use ALTER TABLE to add the new column. In most relational databases, adding a nullable column with no default is fast and does not block reads. If the column needs a default value, set it after creation in batches to avoid long locks. Always verify index needs; avoid automatic indexing unless the column is critical to query performance.
When the column will hold derived or computed data, backfill in small steps. Use job queues or scheduled tasks to spread the load. Monitor performance during the migration. In production, watch for query plans that change when the new column is introduced.
If you use feature flags, deploy code that writes to the new column before the reads switch over. This dual-write approach allows time to validate the data without risking broken output. When the migration is stable, cut over to reading from the new column.