Change and migrations

• A deploy replaces instances a few at a time. For minutes, v1 and v2 share one database.
• A rollback runs the same overlap in reverse.
• So a schema change and the code that needs it never ship as one step.

During a rolling deploy, both versions serve traffic, so every schema change must be compatible with the old and the new code.

I use a canary with automated analysis for code, and feature flags to turn on behaviour separately from the deploy.

• Locks come from pg_locks inside each statement's transaction. Waits come from 8 sessions that ran during the change.
• A rewrite copies every row into a new file under ACCESS EXCLUSIVE. Reads and writes stop for the whole copy.
• After the type change, 7 reads failed with 0A000: cached plan must not change result type. Running sessions held prepared statements for the old type.
• Since Postgres 11, ADD COLUMN with a constant default changes only the catalog: 4 ms here.

ACCESS EXCLUSIVE blocks reads and writes, SHARE blocks writes, and SHARE UPDATE EXCLUSIVE blocks neither. I pick the form that takes the weakest lock for the shortest time.

• CONCURRENTLY builds the index in two passes and waits for older transactions. It runs longer: 1.3 s against 0.9 s.
• NOT VALID adds the rule for new rows at once. VALIDATE checks old rows later under a weak lock.

The safe forms do the same work, but writers wait milliseconds instead of seconds.

• A report holds the table for 1.5 s. An ALTER that needs 4 ms queues behind it.
• Without a timeout, reads and writes stalled for 1,492 ms.
• With lock_timeout of 100 ms, the ALTER needed 6 attempts. No query waited more than 103 ms.

A waiting ALTER blocks every query behind it. I run DDL with a 100 ms lock_timeout and retry, so the worst stall is 100 ms, not the length of the longest transaction.

apply_ddl(statement):
  REPEAT up to 50 times:
    BEGIN
      SET LOCAL lock_timeout = '100ms'1    // give up fast
      run statement
    COMMIT; RETURN done
    ON lock_not_available (55P03)2:
      ROLLBACK                           // leave the queue3
      sleep 200 ms, then try again4
  RETURN failed: tell the owner

// ApplyDDL runs one DDL statement with a short lock_timeout, and retries when the lock is not
// available (55P03). A DDL request that waits for its lock also blocks every later query on the
// table, so it must give up quickly and try again later. lockTimeout 0 means wait forever.
func ApplyDDL(ctx context.Context, db *pgxpool.Pool, sql string, lockTimeout time.Duration, attempts int, pause time.Duration) (int, error) {
  for try := 1; ; try++ {
    err := pgx.BeginFunc(ctx, db, func(tx pgx.Tx) error {
      set := fmt.Sprintf("SET LOCAL lock_timeout = '%dms'", lockTimeout.Milliseconds())
      if _, err := tx.Exec(ctx, set); err != nil {
        return fmt.Errorf("set lock_timeout: %w", err)
      }
      _, err := tx.Exec(ctx, sql)
      return err
    })
    var pgErr *pgconn.PgError
    switch {
    case err == nil:
      return try, nil
    case !errors.As(err, &pgErr) || pgErr.Code != "55P03":
      return try, fmt.Errorf("%s: %w", sql, err)
    case try == attempts:
      return try, fmt.Errorf("%s: lock not available after %d attempts: %w", sql, try, err)
    }
    select {
    case <-time.After(pause):
    case <-ctx.Done():
      return try, ctx.Err()
    }
  }
}

• Tested: with a 50 ms timeout and 3 attempts against an open transaction, it fails with 55P03 after 3 tries.
• Also set statement_timeout, so a slow statement cannot run for hours.

Every migration statement runs with SET LOCAL lock_timeout and a retry loop. A lock it cannot get fails fast and harms nobody.

Source: the Postgres 16 ALTER TABLE and CREATE INDEX documentation. The first three rows are measured in panel C.

I build indexes concurrently and add constraints NOT VALID, then validate them. A type change becomes a new column.

I expand the schema first, then canary the code with automated analysis and roll out. I contract only when the old version can never come back.

Postgres gives me catalog-only changes, concurrent index builds, NOT VALID constraints and lock_timeout. The load balancer and feature flags let me move traffic and behaviour separately.

At every step of expand and contract, the versions that serve traffic both work. A rename in place breaks the old version the moment it runs.

backfill(batch = 5000, pause = 10 ms):
  top = max(id)
  FOR lo FROM 1 TO top STEP batch:
    UPDATE customers SET full_name = name   // one short transaction1
    WHERE id >= lo AND id < lo + batch2
      AND full_name IS NULL                 // skip done rows3
    sleep pause                             // replicas, vacuum keep up4

// Backfill copies name into full_name for every customer, in batches of size ids. Each batch is
// its own short transaction, so a row stays locked for one batch only, and the pause between
// batches lets replicas and vacuum keep up. The WHERE clause skips rows already done, so a
// stopped backfill can restart from the beginning.
func Backfill(ctx context.Context, db *pgxpool.Pool, size int, pause time.Duration) (BackfillStats, error) {
  var top int64
  if err := db.QueryRow(ctx, "SELECT coalesce(max(id), 0) FROM customers").Scan(&top); err != nil {
    return BackfillStats{}, fmt.Errorf("read max id: %w", err)
  }
  var st BackfillStats
  start := time.Now()
  for lo := int64(1); lo <= top; lo += int64(size) {
    t := time.Now()
    tag, err := db.Exec(ctx, schema["backfill_batch"], lo, lo+int64(size))
    if err != nil {
      return st, fmt.Errorf("batch from id %d: %w", lo, err)
    }
    st.LongestMs = max(st.LongestMs, float64(time.Since(t).Microseconds())/1000)
    st.Rows += tag.RowsAffected()
    st.Batches++
    select {
    case <-time.After(pause):
    case <-ctx.Done():
      return st, ctx.Err()
    }
  }
  st.Ms = float64(time.Since(start).Microseconds()) / 1000
  st.PerSec = float64(st.Rows) / time.Since(start).Seconds()
  return st, nil
}

UPDATE customers SET full_name = name
WHERE id >= $1 AND id < $2 AND full_name IS NULL;

I backfill by primary key range in batches of a few thousand rows, each its own transaction, with a pause. Rows stay locked for one batch, not for the whole job.

• Services talk to version N-1 and N+1 during a rollout, so APIs follow the same expand-and-contract rule.

Every deploy must be safe to roll back until the contract step. After that, I roll forward.

The deadlines are derived: 100,000,000 rows ÷ 3,600 s ≈ 27,778 rows a second. The 100M estimate assumes the batched rate stays linear.

On small tables, any migration at deploy time works. As tables grow, I add lock_timeout, then expand and contract with concurrent builds, then throttled backfill jobs, then copy-and-swap for changes Postgres cannot do online.