Adding a new column sounds simple, but in production systems it can be risky. Downtime costs money. Queries break. Migrations stall. To handle it right, you need a clear process and a strategy that works in both SQL and NoSQL databases.
Define the schema change. The first step is to decide the column name, type, default value, and constraints. In relational databases like PostgreSQL or MySQL, this means running ALTER TABLE with precise syntax. In NoSQL systems like MongoDB, adding a new field happens at the document level, but still requires consistent data handling.
Plan for backward compatibility. Deploy the schema change in a way that allows old and new application code to run side by side. Adding a column with a default or nullable value prevents insert and update failures. Avoid dropping or renaming existing columns until all dependent services are updated.
Handle large tables carefully. On tables with millions of rows, adding a new column can lock writes and reads. Use online schema change tools or roll out the change in smaller steps. For example, create the column without defaults, backfill values in batches, and then add constraints.