The query ran clean, but the table was wrong. You needed a new column, and you needed it without downtime, without breaking production, and without corrupting data.
A new column is one of the most common schema changes in modern databases. It can look simple, but in high-load environments the work can be risky. Adding a column locks tables, triggers schema replication, and can slow query performance if done without care. Done right, it’s fast, safe, and invisible to end users.
Choosing the right method depends on the database engine, table size, and availability requirements. In PostgreSQL, ALTER TABLE ADD COLUMN is straightforward when adding a nullable column with a default of NULL. For large datasets, avoid setting a non-null default in the same command—this triggers a table rewrite. Use a two-step migration: first add the column as nullable, then backfill in small batches.
In MySQL and MariaDB, adding a new column can invoke a full table copy depending on the storage engine and version. Use ALGORITHM=INPLACE or ALGORITHM=INSTANT where available. These newer methods avoid table rebuilds and are critical for uptime. Always confirm support for these algorithms before deployment.