The screen flashed red. A migration had failed. The log showed the cause: a missing new column.
Adding a new column to a database sounds simple, but it can cripple production if done wrong. The process must consider schema changes, indexing, defaults, locking, and backward compatibility. For relational databases like PostgreSQL, MySQL, and MariaDB, a new column must be added without blocking reads or writes. This requires the right DDL statement, wrapped in a safe deployment strategy.
When adding a new column in PostgreSQL, ALTER TABLE is the command of choice. If the column has a default value, PostgreSQL writes it for every row on creation, which can lock the table. To avoid this, first add the column without a default, then apply the default in a separate step. Use NULL for compatibility, migrate data in batches, and finally enforce NOT NULL if needed. This pattern keeps downtime near zero.
In MySQL, adding a column can cause table rebuilds. Use ALGORITHM=INPLACE when possible. For large datasets, tools like gh-ost or pt-online-schema-change can perform the migration without locking the table. Always monitor replication lag during the process to avoid cascading failures.