Adding a new column to a production database is simple in theory and dangerous in practice. Speed matters, but so does safety. The wrong migration locks tables for minutes. A misplaced default rewrites millions of rows. Done right, the change is invisible to users. Done wrong, it’s downtime.
Plan the schema change before you touch the database. Define the new column with the exact type, nullability, and constraints you need. Avoid heavy defaults if the table is large. Add the column as nullable, deploy the migration, and backfill data in small batches. Only then enforce NOT NULL and defaults. This minimizes locks and reduces replication lag.
Test the migration in an environment that mirrors production size. Monitor query performance on the altered table. If your system supports online schema changes—like PostgreSQL’s ADD COLUMN or MySQL with pt-online-schema-change—use them. Version control both the migration script and the application code that uses the new column. Roll out code that writes to both the old and new fields if you need backward compatibility. Switch reads only after backfill completes and the column is verified.