Database DevOps in CI/CD: Automate DB Migrations Without Downtime

Database DevOps in CI/CD: Automate DB Migrations Without Downtime

We integrate database migration automation directly into your CI/CD pipeline, so that every schema change goes through review, testing, and controlled deployment. Forget manual ALTER TABLE in production — this is a risk we eliminate. In practice, this means: each migration is versioned, undergoes SQL linting, is executed before the application deployment, and if an error occurs, it blocks the release.

For example, on one project (PostgreSQL, 500 GB of data), the team applied migrations manually, which led to three incidents per quarter: twice they forgot to apply, once they applied twice. After implementing automation, the number of incidents dropped to zero.

Why are manual migrations dangerous?

Manual application of migrations is a classic source of incidents: forgot to apply after deployment, applied twice, deployment rolled back but migration not. And ALTER TABLE orders ADD COLUMN discount_cents INT on 200 million rows takes 40 minutes and blocks all INSERT/UPDATE. Database DevOps solves these problems: each migration is versioned, idempotent, and goes through the same pipeline as code.

How to avoid locks during mass DDL?

For tables with millions of rows, a standard ALTER TABLE is dangerous. Use:

• CONCURRENTLY for creating indexes (does not block writes).
• pg_repack for reorganizing tables without downtime.
• Backfill — gradual filling of a column with DEFAULT NULL followed by adding a constraint.

For example, adding a column status VARCHAR(16) to a table with 100 million rows: without backfill — 30 minutes of lock, with backfill — zero seconds.

How to ensure zero-downtime during migrations?

For tables with millions of rows, standard DDL can cause long locks. Use CONCURRENTLY for indexes, pg_repack for reorganization, and backfill for adding columns. In PostgreSQL, adding a column with DEFAULT NULL is instant, but without DEFAULT — a lock for full table rewrite. For tables > 10 million rows, use batch backfill: add the column as nullable, then update records in batches in the background, and finally add NOT NULL and DEFAULT. This reduces lock to milliseconds. (Flyway Documentation: Zero-downtime Migrations)

How we automate migrations in CI/CD

We set up the pipeline with Flyway or Liquibase — depending on your stack. Migrations are named following the template V{version}__{description}.sql (Flyway) or changeset (Liquibase).

1. Repository preparation: create a db/migrations directory, define the naming format.
2. SQL linting: integrate sqlfluff with rules for PostgreSQL (L003, L010, L014).
3. Secret configuration: add environment variables DB_HOST, DB_USER, DB_PASSWORD to CI/CD.
4. Migration command: execute flyway migrate with parameters -connectRetries=10 and -validateOnMigrate=true.
5. Rollback validation: on staging, test rollback to a specific version.

Order matters: the migration is executed before the application deployment. If it fails, the deployment does not start.

Example migration Flyway:

-- V5__create_idx_orders_user_id.sql CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_user_id ON orders(user_id); 

Pipeline GitHub Actions with validation and migration:

---
name: Database Migration
on:
  push:
    branches: [main]
jobs:
  lint-and-migrate:
    runs-on: ubuntu-latest
    environment: production
    steps:
      - uses: actions/checkout@v4
      - name: Lint SQL
        run: |
          pip install sqlfluff
          sqlfluff lint db/migrations/ --dialect postgres --rules L003,L010,L014
      - name: Flyway migrate
        uses: docker://flyway/flyway:10-alpine
        with:
          args: >
            -url=jdbc:postgresql://${{ secrets.DB_HOST }}:5432/${{ secrets.DB_NAME }}
            -user=${{ secrets.DB_USER }}
            -password=${{ secrets.DB_PASSWORD }}
            -locations=filesystem:./db/migrations
            -connectRetries=10
            -validateOnMigrate=true
            -outOfOrder=false
            migrate
---

Similarly, it is set up in GitLab CI and Jenkins.

Flyway vs Liquibase Comparison

Liquibase is better than Flyway in rollback and multi-database support: liquibase rollback --tag=v2.5.0 rolls back all changesets up to the tag.

Database DevOps Implementation Process

Get a consultation for your project assessment — our engineers will contact you within a day.

Common mistakes and how to avoid them

• Never edit already applied migrations. Only add new ones.
• Before DROP TABLE, take a DB snapshot — it will save you in case of error.
• For indexes on large tables, always use CONCURRENTLY.

What's included in the work

• Audit of current SQL scripts and schema
• Development of versioned migrations for Flyway or Liquibase
• Setup of CI/CD pipeline (GitHub Actions, GitLab CI, Jenkins)
• Zero-downtime strategy for large tables (pg_repack, backfill)
• Documentation on rollback and testing
• Team training (1 hour)

Our engineers have 5+ years of experience and have implemented over 40 projects on migration automation. We guarantee schema safety and absence of lock conflicts.

Timelines

Basic setup takes 2 to 4 days. If conversion of existing SQL scripts into migration format is required — up to a week. The cost is calculated individually. Contact us — we will evaluate your project turnkey.

Get in touch for a consultation on your project — we will select the optimal tool and strategy.