Database Schema Evolution in Distributed Systems

Changing a database schema is easy when one application owns it. It becomes a coordination problem the moment other services read the same tables, because the schema is then a contract and the deploys are independent. This article describes the standard ways to evolve a shared schema safely and what coordination information you need.

Why shared schemas are hard

• Migrations and application deploys are separate events, so there is always a window where new code meets the old schema or the reverse.
• Every reader of a table is a consumer of its shape: dropping a column breaks any service that selects it.
• Readers are often not in the owning team's repository, or are reports and scripts nobody tracks.

The expand and contract pattern

Make each step backward compatible, and remove the old shape only once nothing uses it.

-- 1. expand: add the new column, keep the old one
ALTER TABLE customers ADD COLUMN full_name text;

-- 2. backfill and keep both in sync during the transition
UPDATE customers SET full_name = fullname WHERE full_name IS NULL;
-- (writers now write both columns; a trigger or application code does this)

-- 3. migrate readers to full_name, service by service

-- 4. contract: only after every reader has moved
ALTER TABLE customers DROP COLUMN fullname;

The risky step is the last one, and its safety depends entirely on knowing that every reader has moved. That is a question about people and services, not SQL.

Other common practices

• Additive-only changes by default, with removals as a deliberate, announced event.
• Expose a stable view or API rather than letting other services read base tables, so the owner can refactor underneath.
• Migration tooling with ordering (Flyway, Liquibase, Prisma, Alembic and similar) and CI checks that migrations apply on a copy of production-shaped data.
• Lock awareness. Some ALTER TABLE forms lock the table; check your database's rules before running them on large tables.

What coordination information you need

• Which services read or write each table or column (the consumer list).
• Whether each consumer has moved off a column you intend to remove.
• A record of who agreed to the removal and when.