Adding a new column to a production table sounds simple. It isn’t. A careless migration can lock rows, stall writes, and trigger cascading errors across services. Whether it’s PostgreSQL, MySQL, or a distributed system, schema change operations demand precision and speed.
A new column defines more than data type. You need to decide on nullable vs. non-nullable, default values, indexing, and storage format. A boolean flag? A JSONB payload? Every choice affects query performance and downstream consumers.
Start by assessing the impact. On large tables, an ALTER TABLE ... ADD COLUMN operation can block transactions if executed without safeguards. In PostgreSQL, adding a nullable column without a default is fast because it only updates metadata. Adding a column with a default writes to every row, which can cause downtime. Use DEFAULT plus NOT NULL cautiously, and prefer staged migration:
- Add the column as nullable
- Backfill data in controlled batches
- Add constraints after population
For MySQL, ALTER TABLE behavior depends on the storage engine. InnoDB can rebuild the entire table if the new column changes row format, so online DDL features like ALGORITHM=INPLACE and LOCK=NONE are essential.
Indexing a new column has its own trade-offs. If the column is used in frequent queries, add the index after data is filled to avoid overhead during backfill. In analytics-heavy workloads, consider partial indexes or functional indexes to reduce size and optimize scans.