Tenant 47 needs a column the schema doesn't have yet
Tenant 47's account manager wants to store a GST treatment code on every invoice line item. The column doesn't exist. You have 340 tenants on shared Postgres, and the largest tenant runs 900 invoices an hour between 9am and 7pm. There is no two-hour window where traffic drops to zero.
The migration has to be online, and it has to be reversible.
We run additive migrations in three phases: expand, migrate, contract. Each phase ships independently and the app stays writable throughout.
Expand
Add the column with a default but no NOT NULL constraint. On Postgres 11+, adding a column with a constant default rewrites no rows — the default lives in the catalog. This is fast and safe:
ALTER TABLE invoice_lines
ADD COLUMN gst_treatment TEXT DEFAULT 'standard';A NOT NULL constraint added later would trigger a full table scan and rewrite. Don't add it yet. The column is nullable in practice, and the application code treats NULL as "standard" until the backfill completes.
Migrate
Deploy application code that writes to the new column. Reads still fall back to the default. Then backfill existing rows in batches, scoped per tenant so a slow tenant doesn't block the rest:
UPDATE invoice_lines
SET gst_treatment = 'standard'
WHERE tenant_id = :tenant_id
AND gst_treatment IS NULL
AND id BETWEEN :start_id AND :end_id;Run this from a worker that processes one tenant at a time, 1,000 rows per batch, with a 50ms sleep between batches. On the largest tenant this takes about 14 minutes. On the smallest, under a second. If the worker dies, restart it — the WHERE clause makes it idempotent.
Contract
Once every row is backfilled and the application is writing the column on every new invoice line, add the NOT NULL constraint. On a fully backfilled table this is a metadata-only operation:
SET lock_timeout = '2s';
ALTER TABLE invoice_lines
ALTER COLUMN gst_treatment SET NOT NULL;The `lock_timeout` ensures that if something is holding an ACCESS EXCLUSIVE lock — a long-running report query, an autovacuum — the constraint addition fails rather than blocking writes. You retry it during a low-traffic period.
What we don't do
We don't rename columns in a single migration. A rename is two operations: add the new column, then backfill from the old one, then switch reads, then drop the old one. That's the same three-phase pattern over two releases.
We don't use CREATE INDEX without CONCURRENTLY on any table larger than a few thousand rows. A plain CREATE INDEX blocks writes. CONCURRENTLY takes longer and can fail if there's a unique violation, but it doesn't block inserts:
CREATE INDEX CONCURRENTLY
idx_invoice_lines_tenant_gst
ON invoice_lines (tenant_id, gst_treatment);We don't run migrations through the application's ORM migration runner in production. Alembic and Rails migrations acquire locks that the ORM doesn't always surface. We run raw SQL through a migration tool that logs every statement, its duration, and the lock it acquired. If a migration takes more than 5 seconds on a table that receives writes, we investigate before proceeding to the next statement.
Rolling back
Every migration has a reverse script written before the forward one ships. For an additive migration, the reverse is `ALTER TABLE invoice_lines DROP COLUMN gst_treatment`. Dropping a column is fast — it's a catalog update. But the application code deployed alongside the migration must tolerate the column's absence, because the rollback runs before the code rollback. We handle this by keeping the fallback-to-default logic in the read path for one full release cycle after the contract phase completes.
The tradeoff is release complexity. A single feature change spans two deploys — one for the expand+migrate code, one for the contract constraint. We accept this because the alternative is a maintenance window, and our tenants' traffic patterns don't leave us one.