Adding a new column is one of the most common schema changes in software. It sounds simple, but it carries risk. Each table represents relationships, performance constraints, and decades of decisions. The wrong change in production can lock rows, slow queries, or block the entire application.
The first decision: define the column type. Choose the smallest type that stores all future values. Avoid TEXT when a VARCHAR(255) is enough. Enforce NOT NULL only when every row, now and later, will have a value. Defaults must be set carefully—migrating millions of rows to a default value can freeze writes if done in one transaction.
The next step is migration strategy. Large tables require online migrations to prevent downtime. For MySQL, tools like gh-ost or pt-online-schema-change rewrite tables in the background while handling updates. PostgreSQL supports ADD COLUMN instantly if there’s no default. If a default is required, split the migration: add the nullable column first, then backfill in batches, and finally enforce constraints.
Do not trust development data. Test new columns against a copy of production scale. Measure query plans before and after the change. Indexes on the new column can be expensive to create during peak load; build them during low-traffic windows or use partial indexes when possible.