When you add a new column to a database table, the details matter. Name, type, default values, indexes. A single mismatch can cause downtime, broken queries, and failed deployments. In high-traffic systems, adding a new column without care can lock tables, block writes, and slow reads. The path to doing it right is precise and predictable.
First, define the schema change. Decide if the new column is nullable, if it should have a default, and if it needs an index. Avoid unnecessary defaults on large tables, as they can trigger a full table rewrite. In PostgreSQL, adding a nullable column without a default is near-instant. Adding a default to existing rows can take minutes or hours depending on data size.
Second, plan the deployment. For zero-downtime changes, use online schema migration tools like pt-online-schema-change for MySQL or gh-ost. In PostgreSQL, leverage techniques like adding the new column without a default, then backfilling in small batches, and applying the default later. This avoids locking the table for the entire backfill.