Zero Downtime Migrations for Go Services in Production

Most schema changes are quick. The ones that hurt are the few that take a strong lock on a table every request touches, and you cannot tell which is which by reading the SQL. A change that is instant on a laptop with 500 rows can hold up a service for minutes on a table with 200 million, and the worst of them hurt even when they run quickly, because of what queues up behind them. This guide measures the common changes from Go against PostgreSQL 18.6 on Go 1.25.6, so each rule comes with numbers: which statements rewrite or scan a table, which take a lock that blocks writes, and the queue effect that turns a short wait into an outage.

It extends the database cornerstone and assumes a migration runner such as goose or golang-migrate already applies your files. The runner does not make a migration safe. The statements inside do.

The principle: short locks, and never wait quietly

Every alter table takes a table lock, and for most forms it is ACCESS EXCLUSIVE, which conflicts with everything: reads, writes, even other schema changes. The lock itself is not the problem when it is held for a millisecond. Two things make it a problem:

1. Duration. If the statement has to rewrite or scan the table while holding the lock, every query on the table waits for the whole run.
2. Waiting for the lock. If another transaction already holds a lock on the table, the alter waits. While it waits, it sits in the lock queue, and later queries that would normally share the table queue behind it, because a lock request that conflicts with a waiting request must wait too. One idle transaction, one slow report, and a migration that was going to take a millisecond now freezes the table.

Everything below is a way to keep the first short and bound the second.

The lab

The program builds a table of 2 million rows, then times each change. Where a change might block writers, a goroutine inserts a row every two milliseconds and reports the slowest insert it saw while the statement ran. That number is what a user of the service would feel.

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"sync"
	"time"

	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable&pool_max_conns=10"

func exec(ctx context.Context, pool *pgxpool.Pool, q string) time.Duration {
	start := time.Now()
	if _, err := pool.Exec(ctx, q); err != nil {
		log.Fatalf("%s: %v", q, err)
	}
	return time.Since(start).Round(time.Millisecond)
}

// whileRunning inserts a row every 2ms and reports the slowest insert seen while fn ran.
func whileRunning(ctx context.Context, pool *pgxpool.Pool, fn func()) (n int, slowest time.Duration) {
	stop := make(chan struct{})
	var wg sync.WaitGroup
	wg.Add(1)
	go func() {
		defer wg.Done()
		for {
			select {
			case <-stop:
				return
			default:
			}
			start := time.Now()
			if _, err := pool.Exec(ctx, "insert into orders603 (customer, qty) values (1, 1)"); err != nil {
				log.Print(err)
				return
			}
			if d := time.Since(start); d > slowest {
				slowest = d
			}
			n++
			time.Sleep(2 * time.Millisecond)
		}
	}()
	time.Sleep(50 * time.Millisecond)
	fn()
	time.Sleep(50 * time.Millisecond)
	close(stop)
	wg.Wait()
	return n, slowest.Round(time.Millisecond)
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, dsn)
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()

	exec(ctx, pool, "drop table if exists orders603")
	exec(ctx, pool, "create table orders603 (id bigserial primary key, customer int not null, qty int not null)")
	exec(ctx, pool, "insert into orders603 (customer, qty) select i % 1000, 1 from generate_series(1, 2000000) i")
	exec(ctx, pool, "analyze orders603")

	fmt.Println("1. what rewrites the table")
	fmt.Println("   add column with a constant default:", exec(ctx, pool, "alter table orders603 add column note text default 'new'"))
	fmt.Println("   alter column type int to bigint:   ", exec(ctx, pool, "alter table orders603 alter column qty type bigint"))

	fmt.Println("2. building an index while inserts continue")
	n, slow := whileRunning(ctx, pool, func() { exec(ctx, pool, "create index orders603_customer on orders603 (customer)") })
	fmt.Printf("   plain create index:        %d inserts, slowest %v\n", n, slow)
	exec(ctx, pool, "drop index orders603_customer")
	n, slow = whileRunning(ctx, pool, func() { exec(ctx, pool, "create index concurrently orders603_customer on orders603 (customer)") })
	fmt.Printf("   create index concurrently: %d inserts, slowest %v\n", n, slow)

	fmt.Println("3. adding NOT NULL while inserts continue")
	exec(ctx, pool, "alter table orders603 add column ref int default 0")
	n, slow = whileRunning(ctx, pool, func() { exec(ctx, pool, "alter table orders603 alter column ref set not null") })
	fmt.Printf("   set not null:              %d inserts, slowest %v\n", n, slow)
	exec(ctx, pool, "alter table orders603 alter column ref drop not null")
	n, slow = whileRunning(ctx, pool, func() {
		exec(ctx, pool, "alter table orders603 add constraint ref_nn check (ref is not null) not valid")
		exec(ctx, pool, "alter table orders603 validate constraint ref_nn")
	})
	fmt.Printf("   check not valid + validate: %d inserts, slowest %v\n", n, slow)

	fmt.Println("4. a migration that waits for a lock blocks everyone behind it")
	holder, err := pool.Begin(ctx)
	if err != nil {
		log.Fatal(err)
	}
	if _, err := holder.Exec(ctx, "select count(*) from orders603"); err != nil { // an open transaction holding a share lock
		log.Fatal(err)
	}
	var wg sync.WaitGroup
	var alterErr error
	var alterWaited, readWaited time.Duration
	wg.Add(2)
	go func() {
		defer wg.Done()
		start := time.Now()
		conn, _ := pool.Acquire(ctx)
		defer conn.Release()
		conn.Exec(ctx, "set lock_timeout = '1500ms'")
		_, alterErr = conn.Exec(ctx, "alter table orders603 add column flag boolean")
		alterWaited = time.Since(start).Round(10 * time.Millisecond)
	}()
	go func() {
		defer wg.Done()
		time.Sleep(300 * time.Millisecond)
		start := time.Now()
		var c int
		if err := pool.QueryRow(ctx, "select count(*) from orders603 where id < 10").Scan(&c); err != nil {
			log.Print(err)
		}
		readWaited = time.Since(start).Round(10 * time.Millisecond)
	}()
	wg.Wait()
	holder.Rollback(ctx)
	var pgErr *pgconn.PgError
	if errors.As(alterErr, &pgErr) {
		fmt.Printf("   alter gave up after %v with SQLSTATE %s: %s\n", alterWaited, pgErr.Code, pgErr.Message)
	}
	fmt.Printf("   a plain read of 9 rows arrived 300ms later and waited %v\n", readWaited)
}

1. what rewrites the table
   add column with a constant default: 2ms
   alter column type int to bigint:    1.299s
2. building an index while inserts continue
   plain create index:        33 inserts, slowest 519ms
   create index concurrently: 231 inserts, slowest 5ms
3. adding NOT NULL while inserts continue
   set not null:              35 inserts, slowest 85ms
   check not valid + validate: 58 inserts, slowest 2ms
4. a migration that waits for a lock blocks everyone behind it
   alter gave up after 1.5s with SQLSTATE 55P03: canceling statement due to lock timeout
   a plain read of 9 rows arrived 300ms later and waited 1.2s

The numbers will move on other hardware and with other row counts. The shape of each result is what to carry away.

What rewrites the table

The first block of output compares two changes that look equally small:

• add column note text default 'new' took 2ms. Since PostgreSQL 11, adding a column with a constant default does not rewrite the table: the default is stored in the catalog and applied when old rows are read. It is metadata only, however many rows there are.
• alter column qty type bigint took 1.299s on 2 million rows. A type change that cannot reuse the stored bytes rewrites every row and rebuilds every index on the table, all under ACCESS EXCLUSIVE. At 200 million rows that is minutes of an unavailable table, and it needs about double the disk while it runs.

A default that is volatile, such as default now() or default gen_random_uuid(), also forces a rewrite, because each row needs its own value. The safe sequence is to add the column nullable, set the default for new rows, backfill old rows in batches, and only then add the constraint. The PostgreSQL manual lists which alter table forms rewrite in the notes of its ALTER TABLE page; read it for any form you have not used before.

For a type change, use expand and contract:

1. Expand. Add qty_big bigint as a nullable column. Deploy code that writes both columns.
2. Backfill. Copy qty into qty_big in batches of a few thousand rows, each its own short transaction, with a pause between batches so replicas and vacuum keep up.
3. Switch. Deploy code that reads qty_big. Add a not valid check that it is not null and validate it.
4. Contract. Stop writing qty, then drop it in a later migration.

It is more steps than one alter, and each step is safe to run, roll back and pause. That is the trade: more migrations for no outage.

Index builds

The second block is the clearest result of the lab. While create index ran on 2 million rows, the inserter completed 33 inserts and its slowest took 519ms. A plain index build takes a SHARE lock, which allows reads and blocks every write for the whole build. Writes queue and then arrive together.

create index concurrently took longer in wall time, but the inserter completed 231 inserts and its slowest was 5ms. It builds in two passes without blocking writers, then waits for transactions that started before it to finish. The costs are real and worth knowing:

• It cannot run inside a transaction block. A runner that wraps each file in a transaction needs that file marked as non-transactional (goose uses -- +goose NO TRANSACTION).
• If it fails, it leaves an invalid index behind. Check pg_index.indisvalid, drop the leftover and retry. A migration that is not idempotent will fail on the retry because the name exists.
• It waits for old transactions, so a long running transaction makes it slow. That is the right behaviour: it is waiting without holding anyone up.

The same applies to dropping: drop index concurrently avoids the lock a plain drop takes. Which indexes to build is a different question, covered in index basics and EXPLAIN.

Constraints without the scan

Adding not null to an existing column forces PostgreSQL to scan the table to prove no row violates it, under a strong lock. In the lab, set not null stalled an insert for 85ms on 2 million rows; the stall grows linearly with the table.

The alternative moves the proof out of the lock:

alter table orders add constraint ref_nn check (ref is not null) not valid;
alter table orders validate constraint ref_nn;

not valid records the constraint for new writes without checking old rows, which is instant. validate constraint then scans the table while holding only a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes. In the lab the pair let 58 inserts through with a slowest of 2ms. The same two step pattern works for foreign keys, which otherwise lock both tables while they check. Since PostgreSQL 12, a later set not null on a column that already has a validated check constraint skips its scan, so you can end up with the real not null property without ever paying for the scan under a heavy lock; the manual's ALTER TABLE page describes that optimisation.

The queue behind a waiting ALTER

The last block shows the failure that causes most real outages, and it needs no big table at all. A transaction holds a share lock on the table by reading from it and then doing nothing, the way a stuck HTTP handler or a forgotten psql session would. A migration then runs alter table ... add column, which cannot get its lock and waits.

The migration was given a lock_timeout of 1.5 seconds, and it gave up at 1.5s with SQLSTATE 55P03, canceling statement due to lock timeout. The more important line is the other one. A plain select of nine rows, started 300ms after the alter began waiting, waited 1.2s. It has nothing to do with the migration and it would normally take a millisecond. It waited because it queued behind the pending alter, and the alter was waiting for the idle transaction. Without a lock_timeout the wait is unbounded: as long as the holder stays idle, every query on that table piles up, the connection pool fills, and the service looks down.

The fix is small:

• Set lock_timeout at the start of every migration file, to a few seconds: set lock_timeout = '3s';. A statement that cannot get its lock fails fast instead of becoming a roadblock.
• Make the runner retry a migration that failed with 55P03, with a pause and a limit, so a busy moment delays the deploy and does not break it.
• Set statement_timeout for the same reason on steps that should be quick, so a surprise rewrite is cancelled instead of running for an hour.

A retry loop around the runner is a few lines:

func withRetry(ctx context.Context, attempts int, run func(context.Context) error) error {
	var err error
	for i := 0; i < attempts; i++ {
		if err = run(ctx); err == nil {
			return nil
		}
		var pgErr *pgconn.PgError
		if !errors.As(err, &pgErr) || pgErr.Code != "55P03" {
			return err // only a lock timeout is worth retrying
		}
		time.Sleep(time.Duration(i+1) * 2 * time.Second)
	}
	return err
}

Only a lock timeout is retried. A syntax error or a constraint violation fails on the first attempt, as it should. See query timeouts for how context deadlines on the client side combine with the server side settings.

Backfills belong in batches

A data change on a large table is a migration too, and a single update orders set ref = 0 locks every row it touches until it commits, produces a huge transaction, bloats the table and lags replicas. Run backfills from application code or a job, in batches keyed by primary key:

1. Select the next range of ids, a few thousand at a time.
2. Update that range in its own short transaction.
3. Sleep briefly, check replication lag or load, and continue from the last id.
4. Make the job restartable: it should be safe to run twice and should resume where it stopped.

Keep it out of the schema migration file, so that file stays fast and the backfill can be paused when the database is busy.

Watching a migration while it runs

A migration you cannot observe is one you cannot stop in time. Keep one session open on the database during the deploy and look at two views.

pg_stat_activity shows what each connection is doing and, for a waiting one, what it waits on (wait_event_type is Lock when a query is queued). Sort by query_start and a transaction that has been idle in transaction for ten minutes is the usual suspect behind a stuck alter. pg_locks joined to it shows who holds the lock a waiter needs; pg_blocking_pids(pid) answers that in one call. If the migration is queued, you can cancel the blocker (pg_cancel_backend) or cancel the migration, and both are better decisions made in the first ten seconds than the first ten minutes.

From Go, expose the same facts to the service rather than to a person: log the start and end of each migration file with its duration, and fail the deploy step when a file exceeds a budget. A migration that normally takes 40ms and took 8s tells you the table grew or the lock queued, before any customer writes in.

Renames, drops and foreign keys

Three changes look harmless and are not:

• Renaming a column or a table commits instantly and breaks every running instance that still uses the old name. There is no safe in-place rename on a service that deploys gradually. Add the new column, write both, switch reads, and drop the old one two releases later. A view can bridge a table rename for a while.
• Dropping a column is metadata only, but the old code still selects it. Remove every reference in code first, deploy, and drop it afterwards. An ORM that selects explicit columns will fail on the first query after the drop if even one instance still lists the dropped column.
• Adding a foreign key checks every existing row against the parent table under locks on both tables. Use add constraint ... foreign key ... not valid and validate constraint later, the same two steps as the check constraint above, and make sure the referencing column has an index first, or every delete on the parent scans the child table.

Enum changes, unique constraints and primary keys follow the same pattern: build the supporting index concurrently, then attach it. For a unique constraint, create unique index concurrently followed by add constraint ... unique using index takes only a brief lock.

Testing a migration before production

The lab above is also a template for the test you want in CI: run the migration against a database with a realistic number of rows and a writer running beside it, and fail the build when the slowest write crosses a limit. Row counts matter more than data content; two million narrow rows reproduce the behaviour of a rewrite far better than a fixture of fifty. Pair it with the test that applies every migration from an empty database to the latest version, and with a down or roll forward drill for the last one or two files, so the tool and the files are exercised on every change and not on the night they are needed.

A checklist before shipping a migration

The last row ties the rest together. The code that is running while the migration applies is the old code, and during a rolling deploy two versions of it run side by side. Every migration should leave the schema usable by both: add before use, stop using before dropping, and never rename a column in place (add the new name, write both, switch reads, drop the old). Renaming looks like the safest change and breaks every query that names the old column the moment it commits.

When this advice does not apply

A table with a few thousand rows can take any of these changes in milliseconds, and the extra steps are not worth it; a nightly maintenance window on an internal tool is a legitimate choice. The measurements above are also PostgreSQL's. MySQL's online DDL has its own rules (ALGORITHM=INPLACE, LOCK=NONE, and tools like gh-ost for the rest) and SQLite rewrites more than either. The principle transfers, short locks and no unbounded waits, but the list of safe statements does not. For a major version upgrade of the server itself, which is a larger version of the same problem, see upgrading Postgres under a running service. The lock modes and which statements take them are documented in the PostgreSQL manual's chapter on explicit locking.