Architecture
Changing a SaaS Schema While Old and New Code Coexist
By Vivek Jalondhara · 5 min read
Published
Plan database changes around overlapping application versions, resumable backfills, and the point where rollback stops being simple.
Adding a column is easy to describe. Releasing the code that depends on it is harder when old API instances, background workers, and administrative tools remain active during deployment. They all share the same database while expecting slightly different shapes.
This educational migration uses a hypothetical SmackDab page-definition table. It introduces a saved-schema version field; it does not describe a private migration or guarantee a deployment without interruption.
Write a compatibility table before SQL
List the readers and writers that touch the page definition: the editor API, renderer, worker, and any import tool. For each, state whether it accepts a missing version and what it writes. The important question is whether every combination works during the rollout, not whether the newest application works against an empty database.
In this scenario, all historical definitions use the original format, so assigning version one is a verified mapping. That assumption must be checked against actual stored data before a real migration. If historical formats differ, a bulk constant update would give invalid pages a misleading version instead of migrating them.
Expand before requiring the new field
First add a nullable version column. Deploy code that reads a missing value as the verified legacy version and writes an explicit version for newly saved pages. Old writers can still omit the column. This overlap allows the database and application releases to happen in a controlled order.
Keep the fallback visible as temporary compatibility behavior, with a removal condition. A fallback that remains forever can conceal a broken writer. Observe which write paths still omit the field, and account for delayed jobs running older code.
Backfill in bounded, repeatable batches
Update rows whose version is still null, using a stable key range or another resumable boundary. Commit between batches and record progress. The predicate makes repeating a batch safe for this mapping: an already populated version is not overwritten. Keep the migration’s business rule independent of whichever application default happens to exist later.
Pause if lock waits, replication delay, or normal request behavior changes materially. A small transaction is easier to retry and reduces the amount of work rolled back after a failure. Do not use SKIP LOCKED as proof that every eligible row was visited; skipped rows require another sweep and a final completeness check.
Validate only after old writers are gone
The example adds a check constraint as NOT VALID after compatible writers are deployed. PostgreSQL can then enforce that check for new or changed rows while historical rows are validated separately. Adding it earlier would reject an old writer that still creates a null version.
Validation is still database work and acquires locks; it is not a zero-cost flag flip. Once validation proves the invariant, set the column to NOT NULL and remove the temporary check. Run each planned stage deliberately, with appropriate lock and statement timeouts, instead of pasting every stage into one long deployment transaction.
Treat indexes as their own operation
A new query path may also need an index, but that is a separate operational decision. PostgreSQL’s concurrent index build permits ordinary writes during construction and has restrictions, including running outside a transaction block. A failed attempt can leave an invalid index that needs investigation and cleanup before retrying.
Read the generated migration rather than assuming the ORM chose the desired locking behavior. Verify the deployed PostgreSQL version and the migration runner’s transaction handling. Index creation, constraint validation, and a data backfill need different success checks.
Make contraction a later release
Only remove the missing-version fallback after the constraint is established and all relevant writers comply. If an older application version cannot operate with the new constraint, rolling application code back now requires a compatibility plan. Destructive changes such as dropping an old representation make that boundary even more consequential.
Rehearse with representative saved definitions, concurrent edits, and an interrupted backfill. Keep the old data until the new representation has been read successfully through the real workflow. A migration is complete when the application behaves correctly across the transition, not merely when its SQL process exits.
Separate expansion from enforcement
Educational SQL stages for a page_definitions table. Compatible readers and writers must be deployed and historical rows backfilled between expansion and enforcement. Do not run this as one uninterrupted migration.
Illustrative example · SQL · Simplified for explanation
-- Stage 1: expand, then deploy compatible application code.
ALTER TABLE page_definitions ADD COLUMN schema_version integer;
-- Backfill verified legacy rows in bounded transactions.
-- Retire old writers before continuing.
-- Stage 2: enforce future writes without the initial table scan.
ALTER TABLE page_definitions
ADD CONSTRAINT page_version_present
CHECK (schema_version IS NOT NULL) NOT VALID;
-- Stage 3: check historical rows, then enforce at the column.
ALTER TABLE page_definitions
VALIDATE CONSTRAINT page_version_present;
ALTER TABLE page_definitions
ALTER COLUMN schema_version SET NOT NULL;
ALTER TABLE page_definitions
DROP CONSTRAINT page_version_present;- Inventory readers and writers
- Expand and deploy compatibility
- Backfill and verify completeness
- Validate the invariant
- Retire legacy behavior
Conceptual flow for the example above.
Tradeoff to keep in mind
This fragment assumes the legacy-to-version mapping is already verified. It omits the batch runner, version-value validation, operational timeouts, and a rollback plan specific to the deployment.