The query had been running for hours when the need became clear: we needed a new column. Data had shifted. Requirements had changed. The schema that worked yesterday was now a bottleneck.
Adding a new column seems simple. In production, it can be a knife fight. Every millisecond of downtime matters. Every lock risks a customer experience. The wrong ALTER TABLE can freeze a high-traffic system and take hours to recover.
A clean workflow for adding a new column starts with understanding database engine behavior. In MySQL, a blocking schema change can lock writes until the operation completes. In PostgreSQL, some types of new column additions are cheap, but adding a column with a default value on a massive table can trigger a full table rewrite. On large datasets, these differences define success or failure.
Best practice is to add the new column as nullable with no default first. Then backfill in controlled batches. Only when the data is filled should you add constraints or defaults. This avoids full table locks and keeps the system responsive. Online schema change tools—like pt-online-schema-change for MySQL or PostgreSQL’s native concurrent index creation—can handle much of the complexity.