One Postgres instance, one schema per tenant. It's a layout I like for the services I run in production: isolation is a search_path away, dropping a tenant is DROP SCHEMA ... CASCADE, and backups don't need a WHERE clause.
Then you write your second migration and hit the question this post is about: which tenant is on which version?
Most migration tools assume one schema per database. They keep a single schema_migrations table and a single answer to "what version am I on". With N tenant schemas that single answer is a lie. Tenant acme was created in January and has everything. Tenant beta was created yesterday and needs the full set. Tenant carol was mid-migration when a deploy died. One global table can't represent that.
So I wrote a small runner where the ledger lives inside each tenant schema. Every schema carries its own schema_migrations table, and a tenant's version is a property of the tenant, not of the database. Here's the mechanics.
The shape
Four pieces:
-
public.tenants: a registry mapping tenant IDs to schema names. This is the only source of schema names. Nothing derived from request input ever becomes an identifier. -
schema_migrationsper schema: version, name, applied_at. Drop the schema, its history goes with it. No orphan rows in a global table pointing at a schema that no longer exists. - Embedded SQL files (
//go:embed sql/*.sql), namedNNNN_name.sql, compiled into the binary. The binary and its migration set can't drift apart. - One transaction per schema, with a per-schema advisory lock, and stop-on-first-error across schemas.
MigrateAll
read public.tenants (ORDER BY id)
for each tenant:
ensureSchema idempotent, plain autocommit
BEGIN
pg_advisory_xact_lock(schema)
read schema_migrations
apply pending SQL + insert ledger rows
COMMIT
on error: rollback this schema, stop the whole run
Loading: numeric sort, loud failures
Filenames are the version scheme, so parsing them is where silent bugs live. Version 10 sorts before 2 lexically, and I've seen that exact bug ship elsewhere. The loader sorts numerically and refuses anything malformed:
seen := map[int]string{}
var migs []Migration
for _, e := range entries {
if e.IsDir() || !strings.HasSuffix(e.Name(), ".sql") {
continue
}
version, name, err := parseFilename(e.Name())
if err != nil {
return nil, err
}
if prev, dup := seen[version]; dup {
return nil, fmt.Errorf("duplicate migration version %d: %s and %s", version, prev, e.Name())
}
Duplicates are an error, not a coin flip. 0001_first.sql and 1_second.sql parse to the same version, and the tests pin that down:
func TestLoadFromRejectsDuplicateVersions(t *testing.T) {
// 0001 and 1 parse to the same version; that is a conflict, not an order.
fsys := fstest.MapFS{
"0001_first.sql": file("SELECT 1"),
"1_second.sql": file("SELECT 2"),
}
if _, err := LoadFrom(fsys); err == nil {
t.Fatal("want duplicate version error, got nil")
} else if !strings.Contains(err.Error(), "duplicate") {
t.Fatalf("error should name the duplicate: %v", err)
}
}
testing/fstest.MapFS makes this whole layer testable without touching a database, which is most of why loading is its own function.
Provisioning: idempotent, outside the transaction
Before migrating a schema, the runner makes sure it exists and has a ledger. This runs as plain autocommit statements, not inside the migration transaction:
func ensureSchema(ctx context.Context, pool *pgxpool.Pool, schema string) error {
quoted, err := db.QuoteIdent(schema)
if err != nil {
return err
}
if _, err := pool.Exec(ctx, "CREATE SCHEMA IF NOT EXISTS "+quoted); err != nil && !duplicateObject(err) {
return fmt.Errorf("create schema: %w", err)
}
if _, err := pool.Exec(ctx, "CREATE TABLE IF NOT EXISTS "+quoted+`.schema_migrations (
version bigint PRIMARY KEY,
name text NOT NULL,
applied_at timestamptz NOT NULL DEFAULT now()
)`); err != nil && !duplicateObject(err) {
return fmt.Errorf("create schema_migrations: %w", err)
}
return nil
}
Two things worth pausing on.
QuoteIdent. Schema names are identifiers, and you can't bind identifiers as query parameters. String concatenation into DDL is exactly where injection lives, so every schema name goes through a validate-and-quote step before it touches SQL. Names come from the registry, never from a request, but the quoting happens anyway. Defense you don't have to remember to apply is the only kind that works.
duplicateObject. CREATE IF NOT EXISTS isn't atomic against itself. Two sessions can both pass the existence check, and the loser takes a unique violation on the system catalog instead of the no-op it expected. Rolling deploys hit this for real, since every replica provisions the same schemas at boot:
func duplicateObject(err error) bool {
var pgErr *pgconn.PgError
if !errors.As(err, &pgErr) {
return false
}
// unique_violation (on the system catalog), duplicate_schema,
// duplicate_table.
return pgErr.Code == "23505" || pgErr.Code == "42P06" || pgErr.Code == "42P07"
}
The object exists either way, which is all the caller asked for, so those errors count as success.
Applying: advisory lock, then the ledger diff
The migration itself runs in a single transaction pinned to the schema. Postgres DDL is transactional, so a failing step rolls the whole schema back to where it was. The transaction opens by taking a lock:
if _, err := tx.Exec(ctx, "SELECT pg_advisory_xact_lock(hashtext($1))", schema); err != nil {
return fmt.Errorf("acquire migration lock: %w", err)
}
Why the lock: two runners can hit the same schema at once. Replicas booting during a rolling deploy both run MigrateAll, or a tenant-creation path races a MigrateAll that just read the registry. Without the lock both read an empty ledger, both run the DDL, and the loser dies on the ledger's primary key after blocking on the winner's commit. With it they serialize per schema: the loser waits, sees the winner's committed rows, skips them. It's pg_advisory_xact_lock, transaction-scoped, so there's no unlock path to forget.
After the lock, the loop is boring on purpose:
for _, m := range migs {
if applied[m.Version] {
continue
}
if _, err := tx.Exec(ctx, m.SQL); err != nil {
return fmt.Errorf("apply migration %d (%s): %w", m.Version, m.Name, err)
}
if _, err := tx.Exec(ctx,
"INSERT INTO schema_migrations (version, name) VALUES ($1, $2)",
m.Version, m.Name); err != nil {
return fmt.Errorf("record migration %d (%s): %w", m.Version, m.Name, err)
}
}
The migration SQL and its ledger row commit or roll back together. There's no state where a step ran but wasn't recorded, or was recorded but didn't run.
The sharp edge: partial failure across schemas
Per schema, the run is atomic. Across schemas, it deliberately isn't. MigrateAll walks the registry in stable ID order and stops at the first failure:
for _, t := range tenants {
if err := Apply(ctx, pool, t.schema, migs); err != nil {
return fmt.Errorf("tenant %s: %w", t.id, err)
}
}
So after a failed run you can have schemas A and B on version 5 and schemas C through Z still on version 4. That sounds scary until you consider the alternatives. A cross-schema transaction over hundreds of schemas is a locking disaster. Continue-on-error gives you a scatter of failures to reconcile. Stop-on-first-error gives the operator one failing tenant, named in the error, and a re-run that's safe by construction: already-migrated schemas see full ledgers and no-op, the failing one retries from its rolled-back state.
The operational contract this forces on you is the honest one for multi-tenant systems anyway: your app code must tolerate schemas at version N and N+1 at the same time, at least for the duration of a deploy. Expand-then-contract migrations, in other words. The runner isn't what makes that necessary, it just stops pretending otherwise.
One more consequence I like: per-tenant version also means a tenant created mid-run is just a schema with an empty ledger. Same code path, zero special cases. The registry insert uses ON CONFLICT DO NOTHING with a follow-up SELECT to distinguish "already registered, same schema, fine" from "that schema belongs to someone else, refuse", because the tenants table has two unique keys and a plain conflict target only covers one of them.
The full runner is about 300 lines including comments, in internal/migrate of a multi-tenant gateway I'm building in the open. If you're doing schema-per-tenant and your migration tool keeps one global version number, check what it thinks happened after your next failed deploy. Mine used to think everything was fine.
Top comments (0)