Adding a new column to a database table is simple on paper. In practice, it can break systems, slow queries, or block writes if done wrong. Schema changes are one of the most dangerous moves in production because the database is the backbone. A poorly planned ALTER TABLE can lock rows, cause downtime, or trigger cascading failures.
The first step is to understand the database engine’s behavior. PostgreSQL, MySQL, and SQLite handle new columns differently. Some add them instantly for empty defaults; others rewrite the entire table. Know the cost before you run the command. In PostgreSQL, adding a nullable column with no default is nearly instant. Adding a column with a non-null default rewrites the table and can take minutes or hours.
Use the smallest possible lock. Break the change into safe steps. For example, first add the column as nullable without a default. Then backfill data in batches, using indexed queries that avoid table-wide locks. Finally, set the default and constraints once the data matches the rule. This keeps writes flowing and shields users from downtime.
Coordinate application changes with schema changes. Deploy code that can handle both old and new schemas. Avoid reading from or writing to the new column until the backfill finishes. Roll out feature flags to switch usage in production. This prevents half-migrated data from leaking into live behavior.