Your Migration Will Run Twice: Write It That Way

Karani GeoffreyJune 21, 20267 min read

The 2am lesson

A deploy timed out. Not failed - timed out. The migration step had actually finished on the database, but the CI runner lost its connection and reported failure. The obvious move at 2am is to click "re-run job", so that is what happened.

The migration added a column, backfilled it from another table, and then dropped the old one. On the second run, the ADD COLUMN failed because the column already existed. The migration framework marked the run as failed and rolled back its transaction, but the framework had never recorded the first (successful) run in its version table, because the process died before it committed that bookkeeping row. The database was now in a state the code did not believe was possible: the schema was half-new, the version table said old.

We spent three hours reconstructing what had actually happened from the Postgres logs. The fix, once we understood it, was four lines of SQL.

The thesis of this article: your migration will run more than once, and you do not get to decide when. Design for that and the retry is a non-event. Design against it and the retry is an outage.

Why migrations run twice

People assume double-runs mean someone was careless. They almost never do. The real causes:

A CI job times out or the runner is preempted after the SQL committed but before the exit code was reported.
Someone hits "re-run failed jobs" - which is exactly what the button is for.
Migrations run in the container entrypoint, and you scaled to two replicas. Both start at once.
A rollback redeploys the previous image, which re-runs its own migrations on the way up.
A restore from backup replays a window of history.
A human runs it manually because "CI is stuck", not knowing CI actually got there first.

Every one of these is normal operations. None involve incompetence.

Layer 1: only one process at a time

Before idempotency, exclusivity. In Postgres this is a two-line change using an advisory lock, which is a lock on an arbitrary integer that you define the meaning of:

-- Blocks until it gets the lock. Released automatically when
-- the session ends, even if the process is killed.
SELECT pg_advisory_lock(4815162342);

-- ... your migrations run here ...

SELECT pg_advisory_unlock(4815162342);

If you would rather fail fast than queue, use the non-blocking variant:

SELECT pg_try_advisory_lock(4815162342);
-- returns true if acquired, false if someone else holds it

Most mature migration tools (Flyway, Liquibase, node-pg-migrate, Rails, Django with some setup) do this already. Check whether yours does before you assume it. A hand-rolled migration runner almost certainly does not, and the failure mode is two replicas creating the same table at the same time.

Layer 2: idempotent DDL

This is the part people mean when they say "idempotent migration", and it is the easiest layer:

-- Instead of: ALTER TABLE payouts ADD COLUMN settled_at timestamptz;
ALTER TABLE payouts ADD COLUMN IF NOT EXISTS settled_at timestamptz;

CREATE TABLE IF NOT EXISTS payout_attempts (
  id           bigserial PRIMARY KEY,
  payout_id    bigint NOT NULL REFERENCES payouts(id),
  attempted_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_payout_attempts_payout
  ON payout_attempts (payout_id);

DROP INDEX IF EXISTS idx_payouts_legacy_ref;

For things Postgres has no IF NOT EXISTS clause for, guard them explicitly. Adding a constraint is the usual case:

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname = 'payouts_amount_positive'
  ) THEN
    ALTER TABLE payouts
      ADD CONSTRAINT payouts_amount_positive
      CHECK (amount_minor > 0) NOT VALID;
  END IF;
END $$;

-- Validate separately: takes a weaker lock and can be retried safely
ALTER TABLE payouts VALIDATE CONSTRAINT payouts_amount_positive;

The NOT VALID then VALIDATE split is worth knowing on its own. Adding a validated CHECK constraint scans the whole table while holding a strong lock. Adding it NOT VALID is instant and applies to new rows immediately; validating afterwards takes a SHARE UPDATE EXCLUSIVE lock that lets reads and writes continue. On a table with ten million rows, that is the difference between a two-second migration and a four-minute outage.

Layer 3: separate schema changes from data backfills

This is the layer that actually matters, and where most teams get it wrong. A backfill is not a migration. It has completely different characteristics:

	Schema change	Data backfill
Duration	Milliseconds to seconds	Minutes to hours
Safe in one transaction	Usually yes	Almost never
Failure mode	Clean rollback	Partially applied
Should block a deploy	Yes	No

Putting a ten-minute UPDATE inside a migration transaction means holding row locks on your busiest table for ten minutes while your app times out. And if it fails at minute nine, you get nothing for the effort.

Split it. The migration adds the nullable column. A separate, resumable, batched job fills it. A later migration adds the NOT NULL constraint once the backfill has completed.

import time
import psycopg

BATCH = 2000

def backfill(conn):
    """Resumable: each batch commits, and re-running skips done rows."""
    while True:
        with conn.cursor() as cur:
            cur.execute(
                """
                WITH batch AS (
                    SELECT id FROM payouts
                    WHERE settled_at IS NULL
                      AND status = 'settled'
                    ORDER BY id
                    LIMIT %s
                    FOR UPDATE SKIP LOCKED
                )
                UPDATE payouts p
                   SET settled_at = p.updated_at
                  FROM batch b
                 WHERE p.id = b.id
                """,
                (BATCH,),
            )
            updated = cur.rowcount
        conn.commit()

        if updated == 0:
            break

        print(f"backfilled {updated} rows")
        time.sleep(0.2)  # let replicas catch up

Four properties make this safe to run any number of times:

The WHERE clause is the progress marker. Rows already done no longer match. Re-running from scratch does nothing.
Each batch commits. Killing it halfway loses nothing already committed.
FOR UPDATE SKIP LOCKED means two copies running concurrently divide the work instead of deadlocking.
The sleep keeps replication lag under control. On a small VPS this matters more than you would like.

The CREATE INDEX CONCURRENTLY trap

Building an index on a live table locks out writes. CONCURRENTLY avoids that by taking only a SHARE UPDATE EXCLUSIVE lock. Two things then bite you:

First, it cannot run inside a transaction block. Most migration frameworks wrap every migration in BEGIN/COMMIT by default, so you have to opt out explicitly (Django: atomic = False; Rails: disable_ddl_transaction!; raw SQL runners: check your tool).

Second, and this is the one that surprises people: if a concurrent index build fails, it leaves behind an invalid index. That index is not used by the planner but it does slow down every write to the table. A naive retry with IF NOT EXISTS sees the invalid index, decides there is nothing to do, and you are left permanently with a useless index that costs you writes.

-- Find them
SELECT c.relname AS index_name
FROM pg_class c
JOIN pg_index i ON i.indexrelid = c.oid
WHERE i.indisvalid = false;

-- The correct idempotent recipe
DROP INDEX CONCURRENTLY IF EXISTS idx_payouts_settled_at;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_payouts_settled_at
  ON payouts (settled_at);

Drop first, then create. It looks wasteful. It is the only version that is actually safe to re-run.

Also: set a lock timeout

Even a fast ALTER TABLE has to wait for existing transactions to release their locks. While it waits, it queues, and every query arriving behind it queues too. A migration that should take 5ms can freeze your application for a minute because one long-running report was holding a read lock.

SET lock_timeout = '3s';
SET statement_timeout = '30s';

ALTER TABLE payouts ADD COLUMN IF NOT EXISTS settled_at timestamptz;

If the lock is not available within three seconds, the migration fails cleanly instead of taking your site down. Failing fast is fine, because your migration is now safe to retry - which is the whole point of everything above.

Prove it in CI

All of this is theory until it is tested. The test is embarrassingly simple:

#!/usr/bin/env bash
set -euo pipefail

echo "== first run =="
npm run migrate

echo "== second run, should be a no-op =="
npm run migrate

echo "== third run, because why not =="
npm run migrate

echo "Migrations are idempotent."

Add that to your pipeline. It catches the ADD COLUMN that forgot IF NOT EXISTS on the day it is written, when the fix costs thirty seconds, rather than at 2am when the fix costs three hours and your composure.

The checklist

Guard the whole run with an advisory lock.
Every DDL statement uses IF NOT EXISTS, IF EXISTS, or an explicit catalog check.
Schema changes and data backfills are separate, deployable steps.
Backfills are batched, committing, and resumable, with the WHERE clause as the progress marker.
Concurrent index builds are preceded by a concurrent drop.
lock_timeout and statement_timeout are set.
CI runs the whole migration suite at least twice.

Seven lines. They will save you a night.

#databases#postgresql#migrations#reliability#deployment