The query ran. The table was big. You saw the gap and knew what had to be done: a new column.
Adding a new column to a database table seems simple. It is not. The faster a system moves, the more critical it becomes to execute the change without breaking data integrity, slowing queries, or locking writes.
First, define the purpose of the new column. Store one type of data only. Avoid overloading a single field for multiple meanings. If you must store different formats, create separate columns. This keeps indexes sharp and queries predictable.
In relational databases, use ALTER TABLE with caution. On large tables, a standard ALTER TABLE ADD COLUMN can lock the table and block transactions. For production systems at scale, online schema changes are safer. Tools like pt-online-schema-change, gh-ost, or native database features let you add a column without downtime. Plan the change during low-traffic periods, monitor replication lag, and use staged rollouts.
Decide on defaults. If you set a default value, the database may rewrite the entire table, triggering I/O spikes. In MySQL 8.0 and PostgreSQL 11+, adding a column with a constant default is optimized to be instant. Without that optimization, consider adding the column nullable first, backfilling in batches, and then applying constraints.