Adding a new column in a production database should be simple, but in practice it’s where many deployments break. Whether you use PostgreSQL, MySQL, or SQLite, the process is about more than just ALTER TABLE. A new column changes the shape of your data, affects queries, indexes, and triggers, and must be deployed without downtime.
The first decision: default values. Adding a column with a default to a large table can lock writes for seconds or minutes. On PostgreSQL, use ALTER TABLE ... ADD COLUMN ... DEFAULT with NOT NULL only if the dataset is small. For big tables, add the column as nullable, backfill in batches, then set the default and constraint.
The second decision: nullability. Null columns cost less to add but require application code to handle missing data. Define how the new column will be populated and validated before you ship.
For MySQL, adding a new column can be instant with ALGORITHM=INSTANT in 8.0+—but only if the change meets specific criteria. Otherwise, it copies the table, which will cause downtime. On SQLite, adding a new column without constraints is trivial, but changing constraints later requires rebuilding the table from scratch.