Adding a new column is one of the most common schema changes. It seems simple, but the impact can ripple through queries, indexes, and application logic. Choosing the right data type matters. A VARCHAR that should have been an INTEGER leads to subtle bugs. Consider nullability. Adding a nullable column is safer for production migrations, but it might not align with long-term constraints.
When adding a new column in PostgreSQL, ALTER TABLE ... ADD COLUMN is the core command. Adding it with a default value can lock large tables, so version your updates carefully. In MySQL, behavior can vary based on storage engine, but large table locks are still a risk. For online migrations at scale, use tools like pt-online-schema-change or built-in features like PostgreSQL’s ADD COLUMN without defaults, followed by an UPDATE in batches.
Application code must handle the new column gracefully. This means updating models, serializers, and APIs in lockstep with your schema change. Deploy first with the column unused to ensure compatibility, then backfill data, then start writing and reading the new field. Avoid shipping a migration and new code that depends on it in the same deployment unless your CI and rollback strategy are airtight.