The database schema had been stable for years. Then the product team dropped a new requirement that could not fit anywhere. You know what comes next: a new column.
Adding a new column sounds simple. It isn’t. The smallest schema change can ripple through queries, indexes, application code, migrations, and production performance. Get it wrong, and you break features or stall deployments. Get it right, and you extend your system without downtime or data loss.
The first rule: plan the new column’s data type and constraints before touching anything. Changing a type later can lock tables, burn CPU, and block writes. Align the type with existing patterns. Avoid NULL if you can, but if the data won’t be available at insert time, design for it up front.
The second step: decide on defaults. Default values can be helpful, but on large tables they may trigger a full table rewrite during the ALTER TABLE. In PostgreSQL, adding a column with a constant default requires processing every row. This can be avoided by adding it without a default, then using UPDATE in batches, then setting the default for future writes.