Adding a new column in a production database is simple in theory, but the details decide whether you hit downtime or keep serving traffic. The first step is defining the column type and constraints. Choose the smallest type that holds your data. Apply NOT NULL with a default only if you can enforce it without locking the table for long. On large datasets, that lock can block writes.
Use ALTER TABLE with care. In PostgreSQL, adding a column without a default is near-instant. Adding one with a default rewrites the table unless you use a constant expression that gets stored in the catalog instead. In MySQL, the operation may block depending on storage engine and version. Always test schema changes on a replica or staging environment that mirrors production size.
If your service needs the new column immediately, ship it in phases. Deploy the schema change first with nullable columns. Then backfill data in small batches to avoid write amplification. Once populated, add constraints or indexes in separate migrations. Each step should be measurable and reversible.