Adding a new column to a production database seems simple. It isn’t. Every choice—data type, default value, nullability—has downstream effects. A poorly planned ALTER TABLE locks writes, slows queries, or even corrupts data under load.
Start with the schema definition. Use explicit column names that match your data model and API contracts. Avoid generic names. Define the smallest data type possible. If the column stores dates, use a timestamp type, not a string. For IDs, stick with integers or UUIDs, not random text.
When adding a new column to a large table, avoid blocking operations. In PostgreSQL, use ADD COLUMN with a default only after the column exists, not in the creation step, to prevent full table rewrites. In MySQL, consider INSTANT or ONLINE DDL where available. Always measure migration time in staging against production-sized data.
Backfill existing rows in small, controlled batches. Monitor replication lag if the database has read replicas—adding and populating a new column can delay replication and impact read consistency. Wrap the process in feature flags so new code paths only use the column when it’s ready.