Zero Downtime Database Migrations With Rust Services

The dangerous moment in a schema change is not the DDL itself; it is the window when the old code and the new code run at the same time. A rolling deploy replaces instances one at a time, so for minutes both versions are live and both must work with whatever the database looks like. The method that survives that is expand, migrate, contract. Examples are for PostgreSQL 17 with SQLx 0.8 migrations in a Rust service, Rust 1.90.

The three phases

1. Expand: add new columns, tables or indexes in a way old code ignores. Deploy the migration first; nothing breaks because nothing uses it yet.
2. Migrate: deploy code that writes to old and new, backfill historical rows, then deploy code that reads the new.
3. Contract: after no running code uses the old structure, drop it in a later release.

Each phase is a separate deploy. That is slower than one big migration and is the entire point.

Adding a column

-- safe: nullable column, no default rewrite
ALTER TABLE orders ADD COLUMN currency TEXT;

On PostgreSQL 11 and later, adding a column with a constant default is also instant (the default is stored in the catalog rather than rewriting the table), but a volatile default such as now() or gen_random_uuid() rewrites every row and holds an exclusive lock. Add the column nullable, then set values in batches.

Backfill in batches

use sqlx::PgPool;

pub async fn backfill_currency(pool: &PgPool) -> Result<(), sqlx::Error> {
    loop {
        let updated = sqlx::query(
            "UPDATE orders SET currency = 'USD'
             WHERE id IN (SELECT id FROM orders WHERE currency IS NULL ORDER BY id LIMIT 5000)",
        )
        .execute(pool)
        .await?
        .rows_affected();
        if updated == 0 {
            break;
        }
        tokio::time::sleep(std::time::Duration::from_millis(100)).await; // let replicas and vacuum breathe
    }
    Ok(())
}

A single UPDATE of ten million rows holds locks and bloats the table in one transaction. Batches commit each time, keep locks short, give replication a chance to keep up and can be paused or resumed. Run it from a one-off task, not as part of the schema migration, so a failure does not block the deploy.

Setting NOT NULL safely

-- 1. add the constraint without scanning (brief lock)
ALTER TABLE orders ADD CONSTRAINT orders_currency_not_null CHECK (currency IS NOT NULL) NOT VALID;

-- 2. validate: scans the table but allows concurrent writes
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_not_null;

-- 3. PostgreSQL 12+ uses the validated check to skip the scan
ALTER TABLE orders ALTER COLUMN currency SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_currency_not_null;

Going straight to SET NOT NULL scans the whole table under an exclusive lock. The NOT VALID route does the heavy part under a weaker lock.

Indexes

-- migrations/0042_orders_customer_idx.sql
-- no-transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_customer_idx ON orders (customer_id);

A plain CREATE INDEX blocks writes for the duration of the build. CONCURRENTLY does not, at the cost of taking longer and not being allowed inside a transaction. SQLx wraps migrations in a transaction by default, so mark this file with the -- no-transaction comment on the first line. If a concurrent build fails, it leaves an invalid index that you must drop and retry.

Renames and type changes

A rename breaks old code instantly. Instead:

1. Add the new column; deploy code that writes both and reads old.
2. Backfill; deploy code that reads new.
3. Stop writing the old column; later drop it.

The same recipe changes a type (INT to BIGINT): add a new column of the new type, copy in batches, switch. For compatibility during the transition a trigger or dual-write in the application keeps them equal. Avoid ALTER COLUMN TYPE on big tables: it rewrites the table.

Protect yourself from lock queues

SET lock_timeout = '3s';
SET statement_timeout = '60s';
ALTER TABLE orders ADD COLUMN currency TEXT;

A DDL statement needing an exclusive lock waits behind any running query on the table, and every later query then waits behind it. A harmless migration plus one long report causes an outage. A short lock_timeout makes the migration fail quickly instead, and you retry at a quieter moment. Put these settings at the top of risky migrations or on the migration role.

Ordering the deploy

• Run additive migrations before deploying the code that needs them.
• Run destructive migrations only after every instance running the old code is gone.
• Keep the previous release rollback-safe: it must work against the expanded schema.
• Check migrations in CI against the previous release's tests (SQLx migrations, rollbacks).

Tools such as squawk lint PostgreSQL migrations for unsafe patterns and can run in CI. A review checklist for every migration, "what lock does this take, and for how long?", catches the rest.