Adding a new column to a database should be fast, safe, and predictable. But in production systems, the wrong approach can lock tables, block writes, and cause downtime. The right approach depends on your database engine, schema design, and uptime requirements.
In PostgreSQL, ALTER TABLE ADD COLUMN is the simplest way to create a new column. It runs in constant time when adding nullable columns without defaults. This means you can add a text or integer column instantly on large datasets. But if you specify a non-null constraint with a default value, PostgreSQL rewrites the entire table—a blocking operation for large tables. The safer path is to add the column as nullable, backfill in batches, then apply the NOT NULL constraint.
In MySQL, online schema change tools like gh-ost or pt-online-schema-change let you add a new column without significant lock contention. For engines like InnoDB, altering small tables directly is fine, but large tables should use these tools to avoid long blocking writes. MySQL 8 also supports ALGORITHM=INSTANT for adding some types of columns in microseconds, if the column is appended at the end and meets specific criteria.