Adding a new column to a production database is one of the most common schema changes, yet it can still cause downtime, lock contention, or silent performance regressions if done carelessly. Whether you work with PostgreSQL, MySQL, or a modern cloud-native database, the steps are simple but the details decide if the rollout is safe.
A new column should be added with an explicit data type, default value strategy, and nullability choice. Avoid adding the default inline if the table is large; instead, add the column as nullable, backfill in batches, then apply the NOT NULL constraint. This keeps locks short and replication lag minimal.
If you are working with PostgreSQL, use ALTER TABLE ... ADD COLUMN in a transaction. For MySQL, be aware that older storage engines may lock the table for the whole operation. In both, schema changes on hot tables should be tested in a staging environment with realistic data loads.
Index creation for a new column should be deferred until after backfilling unless queries cannot function without it. Indexes add I/O overhead during writes and can block migrations if not applied carefully. Use CREATE INDEX CONCURRENTLY in PostgreSQL or ALGORITHM=INPLACE in MySQL to reduce blocking.