Adding a new column is one of the most common schema changes, yet it’s where performance, reliability, and deployment discipline can break down. In production systems, the wrong approach can lock tables, block writes, and stall services. The right approach makes the change seamless and safe.
A new column can be part of a schema migration, an optimization, or a feature rollout. Before adding it, define the column type, constraints, and nullability. Decide on default values. In most relational databases, adding a nullable column without a default is fast. Adding a non-nullable column with a default often rewrites the entire table, which can impact uptime.
For PostgreSQL, ALTER TABLE ADD COLUMN is straightforward for nullable fields. For MySQL, the same applies, but column order can still cause a table rebuild. In distributed databases, schema changes must account for versioned application code. Deploy the database change first with the column nullable and unused. Then roll out code that starts writing to it. Only later enforce constraints or defaults once the data is backfilled.
Backfilling is where engineers often get into trouble. Large, single transactions that update every row can lock or overload the database. Use batched updates and monitor load. In PostgreSQL, functions like generate_series() can help break updates into chunks. In systems like MySQL, LIMIT within loops achieves the same result.