Adding a new column is one of the most common database changes, but it can still break production if done wrong. A column changes the shape of your data. It touches queries, indexes, constraints, and application code. The right approach keeps your system fast, correct, and online.
First, define the column. Be explicit about type, default values, and nullability. Choosing the wrong type can lock you into slow migrations later. Use tight types—avoid oversized integers or bloated text when the data is small.
Second, create the column in a way that does not block traffic. In PostgreSQL, ALTER TABLE ... ADD COLUMN is fast for empty defaults or nullable fields. In MySQL, the same statement can lock the table. For big tables, consider adding the column without defaults, then backfill in small batches. This strategy prevents locking and keeps read and write operations flowing.
Third, handle indexing separately. Creating an index during column addition often triggers a full scan. Add the column, deploy code that uses it, then create the index in a controlled migration window.