The database query stalled. Logs showed nothing unusual. The schema looked untouched—until you noticed the missing new column.
Adding a new column should be simple, but in production, it can be dangerous. Schema changes can lock tables, delay queries, or even bring your system down. The safest approach depends on data size, query patterns, and how your application reads and writes that table.
In PostgreSQL, ALTER TABLE is straightforward for small datasets. For large tables, use non-blocking migrations with ADD COLUMN and a default value set in application logic instead of the DDL itself. This avoids rewriting the entire table. In MySQL, ALTER TABLE ... ADD COLUMN may trigger a full table copy unless you use ALGORITHM=INPLACE or INSTANT where supported. Always test in a staging environment with realistic data volumes.
When designing a new column, define clear constraints early. Nullability, indexing, and data type choices have long-term performance and storage impacts. If you need the column for filtering or sorting, add the index after backfilling to prevent full-table locks during live writes. Avoid premature indexing if queries don’t require it yet.