The Expand/Contract Primary Key Migration: Rolling-Deploy-Safe Schema Changes on Postgres

Widening a primary key looks like a one-line database migration. In a single-instance world it is one line. In a rolling-deploy world — where the old code and the new code are both live on production traffic for 60 to 90 seconds while your orchestrator drains the old task — that same one line will break every write on every table it touches for exactly as long as the deploy takes. This post is the pattern we now use on every multi-tenant Postgres schema change: expand first, contract later, with a specific ON CONFLICT structure that lets both versions of your code hit the same table without stepping on each other. It is grounded in the exact migration we just shipped on our multi-tenant apps platform — including the SQL, the ON CONFLICT clauses, and the launch-gated contract step that most tutorials skip.

The Migration That Looked Simple

We run a multi-tenant apps backend where a single Postgres cluster stores entitlements and device tokens for what will eventually be several apps. Right now there is one app in production (a card-game platform) and one about to launch (a home-services app called Seep Ghar). Both apps write to the same pro_entitlements and device_tokens tables.

To let a single user hold a Pro entitlement in Card Game Pro without automatically inheriting Pro in Seep Ghar (that would be revenue leakage in both directions), we needed to widen the primary key on those tables from a single column to a composite:

-- The "obvious" migration
ALTER TABLE pro_entitlements DROP CONSTRAINT pro_entitlements_pkey;
ALTER TABLE pro_entitlements ADD PRIMARY KEY (user_id, app_id);

Two statements. Both fast. Both wrong for a rolling deploy.

What Actually Breaks

Our orchestrator (Cloud Run) drains an old revision over 60 to 90 seconds when a new revision comes up. During that window, both old and new tasks are serving production traffic against the same database. Every upsert into pro_entitlements goes through code that looks like this:

-- OLD code (pre-migration)
INSERT INTO pro_entitlements (user_id, tier, expires_at)
VALUES ($1, $2, $3)
ON CONFLICT (user_id)
  DO UPDATE SET tier = EXCLUDED.tier, expires_at = EXCLUDED.expires_at;

-- NEW code (post-migration)
INSERT INTO pro_entitlements (user_id, app_id, tier, expires_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id, app_id)
  DO UPDATE SET tier = EXCLUDED.tier, expires_at = EXCLUDED.expires_at;

The ON CONFLICT clause names a unique constraint. When you drop the old primary key and add the new one in a single migration:

• NEW code sees the new (user_id, app_id) PK — its ON CONFLICT (user_id, app_id) resolves correctly.
• OLD code still runs ON CONFLICT (user_id) — but that unique constraint no longer exists.

Postgres does not silently degrade this. It raises there is no unique or exclusion constraint matching the ON CONFLICT specification on every upsert until the last old task drains. For an entitlements table, that means every Pro upgrade in that 60- to 90-second window fails. Some of them are payment-provider webhooks that retry once and then move on. You lose real revenue and generate real support tickets for a database change that looked like a two-line migration.

The Expand/Contract Split

The fix is the expand/contract pattern, refined into two very specific rules:

1. Expand step: Add the new column and a new UNIQUE INDEX on the composite. Keep the old primary key. Both ON CONFLICT (user_id) (against the retained PK) and ON CONFLICT (user_id, app_id) (against the new unique index) resolve simultaneously. Zero downtime.
2. Contract step: Later — after every old-code task has drained AND after the second tenant actually starts writing — drop the old primary key. Only then does a user get to hold one entitlement per app.

Here is the actual expand SQL we run on boot:

-- pro_entitlements: EXPAND (safe under rolling deploy)
ALTER TABLE pro_entitlements
  ADD COLUMN IF NOT EXISTS app_id TEXT NOT NULL DEFAULT 'card-game-pro';

CREATE UNIQUE INDEX IF NOT EXISTS
  pro_entitlements_user_app_uidx
  ON pro_entitlements (user_id, app_id);

-- Note: we do NOT drop the (user_id) PK here.

The three important things this does:

• ADD COLUMN IF NOT EXISTS ... DEFAULT is a metadata-only operation on Postgres 11+ for non-volatile defaults. No table rewrite, no full table lock.
• CREATE UNIQUE INDEX IF NOT EXISTS without CONCURRENTLY is fine here because the table is small and the composite must be unique from the moment the new code runs. If your table is large, use CREATE UNIQUE INDEX CONCURRENTLY, then validate.
• We keep the (user_id) primary key. Since the current data has exactly one row per user (only one app writes), (user_id) is de-facto unique. The new unique index over (user_id, app_id) is also unique, satisfying the new code. Both conflict targets resolve. Both code versions upsert without errors.

The Contract Step Nobody Runs

Most expand/contract tutorials stop at the expand step and hand-wave the contract with “run it once you’re confident.” That is a footgun the size of an outage. Here is the specific rule we now follow:

Do not run the contract step until a second tenant actually writes to the table.

Until Seep Ghar starts issuing entitlements or storing device tokens, every row in pro_entitlements has app_id = 'card-game-pro'. Dropping the (user_id) primary key while every row is still flagship gives you no new capability (all rows are still unique by (user_id) alone) and adds risk (during the contract migration, the old PK briefly does not exist).

So our contract SQL is exported from the module as a named constant but not run automatically:

// backend/payment-service/db.js
module.exports.PRO_ENTITLEMENTS_CONTRACT_SQL = `
  ALTER TABLE pro_entitlements DROP CONSTRAINT pro_entitlements_pkey;
`;

module.exports.DEVICE_TOKENS_CONTRACT_SQL = `
  ALTER TABLE device_tokens DROP CONSTRAINT device_tokens_pkey;
`;

The runbook says: on the day Seep Ghar goes live, after all old-code tasks have drained (verified via Cloud Run’s revision-management console), run those two SQL statements manually. Not before.

This has a nice second-order property: any engineer who reads the module sees the contract SQL and knows the schema is halfway through a migration. It is documented in code rather than in a wiki page nobody remembers to update.

Verifying Against a Prod-Schema Replica

The subtle bug with expand/contract migrations is that your local development database has already had psql -f schema.sql run cleanly, so it has whatever schema the code currently expects. Your CI database has the same. The prod-schema-with-old-data reality never enters either.

We now do this as part of the migration verification:

1. Restore a recent production dump into a scratch Postgres instance (docker run --rm -d -p 5433:5432 postgres:15).
2. Boot the new code against that scratch database.
3. Run the expand SQL.
4. Fire test upserts using both the OLD ON CONFLICT (user_id) shape and the NEW ON CONFLICT (user_id, app_id) shape.
5. Confirm both succeed and produce the same row-count outcomes.

For our migration, the results were:

[replica] EXPAND applied — pk_column_names=(user_id)  unique_index=(user_id, app_id)
[replica] OLD upsert (ON CONFLICT user_id): OK
[replica] NEW upsert (ON CONFLICT user_id, app_id): OK
[replica] fresh-schema payment tests: 61/61 passed
[replica] fresh-schema lobby tests: 19/19 passed

The fresh-schema test suites (which represent the eventual post-contract state) also stayed green — the expand step didn’t regress anything for a database that skips straight to the new schema.

When to Reach for Expand/Contract

Not every schema migration needs the expand/contract dance. The pattern is worth the extra thinking when:

• The table has an old code path and a new code path that will both be live during the deploy (any rolling deploy against a table with an ON CONFLICT clause).
• The change removes or renames a constraint that existing SQL statements name explicitly. Adding new columns without touching constraints is usually safe as one-shot.
• The change is on a critical write path. Read-only tables can be migrated more aggressively because a brief error blast is easier to absorb.
• You cannot cheaply take a maintenance window. If you can announce a 5-minute outage on a weekend, a straight DROP CONSTRAINT / ADD CONSTRAINT is simpler. In production SaaS with paying customers, the maintenance window itself often costs more than the engineering time for expand/contract.

Related Migration Patterns Worth Knowing

The expand/contract pattern is one of a small family of rolling-deploy-safe migration techniques. Adjacent patterns worth having in your toolkit:

• ALTER TABLE ... ADD COLUMN ... DEFAULT is metadata-only on Postgres 11+ for non-volatile defaults. On Postgres 10 or earlier, it rewrites the entire table. Check your version.
• CREATE INDEX CONCURRENTLY avoids table locks but takes about 2× as long. Almost always the right call for tables over a few million rows.
• Dropping columns via a two-phase soft-drop (rename column, deploy code that ignores it, later DROP COLUMN) — same shape as expand/contract but for the delete direction.
• NOT NULL backfill on a new column — related to the Mautic migration bug we covered in Debugging Mautic /api/contacts 500 After Upgrade. Doctrine’s migration generator does not backfill data for new NOT NULL columns; add a data-cleanup step yourself.

For a broader look at how migration bugs manifest in real self-hosted stacks, see our Mautic 5→7 Docker migration war story (Mautic 5.2.9 to 7.1.2 on Docker: The Migration Path That Actually Works) and the mysqldump footgun that can silently overwrite a live database (The mysqldump —databases Footgun).

The Short Version

If you take one thing from this post, take this: rolling deploys are a distributed-systems problem, and your database migrations are the shared state. The moment your migration removes or renames anything the OLD code path names explicitly (a column, a constraint, an index), you own a 60-to-90-second production incident unless you split the change into a safe expand and a launch-gated contract.

For any migration that touches an ON CONFLICT clause or drops a constraint, treat the schema change as two deploys: a compatible additive one (expand) and a cleanup one (contract) that waits for real business justification before running. Your on-call rotation will thank you.

Related Reading

• Mautic 5.2.9 to 7.1.2 on Docker: The Migration Path That Actually Works — a different-flavour migration war story, doctrine + typed properties instead of Postgres + primary keys.
• The mysqldump --databases Footgun: How to Not Silently Overwrite Your Live Database — another silent-failure database story.
• Cloud Run min-instances=1: The Silent $150/Month Budget Trap — since Cloud Run’s rolling-deploy behaviour is the trigger for this whole class of bug on GCP.