The database was fast, but it refused to grow without a fight. You faced it yesterday—tens of millions of rows, a schema locked in production, and a feature request that demands a new column.
Adding a new column isn’t just typing an ALTER TABLE command and calling it done. At scale, it can block queries, lock writes, and trigger costly downtime. The operation looks harmless in development, but once data volume rises, the risks multiply. You need to plan for schema changes that preserve both performance and data integrity.
Start with the migration strategy. On smaller datasets, straightforward DDL works. On larger systems, prefer an online schema change using tools like gh-ost or pt-online-schema-change. These create the new column in a shadow table, copy data in chunks, and swap tables with minimal lock time. This keeps production running while the structural change takes place.
Decide on the column type and default values up front. Avoid non-nullable columns with defaults on massive tables; the engine will try to rewrite the entire dataset. Instead, make the column nullable at first, then backfill values in controlled batches, and finally apply constraints once all data is valid.