Adding a new column is simple, but in production environments the details matter. Databases lock. Queries stall. Migrations crawl. The way you create and populate that column can decide whether your system stays online or grinds to a halt.
First, define the column in a way that avoids full table rewrites. In PostgreSQL, adding a nullable column without a default is instant. In MySQL, the version and storage engine determine if the operation is online or blocking. Always review the database documentation for your exact release before deploying.
If you need a default value, avoid setting it in the schema change unless the database supports fast defaults. Instead, add the column as nullable, backfill the data in batches, then alter the column to set the default and enforce constraints. This reduces downtime risk and keeps transaction logs manageable.
For wide tables or high-traffic workloads, batch updates are essential. Use primary key ranges or time-based slices. Keep transactions small. Monitor replication lag if you run read replicas. Large one-shot updates can overwhelm both the primary and secondaries.