The query was slow. The schema was wrong. You needed a new column.
Adding a new column is one of the most common schema changes in production systems. It is also one of the most dangerous if done without planning. Column additions can trigger table rewrites, lock writes, or cause replication lag. On large datasets, a careless change can take down your service.
Start by defining the exact purpose of the new column. Determine its data type, default value, and constraints. Avoid adding unnecessary indexes until you know the query patterns. Every index slows writes and increases storage costs.
In relational databases like PostgreSQL and MySQL, ALTER TABLE ADD COLUMN is straightforward, but its performance footprint depends on the database version and storage engine. Newer PostgreSQL versions can add nullable columns with defaults instantly. MySQL behaves differently depending on table format—InnoDB can be optimized with ALGORITHM=INPLACE. Always test the DDL against a staging database with production-level volume.
When adding a non-nullable column to a large table, consider a multi-step migration. Add the column as nullable. Backfill the data in batches using controlled transactions to avoid overwhelming IOPS. Then apply the NOT NULL constraint in a separate migration. This reduces lock time and avoids blocking queries.