The query finished. The data looked perfect. But the table needed a new column.
Adding a new column is one of the most common schema changes in production databases. It sounds simple, but the execution can be dangerous if you ignore performance, locking, and data consistency. A careless change can block writes, slow queries, or even take an application offline.
The first step is choosing the right approach for the database engine. In PostgreSQL, ALTER TABLE ... ADD COLUMN is fast for columns with NULL defaults because it only updates metadata. But adding a column with a non-null default forces a table rewrite, which can lock the table and generate heavy I/O. MySQL is similar, though online DDL options exist in more recent versions. With large tables, every second of lock time matters.
Plan your migration. For zero downtime, break the change into steps. First, add the column as nullable and with no default. Then backfill data in small batches to avoid overwhelming resources. Once the column is populated, set the default and constraints in a final pass. This pattern reduces locking and avoids long transactions.