How to Add a New Column Without Downtime

Adding a new column should be fast, predictable, and safe. In SQL, the ALTER TABLE command is the primary tool. For PostgreSQL:

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;

For MySQL:

ALTER TABLE users ADD COLUMN last_login DATETIME;

Default values require care. In large tables, adding a column with a default can lock writes. In PostgreSQL 11+, adding a column with a constant default is optimized and does not rewrite the table. Choose this approach when possible:

ALTER TABLE users ADD COLUMN active BOOLEAN DEFAULT true;

Indexes on a new column should come after the column exists and is populated. Creating them during the same migration can slow deploys, especially on high-traffic systems.

When adding a new column in production, follow these steps:

1. Add the column with no default if using older versions of the database.
2. Backfill data in small batches to reduce load.
3. Add defaults and constraints only after the backfill completes.
4. Create indexes last to prevent migration bottlenecks.

Use transactional DDL when the database supports it. This reduces the chance of leaving the schema in a partial state if a migration fails.

Application code should be deployed in sync with the schema change. Read and write paths may need to handle both old and new states until the rollout completes. Monitor error rates and query performance after releasing a new column change.

A seemingly small schema change can impact uptime, latency, and reliability. Treat adding a new column like deploying a feature: review, test, monitor, and iterate.

See how to add a new column, run migrations, and ship changes without downtime. Try it on hoop.dev and see it live in minutes.