Whether you are working in PostgreSQL, MySQL, or SQLite, adding a new column is a common database migration task. It changes the table schema, stores new data, and supports updated features without losing existing records. Knowing the right way to add a column avoids downtime, broken queries, and mismatched data.
In SQL, the syntax is straightforward:
ALTER TABLE users
ADD COLUMN last_login TIMESTAMP;
This works for small datasets, but larger systems need more precision. Adding a column with a default value in some databases can lock the table. In PostgreSQL, adding a nullable column is instant, but adding one with a default will rewrite the whole table in older versions. Newer versions optimize this when the default is constant. MySQL can behave differently based on storage engine. Always check the documentation for your database version before running a migration in production.
Plan for schema migrations like deployments. Run them in off-peak hours if locks are expected. Monitor replication lag when adding a column in a replicated environment. In MySQL, use pt-online-schema-change or similar tools for large tables. In PostgreSQL, split the operation into multiple steps: create the column as nullable, backfill data in controlled batches, and then set constraints.