Adding a new column to a database can be fast—or it can bring production to its knees. The difference comes down to how you design, run, and deploy the change. Whether you work with Postgres, MySQL, or a cloud-native database, the principles are the same: keep it atomic, keep it reversible, and keep it sane under load.
First, define the column with precision. Choose the smallest data type that can store the values without conversion overhead. Avoid NULL defaults unless needed. Decide if the column requires an index now or after backfilling. Creating an index during peak traffic can block writes. Split it into separate operations when possible.
For large datasets, use online schema change tools. These let you add a new column without locking the entire table. In Postgres, ALTER TABLE ... ADD COLUMN is often instant if you set a constant DEFAULT—but in some cases, it triggers a table rewrite. Test on a staging clone before touching production.
Backfilling data into the new column should not be done with a single massive query. Use batched updates with transaction limits. Monitor replication lag if you run read replicas. Adding a column without backfill may be safer if legacy code can handle it, but have a migration plan for data consistency.