Adding a new column is one of the most common database migrations. Done right, it improves data structure without breaking queries. Done wrong, it locks tables, drops data, or slows the system for hours. The process is simple in theory—alter the table, define the type, set defaults—but in live systems, the details define success.
The first question is scope. Will the new column be nullable? Creating it as NULL reduces lock time and storage migration cost, but it may require backfilling later. If the column is NOT NULL with a default, check how your database engine handles this. In PostgreSQL, for example, adding a NOT NULL column with a constant default may rewrite the whole table, blocking concurrent writes. MySQL and MariaDB can sometimes handle this instantly with ALGORITHM=INPLACE, but only under certain constraints.
Next, consider indexing. Adding an index on the new column during the migration will compound table locks. It’s often faster to add the column first, then create the index in a separate step. This way, you can control impact and monitor system load.