Zero-downtime Postgres migrations with GORM
A migration takes down a production service in one of two ways: it holds a lock that blocks every query against a table, or it changes a shape the currently running code still expects. Both are avoidable, and the techniques are not complicated once you know which statements are dangerous.
This covers which Postgres operations are safe, which are not and what to do instead, and the expand-and-contract sequence that makes a schema change survive a rolling deploy.
19 min read. Commands and output last run against Grit v3.560.0.
What you will have at the end
A list of the DDL that is safe to run on a live table, and the list that is not.
A migration sequence that works while two versions of your application are running at once, which is what a rolling deploy means.
A rollback plan that is not "restore the backup".
Where AutoMigrate stops
db.AutoMigrate(&User) compares structs to the database and adds what is missing. It is excellent for development and unusable in production, for four specific reasons rather than as a matter of taste.
- It never drops or renames. Remove a field and the column stays. Rename one and you get a new column beside the old one, with the data in the old one.
- It cannot be reviewed. There is no artefact in the pull request. Nobody can see that this deploy adds a NOT NULL column to a table with forty million rows until it is running.
- There is no down. No rollback, no record of what ran, no way to reproduce the schema at a given release.
- It takes whatever lock the generated statement needs. You did not choose the statement, so you cannot know what it will hold.
So: AutoMigrate locally for speed, versioned SQL files everywhere else. The structs remain the source of truth for the Go side; the migration files are the record of how the database got here.
Which statements are safe
The question is always which lock a statement takes and for how long. An ACCESS EXCLUSIVE lock blocks every read and every write on that table, including plain SELECT. Taking one for ten milliseconds is invisible. Taking one for four minutes is an outage.
Safe: instant, regardless of table size
ALTER TABLE invoices ADD COLUMN note text; -- nullable, no defaultALTER TABLE invoices ADD COLUMN status text DEFAULT 'draft'; -- PG 11+: metadata onlyALTER TABLE invoices DROP COLUMN note; -- metadata onlyALTER TABLE invoices RENAME COLUMN a TO b; -- instant, and see belowALTER TABLE invoices ALTER COLUMN note DROP NOT NULL;CREATE INDEX CONCURRENTLY idx_invoices_status ON invoices (status);
Adding a column with a default stopped rewriting the table in Postgres 11. On an older version it rewrites every row while holding the lock, which is the single most common cause of a migration outage in the wild.
Dangerous: a full table scan or rewrite, under a lock
CREATE INDEX idx ON invoices (status); -- blocks writes for the buildALTER TABLE invoices ADD COLUMN x int NOT NULL; -- must check every rowALTER TABLE invoices ALTER COLUMN total TYPE bigint; -- rewrites the tableALTER TABLE invoices ADD CONSTRAINT ... CHECK (...); -- validates every rowALTER TABLE invoices ADD FOREIGN KEY ...; -- validates every rowALTER TABLE invoices SET NOT NULL; -- scans every row
The safe equivalents
-- Index: build it without blocking writes. It cannot run inside a-- transaction, which matters for your migration runner.CREATE INDEX CONCURRENTLY idx_invoices_status ON invoices (status);-- A check constraint, in two steps: add it unvalidated, which takes a-- brief lock, then validate it, which takes a weak one.ALTER TABLE invoices ADD CONSTRAINT total_positiveCHECK (total >= 0) NOT VALID;ALTER TABLE invoices VALIDATE CONSTRAINT total_positive;-- NOT NULL on Postgres 12+: a validated CHECK lets the scan be skipped.ALTER TABLE invoices ADD CONSTRAINT x_not_null CHECK (x IS NOT NULL) NOT VALID;ALTER TABLE invoices VALIDATE CONSTRAINT x_not_null;ALTER TABLE invoices ALTER COLUMN x SET NOT NULL; -- now instantALTER TABLE invoices DROP CONSTRAINT x_not_null;
A lock queues behind, and in front of, everything
A migration waiting for an ACCESS EXCLUSIVE lock does not wait quietly. Every query that arrives after it queues behind it, including reads, because the lock request is ahead of them. One long-running SELECT can therefore cause a complete outage on a table: the migration waits for the select, and everything else waits for the migration.
Always bound the wait, so a stuck migration fails fast instead of taking the site:
SET lock_timeout = '3s';SET statement_timeout = '30s';
Expand and contract
During a rolling deploy, the old and new versions of your application run at the same time against the same database. Any migration that both versions cannot live with is an outage, however fast it runs.
The sequence is four deploys, and the discipline is that each one is reversible.
Goal: rename invoices.total (int) to total_cents (bigint).1. EXPAND add total_cents, nullable. Old code ignores it.2. BACKFILL copy in batches. Both columns exist; both versions work.DUAL WRITE new code writes both, reads total. Deployable, reversible.3. SWITCH new code reads total_cents, still writes both.4. CONTRACT stop writing total. A later deploy drops it.
1. Expand
ALTER TABLE invoices ADD COLUMN total_cents bigint;
2. Backfill, in batches
// Never a single UPDATE over the whole table: it holds row locks on// every row it has touched until it commits, and a failure at 90% rolls// back the entire thing.func backfillTotalCents(ctx context.Context, db *gorm.DB) error {for {res := db.WithContext(ctx).Exec(`UPDATE invoices SET total_cents = total::bigintWHERE id IN (SELECT id FROM invoicesWHERE total_cents IS NULLORDER BY idLIMIT 5000FOR UPDATE SKIP LOCKED)`)if res.Error != nil {return fmt.Errorf("backfill batch: %w", res.Error)}if res.RowsAffected == 0 {return nil}// Breathe. Replication lag is the thing that bites here: a tight// loop can push a read replica minutes behind.time.Sleep(100 * time.Millisecond)}}
SKIP LOCKED means the backfill steps around rows your application is currently updating rather than waiting for them, so the two do not fight. Restartable by construction: it selects only rows that are still null.
3. Dual write, then switch
// Deploy A: write both, read the old. Safe to roll back, because the// old column is still current.inv.Total = centsinv.TotalCents = int64(cents)// Deploy B, after the backfill has finished: read the new, keep writing// both. Still safe to roll back.// Deploy C: stop writing the old one.// Only now is dropping the column safe, and it is a separate migration.
Why not just rename it
ALTER TABLE ... RENAME COLUMN is instant and is still an outage during a rolling deploy: the moment it commits, every running instance of the old version is querying a column that no longer exists. The rename is cheap for the database and catastrophic for the application. Expand and contract exists for the application, not for the lock.
The migration runner
Four properties matter, and most hand-rolled runners miss at least one.
- A lock, so two instances cannot migrate at once. Rolling deploys start several pods simultaneously. An advisory lock in Postgres is the simplest correct answer.
- A record of what has run, with a checksum, so an edited migration that already ran is an error rather than a silent divergence.
- Each migration in its own transaction, with an escape hatch for the statements that cannot be, which is
CREATE INDEX CONCURRENTLYand a few others. - A timeout, so a blocked migration fails rather than queueing the whole application behind it.
// One instance migrates; the others wait and then find nothing to do.// 4242 is an arbitrary constant, shared by every instance of this app.if err := db.Exec("SELECT pg_advisory_lock(4242)").Error; err != nil {return fmt.Errorf("acquire migration lock: %w", err)}defer db.Exec("SELECT pg_advisory_unlock(4242)")
Migrating on application boot is a trap
It is convenient and it couples deployment to schema change: a migration that takes four minutes delays every pod's readiness, and a failed migration crash-loops the application rather than failing one job. Run migrations as a separate step that must succeed before the new version is rolled out.
Rolling back
Down migrations are worth writing and are not a plan. A down that drops a column you spent a deploy backfilling destroys data, and you will not run it in the incident, so it provides comfort rather than safety.
The honest position:
- Write the down anyway, for local development and for the cases where it is genuinely reversible, such as dropping an index you just created.
- Make every forward migration safe for the previous version of the code. That is what the expand-and-contract shape buys: the rollback is a code rollback, which is fast and tested, rather than a schema rollback, which is neither.
- Never combine a destructive change with a release you might reverse. Drop the column a week later, in its own deploy, when the previous version is no longer something you would roll back to.
- Take a snapshot before a destructive migration, and know how long a restore actually takes. An untested restore is an assumption.
Testing a migration before it meets production
Running it against an empty development database tells you the syntax is valid and nothing else. Three checks that find real problems:
- Run it against a copy with production-like volume. Lock duration is a function of row count, and every dangerous statement in this page is instant on a thousand rows.
- Run it while the old version is serving traffic. A load generator against the old code during the migration is the only way to observe the lock you did not expect.
- Check what the migration actually did, not what the struct says. A test schema built by
AutoMigrateand a production schema built by migrations are different schemas, and a constraint that exists in one and not the other fails silently in the accepting direction: the test inserts the row and passes.
-- During the migration, from another session: what is blocked, and by-- what. The most useful query in an incident.SELECT pid, state, wait_event_type, left(query, 80) AS query,now() - query_start AS durationFROM pg_stat_activityWHERE datname = current_database() AND state <> 'idle'ORDER BY query_start;
Generated migrations
$grit generate migration add_total_cents_to_invoices$grit generate migration add_status_index --index invoices:status # CONCURRENTLY$grit migrate # advisory lock, checksums$grit migrate status # what each run changed$grit migrate down # undo the last run
The --index form writes CREATE INDEX CONCURRENTLY and marks the migration as not transactional, because those two facts always travel together and forgetting the second turns the first into an error.
grit index reports tables whose queries would benefit from an index and does not create one, because an index is a write cost as well as a read benefit and that is a decision rather than a fix.
The migrations reference covers the runner, the checksums and the rollback.
Frequently asked questions
- Is AutoMigrate ever acceptable in production?
- For a single-instance internal tool where a few seconds of downtime is irrelevant, it is a reasonable trade. For anything with users it is not, mostly because it cannot be reviewed: nobody sees the statement before it runs, so nobody can notice that it is about to rewrite a large table.
- How long is too long to hold a lock?
- Any
ACCESS EXCLUSIVElock over about a second on a busy table is visible to users, because every query queues behind it. Setlock_timeoutto a few seconds so a migration that cannot get the lock fails rather than building a queue, and retry it when the table is quieter. - Why does CREATE INDEX CONCURRENTLY fail inside my migration?
- It cannot run inside a transaction, and most migration runners wrap each file in one. The runner needs a way to mark a migration as non-transactional. The second thing to know is that a concurrent build can fail and leave an invalid index behind, which must be dropped before retrying; it does not clean up after itself.
- Do I need expand and contract for every change?
- No. Adding a nullable column, adding an index concurrently and dropping an unused index are all single-step and safe. Expand and contract is for changes where the old code and the new code disagree about the shape: renames, type changes, splitting a column, and making something required.
- Should migrations run on application startup?
- No. It couples schema change to deployment, delays readiness for every instance, and turns a failed migration into a crash loop. Run them as a separate step that must succeed before the new version rolls out, so a failure is one visible failed job rather than an application that will not start.
Next steps
- GORM relationshipsThe models these migrations are changing.
- Multi-tenant SaaS in GoWhy schema-per-tenant makes every migration a job with a progress bar.
- Migrations referenceThe generated runner, checksums, the advisory lock and rollback.
- ScalingRead replicas, connection pooling, and what a migration does to both.
