The query ran clean, but the table wasn’t ready. You needed a new column, and the schema had to change without downtime. That’s the moment where databases reveal their limits, or their strength.
Adding a new column is one of the most common schema migrations. It sounds simple, but at scale it can block writes, lock rows, or cause unplanned latency. The process depends on your database engine, the type and nullability of the column, default values, and whether backfilling data is required.
In PostgreSQL, ALTER TABLE ADD COLUMN is straightforward when no default is set. Such operations are metadata-only and complete fast. But introducing a non-null column with a default forces a rewrite of the entire table. That’s expensive. One pattern is to add the column as nullable, update rows in batches, then apply the SET NOT NULL constraint. This avoids locking the table for long periods.
In MySQL, the behavior varies by storage engine and version. Modern InnoDB supports instant adds for some column types, but incompatible changes trigger a table rebuild. Check the execution plan before running migrations in production.