Adding a new column seems simple, but the cost of doing it wrong is high. In production systems, schema changes can block writes, lock tables, and break downstream services. The right approach depends on the database, the scale of your data, and the tolerance for downtime.
In relational databases like PostgreSQL or MySQL, ALTER TABLE ADD COLUMN is the direct command. For small tables, it runs in milliseconds. For large tables, it can be a locking operation that halts traffic. Some engines, like newer versions of PostgreSQL, can add certain column types instantly by updating metadata. Others require rewriting the table on disk, which can be slow and disruptive.
To avoid downtime, many engineers create a new column as nullable with a default of NULL. This bypasses heavy writes during schema change and lets you backfill the data in batches. In MySQL, online DDL (ONLINE or INPLACE algorithms) can reduce lock time. With PostgreSQL, using logical replication or migration tools like pg_repack can achieve a similar effect.
For distributed databases like CockroachDB or Yugabyte, the process is asynchronous. The system propagates schema changes across nodes without requiring a massive blocking operation. On the other hand, NoSQL systems such as MongoDB don’t require explicit schema, but adding a new field still has implications for storage and queries.