Adding a new column should be simple, but in production systems it can carry more risk than a full table change. Schema changes touch live data, impact queries, and can lock tables during execution. Choosing the right strategy for adding a column depends on database size, transaction volume, and tolerance for downtime.
In SQL databases, a new column can be added with an ALTER TABLE command. This is straightforward for small tables, but large datasets require more care. For MySQL and PostgreSQL, synchronous schema changes can block reads and writes. To avoid this, many teams use online schema change tools like pt-online-schema-change or gh-ost, or leverage native features such as PostgreSQL’s ADD COLUMN with a default of NULL, followed by batched backfills.
When adding a new column in a microservice environment, coordinate schema changes with application code changes. First, deploy code that can handle both the old and new schema. Add the new column without defaults to keep the operation fast. Then, backfill the data in batches to avoid locking or high replication lag. Finally, update the code to rely on the new column once the data is ready. This sequence reduces risk and keeps the system available.