Adding a new column to a production database should be simple. In practice, it can threaten uptime, lock tables, and break dependent services. The right approach depends on your data size, schema constraints, and deployment process.
First, define the new column with the correct data type. Avoid default values that trigger a full table rewrite unless absolutely required. In relational databases like PostgreSQL and MySQL, adding a nullable column without a default is usually instant, regardless of table size. Non-null columns with defaults can cause full table scans, which block writes and degrade performance.
For large datasets, use an additive change strategy. Add the new column as nullable, backfill in small batches, then enforce constraints. This reduces risk and allows for fast rollbacks. Monitor replication lag when modifying tables on read replicas. In distributed systems, update application code to handle both old and new schemas during the migration window.