Adding New Database Structures Without Breaking Running Applications

You have a users table with a full_name column. The product team wants to split names into first_name and last_name for better personalization. You need to make this change without taking down the application or breaking existing features.

The obvious approach would be to drop full_name, add the two new columns, and update all code at once. But that requires coordinated deployments, downtime, and a perfect rollback plan if something goes wrong. In practice, this kind of change often leads to late-night rollbacks and angry users.

There is a safer way: add the new structure first, without touching the old one. Let the old and new coexist until you are ready to fully switch over.

The Expand Phase: Add Without Removing

The expand-contract pattern starts with the safest possible step. You add new columns, tables, or constraints to the database while leaving everything that already exists completely untouched. Old applications that still run against the old schema will not notice any difference. New code can start using the new structures immediately.

This is the core idea of the expand phase: you introduce new database objects without modifying or removing existing ones. The old schema remains fully functional. The new schema is an addition, not a replacement.

The diagram below shows the before and after state of the users table during the expand phase, and how old and new applications interact with the schema.

flowchart TD subgraph Before[Before Expand Phase] T1[users table] C1[full_name VARCHAR] end subgraph After[After Expand Phase] T2[users table] C2[full_name VARCHAR] C3[first_name VARCHAR NULL] C4[last_name VARCHAR NULL] end OldApp[Old Application] -->|reads only| C2 NewApp[New Application] -->|reads/writes| C2 NewApp -->|reads/writes| C3 NewApp -->|reads/writes| C4 Before -->|ALTER TABLE ADD COLUMN| After

A Concrete Example

Take the users table with a full_name column. In the expand phase, you add two new columns:

Here is the SQL to add the new columns:

ALTER TABLE users ADD COLUMN first_name VARCHAR(100) NULL;
ALTER TABLE users ADD COLUMN last_name VARCHAR(100) NULL;

ALTER TABLE users ADD COLUMN first_name VARCHAR(100) NULL;
ALTER TABLE users ADD COLUMN last_name VARCHAR(100) NULL;

The full_name column stays exactly as it is. Old applications that read full_name continue to work without any code changes. New applications can start writing to first_name and last_name while still reading full_name for backward compatibility.

No downtime. No coordinated release. No risk of breaking existing queries.

The Critical Rule: New Columns Must Be Optional

The most common mistake in the expand phase is adding a new column with NOT NULL and no default value. This immediately breaks any application that inserts rows without specifying the new column. Old applications that do not know the column exists will fail on every insert.

The rule is simple: every new column must be nullable or have a sensible default value. If you need a NOT NULL constraint, add the column as nullable first, backfill the data, and then add the constraint in a later phase. Do not force all existing applications to change their insert statements at the same time.

The same logic applies to new tables. A new table does not change any existing structure. Old applications never need to know it exists. New applications can start writing to it immediately. There is no conflict because nothing changed in the tables they already use.

Handling Constraints Safely

Constraints need extra care during the expand phase. If you add a UNIQUE constraint on a new column, make sure existing data does not violate it. If you add a FOREIGN KEY, ensure all existing rows reference valid parent rows.

For CHECK constraints, verify that the condition does not conflict with existing data. Some databases support a NOT VALID option that applies the constraint only to new rows, letting you validate existing data separately later. This is useful when you are unsure about data quality in the old rows.

The principle is the same: do not introduce a constraint that will fail against existing data. If you cannot guarantee it, defer the constraint or add it in a way that does not block writes.

Naming Matters

New columns and tables need clear names that distinguish them from the old structure. Avoid names like name_new, temp_name, or name_v2. These create confusion during the contract phase when you need to know which structure is the canonical one.

Use names that describe the actual data. first_name and last_name are better than name_split_1 and name_split_2. Good names make the transition easier for everyone who works with the schema later.

What the Expand Phase Does Not Require

The expand phase does not require any code changes in old applications. They continue to use the same schema, the same queries, and the same logic. This is what makes the expand phase safe to run at any time, even during peak production hours.

No downtime. No application restarts. No queries suddenly failing because a column disappeared. The only change is in the database schema, and that change is purely additive.

When the Expand Phase Is Complete

The expand phase finishes when the new structures exist in the database and are ready for use. Old applications still use the old structures. New applications can start using the new structures. Both paths work simultaneously.

At this point, you have a database that supports two versions of the schema. This is the foundation for the next step: gradually migrating applications to use the new structures without breaking anything.

Practical Checklist for the Expand Phase

  • New columns are nullable or have a default value
  • New tables do not modify existing table structures
  • New constraints do not conflict with existing data
  • Names clearly distinguish new structures from old ones
  • Old applications continue to work without code changes
  • New applications can start using new structures immediately

The Takeaway

The expand phase is the safest database change you can make because it only adds. No removal, no modification, no risk of breaking running applications. Add new columns as nullable, keep old columns intact, and let both schemas coexist. This gives you the freedom to migrate applications at your own pace, without coordinating a big-bang release or scheduling downtime.