The query ran in under a second, but the results were wrong. The table needed a new column.
Adding a new column sounds simple, but in production systems it can be a fault line. The change must be atomic. It must not lock critical reads or writes. It must roll forward cleanly and roll back if needed. Downtime is not an option.
In SQL, the ALTER TABLE statement is the start. The syntax is direct:
ALTER TABLE orders ADD COLUMN shipped_at TIMESTAMP NULL;
This works on small datasets. On large tables under load, it can block access. Some databases mitigate this with non-blocking schema changes. PostgreSQL can add nullable columns instantly. MySQL with InnoDB may require extra care.
Plan the new column with intent. Define the exact data type. Avoid default values if they trigger table rebuilds. Examine existing indexes; adding a column rarely needs an index at first. Migrations should run in controlled environments before production.
Use feature flags to hide dependent code until the new column is ready. Write migration scripts to be idempotent. Test for both forward and backward compatibility, so you can revert without corrupting data or blocking queries.
Audit every client that queries the table. New columns can break strict ORM bindings or JSON serializers. Even unused fields can cause version mismatches.
The correct process to add a new column is an engineering decision, not a guess. Treat it like code: review it, test it, automate it, and deploy with confidence.
Ready to make safe, zero-downtime database changes? See how hoop.dev can run your migrations in minutes.