What is a safe schema rollout in CI/CD, and why is the mixed-version window the real risk?

A safe schema rollout treats the database change as its own deployment step. The pipeline runs it exactly once, either before or after the application rollout, and it must be backward-compatible with whichever application version is still serving traffic. The SQL is rarely the hard part. The risk sits in the mixed-version window: during a rolling deploy, replicas running version N-1 and version N both read and write the same schema. If the migration renames a column, N-1 pods start failing the moment it commits. If the code ships first, N pods query a column that does not exist yet.

The expand/contract pattern closes that window. You first expand the schema by adding columns, tables and indexes that old code ignores. Then you switch the code to use the new structures, backfill existing rows, and contract by dropping the old structures in a later release, once no running version depends on them.

Change style Safe during mixed-version window Releases per logical change
In-place rename or drop No 1
Expand/contract Yes 2–3 or more

This applies to backend and platform engineers running zero-downtime deploys with multiple replicas, or with more than one service on a shared database. A single-instance app with a maintenance window can skip it.

The cost is real. One logical change spans several releases. Dual-write or dual-read code has to be tracked and removed, or it becomes permanent.

Who should use this checklist?

Use this checklist if any of the following describes your system:

  • You run rolling or blue/green deploys with more than one replica. Old and new application versions overlap, so every schema change must work with both.
  • Several services or jobs share one database. You cannot deploy every consumer at the same moment, so the schema has to stay compatible with all of them across their own release cycles.
  • You have tables large enough that locks or rewrites matter. A statement that is instant on a small table can block traffic for minutes on a large one.
  • Migrations run from a pipeline rather than by hand. Automation has to detect failures, stop the rollout and leave a record, because nobody is watching a terminal.

The roles involved are the migration author, who writes the expand/contract steps; the reviewer, who checks lock behavior and N-1 compatibility; the platform or pipeline owner, who maintains the gated migration step; and the on-call engineer, who watches the deploy and decides on rollback.

Do not use the full process for a single-instance service, a prototype, or an internal tool where a maintenance window with planned downtime is acceptable. Stopping the app, migrating and restarting is simpler there and carries no mixed-version risk.

How does a gated, expand/contract rollout work end to end?

The process connects the sections below into one flow:

  1. Author the expand migration. Add new columns, tables or indexes that the current code ignores. Set lock_timeout and statement_timeout at the top of the session, and use online variants such as CREATE INDEX CONCURRENTLY and NOT VALID constraints where the engine supports them.
  2. Run automated checks in CI. The pull request applies the full migration history to an empty database, applies pending migrations to production-shaped data while recording runtime, and runs the N-1 release's tests against the new schema.
  3. Classify and review. The reviewer puts the change on the fast path or the full-review path and confirms the checklist items: N-1 compatibility, locks, runtime, backfill strategy, rollback path and on-call owner.
  4. Run the migration once as a gated step. After merge, the pipeline launches a single migration Job. A database-level lock prevents duplicate runs, backoffLimit: 0 and activeDeadlineSeconds make failures surface quickly, and the pipeline waits for Complete or Failed before going further.
  5. Roll out the application. Only after the migration succeeds does the Deployment change. Old and new pods coexist safely because the schema supports both.
  6. Backfill separately. A batched, resumable job fills new columns in short transactions, outside the DDL migration.
  7. Watch and record. The deploy record stores the applied migration version, logs go to durable storage, and the on-call engineer watches lock waits, replication lag and error rates against thresholds agreed in the pull request.
  8. Contract in a later release. Once no running version reads the old structures, a second migration drops them, following the same gated path.

The core rule behind every step: the database change and the code change are deployed separately, and each one must be safe on its own with whichever version of the other is live.

Where should database migrations run: pipeline stage, Kubernetes Job, init container or app startup?

Per-replica options look convenient because they ship with the image, but each one runs once per pod. Three replicas rolling out means three processes trying the same DDL at once. Most migration tools take an advisory lock or write to a version table, so the losers usually wait or no-op. That only protects you if the tool does it correctly. It also ties readiness to DDL duration. A slow index build holds every new pod out of service, and if it outlasts the startup probe, Kubernetes restarts the pod and the migration starts again.

A gated step separates the two. The pipeline runs the migration once and waits for it to succeed before the Deployment changes.

build image
    │
    ▼
migration Job (expand) ──fails──► stop, Deployment untouched
    │
    ▼
wait for Job completion
    │
    ▼
Deployment rollout (old + new pods coexist)
    │
    ▼  later release, after old pods are gone
contract migration Job
Location Runs Readiness coupled to DDL Best fit
Dedicated CI/CD stage Once No Multi-replica, shared databases
Kubernetes Job, gated Once No Cluster-native pipelines
Init container Per pod Yes Rarely; Job is usually better
App startup hook Per pod Yes Single replica, low traffic

A startup hook is still defensible when the service has one replica and can tolerate a short outage. It needs no extra pipeline plumbing.

The gated step has costs. It adds pipeline latency and a new failure point. The Job can hang, lose credentials, or get evicted, and the pipeline has to detect and report each of those outcomes.

 ┌─────────────┐   ┌──────────────────┐  fails  ┌──────────────────────┐
 │ build image │──►│ migration Job    │────────►│ stop pipeline;       │
 └─────────────┘   │ expand, runs once│         │ Deployment untouched │
                   └────────┬─────────┘         └──────────────────────┘
                            │ succeeds
                            ▼
                   ┌──────────────────┐
                   │ wait for Job     │
                   │ completion       │
                   └────────┬─────────┘
                            │
                            ▼
         ┌──────────────────────────────────────┐
         │ Deployment rollout                   │
         │ ┌───────┐ ┌───────┐ ┌───────┐        │
         │ │old pod│ │new pod│ │new pod│        │
         │ └───────┘ └───────┘ └───────┘        │
         │ old + new pods coexist               │
         │ readiness not tied to DDL            │
         └──────────────────┬───────────────────┘
                            │ later release, after old pods are gone
                            ▼
                   ┌──────────────────┐
                   │ contract         │
                   │ migration Job    │
                   └──────────────────┘

Gated rollout: the expand migration runs once as a Job and must complete before the Deployment changes. A failed Job stops the pipeline, and the contract migration runs in a later release after the old pods are gone.

How do you guarantee a migration Job runs exactly once and fails visibly?

Kubernetes will not guarantee single execution for you. A Job can start a second pod after an eviction or node failure while the first is still running. Single execution has to come from the database. Rely on the migration tool's own lock, or take a database advisory lock (in PostgreSQL, pg_advisory_lock) before applying anything, so a concurrent run blocks or exits instead of applying the same change twice.

Bound the Job so failures surface quickly. Set backoffLimit low, often zero, because blind retries of DDL rarely help. Set activeDeadlineSeconds so a migration stuck behind a lock gets killed instead of hanging. The pipeline should wait for the Job to report either Complete or Failed, with its own timeout, and print the pod logs on failure so nobody has to dig through the cluster to find them.

Timeouts and retries can leave a migration partially applied. MySQL commits implicitly around DDL, and PostgreSQL's CREATE INDEX CONCURRENTLY cannot run inside a transaction and leaves an invalid index if it fails. When that happens, the tool's state table can say a version was not applied while half of it exists. Detect this by diffing the live schema against the expected one after any failed run. Reconcile by hand: finish or drop the partial objects, then correct the state table explicitly. Do not just rerun the migration.

Run migrations under a dedicated DDL-capable role, separate from the application's role. Inject its credentials through the platform's secret mechanism and mount them only into the migration Job. This means one more credential to rotate and one more identity to audit.

apiVersion: batch/v1
kind: Job
metadata:
  name: db-migrate-2024-06-01-add-orders-index
  namespace: shop
spec:
  # No automatic retries: a re-run of DDL after a partial failure
  # needs a human to inspect the schema first.
  backoffLimit: 0
  # Kill the Job if it hangs (e.g. waiting on a lock) longer than 10 minutes.
  activeDeadlineSeconds: 600
  # Keep the finished Job and pod around for an hour so logs can be collected.
  ttlSecondsAfterFinished: 3600
  template:
    spec:
      # Never restart the container in place; a failure must surface as Failed.
      restartPolicy: Never
      containers:
        - name: migrate
          image: registry.example.com/shop/migrations:4f2c9e1
          # The migration tool takes its own lock (or pg_advisory_lock)
          # before applying anything, so a duplicate pod blocks or exits.
          command: ["/app/run-migrations.sh"]
          env:
            # DDL-capable role, separate from the app's runtime role.
            # This Secret is referenced only by this Job, not by app Deployments.
            - name: DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: shop-db-migrator
                  key: database-url
          resources:
            requests:
              cpu: 100m
              memory: 128Mi

A migration Job with no retries, a hard deadline, a Never restart policy, and credentials for a dedicated migrator role taken from a Secret that only this Job references.

How do you check lock and runtime impact before merging a schema change?

Before a migration merges, the reviewer should know which locks each statement takes and whether it rewrites the table. In PostgreSQL, the dangerous case is often not the slow statement itself but the lock queue. An ALTER TABLE waiting for an ACCESS EXCLUSIVE lock blocks every query that arrives after it, including plain reads, until it gets the lock and finishes.

Set limits at the top of the migration session. A SET lock_timeout of a few seconds makes the statement give up instead of stalling traffic. statement_timeout caps runaway work. The trade-off is that on busy tables these timeouts will fail migrations. The pipeline then needs a retry with backoff, or the change has to be scheduled off-peak.

Operation Likely impact Safer alternative
CREATE INDEX Blocks writes for the full build CREATE INDEX CONCURRENTLY, which cannot run inside a transaction block
ALTER COLUMN ... TYPE Usually a full rewrite under an exclusive lock Add a new column, backfill it, then switch reads and writes to it
ADD COLUMN with a volatile default Full table rewrite Add the column as nullable, then backfill
ADD FOREIGN KEY Validates every row while holding locks on both tables Add with NOT VALID, then run VALIDATE CONSTRAINT separately
SET NOT NULL Full scan under an exclusive lock Add a CHECK constraint as NOT VALID, validate it, then set NOT NULL
Large UPDATE backfill Long transaction, row locks, table bloat Batch it in a separate job outside the migration transaction

Exact locking and rewrite behavior varies by database engine and version. Check the documentation for the version you run in production, not the latest release.

-- Session limits: give up quickly on locks instead of queueing traffic behind us.
SET lock_timeout = '3s';
SET statement_timeout = '60s';

-- 1. Add the column as nullable with no default: metadata-only change.
ALTER TABLE orders ADD COLUMN customer_ref bigint;

-- 2. Foreign key without scanning existing rows; only new writes are checked.
ALTER TABLE orders
  ADD CONSTRAINT orders_customer_ref_fkey
  FOREIGN KEY (customer_ref) REFERENCES customers (id) NOT VALID;

-- 3. Validate separately; this takes a weaker lock than the original ADD.
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_ref_fkey;

-- 4. Build the index without blocking writes.
-- Must run outside a transaction block, so run it as its own statement.
-- The build can take a while, so lift the statement cap for this step only.
SET statement_timeout = 0;
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_ref_idx
  ON orders (customer_ref);

-- If a concurrent build fails, it can leave an INVALID index behind.
-- Find any leftovers, then DROP INDEX CONCURRENTLY and retry.
SELECT indexrelid::regclass AS invalid_index
FROM pg_index
WHERE indisvalid = false;

-- 5. Backfill runs in a separate job, one short batch per transaction.
-- Repeat until it reports 0 rows updated.
UPDATE orders o
SET customer_ref = o.legacy_customer_id
WHERE o.id IN (
  SELECT id FROM orders
  WHERE customer_ref IS NULL AND legacy_customer_id IS NOT NULL
  ORDER BY id
  LIMIT 5000
);

A PostgreSQL migration that sets lock and statement timeouts, adds a foreign key as NOT VALID then validates it, builds an index concurrently, and backfills in small batches from a separate job.

How should you test database migrations in CI?

Three checks cover different failure modes, and each one catches problems the others miss.

Apply from an empty schema. Run every migration in order against a fresh database. This shows that the migration history is still a working source of truth. It catches migrations that were edited after being applied somewhere, ordering mistakes, and changes that only worked because a developer's local database already had some missing object.

Apply on top of a production-shaped dataset. Restore a recent snapshot with sensitive data removed, or seed a dataset with realistic types, nulls, duplicates and foreign-key edge cases. Then apply only the new migrations. Empty tables never fail a NOT NULL backfill or a unique constraint. Real data shapes do.

Run the N-1 test suite against the new schema. Check out the previous release's application code and run its tests against the migrated database. During the mixed-version window, the old version is still serving traffic, and this check proves it keeps working.

A pipeline step outline:

  1. Start an ephemeral database at the same engine version as production.
  2. Apply all migrations to the empty database, then run the current test suite.
  3. Restore the snapshot or seed, apply the pending migrations, and record the wall-clock time.
  4. Check out the N-1 tag and run its tests against that schema.

Keep the runtime number from step 3 in perspective. Seeded data rarely matches production table sizes, concurrent traffic or lock contention. Present it as an illustrative estimate and state the assumptions: "Backfill took 40 s on 2M seeded rows. Assuming linear scaling and no contention, 50M rows would take about 17 minutes." Reviewers can then challenge the assumptions directly, and nobody treats the figure as a measured production result.

name: migration-checks
on: pull_request
jobs:
  migrations:
    runs-on: ubuntu-latest
    services:
      postgres:
        image: postgres:16            # pin to production's engine version
        env: { POSTGRES_PASSWORD: ci-only-password }
        ports: ["5432:5432"]
        options: >-
          --health-cmd pg_isready --health-interval 5s --health-retries 10
    env:
      PGHOST: localhost
      PGUSER: postgres
      PGPASSWORD: ci-only-password
      PREV_RELEASE_TAG: v1.4.0        # N-1 release still serving traffic
    steps:
      - uses: actions/checkout@v4
        with: { fetch-depth: 0 }      # full history so the N-1 tag is available
      - name: "Check 1: full history on an empty schema"
        run: |
          createdb empty_check
          DATABASE_URL=postgres:///empty_check ./scripts/migrate up
          DATABASE_URL=postgres:///empty_check ./scripts/test
      - name: "Check 2: pending migrations on production-shaped data"
        run: |
          createdb shaped_check
          # Sanitized dump already at production's current migration version
          pg_restore --no-owner -d shaped_check fixtures/sanitized.dump
          start=$(date +%s)
          DATABASE_URL=postgres:///shaped_check ./scripts/migrate up
          # Seeded timing only: report as an illustrative estimate, not a measurement
          echo "Pending migrations took $(( $(date +%s) - start ))s on seeded data"
      - name: "Check 3: N-1 test suite against the new schema"
        run: |
          git worktree add ../n-minus-1 "$PREV_RELEASE_TAG"
          cd ../n-minus-1
          # Old code must not run its own migrations; it only exercises the schema
          DATABASE_URL=postgres:///shaped_check ./scripts/test

A GitHub Actions job that applies all migrations to an empty database, applies pending migrations to a restored sanitized dump while recording wall-clock time, and runs the previous release's tests against the migrated schema.

What should a rollback plan for a schema migration include?

Before merging, the pull request should name one rollback path for the migration. If the author cannot name one, the change is not ready.

Rollback path Use it when What it costs
Roll the application forward with a fix The schema is additive and the bug is in code Time to ship a patch while the new schema stays in place
Down migration The change lost no data, such as an added index or a nullable column Lock and runtime cost similar to the forward migration
Restore from backup only The change dropped or rewrote data Every write made since the snapshot

Rolling back code is usually the safer choice. Expand-style changes are designed so the previous application version can run against the new schema. Redeploying an old image removes the bug and leaves the schema untouched.

Down migrations give false confidence for lossy changes. A down migration can recreate a dropped column, but the column comes back empty. It can also try to reverse a data transformation, but if that transformation merged or truncated values, the original data is gone. Treat these down migrations as schema repair, not data recovery. Restoring from backup does recover the old data, but it discards every order, signup or update that happened after the snapshot. You then need a reconciliation plan for those writes, and the person running the restore needs to know that before starting it.

Observability makes the rollback decision possible:

  • Record the applied migration version in the deploy record. Incident responders should not have to work out the schema state from memory.
  • Ship pipeline and Job logs to durable storage. Kubernetes can delete completed Job pods along with their logs.
  • Watch lock waits, replication lag and application error rates during the migration and for a defined window afterwards. Agree on the abort thresholds in the pull request before the deploy, not during an incident.

Pre-merge and pre-deploy checklist: when to use the full review and when to take the fast path

Reviewers should be able to answer every item with a yes, a number or a name. If any answer is "unknown", the migration is not ready to merge.

  • N-1 compatibility: the currently deployed code keeps working against the new schema.
  • Lock behavior: the lock level each statement takes and how long it holds it.
  • Estimated runtime: measured against production-sized data, not a developer laptop.
  • Backfill strategy: batched, throttled, resumable, and separate from the DDL.
  • Rollback path: a forward fix or a reverse migration, with any data-loss caveat stated.
  • On call: a named person who is available for the deploy and the watch window.

Choosing the review path

Change type Path Reason
New table, new nullable column without a default, new index built online Fast path Old code ignores it, and locking is brief or absent
Drop or rename, type change, NOT NULL, new constraint, large backfill Full review Breaks N-1 code or holds locks proportional to table size

For the fast path, one reviewer confirms the classification. Everything else needs the full checklist and a second reviewer.

A destructive change now becomes several releases: expand, migrate data, switch reads, then contract. Each step is a separate deploy that has to be coordinated. That adds real development cost for every logical change.

In return, incidents are less likely to land during peak traffic, nobody spends an evening unwinding a half-applied migration, and review time becomes predictable. The fast path keeps the overhead off the large majority of changes, which are trivial.

Skip all of this for a single-instance internal tool, or when a maintenance window with acceptable downtime lets you stop the app, migrate, and restart.

How can this process save engineering time?

The process adds work up front, but it removes several kinds of expensive, unplanned work later.

  • Fewer migration incidents to debug. A locked table during peak traffic, or a rolling deploy where N-1 pods fail against a renamed column, pulls several engineers into an incident. Lock timeouts, expand/contract and the N-1 test suite stop most of these before production.
  • No manual cleanup of half-applied migrations. Single execution through a database lock, backoffLimit: 0 and a hard deadline mean a failed migration stops cleanly with its logs captured. Engineers do not have to work out which statements ran on which retry.
  • Faster diagnosis when something does go wrong. The applied migration version in the deploy record, durable Job logs and agreed abort thresholds let on-call make a rollback decision without reconstructing state from memory.
  • Automated checks replace ad hoc manual testing. The three CI checks run on every pull request, so reviewers do not need to set up a local database or guess how a migration behaves on real data shapes.
  • Shorter reviews for routine changes. The fast path lets one reviewer approve additive changes quickly, so the full review effort goes only to changes that can break N-1 code or hold long locks.
  • Fewer coordinated deploy windows. Because schema and code changes are each backward-compatible, teams sharing a database do not need to schedule joint releases or after-hours maintenance for most changes.

To judge whether the overhead pays off for your team, put your own numbers into a simple comparison. The figures below are an illustrative estimate with assumed inputs, not measured results:

  • Assumed: 3 risky migrations per quarter go through full review, each adding 1 engineer-hour of review and checklist work. That is 3 engineer-hours per quarter.
  • Assumed: without the process, one of those migrations per quarter causes an incident or a half-applied state that takes 3 engineers 3 hours to diagnose, repair and reconcile. That is 9 engineer-hours, plus any customer-facing downtime.
  • Under these assumptions, the process saves about 6 engineer-hours per quarter before counting the cost of downtime or the disruption of out-of-hours work.

If your assumed incident rate is far lower, or your risky changes are rare, the fast path and a lighter review may be enough. The comparison shows which inputs drive the decision, so the team can argue about the assumptions instead of the conclusion.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to database migrations ci cd, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.