Adding a new column sounds simple, but poor execution can lock rows, stall writes, or crash production. The key is understanding how your database handles schema changes. In MySQL, ALTER TABLE can rewrite the whole table, blocking access on large datasets. PostgreSQL is faster for adding nullable columns with defaults, but still runs a table scan if you set that default at creation.
Choose your column type carefully. A poorly chosen type bloats storage and slows indexes. For high-write workloads, keep it lean—integers and short varchars will outperform large text or JSON fields. Always specify precision for numeric types to prevent drift.
For downtime-sensitive systems, use an additive migration strategy. First, add the column as nullable without defaults. Backfill data in small batches under load. Only then set the default or constraint. This reduces locks and keeps the system responsive. Tools like gh-ost or pt-online-schema-change let you run online migrations with minimal disruption.