Adding a new column sounds simple. In production systems, it is not. The wrong approach can lock tables, block writes, or trigger cascading failures. The right approach avoids downtime, preserves integrity, and ships fast.
A new column alters the database schema. In SQL, it starts with ALTER TABLE ... ADD COLUMN .... But syntax is the easy part. The hard part is knowing how your database engine executes this change. Some engines rewrite the entire table. Others allow metadata-only changes. Before running anything, review execution plans and storage formats.
On high-traffic systems, a blocking DDL statement can pause reads and writes. This is why engineers turn to online schema change tools like pt-online-schema-change for MySQL or gh-ost. In PostgreSQL, adding a nullable column without a default is often instantaneous. Adding a default with a non-null constraint can rewrite the table, so break it into steps:
- Add the new column as nullable.
- Backfill in batches to avoid locking.
- Apply constraints after the data migration.
Plan for indexes too. Creating an index on a large table can block queries. Use concurrent index creation when your database supports it. Monitor replication lag to avoid downstream delays.