Adding a new column seems simple. In practice, it’s a high‑risk schema change. The wrong approach locks tables, blocks queries, or triggers cascading failures. The right process keeps systems online and safe while the database evolves.
First, define the purpose of the new column. Is it required for every row? Can it be nullable? Will it store computed values or raw data? Decisions here dictate performance, size on disk, and migration cost.
Second, plan the schema migration. For large datasets, adding a new column with a default value can cause a full table rewrite. This blocks reads and writes in some databases. In MySQL or PostgreSQL, avoid this by adding the column without a default, then backfilling data in small batches. For column stores like ClickHouse, design with storage format and index granularity in mind.
Third, version your code and schema changes. Deploy the schema update first, keeping it backward‑compatible. Then update application logic to write to both the old and the new column if needed. After validation, remove the old source of truth only when it’s safe.