Adding a new column to a database table should be simple. Yet in production systems, it’s a source of downtime, broken queries, and silent data loss. The wrong ALTER TABLE strategy can lock rows, block writes, or cause schema drift across replicated clusters.
A new column in PostgreSQL, MySQL, or any relational database requires more than a single SQL statement. You must plan for defaults, nullability, indexing, and application code changes. Adding a nullable column avoids full-table rewrites, but your code must handle NULL values. Adding a column with a non-null default in older Postgres versions rewrites every row, which can stall large tables. MySQL’s behavior depends on its storage engine and version, with InnoDB typically rewriting the table for most structural changes.
Backward compatibility matters. The application should be able to read and write before and after the new column exists. This is why many teams use a two-step deployment: first add the nullable column, then backfill data in small batches, and finally add constraints or defaults. This reduces lock times and avoids high I/O spikes.
For distributed systems, you need to ensure schema change events are ordered and replicated consistently. A new column that’s visible in one region but missing in another can trigger unexpected exceptions and partial failures. This is often a coordination problem, requiring careful staging between database migrations and application rollouts.