Adding a new column to a live database table can be simple, or it can cripple performance if done wrong. Schema changes are not just code changes. They’re operations that alter storage, indexing, and query plans. The wrong approach risks locking tables, blocking writes, or breaking downstream services.
Before running ALTER TABLE ADD COLUMN, understand the table size, row count, and live traffic load. For large datasets, a naive DDL command will lock the table until it completes. On high-traffic systems, that can mean downtime. Instead, use an online schema change tool such as pt-online-schema-change or gh-ost. These copy the table structure, add the new column asynchronously, and swap it in without blocking critical queries.
Decide on defaults carefully. A NOT NULL column with a default can trigger a full rewrite of existing rows, locking the table and bloating I/O. Sometimes it’s better to add the column nullable, backfill data in batches, then enforce constraints later. Index creation should follow the same principle. Create indexes after the column is added and backfilled, using concurrent or online index builds when supported.