Adding a new column is one of the most common schema changes in software. It seems simple, but mistakes here can lock rows, stall deployments, or trigger downtime. The right approach keeps production safe while giving you the flexibility to evolve your data model fast.
When you add a new column in SQL, you change the structure of a table. The definition of that column — its name, data type, nullability, default value — must be correct at creation. Changing these later can be costly if the table holds millions of rows.
In PostgreSQL, ALTER TABLE table_name ADD COLUMN column_name data_type is straightforward for small tables. In large, high-traffic tables, you must watch for table rewrites, index impacts, and long-running locks. If the column has a default that is not NULL, PostgreSQL will backfill all rows, consuming I/O and blocking writes. Adding it as nullable first, then updating in batches, can avoid downtime.
MySQL works differently. Depending on the storage engine and version, adding a column may copy the entire table, which is expensive at scale. Use ALGORITHM=INPLACE or INSTANT when possible to reduce lock time. Always check information_schema to confirm column order and schema state after deployment.