Designing Schemas That Can Change Safely

Learning outcomes

Database schemas are not finished artifacts. Requirements change, data volumes grow, new services appear, and old assumptions become false. A production-ready design therefore includes not only today's tables but a strategy for safe evolution.

Schema evolution is normal

Common changes include:

• adding an optional attribute;
• introducing a new reference entity;
• renaming a column;
• splitting one table into two;
• changing nullability;
• adding uniqueness or foreign-key constraints;
• changing data type;
• replacing one relationship with another.

Logical change versus deployment change

Business requirement:

WorkOrder must support priority levels.

Logical design may be simple:

priority_code

But production deployment asks additional questions: existing rows, old application versions, defaults, indexes, validation, and rollback.

Additive changes are usually safer

ALTER TABLE work_orderADD COLUMN priority_code text NULL;

An optional new column is often compatible with old code because old code ignores it.

Destructive changes are riskier

DROP COLUMN old_status;RENAME COLUMN customer_id TO account_id;ALTER COLUMN code TYPE bigint;

These can immediately break running application instances, reports, ETL, stored procedures, or external integrations.

Compatibility window

Suppose version A reads customer_id and version B reads account_id. During rolling deployment, both versions may run simultaneously. The schema must temporarily satisfy both contracts.

Know your consumers

Database consumers may include:

• web/API services;
• background jobs;
• reporting tools;
• ETL pipelines;
• admin scripts;
• data science notebooks;
• external partners.

A migration is safe only if relevant consumers are considered.

Nullability as an evolution tool

When adding a required field, start nullable:

ADD COLUMN service_region_id bigint NULL;

Then deploy writers, backfill existing rows, validate completeness, and only later add NOT NULL.

Defaults can hide semantics

A fake default such as:

priority_code = 'unknown'

may let migration succeed while corrupting business meaning. Use defaults only when they represent valid domain semantics.

Constraint rollout can be staged

For large tables:

1. add a constraint in a non-blocking/not-yet-validated mode if supported;
2. clean invalid historical rows;
3. validate the constraint;
4. make future writes enforce it.

Indexes are migrations too

A new foreign key may require an index for workload reasons. Building that index on a billion-row table is an operational event, not just one DDL statement.

Model for stable identity

Stable surrogate keys can make schema evolution easier because business labels, codes, or natural keys can change without forcing every foreign key to change.

But preserve business uniqueness

Evolution-friendly surrogate IDs do not justify discarding alternate-key constraints. Stable internal identity and business uniqueness solve different problems.

Reference tables ease controlled expansion

A status reference table can allow adding a new status value without changing table structure, while still preserving governance and workflow rules.

JSON can defer shape decisions—but not forever

Chapter 15 showed that flexible payloads can absorb variable fields. When one field becomes business-critical, migrate it into a typed relational representation rather than letting flexibility become permanent ambiguity.

WorkshopHub evolution examples

Migration design document

For significant changes, document:

current schematarget schemacompatibility periodmigration stepsbackfillvalidationapplication deployment orderfailure/rollback plancleanup step

Practice: required new column

Summary and next lesson

Safe schema evolution prefers compatibility and staged change over destructive cutovers. The next lesson formalizes backward and forward compatibility so old and new application versions can coexist during deployment.

References

• Martin Fowler, writings on evolutionary database design.
• Pramod Sadalage and Scott Ambler, Refactoring Databases.
• Martin Kleppmann, Designing Data-Intensive Applications.