Adding a new column sounds small, but it can decide whether a system stays fast or grinds under load. In SQL databases, a poorly planned column can bloat tables, break queries, and slow critical paths. The work starts with defining the column name, data type, and default values. Then you think about indexing. Without the right index, even a simple lookup can trigger full table scans that lock rows and delay writes.
In PostgreSQL, use ALTER TABLE ... ADD COLUMN with care. On small tables, it’s instant. On large ones, it can cause long locks, blocking inserts and updates. Adding a column with a non-null default in older PostgreSQL versions rewrote the whole table. Newer versions optimize this, but always test with production-like data. MySQL behaves differently; with ALTER TABLE it may rebuild the table under the hood depending on the storage engine and options used.
When the new column changes query patterns, update your indexes in the same migration set. This keeps your read paths efficient from day one. Consider constraints as part of the design — NOT NULL, UNIQUE, or foreign keys — but apply them after initial backfill to avoid blocking application writes.