Adding a new column to a database table is simple in theory, but the details matter. Done wrong, it can lock tables, slow queries, or even take your application down. Done right, it expands capability without risk.
The key is to define the type with precision. Choose the smallest data type that fits the use case. Use NOT NULL with defaults when possible to avoid null handling overhead. Set clear constraints early so downstream data remains clean.
When running migrations, avoid locking production tables during peak traffic. On PostgreSQL, adding a column with a default value before the NOT NULL constraint can minimize disruptive rewrites. On MySQL, watch for implicit table rebuilds when adding certain types. Always test schema changes on production-sized datasets before rollout to measure time and impact.
Indexing a new column should be deliberate. Skip automatic indexing unless the column will be queried or joined frequently. Every index has a cost in both storage and write speed.