The query returns, but the data feels wrong. The shape is there, yet something’s missing: a new column.
Adding a new column to a database is simple in theory. In production, it’s a surgical operation. Schema changes can lock tables, spike CPU, and delay queries. A careless ALTER TABLE can bring down critical systems during peak traffic. The right approach balances precision with speed, avoids downtime, and preserves data integrity.
The safest way to add a new column starts with knowing your database and its constraints. In PostgreSQL, ALTER TABLE ADD COLUMN is fast for nullable columns without defaults because it only updates metadata. Add a default with care; older versions rewrite the table, which can be slow for large datasets. MySQL behaves differently—some operations trigger a full table rebuild. Always check version-specific behavior.
For non-null columns, add them nullable first. Backfill data in small batches to avoid write amplification. Then apply a NOT NULL constraint once the data is complete. This approach reduces lock times to seconds instead of hours. If you need computed data, use generated columns when supported, or maintain values in application code until the migration is complete.