Adding a new column is simple in concept but full of traps in production. Schema changes touch storage, indexes, queries, and sometimes the application layer. A careless ALTER TABLE can trigger a full table lock, blocking reads and writes until it finishes. In high-traffic systems, that’s unacceptable.
The first step is to decide on the column type and default. Some databases, like PostgreSQL with ADD COLUMN ... DEFAULT, can rewrite the entire table if you set a non-null default. This can take minutes or hours depending on table size. Avoid immediate rewrites by creating a nullable column with no default and then backfilling data in batches.
Backfilling is safer when done incrementally. Write a script or job that updates rows in small chunks, committing between each step. Monitor CPU, I/O, and replication lag. In MySQL, using pt-online-schema-change or native ALTER TABLE ... ALGORITHM=INPLACE can keep the table online during the alteration, but always test in staging first.
Once the column exists and is backfilled, audit every query that touches the table. Review SELECT lists, WHERE clauses, and indexes to ensure the new column is used efficiently. Add indexes only if needed—indexes speed up reads but slow down writes.