Adding a new column to a table is one of the most common database operations, yet it touches every layer of an application. Schema changes impact read and write queries, indexes, migrations, replication, and downstream systems. Done carelessly, it can lock tables, drop performance, and block deploys. Done right, it provides new capability without disruption.
When you create a new column, start by knowing its purpose and type. Choose the smallest data type that holds the range of expected values. Smaller columns use less memory, fit better in cache, and improve query performance. Avoid NULLs unless they have real meaning. If the column has a default value, define it in the migration to keep data consistent from the start.
For production systems, every new column should be added with minimal locking. In PostgreSQL, use ADD COLUMN with a default value only if the default is baked into the schema and written to new rows automatically. For large tables, first add the column without a default, then backfill data in batches. This prevents long transactions and keeps the database responsive.
Index the new column only if it will be filtered or joined in production-critical queries. Every index has a maintenance cost on writes. Add indexes in a separate migration to isolate risk and make rollbacks simple.