Home · About · Services · Industries · Projects · Products · Process · Blog · FAQ · Contact
Database migrations in CI/CD: how to run them safely How we run schema migrations in CI/CD pipelines without downtime: a separate migration step, expand and contract, lock timeouts, real-database tests and…
The deploy went green. Two minutes later the API started returning errors, because the new release expected a column the migration had not finished adding. The migration itself was waiting on a lock held by a long report query, and every write to that table was now queued behind it. Nothing was wrong with the SQL. The problem was how and when it ran.
The short answer: run migrations as their own pipeline step, once, before the new code goes live. Make every schema change backward compatible with the release currently running, using the expand and contract pattern. Set a lock timeout so a blocked migration fails fast rather than stalling traffic. Test migrations against a real database in CI. Roll forward, not back.
Below is each part, with the commands and settings we use. The examples use PostgreSQL and GitHub Actions; the ideas carry over to MySQL, GitLab CI or Jenkins.
Run migrations once, as a separate step The most common setup we inherit runs migrations when the application starts: a line in the container entrypoint, or an ORM flag that migrates on boot. It works with one server. With three replicas, or a rolling deploy, several processes try to apply the same migration at the same moment. Some tools take an advisory lock to serialise this, but not all do, and even with a lock you now have application start-up waiting on DDL, health checks timing out and a deploy that is hard to reason about.
We prefer a dedicated job in the pipeline that runs the migration tool once, against the target database, and only then deploys the new application version. If the migration fails, the deploy stops and the old release keeps serving. Typical commands for that step:
Django: python manage.py migrate --noinput Alembic (FastAPI, Flask): alembic upgrade head Prisma: npx prisma migrate deploy (never migrate dev outside a developer machine) Rails: bin/rails db:migrate Plain SQL with Flyway or Liquibase: flyway migrate or liquibase update On Kubernetes the same idea becomes a Job (or a Helm pre-upgrade hook) that must complete before the Deployment rolls out, rather than an init container on every pod. On a single VPS with Docker Compose or Dokploy it can be a one-off container run from the new image before the service is restarted.
Make every change safe for the running release During any deploy there is a window when the new schema is live and the old code is still serving requests. With a rolling update that window can last minutes, and if you need to roll the application back it lasts much longer. So every migration has to work with both the previous and the next version of the code. The pattern for this is expand and contract: add new things first, switch the code over, and remove old things in a later release.
Renaming a column is the classic example. A single ALTER TABLE ... RENAME COLUMN breaks the running release instantly. Done safely, it takes several deploys:
Release 1 (expand): add the new column, nullable. Code writes to both columns and still reads the old one. Backfill: copy existing values across in batches, outside the deploy, so no single transaction locks the table for long. Release 2: code reads from the new column and keeps writing both. Release 3 (contract): code stops touching the old column. A migration in the following release drops it. This feels slow at first. In practice the extra releases are small, and each can be rolled back on its own. The same thinking applies to dropping tables, changing column types, splitting a table and making a column required.
Know which statements take heavy locks Most PostgreSQL DDL needs an ACCESS EXCLUSIVE lock on the table, even if only briefly. The statement waits for running queries to finish, and while it waits, every new query on that table queues behind it. That is how a one-second ALTER TABLE turns into a five-minute outage. Two habits prevent most of this. First, set a short lock_timeout at the top of each migration so it gives up and fails the pipeline rather than blocking traffic; you then retry at a quieter moment. Second, use the forms of each change that avoid long table rewrites or scans.
-- At the start of every migration
SET lock_timeout = '5s';
SET statement_timeout = '15min';
-- Adding a column with a constant default is a metadata change (PostgreSQL 11+)
ALTER TABLE orders ADD COLUMN source text DEFAULT 'web';
-- Foreign key without a long lock: add NOT VALID, then validate separately
ALTER TABLE orders ADD CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id) REFERENCES customers (id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_fk;
-- Making a column required without a long scan under lock (PostgreSQL 12+)
ALTER TABLE orders ADD CONSTRAINT orders_source_nn CHECK (source IS NOT NULL) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT orders_source_nn;
ALTER TABLE orders ALTER COLUMN source SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_source_nn;Indexes need special care. A plain CREATE INDEX blocks writes to the table for as long as the build takes. CREATE INDEX CONCURRENTLY does not, but it cannot run inside a transaction block, and most migration tools wrap each migration in one. Django has AddIndexConcurrently in django.contrib.postgres and a per-migration atomic = False setting, Alembic supports an autocommit block, and Flyway and Liquibase have their own switches for non-transactional scripts. If a concurrent build fails it leaves an INVALID index behind. Drop it and build again, because IF NOT EXISTS will quietly skip the broken one.
-- Must run outside a transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_created_at_idx
ON orders (created_at);MySQL 8 has its own rules. Many changes can use ALGORITHM=INSTANT or INPLACE, and specifying the algorithm explicitly makes the statement fail instead of silently copying the table. For very large tables, tools such as gh-ost or pt-online-schema-change are still the usual answer.
Test migrations in CI against a real database SQLite in tests and PostgreSQL in production will eventually bite you. Run the pipeline's migration step against the same database engine and major version as production, in a service container. We check three things on every pull request: that the migrations apply cleanly to an empty database, that there are no model changes without a matching migration, and that risky statements are flagged for review.
# .github/workflows/ci.yml (excerpt)
jobs:
migrations:
runs-on: ubuntu-latest
services:
postgres:
image: postgres:16
env:
POSTGRES_PASSWORD: test
ports: ['5432:5432']
options: >-
--health-cmd pg_isready --health-interval 5s
--health-timeout 5s --health-retries 10
env:
DATABASE_URL: postgres://postgres:test@localhost:5432/postgres
steps:
- uses: actions/checkout@v5
- uses: actions/setup-python@v6
with:
python-version: '3.12'
- run: pip install -r requirements.txt
- run: python manage.py makemigrations --check --dry-run
- run: python manage.py migrate --noinputFor the review step, a linter such as Squawk reads PostgreSQL migration SQL and warns about blocking index builds, missing lock timeouts and column type changes that rewrite the table. It will not catch everything, but it puts the risky statement in front of a reviewer. Once a month, or before a large change, it is also worth restoring a recent production backup into a staging database and timing the migration there. A change that takes two seconds on an empty CI database can take twenty minutes on a table with fifty million rows.
Wire it into the deploy safely A few pipeline settings stop the most common accidents:
Serialise deploys per environment. In GitHub Actions, a concurrency block with group: deploy-production and cancel-in-progress: false stops two runs migrating the same database at once. Use a dedicated migration role with DDL rights, separate from the application's runtime credentials, and store it as an environment secret. Gate production with an environment approval when a pull request contains a migration, so someone has read the SQL before it runs. Take or confirm a recent backup before destructive migrations. Point-in-time recovery is better still, because it lets you restore to the minute before the change. Log the migration output and the time taken to the pipeline run, so you have a record when something behaves differently in production. Plan to roll forward Down migrations are attractive in theory and rarely help in practice. Once a migration has dropped a column or transformed data, the down script cannot bring the old values back, and by the time you notice a problem, new writes have landed on the new schema. Because expand and contract keeps the schema compatible with the previous release, the usual recovery is simpler: roll the application back, leave the schema alone, then fix the problem with a new forward migration. Keep down migrations if your tool generates them, but do not make your incident plan depend on them. For destructive changes, the real rollback is the backup, which is why it must be recent and tested.
A checklist before you merge a migration Does the migration work with the code currently in production, not just the new release? Does it set lock_timeout, and does it avoid long locks on busy tables? Are new indexes built concurrently, outside a transaction? Are large backfills done in batches, outside the deploy step? Has it run in CI against the same database engine and major version as production? Do you know roughly how long it takes on production-sized data? Is the migration step separate from application start-up and serialised per environment? Is there a recent, tested backup if the change drops or rewrites data? If your pipeline still migrates on boot, or nobody is sure what happens when a migration fails halfway, that is worth fixing before the next big schema change rather than during it.
Infigini IT Services · 11 Kanwali Road, Dehradun 248001, India · info@infigini.com · +91 95684 87789