Adding a new column is one of the most common database schema changes. Done wrong, it can lock tables, slow queries, or break deployments. Done right, it is seamless and fast, no matter the traffic load.
A new column starts with a schema migration. Use an explicit migration tool, not ad-hoc SQL. This ensures version control, repeatability, and safe rollbacks. Define the column type with precision—avoid ambiguous defaults. For example, choose timestamp with time zone over a generic datetime if you need consistent global data.
When adding a new column to a large production table, plan for online schema changes. Use tools like pt-online-schema-change, gh-ost, or built-in features for your database engine. These avoid long-lived locks and keep writes flowing. Test the process on staging data that matches production size and shape.
If the new column requires a default value, avoid backfilling in a single blocking transaction. Instead, add the column as nullable, deploy, then run background batch updates. When complete, add NOT NULL and the default constraint in later migrations. This pattern prevents downtime and reduces resource spikes.