The table was running hot. Queries hit it every second. Then the demand came: add a new column without slowing it down.
A new column seems simple. In SQL, you can run ALTER TABLE ADD COLUMN. But the reality depends on the database engine, the table size, and the uptime requirements. On a small dataset, it’s a quick change. On production tables with millions of rows, it can lock writes, flood I/O, and stall applications.
The safest approach starts with understanding the storage engine. In PostgreSQL, adding a new column with a default non-null value rewrites the whole table. MySQL and MariaDB can add nullable columns instantly in certain conditions. In distributed systems like CockroachDB, schema changes are asynchronous but require care to avoid version mismatches between services.
Plan the change. Assess traffic patterns, replication lag, and backup integrity. In high-traffic databases, apply a migration tool that can run in phases. Add the new column as nullable or with no default for instant speed, then backfill values in controlled batches. After backfill, apply constraints and defaults. This avoids long locks and unpredictable downtime.