Adding a new column is one of the most common schema updates. It sounds simple, but in production, nothing about it is trivial. The wrong approach risks downtime, table locks, or corrupted data. The right approach makes it seamless, atomic, and safe.
In SQL databases like PostgreSQL, MySQL, or MariaDB, the ALTER TABLE statement is the foundation. A typical command looks like:
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
On small datasets, this completes instantly. But on large tables, adding a column can lock writes and block queries. For high-traffic applications, that means requests start queuing, APIs time out, and jobs fail. To handle this, modern strategies involve:
- Adding the new column as nullable
- Avoiding default values at creation time
- Backfilling data in small batches
- Updating application code after the column is ready
In PostgreSQL, adding a column without a default is nearly instant because it only updates metadata. Setting a default during creation forces a table rewrite. For large datasets, add the column first, then set the default later. MySQL before version 8 requires more care due to table rebuilds on schema changes; tools like pt-online-schema-change help apply the update without downtime.