Adding a new column is one of the most common schema changes in production. It sounds simple, but mistakes here can lock tables, cause downtime, or corrupt data. The key is to know the right approach for your system and workload.
First, decide on the column type. Choosing the right data type early avoids costly migrations later. Use the smallest type that fits your data. For text, be strict with limits. For numbers, match the range to actual needs. Avoid generic types like TEXT or large integers unless necessary.
Next, consider defaults. Setting a default value for a new column can create performance problems if it forces a table rewrite. In large datasets, this can lock rows for minutes or hours. Instead, add the column as nullable, backfill it in batches, then change to non-null with a default once the data is ready.
Adding a new column in PostgreSQL or MySQL is not the same as in a document store or key-value database. Relational databases require schema migrations, while NoSQL systems let you add fields freely, but at the cost of weaker type enforcement. The trade-off depends on your consistency requirements and query patterns.