The query has been running for hours, but the data is still wrong. You check the schema. The mistake stares back at you: a column is missing. Now you need a new column, and you need it fast.
Adding a new column should be simple, but the wrong approach can break production, lock tables, or corrupt data. The key is to choose the right migration path for your database and workload.
In SQL, the most direct method is ALTER TABLE ... ADD COLUMN. For small datasets, this runs in milliseconds. But on large or high-traffic systems, a blocking alter can halt writes and degrade performance. Use online schema change tools like pt-online-schema-change for MySQL or gh-ost for safer migrations at scale. PostgreSQL supports certain ADD COLUMN operations instantly if you define a default NULL value. Setting a non-null default forces a table rewrite—avoid it unless required.
When building distributed systems, adding a new column often means more than a database migration. Consider backward-compatibility in APIs and services. Deploy the schema change first, then roll out code that writes to the new column, and only later start reading from it. This three-step pattern reduces risk and supports zero-downtime releases.
Indexing the new column is another decision point. Adding an index at the same time as creating the column can increase downtime. It’s often safer to create the column first, backfill data in batches, and then add the index. Monitor replication lag if you run replicas; schema changes can push them out of sync if not paced carefully.