A schema change sounds simple. It’s not. In production, every column you add carries risk. Data migrations can lock rows. Index rebuilds can slow queries. If the table is big, downtime can feel inevitable. But there are ways to add a new column without wrecking performance or breaking deploys.
Start by understanding the database engine’s behavior. In PostgreSQL, adding a nullable column with no default is fast. It only updates the catalog metadata. But adding a column with a default value forces a table rewrite, which can block writes for minutes or hours. In MySQL, the exact mechanics vary by storage engine and version. Some operations can be online; others can’t. Always check the docs for your version before running ALTER TABLE.
For large datasets, plan the migration in steps. Add the column as nullable and without a default. Backfill values in small batches. Then apply the default constraint once all rows are populated. This avoids long locks and keeps application uptime.
If your application depends on the new column immediately, deploy in two phases. First deploy schema changes safe for production size. Then roll out code that uses the column. This gives you rollback safety if the migration goes wrong.