Adding a new column to a database is simple in theory but risky in practice. The goal is zero downtime, no broken queries, and clean forward compatibility. The method you choose depends on schema size, traffic patterns, and the database engine.
First, define the new column in a way that does not lock the table for longer than necessary. In most relational databases—PostgreSQL, MySQL, MariaDB—you can use ALTER TABLE ... ADD COLUMN without a full rebuild if you avoid defaults that require rewriting all rows. Use DEFAULT NULL or set defaults in application logic until the backfill is complete.
Second, deploy the schema change before the code that writes to the column. This prevents write errors from code hitting a column that doesn’t yet exist. When reading from the new column, guard against missing data during the migration window.
Third, backfill carefully. For large tables, use batch updates in transactions small enough to avoid blocking reads and writes. In PostgreSQL, tools like pg_background or application-level jobs can process millions of rows safely without saturating I/O.