Zero-Downtime Database Migrations: How We Changed 40 Tables Without Losing a Single Request

Our payments team had a tradition. Every quarter, someone would propose a schema change, and the tech lead would open the same bookmarked Confluence page titled "Database Migration Runbook." The runbook started with "Schedule a maintenance window" and ended with "Verify data integrity and notify stakeholders." Between those two lines were forty-seven steps, a Slack escalation tree, and a note that read "Last used: Q3 2024, duration: 3 hours 22 minutes, incident created: yes."

We retired that runbook eight months ago. Every schema change since — forty tables, across six services — has deployed during business hours, through our normal CI/CD pipeline, with zero downtime and zero customer impact. No maintenance windows. No 2 AM Slack threads. No incidents.

This post walks through the patterns, the Flyway configuration, and the specific mistakes that taught us how to do it. Everything here runs in production on the systems I've built over nine years.

Why Database Migrations Break Rolling Deploys

The problem is straightforward once you see it, but it catches teams who've only ever deployed with downtime.

In a rolling deployment, Kubernetes brings up new pods while old pods are still serving traffic. For a brief window — usually thirty seconds to three minutes depending on your readiness probe configuration and pod startup time — two versions of your application are running simultaneously against the same database.

If your Flyway migration runs at application startup (the Spring Boot default), the first new pod to start alters the schema. The old pods, still handling requests, suddenly face a database that no longer matches their entity mappings.

Drop a column that old code reads? PSQLException: column "full_name" does not exist. Rename a column? Same error, different name. Add a NOT NULL column without a default? Every INSERT from the old pods fails.

I watched this happen on a Wednesday afternoon in 2023. A teammate renamed user_type to account_type in a single migration. The Flyway script ran in 200 milliseconds. The rolling deploy took ninety seconds. For those ninety seconds, every request that touched the users table returned a 500. Our error rate went from 0.02% to 34%. The PagerDuty alert fired before the deploy even finished.

The fix wasn't to deploy faster. The fix was to stop making schema changes that assume only one version of the application exists.

The Expand-Migrate-Contract Pattern

Every safe migration follows three phases, deployed as separate releases:

Expand: Add new structures (columns, tables, indexes) alongside the existing ones. New columns must be nullable or have defaults. No existing column is modified or removed. The current application version ignores the new columns — it doesn't read or write them. The new application version writes to both old and new columns (dual-write). This release is completely safe to roll back: the old code simply ignores columns it doesn't know about.

Migrate: Backfill the new columns with data from the old ones. Switch reads to the new columns. The application still writes to both — if you need to roll back to the previous version, the old columns have valid data. Run this phase, monitor for 24-48 hours, and verify data parity between old and new.

Contract: Remove the old columns. Update NOT NULL constraints on the new ones. The application no longer dual-writes. This is the only phase that's not instantly rollable — you'd need to re-expand if something goes wrong, which is why we wait at least one full sprint between Migrate and Contract.

Three deployments instead of one. More releases, less risk. The total effort is higher than a single-shot migration, but the total risk is close to zero. And "close to zero" matters when your payment processing table handles fourteen thousand transactions per hour.

A Real Migration: Splitting Amount Storage

Our transactions table stored monetary amounts as DOUBLE PRECISION in a column called amount. This worked until it didn't — floating-point arithmetic in financial calculations is a well-documented path to rounding errors, and we found a discrepancy of ₹2,847 across 400,000 transactions in a quarterly reconciliation. Not catastrophic, but unacceptable for a fintech service.

The target schema: replace amount DOUBLE PRECISION with amount_minor BIGINT (storing paisa/cents as integers) and currency_code VARCHAR(3). For ₹1,234.56, we'd store amount_minor = 123456 and currency_code = 'INR'.

Release 1: Expand

The Flyway migration:

-- V24__add_currency_columns.sql
ALTER TABLE transactions ADD COLUMN amount_minor BIGINT;
ALTER TABLE transactions ADD COLUMN currency_code VARCHAR(3);
CREATE INDEX idx_transactions_currency ON transactions(currency_code);

Both columns are nullable. The index is CREATE INDEX, not CREATE INDEX CONCURRENTLY — for this table size (12 million rows), the non-concurrent create took 1.8 seconds in staging, which was acceptable. For tables above 50 million rows, we use CONCURRENTLY and run it outside Flyway since it can't run inside a transaction.

The application change: the repository layer writes to all three columns on every INSERT and UPDATE.

@Override
public Transaction save(Transaction tx) {
    tx.setAmountMinor(convertToMinor(tx.getAmount(), tx.getCurrency()));
    tx.setCurrencyCode(tx.getCurrency().getCurrencyCode());
    return delegate.save(tx);
}

private long convertToMinor(double amount, Currency currency) {
    int fractionDigits = currency.getDefaultFractionDigits();
    return Math.round(amount * Math.pow(10, fractionDigits));
}

Reads still use amount. The new columns exist but are invisible to the read path. If this release breaks anything, we roll back and the new columns sit there harmlessly.

Release 2: Migrate (Backfill + Switch Reads)

The backfill does not run in Flyway. Flyway migrations execute during application startup, inside a transaction, holding a lock on the flyway_schema_history table. A backfill touching 12 million rows would block every other pod from starting for the entire duration. That's not zero downtime — that's a self-inflicted outage.

Instead, the backfill runs as an application-level scheduled task:

@Component
public class CurrencyBackfill {

    private final JdbcTemplate jdbc;
    private static final int BATCH_SIZE = 5000;
    private static final long SLEEP_MS = 200;

    @EventListener(ApplicationReadyEvent.class)
    public void backfill() {
        long maxId = jdbc.queryForObject(
            "SELECT COALESCE(MAX(id), 0) FROM transactions WHERE amount_minor IS NULL",
            Long.class
        );
        if (maxId == 0) return;

        long cursor = 0;
        while (cursor < maxId) {
            int updated = jdbc.update("""
                UPDATE transactions
                SET amount_minor = ROUND(amount * 100),
                    currency_code = 'INR'
                WHERE id > ? AND id <= ?
                  AND amount_minor IS NULL
            """, cursor, cursor + BATCH_SIZE);

            cursor += BATCH_SIZE;
            if (updated > 0) {
                try { Thread.sleep(SLEEP_MS); } catch (InterruptedException e) {
                    Thread.currentThread().interrupt();
                    return;
                }
            }
        }
    }
}

Five thousand rows per batch. Two hundred milliseconds of sleep between batches. At this pace, 12 million rows backfill in roughly fourteen minutes with no measurable impact on read latency — the WHERE amount_minor IS NULL clause means we're only touching rows that haven't been backfilled, and the batched approach keeps lock duration per transaction under 50 milliseconds.

The ApplicationReadyEvent listener means the backfill starts after the pod is fully healthy and serving traffic. It's also idempotent — the IS NULL filter means restarting a pod mid-backfill picks up where it left off.

After the backfill completes and we've verified row counts match (SELECT COUNT(*) WHERE amount_minor IS NULL returns zero), we switch reads:

public MonetaryAmount getTransactionAmount(Transaction tx) {
    if (tx.getAmountMinor() != null) {
        return MonetaryAmount.ofMinor(tx.getAmountMinor(), tx.getCurrencyCode());
    }
    return MonetaryAmount.ofFloat(tx.getAmount(), "INR");
}

The fallback to the old column is a safety net. For the first week, we logged every time the fallback path was hit — within 24 hours of the backfill completing, the count was zero. The writes still populate all three columns.

Release 3: Contract

This release ships at least one sprint after the backfill. The application code no longer reads or writes the old amount column.

-- V26__drop_amount_float.sql
ALTER TABLE transactions ALTER COLUMN amount_minor SET NOT NULL;
ALTER TABLE transactions ALTER COLUMN currency_code SET NOT NULL;
ALTER TABLE transactions DROP COLUMN amount;

The SET NOT NULL runs instantly on PostgreSQL 12+ when every row already has a value — it just adds a catalog constraint without scanning the table. The DROP COLUMN is also near-instant; PostgreSQL marks the column as dropped in the catalog without rewriting the table.

The old amount column is gone. The entity class no longer has it. The dual-write code is deleted. The backfill class is deleted. The codebase is cleaner than before the migration started.

Patterns We Use Repeatedly

Adding a NOT NULL Column

Never ADD COLUMN ... NOT NULL on a populated table. Instead:

1. ADD COLUMN ... DEFAULT 'value' (PostgreSQL 11+ handles this without rewriting the table — the default is stored in the catalog, not written to every row)
2. Deploy the new code that populates the column explicitly
3. Next release: optionally drop the default if you don't want one

Renaming a Column

You can't rename in place during a rolling deploy. Instead:

1. Add the new column (nullable)
2. Dual-write both columns
3. Backfill old rows
4. Switch reads to the new column
5. Next release: drop the old column

Yes, five steps for a rename. The alternative is a 500 error for every request during the rolling update window. We'll take the extra steps.

Changing a Column Type

Same expand-migrate-contract structure. Add a new column of the target type, dual-write with conversion logic, backfill, switch reads, drop the old column. We migrated VARCHAR(50) to TEXT this way — though PostgreSQL can do that particular conversion in place without rewriting (it's binary-compatible), we still used the pattern because our staging tests showed that Hibernate's column validation disagreed with the in-place change and threw a SchemaManagementException on startup.

Adding an Index on a Large Table

CREATE INDEX acquires a SHARE lock that blocks writes. On a table receiving constant inserts, this means queuing up writes for the entire index build duration.

Use CREATE INDEX CONCURRENTLY instead. It takes longer (roughly 2-3x) but only blocks other DDL, not DML. The catch: it cannot run inside a transaction, and Flyway wraps every migration in a transaction by default.

Our workaround: a Flyway callback that detects concurrent index scripts and executes them outside the transaction wrapper.

@Component
public class ConcurrentIndexCallback implements Callback {

    @Override
    public boolean supports(Event event, Context context) {
        return event == Event.BEFORE_EACH_MIGRATE;
    }

    @Override
    public boolean canHandleInTransaction(Event event, Context context) {
        MigrationInfo info = context.getMigrationInfo();
        return info != null
            && !info.getScript().contains("_concurrent_");
    }

    @Override
    public void handle(Event event, Context context) {}
}

Any Flyway script with _concurrent_ in the filename runs outside a transaction. The CREATE INDEX CONCURRENTLY statement executes without conflict.

-- V27__concurrent_idx_transactions_status.sql
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_transactions_status
    ON transactions(status, created_at);

Coordinating with Kubernetes

Flyway runs during Spring Boot startup, before the readiness probe passes. This means:

1. Pod starts, Flyway acquires the schema history lock
2. Migration executes
3. If it succeeds, Spring context finishes loading, readiness probe returns 200
4. Kubernetes routes traffic to the pod

The schema history lock (SELECT FOR UPDATE on flyway_schema_history) ensures only one pod runs migrations. Other pods block on the lock until the first pod commits, then they see the migration as already applied and skip it.

The risk: if a migration takes too long, the liveness probe fails and Kubernetes kills the pod mid-migration. Our liveness probe has a failureThreshold: 6 with periodSeconds: 10 — sixty seconds before Kubernetes intervenes. Every Flyway script must complete well within that. For the expand and contract phases, DDL statements take milliseconds. The backfill phase runs outside Flyway entirely, so it's not constrained.

livenessProbe:
  httpGet:
    path: /actuator/health/liveness
    port: 8080
  initialDelaySeconds: 30
  periodSeconds: 10
  failureThreshold: 6
readinessProbe:
  httpGet:
    path: /actuator/health/readiness
    port: 8080
  initialDelaySeconds: 10
  periodSeconds: 5
  failureThreshold: 3

The Checklist

After the user_type → account_type rename incident, we added a migration checklist to our PR template. CI blocks the merge if the checklist section is present but has unchecked items.

The questions are simple, but they've caught issues on at least a dozen PRs:

Before the migration PR merges:

• Can the current production app version work with the new schema?
• Can the new app version work with the current production schema?
• Are all new columns nullable or defaulted?
• Does the migration script avoid DDL on tables larger than 10 million rows? (If not, is the operation proven safe — like PostgreSQL's instant default?)
• Is the backfill script idempotent?
• Has the backfill been tested against production-volume data in staging?
• Is the contract step in a separate PR, scheduled for a future sprint?

After the expand deploys:

• Did all pods start without Flyway errors?
• Are dual-writes populating the new columns? (Spot-check ten recent rows)
• Is the backfill script ready to run?

We've run migrations on tables from 500 rows to 80 million rows with this process. The small tables feel over-engineered — three releases for a column rename on a 500-row lookup table is admittedly heavy. But the discipline means nobody has to make a judgment call about which tables are "big enough" to need the safe pattern. The pattern is the same regardless. Consistency removes the decision, and removing the decision removes the risk.

What I'd Recommend

If you're running Spring Boot with Flyway and deploying to Kubernetes (or any rolling-deploy infrastructure), start with two rules:

Rule 1: Never remove or rename a column in the same release that changes the code using it. Expand first, contract later. Always.

Rule 2: Treat the database schema as a public API. Two consumers (old app, new app) must work against it simultaneously. A breaking schema change is a breaking API change, and you wouldn't deploy a breaking API change without a migration path.

The expand-migrate-contract pattern isn't new. It's not clever. It's not even particularly interesting to implement. But it's the difference between deploying schema changes with confidence during business hours and scheduling maintenance windows at 2 AM on a Saturday.

The runbook is gone. The maintenance windows are gone. The 2 AM deploys are gone. What replaced them is a checklist, a pattern, and the discipline to follow it across every service we operate.

Questions about migrating a specific table shape, or hitting an edge case with Flyway? Reach out — I've probably hit the same wall.

---

Related Articles

• Java Virtual Threads in Production: How We Replaced 200-Thread Pools with One Line of Config — another production migration story from the same stack
• Building Microservices at 130 Million Requests Per Day — the architecture where these migrations happen
• OpenBanking PSD2 API Development — fintech patterns where zero-downtime matters most