Migrations & transactions

Your schema will change as long as your service lives, and every change has to reach development laptops, CI and production in the same order. Meanwhile, requests run concurrently against that schema, and any operation that touches more than one row needs to succeed or fail as a unit. This lesson covers both halves of keeping data consistent: migrations for evolving the schema, and transactions for evolving the data.

#Why schema changes need migrations

A migration is a small, numbered SQL file that moves the schema one step forward. A migration tool applies pending files in order and records each applied version in a table in the database itself. Every environment converges on the same schema just by running "apply everything pending".

That gives you three rules that prevent most schema disasters:

1. Migrations live in the repository next to the code that needs them, and go through code review.
2. Never edit a migration that has been applied anywhere shared. The tool won't re-run it, so environments silently diverge. Write a new migration instead.
3. Never change production by hand with psql. If it isn't in a migration, it doesn't exist.

#Writing migrations with goose

This lesson uses goose, a popular Go migration tool. (golang-migrate works the same way, with separate .up.sql and .down.sql files.) Install the CLI and point it at your database with environment variables:

go install github.com/pressly/goose/v3/cmd/goose@latest

export GOOSE_DRIVER=postgres
export GOOSE_DBSTRING="postgres://app:dev@localhost:5432/app?sslmode=disable"
export GOOSE_MIGRATION_DIR=./migrations

mkdir -p migrations   # goose create fails if the directory doesn't exist
goose create create_accounts sql

That creates migrations/20261011090000_create_accounts.sql, a timestamped file. Each file has an Up section and a Down section, which undoes it:

-- +goose Up
CREATE TABLE accounts (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    owner      text   NOT NULL,
    balance    bigint NOT NULL CHECK (balance >= 0),
    created_at timestamptz NOT NULL DEFAULT now()
);

-- +goose Down
DROP TABLE accounts;

Apply it and check the state:

$ goose up
OK   20261011090000_create_accounts.sql (3.47ms)
goose: successfully migrated database to version: 20261011090000

$ goose status
    Applied At                  Migration
    =======================================
    Sun Oct 11 08:12:14 2026 -- 20261011090000_create_accounts.sql

goose records versions in a goose_db_version table. goose down rolls back the most recent migration. Down migrations are useful in development, but in production you almost always roll forward with a new fix, because a down migration that drops a column also drops its data.

goose runs each SQL migration inside a transaction. PostgreSQL supports transactional DDL, so if statement three of five fails, the first two are rolled back too and the database is never left half-migrated. Two annotations adjust parsing: wrap function bodies containing semicolons in -- +goose StatementBegin / -- +goose StatementEnd, and add -- +goose NO TRANSACTION for statements PostgreSQL refuses to run inside a transaction, such as CREATE INDEX CONCURRENTLY.

#Running migrations from your Go binary

Shipping migrations inside the binary means a deploy can't run code against a schema it doesn't expect. Embed the files and run them with goose's Provider at startup, or from a migrate subcommand your deploy pipeline runs before the new version starts:

//go:embed migrations/*.sql
var embedded embed.FS

func Migrate(ctx context.Context, pool *pgxpool.Pool) error {
	migrations, err := fs.Sub(embedded, "migrations")
	if err != nil {
		return err
	}
	// A PostgreSQL advisory lock stops two replicas migrating at once.
	locker, err := lock.NewPostgresSessionLocker()
	if err != nil {
		return err
	}
	db := stdlib.OpenDBFromPool(pool) // goose needs a *sql.DB
	defer db.Close()                  // does not close the pool

	provider, err := goose.NewProvider(goose.DialectPostgres, db, migrations,
		goose.WithSessionLocker(locker))
	if err != nil {
		return err
	}
	results, err := provider.Up(ctx)
	for _, r := range results {
		log.Printf("migrated %s (%s)", r.Source.Path, r.Duration)
	}
	return err
}

#Changing a live schema safely

During a rolling deploy, old and new versions of your code run against the same database at the same time. So every migration must be compatible with the code running before it and the code running after it. Renaming owner to owner_name in one step breaks every old instance the moment it applies.

The fix is the expand/contract pattern, spread across several deploys:

1. Expand: add the new column owner_name, nullable. Old code ignores it.
2. Dual write: deploy code that writes both columns, then backfill old rows in batches.
3. Switch reads to owner_name, and add the NOT NULL constraint.
4. Contract: once no running code uses owner, drop it.

Also watch locks. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, and while one waits behind a long-running query, every other query on that table queues behind it. Starting migrations with SET lock_timeout = '5s'; makes them fail fast and retry instead of stalling your traffic.

#Transactions in Go

A transaction groups statements so they all commit or none do. Moving money between accounts is the classic case: debiting one account without crediting the other must be impossible. With pgx, you begin a transaction from the pool and then run every statement on the returned pgx.Tx:

var (
	ErrInsufficientFunds = errors.New("insufficient funds")
	ErrAccountNotFound   = errors.New("account not found")
)

func Transfer(ctx context.Context, pool *pgxpool.Pool, from, to, amount int64) error {
	tx, err := pool.Begin(ctx)
	if err != nil {
		return err
	}
	defer tx.Rollback(ctx) // returns ErrTxClosed after a successful Commit; safe to ignore

	// Lock both rows in ascending id order so opposite transfers can't deadlock.
	rows, _ := tx.Query(ctx,
		`SELECT id FROM accounts WHERE id IN ($1, $2) ORDER BY id FOR UPDATE`, from, to)
	locked, err := pgx.CollectRows(rows, pgx.RowTo[int64])
	if err != nil {
		return err
	}
	if len(locked) != 2 {
		return ErrAccountNotFound
	}

	tag, err := tx.Exec(ctx,
		`UPDATE accounts SET balance = balance - $2 WHERE id = $1 AND balance >= $2`, from, amount)
	if err != nil {
		return err
	}
	if tag.RowsAffected() == 0 {
		return ErrInsufficientFunds
	}
	if _, err := tx.Exec(ctx,
		`UPDATE accounts SET balance = balance + $2 WHERE id = $1`, to, amount); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

The defer tx.Rollback(ctx) line is the key idiom. Every early return (an error, insufficient funds, a panic) rolls back automatically, and after Commit succeeds the deferred rollback does nothing. You never have to remember to clean up on each error path. pgx also offers pgx.BeginFunc(ctx, pool, func(tx pgx.Tx) error { ... }), which commits if your function returns nil and rolls back otherwise.

To let store functions run either standalone or inside a caller's transaction, accept a small interface that both *pgxpool.Pool and pgx.Tx satisfy:

type DBTX interface {
	Exec(ctx context.Context, sql string, args ...any) (pgconn.CommandTag, error)
	Query(ctx context.Context, sql string, args ...any) (pgx.Rows, error)
	QueryRow(ctx context.Context, sql string, args ...any) pgx.Row
}

#Concurrency inside transactions

A transaction makes changes atomic, but it doesn't automatically stop two transactions from interfering. The classic bug is the lost update: two requests each SELECT balance (both read 100), each compute 100 - 30 in Go, and each write 70. One withdrawal vanishes. Under PostgreSQL's default READ COMMITTED isolation, wrapping that code in a transaction does not fix it.

Transfer above uses the two standard fixes:

• Do the arithmetic in SQL. SET balance = balance - $2 WHERE balance >= $2 reads and writes the row atomically, and the condition enforces the business rule at the same moment. RowsAffected() == 0 tells you it failed.
• Lock rows you read before writing. SELECT ... FOR UPDATE locks the rows until the transaction ends, so a second transfer touching the same account waits.

Locking in a consistent order matters too. If transfer A→B locks A then B while B→A locks B then A, each waits for the other forever. PostgreSQL detects the deadlock and aborts one with SQLSTATE 40P01. Sorting by id before locking makes that impossible.

For complex invariants that are hard to express with locks, run the transaction at SERIALIZABLE isolation (pool.BeginTx(ctx, pgx.TxOptions{IsoLevel: pgx.Serializable})). PostgreSQL then aborts any transaction that could have produced an anomaly with SQLSTATE 40001, and your job is to retry it:

func withRetry(ctx context.Context, attempts int, fn func() error) error {
	var err error
	for i := range attempts {
		err = fn()
		var pgErr *pgconn.PgError
		if !errors.As(err, &pgErr) || (pgErr.Code != "40001" && pgErr.Code != "40P01") {
			return err // success, or an error that retrying won't fix
		}
		select {
		case <-time.After(time.Duration(i+1) * 20 * time.Millisecond):
		case <-ctx.Done():
			return ctx.Err()
		}
	}
	return err
}

#Practice

1. Add a goose migration that creates a transfers table (id, from_id and to_id referencing accounts, amount bigint with CHECK (amount > 0), created_at), with a Down section.
2. Change Transfer so it also inserts a transfers row in the same transaction.
3. Write a main that runs Migrate, seeds three accounts with 1,000 each, fires 200 concurrent random transfers between them, then prints the outcome counts and the total balance. The total must still be 3,000, with no deadlock errors.

-- +goose Up
CREATE TABLE transfers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    from_id    bigint NOT NULL REFERENCES accounts (id),
    to_id      bigint NOT NULL REFERENCES accounts (id),
    amount     bigint NOT NULL CHECK (amount > 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    CHECK (from_id <> to_id)
);

-- +goose Down
DROP TABLE transfers;

if _, err := tx.Exec(ctx,
	`INSERT INTO transfers (from_id, to_id, amount) VALUES ($1, $2, $3)`, from, to, amount); err != nil {
	return err
}

func main() {
	ctx := context.Background()
	pool, err := pgxpool.New(ctx, os.Getenv("DATABASE_URL"))
	if err != nil {
		log.Fatal(err)
	}
	defer pool.Close()
	if err := Migrate(ctx, pool); err != nil {
		log.Fatal(err)
	}
	_, err = pool.Exec(ctx, `TRUNCATE transfers, accounts RESTART IDENTITY;
		INSERT INTO accounts (owner, balance) VALUES ('ana', 1000), ('ben', 1000), ('cy', 1000)`)
	if err != nil {
		log.Fatal(err)
	}

	var wg sync.WaitGroup
	var mu sync.Mutex
	counts := map[string]int{}
	for range 200 {
		wg.Add(1)
		go func() {
			defer wg.Done()
			from := rand.Int64N(3) + 1
			to := from%3 + 1 // a different account
			err := Transfer(ctx, pool, from, to, rand.Int64N(50)+1)
			mu.Lock()
			defer mu.Unlock()
			if err == nil {
				counts["ok"]++
			} else {
				counts[err.Error()]++
			}
		}()
	}
	wg.Wait()

	var total int64
	if err := pool.QueryRow(ctx, `SELECT sum(balance) FROM accounts`).Scan(&total); err != nil {
		log.Fatal(err)
	}
	fmt.Println(counts, "total:", total)
}

map[ok:200] total: 3000

#Key takeaways

• Migrations are versioned SQL files applied in order and recorded in the database; never edit one that has been applied, and never patch production by hand.
• goose wraps each SQL migration in a transaction; use NO TRANSACTION for CREATE INDEX CONCURRENTLY.
• Every migration must work with both the old and new code: use expand/contract and set a lock_timeout.
• Begin with pool.Begin, defer tx.Rollback(ctx), run everything on tx, and finish with tx.Commit.
• Prevent lost updates with arithmetic in SQL or SELECT ... FOR UPDATE, lock rows in a consistent order, and retry 40001/40P01 errors.
• Keep transactions short and do side effects after commit.