Adding a new column seems simple, but it can break queries, slow down requests, or lock tables during migrations. The safest approach depends on your database engine, data volume, and uptime requirements. In Postgres, ALTER TABLE ... ADD COLUMN is usually fast for nullable columns without defaults. For large datasets or columns with non-null defaults, the operation may rewrite the table. For MySQL, adding a column can lock the table unless you use online DDL features or tools like pt-online-schema-change.
A migration plan for a new column should include:
- Checking indexes and constraints before altering the schema.
- Adding columns as nullable first, then backfilling with batches.
- Applying defaults and non-null constraints after data is populated.
- Verifying application code reads and writes the new column correctly.
Test migrations in staging with production-like data. Measure the execution time and watch for locks. Use transactional DDL where possible, or break the change into stages to avoid downtime. Keep in mind that ORMs may generate unsafe SQL by default; review raw queries before running them.