Adding a new column is simple in theory and a minefield in production. The wrong type can break queries. A missing default can lock writes. A careless alteration can trigger full-table rewrites and bring traffic to a crawl. Every database—PostgreSQL, MySQL, SQLite—handles schema changes with its own quirks.
Start by checking data volume. For small tables, ALTER TABLE ADD COLUMN with a default is fast. For large datasets, avoid defaults and nullable changes in a single statement. Add the column first. Backfill data in batches. Then add constraints. This reduces lock time and avoids blocking reads and writes.
In PostgreSQL, an ADD COLUMN with a constant default rewrites the table. Instead, add it as nullable, backfill, then set NOT NULL with a default in a separate operation. For MySQL, altering wide tables can trigger a full copy; ensure you have enough disk and time budget. SQLite rewrites the entire table for any schema change, so plan pre-deployment backups.