Database Migration Strategy #

In traditional monolithic application architecture, database migrations (like changing column types, adding tables, or changing data relationships) are usually done during an agreed maintenance window. The entire application is shut down (downtime), the database migration script runs, and the new application starts from a clean state. However, in the cloud-native era demanding 24/7 service availability, this approach is no longer relevant. We must be able to do database migrations without a single moment of downtime.

In Kubernetes, the database migration challenge is multiplied because of the Rolling Update mechanism. During the release transition lasting a few minutes, old version (v1) Pods and new version (v2) Pods run simultaneously in the cluster, both accessing the same physical database. If we directly apply database schema changes that cut backward compatibility (non-backward compatible), one Pod version is guaranteed to suffer mass query failures. This article dissects database schema orchestration strategies using the Expand-Contract pattern, implementation using Kubernetes Jobs, and automatic migration tooling integration in production.

The Classic Problem: One Database for Two Application Versions #

When we trigger a Rolling Update in Kubernetes, we indirectly create a transition period where two different application logic versions interact with one database simultaneously.

Let’s say we want to change a database table column name from user_name to username:

flowchart TD
    Traffic["User Traffic"] --> PodV1["v1 Pod (Old)"]
    Traffic --> PodV2["v2 Pod (New)"]

    PodV1 -->|"SELECT user_name"| DB["Shared Database\n(Table structure: id, username, email)"]
    PodV2 -->|"SELECT username"| DB

If the database migration script directly changes the column name before the Rolling Update starts:

  • New v2 Pods starting up run smoothly because the SELECT username query finds its column in the database.
  • Old v1 Pods still actively serving remaining user requests immediately produce HTTP 500 errors because the SELECT user_name query fails to execute on a column that no longer exists.

The main goal of the database migration strategy in Kubernetes is guaranteeing backward compatibility at every transition step, so no query from either v1 or v2 versions ever fails.

The Expand-Contract Pattern (Evolve Pattern) #

To elegantly solve the problem above without downtime, we use the Expand-Contract pattern (sometimes called Parallel Run). This pattern splits one backward-compatibility-breaking database schema change into five separate release phases, each phase being safe for all active application versions.

Let’s take the example of changing the user_name column name to username on the users table:

flowchart TD
    Start["Phase 1: Initial State<br/>'(Only the user_name column exists)'"] --> Step1["Phase 2: Expand (Additive)<br/>'(Create the username column, sync data)'"]
    Step1 --> Step2["Phase 3: Deploy Interim App<br/>'(App reads user_name, writes to BOTH)'"]
    Step2 --> Step3["Phase 4: Backfill & Verify<br/>'(Migrate old rows, switch reads to username)'"]
    Step3 --> Step4["Phase 5: Contract (Destructive)<br/>'(Drop the old user_name column from the database)'"]
    
    style Step1 stroke:#0288d1,stroke-width:2px
    style Step3 stroke:#f57c00,stroke-width:2px
    style Step4 stroke:#388e3c,stroke-width:2px

Phase 1: Initial State #

The initial database schema only contains the old column:

  • The v1 application reads and writes to the user_name column.

Phase 2: Expand Stage (Additive Database Release) #

We add the new column without touching or deleting the old column.

  • Database Schema: Run an SQL query to create the new username column (allowed to be Null temporarily).
    ALTER TABLE users ADD COLUMN username VARCHAR(255) NULL;
  • Application: v1 Pods keep running and only interact with user_name.

Phase 3: Deploy Interim Application Stage (Dual-Write) #

We release an intermediate application code version (interim/v1.5) into the cluster using a Rolling Update.

  • Application Logic: This interim application is specifically designed to read from the old column (user_name), but write to both columns at once (user_name and username) for every new transaction (dual-write).
  • Result: New data rows are guaranteed to have synchronized values in both columns. Remaining old v1 Pods stay safe reading user_name.

Phase 4: Data Backfill & Validation Stage #

Historical data before phase 3 doesn’t have values in the new username column. We must run a batch script (data backfill) slowly in the background to copy values from user_name to username on old rows not yet updated.

-- Run gradually (batching) to avoid table locks on large databases
UPDATE users SET username = user_name WHERE username IS NULL;

After all data is 100% synchronized, we release the v2 application version.

  • v2 Application Logic: Reads and writes exclusively only to the new column (username).
  • Rolling Update: All interim Pods (v1.5) are replaced by v2 Pods. After the transition completes, no more processes read the user_name column.

Phase 5: Contract Stage (Destructive Database Release) #

The final phase is cleanup. Because no applications in the cluster access the old column anymore, we can safely drop the user_name column without crash risk.

ALTER TABLE users DROP COLUMN user_name;

Deployment Ordering and Lifecycle Orchestration #

The execution order of database migrations relative to application deployment timing in Kubernetes is an iron rule that must not be violated:

  • Additive Statements (Additive Migrations): Like creating new tables, adding new columns, or creating new indexes.
    • Rule: Must run BEFORE the new application version (v2) deploys. If v2 containers start and don’t find the new column they need, the application immediately crash-loops.
  • Destructive Statements (Destructive Migrations): Like dropping old columns, removing stale indexes, or deleting tables.
    • Rule: Must run AFTER all old version (v1) application Pods are completely dead from the cluster.

Running Migrations Using Kubernetes Jobs #

One of the most common anti-patterns is embedding database migration commands into application container initialization scripts (e.g. in the Docker ENTRYPOINT or CMD like python manage.py migrate or npm run db:migrate).

Why Is In-Container Migration Very Dangerous? #

  1. Race Conditions: If we scale the Deployment to 5 Pod replicas, all five new Pods try executing the same database migration script simultaneously at startup. This triggers deadlocks on the database migration metadata table.
  2. Performance Overhead & Timeouts: Long-running migration initialization can cause startup/readiness probes to fail, forcing Kubernetes to kill the container mid-migration before it finishes.
  3. Least Privilege Issues: Our application Pods are forced to hold admin-level database credentials (with ALTER, DROP, CREATE permissions) for their entire lifetime, when at normal runtime the application only needs regular data manipulation access (SELECT, INSERT, UPDATE).

Best Solution: Kubernetes Jobs #

Use a separate Kubernetes Job object to execute the database migration. Jobs are designed to run one task until completion (run-to-completion) and only run once (single pod execution).

sequenceDiagram
    participant CI as CI/CD Pipeline
    participant Job as K8s Migration Job
    participant DB as Database Server
    participant Deploy as K8s Deployment
    
    CI->>Job: Apply the Migration Job Manifest
    Job->>DB: Run the Additive SQL Script (Expand)
    DB-->>Job: Migration Succeeded (Exit Code 0)
    Job-->>CI: Job Status: Complete
    CI->>Deploy: Apply the Deployment Update (Rolling Update v2)
    Note over Deploy: New Pods run smoothly because the database schema is ready!

Kubernetes Job Manifest for Database Migrations (Using Flyway) #

Here’s a production-ready Job manifest example using the popular migration tool Flyway to safely orchestrate database schema changes:

apiVersion: batch/v1
kind: Job
metadata:
  name: database-migration-v1-11-0
  namespace: backend
spec:
  # Retry tolerance limit if the Job fails (3 times)
  backoffLimit: 3
  # Total Job execution timeout limit (10 minutes)
  activeDeadlineSeconds: 600
  template:
    metadata:
      labels:
        app: database-migration
    spec:
      # Stop the Job if successful, retry the container if it fails
      restartPolicy: OnFailure
      initContainers:
      # A dedicated initContainer to verify the database is ready to accept connections (TCP check)
      - name: wait-for-postgres
        image: busybox:1.36
        command:
        - sh
        - -c
        - |
          until nc -z postgres-service.database.svc.cluster.local 5432; do
            echo "Waiting for PostgreSQL database connection..."
            sleep 2
          done          
      containers:
      - name: flyway
        image: flyway/flyway:9.22-alpine
        args:
        - -url=jdbc:postgresql://postgres-service.database.svc.cluster.local:5432/app_db
        - -schemas=public
        - -user=$(DB_USER)
        - -password=$(DB_PASSWORD)
        - -connectRetries=10
        - migrate
        env:
        - name: DB_USER
          valueFrom:
            secretKeyRef:
              name: db-credentials
              key: admin-username
        - name: DB_PASSWORD
          valueFrom:
            secretKeyRef:
              name: db-credentials
              key: admin-password
        volumeMounts:
        - name: sql-migrations
          mountPath: /flyway/sql
      volumes:
      # Stores the migration SQL query files inside a ConfigMap
      - name: sql-migrations
        configMap:
          name: db-sql-migrations-v1-11-0

Critical Scenarios Requiring a Maintenance Window #

Although the Expand-Contract pattern can solve most migration problems, there are certain infrastructure scenarios where service downtime (maintenance window) remains unavoidable to prevent total database degradation.

1. Column Data Type Changes on Giant Tables #

Changing a column’s data type (e.g. from INTEGER to BIGINT for transaction ID columns running out of capacity) on a table holding hundreds of millions of data rows.

  • Problem: The ALTER TABLE ... ALTER COLUMN command on most relational database engines (like PostgreSQL or MySQL) locks the table exclusively (Exclusive Table Lock). This operation forces the database to rewrite all data on the host disk, taking tens of minutes to hours. During that period, all application write queries get stuck (transaction queues pile up) and lead to total service failure.
  • Zero-Downtime Mitigation: Requires special tactics like using third-party tools (e.g. pg_repack in PostgreSQL or gh-ost/pt-online-schema-change in MySQL) that create a shadow table and migrate data asynchronously in the background. If these tools aren’t available, a maintenance window is the only safe option.

2. Database Engine Migrations (Major Engine Upgrades) #

Upgrading the database engine’s major version (e.g. upgrading PostgreSQL from version 12 to 15) or moving a physical database from local infrastructure (on-premise) to a managed cloud database service (like AWS RDS or GCP Cloud SQL).

  • Problem: The data export-import process (dump & restore) requires a pause period where data in the source database must not change at all to avoid final data inconsistencies (data drift).
  • Solution: Apply a maintenance window, enable the maintenance page on the Ingress, do a full backup, run CDC (Change Data Capture) data replication if possible to minimize the window, and do a planned cutover.

Anti-Patterns vs Best Solutions #

Here’s a compilation of common database migration management mistakes in Kubernetes along with their fixes.

Anti-Pattern 1: Ignoring Safe Database Rollback Script Preparation #

Developer teams only focus on writing up migration scripts, but don’t prepare or test down (rollback) migration scripts. When the new application version (v2) deploys and triggers critical errors in production, operators immediately run the application rollback command (kubectl rollout undo). However, because the database schema has already changed and isn’t rolled back, the just-reactivated v1 Pods immediately crash-loop because they can’t read the new column format.

Best Solution #

Every time you write a database migration script (e.g. the SQL file V1.2.0__add_discount.sql), you must create its matching rollback script (e.g. U1.2.0__add_discount.sql in Flyway). This rollback script must be periodically tested in the staging environment to guarantee its reliability for production emergency conditions.

Anti-Pattern 2: Dropping Old Columns in the Early (Expand) Phase Simultaneously #

Combining the new column addition query and the old column deletion query in the same SQL migration script file for practicality. This instantly cancels the backward-compatibility principle during the Rolling Update process.

Best Solution #

Split the process into two separate releases. The first release is only Additive (adding). The second Destructive (deleting) release may only execute after the new application is proven stable in production for at least several days.

Production Database Migration Audit Checklist #

Make sure developer and operator teams audit using the following checklist before executing database migrations in production:

SCHEMA & DATA COMPATIBILITY:
  □ The new database schema is verified backward-compatible with the old Pod code (v1).
  □ The Expand-Contract pattern is applied for breaking changes (e.g. column name / data type changes).
  □ Data backfill scripts are prepared separately with batch size limits to avoid database CPU spikes.
  □ Database rollback scripts (Down Migrations) are created and tested for feasibility.

KUBERNETES JOB ORCHESTRATION:
  □ Migration scripts run through a separate Kubernetes Job object (not in-app startup).
  □ The migration Job includes a 'wait-for-db' initContainer to verify database connectivity.
  □ Migration Job database credentials use a dedicated admin-permission account, separate from runtime Pods.
  □ The migration Job timeout limit (activeDeadlineSeconds) is set reasonably.

EMERGENCY & BACKUP PROCEDURES:
  □ Automatic database backups (snapshots) are confirmed to have run successfully before the migration Job starts.
  □ Data recovery procedures (backup restores) have been tested to measure RTO (Recovery Time Objective).
  □ Operator teams have quick access to migration Job execution logs (`kubectl logs job/...`) to monitor SQL syntax errors.

Summary #

  • Expand-Contract for zero-downtime — Split backward-compatibility-breaking database migrations into several phases: add columns (expand), deploy a dual-write interim app, backfill data, and finally drop stale columns (contract).
  • Use Kubernetes Jobs — Never run automatic database migrations in the application container startup main thread to avoid deadlocks and runtime access restrictions.
  • Additive before, Destructive after — Run column addition SQL queries before new Pod deployment starts, and run deletion queries after all old Pods die from the cluster.
  • Use wait-for-db initContainers — Equip migration Jobs with an initContainer doing TCP port connectivity checks to the database before the main migration container runs.
  • Understand table lock limitations — Giant table restructuring operations (like data type changes) trigger exclusive locks requiring a maintenance window if dynamic schema migration tools aren’t used.
  • Must test rollback scripts — Always prepare database rollback scripts (down migrations) aligned with application revisions to guarantee smooth emergency handling.

← Previous: Recreate   Next: Rollback Strategy →

About | Author | Content Scope | Editorial Policy | Privacy Policy | Disclaimer | Contact