Adding a new column sounds simple. It can break production if you get it wrong. The process touches code, database migrations, deployment order, locking behavior, and backward compatibility. The right approach avoids downtime and data corruption.
Start with a clear migration plan. Choose a column name that will never need changing. Define the correct data type up front. Nullable columns reduce risk during rollout. Add defaults later, not at creation, to avoid rewriting massive tables.
In PostgreSQL, use ADD COLUMN in an ALTER TABLE statement for fast, low-impact migrations on empty columns. Avoid adding NOT NULL with a default in the same command on large tables; it will rewrite every row. MySQL behaves differently—always check engine-level behavior and test against a copy of production data before running changes.
For zero-downtime deploys, ship the code that writes to both old and new columns before switching reads over. Backfill data in batches. Monitor locks, statement times, and replication lag. Don’t drop the old column until every consumer is confirmed switched.