Adding a new column to a production database is not just a schema change. It is a decision that impacts queries, performance, and future migrations. Whether you use PostgreSQL, MySQL, or another system, the mechanics are simple, but the implications demand care.
In PostgreSQL, ALTER TABLE table_name ADD COLUMN column_name data_type; executes in constant time for most data types. For large tables with default values, the operation can lock writes. You can avoid downtime by first adding the column as nullable, then backfilling in batches, and finally applying constraints.
In MySQL, adding a new column can trigger a full table rebuild. This may consume significant I/O and lock the table depending on your engine and version. Online schema change tools like pt-online-schema-change or native features in newer releases reduce this impact.
Indexes on a new column speed lookups but slow writes. Adding them as part of the initial operation can extend lock times. Create indexes after data is populated and queries are verified.