Adding a new column should be simple. In practice, it can be slow, dangerous, and expensive. Large datasets make schema changes risky. An ALTER TABLE on a production database can lock writes, blow caches, and bring down services. But new columns are essential. They power new features, unlock analytics, and evolve your product.
When you add a new column, you change the contract between your database and your code. The first step is to decide the column name and type with intent. Names must be unambiguous. Types must match future usage, not just current needs. Avoid NULL defaults unless required. A NOT NULL column with a default value is safer to backfill without downtime.
Choose the migration strategy based on table size and uptime requirements. For small tables, a straightforward ALTER TABLE ADD COLUMN is enough. For large or critical tables, use an online schema change tool like gh-ost or pt-online-schema-change. These tools run in the background, copy data in chunks, and allow writes to continue.
Backfill data in controlled batches. Monitor load on primary and replicas. If the new column is indexed, build the index after data backfill to minimize locking. Test both migration and rollback on a staging environment with production-scale data. Never push an untested schema change live.