Adding a new column to a database table is one of the most common schema changes in modern applications. Done right, it’s safe, fast, and transparent. Done wrong, it can block writes, lock rows, and take your service offline. This guide covers how to create a new column, populate it, and deploy it without downtime.
In relational databases like PostgreSQL and MySQL, a new column can be added with ALTER TABLE. This is simple in development. In production, it’s more complex. Adding a column with a default value can cause a full table rewrite, slowing reads and writes. Instead, add the new column as nullable, backfill data in small batches, and then set the default or constraint.
For PostgreSQL:
ALTER TABLE users ADD COLUMN last_login TIMESTAMP NULL;
Then backfill:
UPDATE users
SET last_login = created_at
WHERE last_login IS NULL
LIMIT 1000;
Repeat until complete. Only then:
ALTER TABLE users ALTER COLUMN last_login SET NOT NULL;
ALTER TABLE users ALTER COLUMN last_login SET DEFAULT now();
For MySQL with large datasets, use pt-online-schema-change or built-in ALGORITHM=INPLACE options to avoid long locks. Always check the execution plan before running in production.
When changes involve indexes, foreign keys, or computed columns, run them separately from the new column creation to limit risk. Monitor replication lag and disk space during the migration.
A new column is not just a field—it’s a schema evolution. Plan for deployment windows, rollback strategies, and code that can handle both old and new schemas during rollout. Use feature flags to ship code before the schema is fully live.
Schema changes are simple until they are not. Treat every new column as a migration that can affect your uptime, your users, and your bottom line.
See how to add a new column in production with zero downtime. Try it live now at hoop.dev.