Adding a new column in a live database is simple in theory and dangerous in practice. Schema changes can block writes, lock rows, and cause cascading failures in connected services. The key is to manage the change without breaking production or slowing queries to a crawl.
A new column should be introduced with a zero-downtime approach. In PostgreSQL, adding a nullable column without a default is fast because it updates only the metadata. Adding a default value on creation can rewrite the entire table, so it’s better to set the default later. In MySQL, small columns are often safe, but format changes to fixed-size rows can still cause a rebuild. Test on a replica before production.
For large tables, backfill the new column in batches. Use small transactions to avoid locking too many rows. Monitor replication lag, CPU usage, and I/O while backfilling. Keep writes and reads consistent by updating application code in phases—first to read the new column when present, then to write new values, and finally to depend on it once populated.