The database table was choking on old structure. It needed a new column, fast.
Adding a new column is one of the most common schema migrations, but it can still bring down production if done carelessly. The right approach depends on database size, locking behavior, and traffic patterns.
In SQL, ALTER TABLE is the standard command. A simple example:
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
On small tables, this is instant. On large ones, it may lock writes or even reads until complete. PostgreSQL handles ADD COLUMN with a default NULL quickly, but adding a column with a non-null default rewrites the table. MySQL can be similar, though online DDL modes help reduce blocking.
Design before you execute. Decide data type, default value, and constraints. Adding a NOT NULL constraint too early can break existing inserts. Use nullable columns first, backfill data in batches, then add constraints in a follow-up migration. This limits downtime and reduces contention.
For zero-downtime migrations on production, wrap changes in deployment strategies:
- Deploy code that can handle both old and new schema.
- Add the new column as nullable with no default.
- Backfill in chunks to avoid locking large ranges.
- Add constraints and indexes in separate operations.
Tools like pt-online-schema-change for MySQL or pg_repack for PostgreSQL can make this safer. Modern migration frameworks also support concurrent operations for indexes and columns.
A new column is not just a schema change. It’s an interface contract between the database and your application code. Get it wrong and you risk corrupt data, locked tables, or broken queries. Get it right and the migration becomes invisible to your users.
See how to ship schema changes without downtime. Try it in minutes with hoop.dev.