Adding a new column sounds simple. It isn't. The wrong approach can lock a table, block writes, and send latency through the roof. Modern databases handle schema changes better than they used to, but the details still matter. You have to balance uptime, performance, and data integrity in the same move.
First, decide how the new column will be added. In most relational databases, ALTER TABLE ADD COLUMN runs fast if no default values or constraints are set. Adding a column with a default forces the database to write to every row, which can take minutes or hours on large tables. Use NULL, populate in batches, and backfill later to avoid full table locks.
In PostgreSQL, adding a column without a default is metadata-only. In MySQL, adding a column may require a table rebuild depending on the engine and version. Check the documentation for your specific version before running changes in production.
If you need the new column populated with a default value, create it first as nullable. Then run a background job or migration script to update rows in small batches, committing frequently to avoid long locks. Once the data is ready, add a NOT NULL constraint in a separate DDL statement. This staged approach reduces downtime and risk.