Adding a new column is simple until it’s not. It can trigger table rewrites, lock writes, slow reads, and create migration bottlenecks. In production systems with high traffic, the wrong approach can cause downtime and data errors. Using the right method depends on your database engine, table size, replication setup, and deployment strategy.
In relational databases like PostgreSQL and MySQL, adding a column without a default can be instant because it only updates metadata. Adding a column with a default or NOT NULL constraint forces the database to rewrite every row, which can take minutes or hours. For large datasets, you should break the change into stages: first add the nullable column, then backfill it in small batches, and only then add constraints.
In distributed systems, schema changes must be backward compatible. Deploy code that reads from both the old and new columns, then write to both until the migration is complete. Only drop old columns after confirming full consistency. Tools like pt-online-schema-change for MySQL or logical replication in PostgreSQL can help avoid blocking operations.