Adding a new column to a table is simple in principle but full of traps in practice. Schema migrations touch live data, downtime is expensive, and mistakes can corrupt production. Whether you use PostgreSQL, MySQL, or distributed SQL, the steps you take determine if your system stays up or collapses under load.
The safest path begins with understanding the impact. A new column affects disk space, query performance, and application logic. You need to choose the right data type first. Avoid defaults that auto-fill millions of rows unless you have capacity. Consider NULL for initial rollouts, then backfill later with controlled increments.
Next comes indexing. Resist adding an index until you see a real need. Indexes on new columns speed reads but slow writes. For high-traffic tables, test on staging to measure performance changes.
For large datasets, use online schema change tools like pt-online-schema-change or gh-ost. They can add a new column without locking the table. In PostgreSQL, adding a nullable column without a default is fast and safe. But adding a NOT NULL with a default rewrites the table—plan for that.