Adding a new column is one of the most common schema changes in modern databases. Done right, it looks simple. Done wrong, it can bring production to a halt. The process depends on your database engine, the scale of your data, and the level of uptime you require.
In relational systems like PostgreSQL or MySQL, you can create a new column with a single ALTER TABLE statement:
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
This works for small tables or low-traffic environments. But on large datasets, this operation can lock the table or take minutes to hours. That’s why experienced teams plan schema changes to be online, safe, and reversible.
For PostgreSQL, adding a nullable column without a default is fast, because it updates only metadata. Adding a column with a default value writes to every row and can bloat transaction logs. Better to add it as nullable, backfill in controlled batches, then set the default.
For MySQL with InnoDB, ALGORITHM=INPLACE can reduce downtime. Still, be aware that some column types trigger a table copy even when you think you’re safe. Always check pt-online-schema-change or native online DDL features before running changes in production.