Adding a new column in your database sounds simple. The wrong approach can still lock tables, slow queries, or break production. Speed and safety come from understanding exactly how schema changes behave under load, and applying them with precision.
The first step: choose your migration method. For small datasets, a direct ALTER TABLE ADD COLUMN works. For large tables, online schema change tools like gh-ost or pt-online-schema-change let you add columns without blocking writes. Both create a copy of the table, apply changes, and swap it in. They protect uptime but require careful setup.
Decide on constraints early. A new column with NOT NULL and no default will rewrite all existing rows. That’s expensive. Adding the column nullable, then backfilling data, then adding constraints in separate steps avoids long locks.
Index only if necessary. Every index increases write cost. If the new column will be used rarely in queries, skip the index until proven otherwise. Test queries first, confirm performance impact.