Tracking Schema Versions Across Thousands of Tenants
“Which tenants are still on version 56?” should take a second to answer. In practice it often takes an afternoon: someone writes a script that connects to every tenant database, reads its migration history table, and prints a list — which is out of date by the time it finishes, and which disagrees with the orchestrator’s records for a dozen tenants restored from backup last month. Fleet migrations depend on knowing the fleet’s state: the next contract migration may only drop a column once every tenant has moved past the release that stopped using it; code may only rely on a new table once the fleet minimum includes it. This guide builds a central, continuously reconciled view of tenant schema versions, detects drift within versions, and alerts on stragglers. It belongs to Migrating Multi-Tenant Databases.
Symptom / Error Signatures
A fleet without reliable version tracking shows:
- Deploys that break a few tenants because code assumed a schema version they had not reached.
- Contract migrations that fail on some tenants because an object they expected to drop was already missing, or never created.
- Disagreement between the orchestrator’s records and what tenants actually contain, usually after restores, manual fixes, or tenants provisioned from an old template.
- No quick answer to “what is the oldest schema version in production?”
Root Cause Analysis
There are two places version information lives, and they drift apart. Each tenant’s migration history table (flyway_schema_history, alembic_version, schema_migrations) records what was applied to that tenant — it is authoritative, but spread across thousands of places. The orchestrator’s central records are convenient but reflect only what the orchestrator did, not restores, manual changes or out-of-band provisioning. A reliable fleet view reads the authoritative sources periodically and keeps a central, queryable copy.
Version numbers alone are not enough. Two tenants at version 58 can still differ if one had a manual hotfix or a partially applied non-transactional migration. A schema fingerprint — a hash of the tenant’s normalised schema definition — detects drift within a version: every tenant at version 58 should share one fingerprint.
| Signal | Source | Detects |
|---|---|---|
| applied version | tenant history table | stragglers, failed rollouts |
| schema fingerprint | hash of catalog definition | manual changes, partial migrations |
| central table vs tenant | reconciler | orchestrator bookkeeping errors, restores |
| fingerprint vs baseline for version | reconciler | drift within a version |
Immediate Mitigation
1. Build a snapshot of the fleet now. A reconciler loop reads each tenant’s latest applied version and writes it centrally.
-- PostgreSQL · central registry · fleet version table
CREATE TABLE IF NOT EXISTS tenant_schema_state (
tenant_id text PRIMARY KEY,
version int,
fingerprint text,
checked_at timestamptz NOT NULL DEFAULT now()
);-- PostgreSQL · run in each tenant schema (search_path set) · Flyway history · latest successful version
SELECT max(version::int) FROM flyway_schema_history WHERE success;2. Compute a schema fingerprint per tenant. Hash a normalised description of tables, columns, types, indexes and constraints — ordered, without OIDs or tenant-specific names.
-- PostgreSQL · run in each tenant schema · fingerprint of columns and indexes
SELECT md5(string_agg(line, E'\n' ORDER BY line)) FROM (
SELECT format('col %s.%s %s %s', table_name, column_name, data_type, is_nullable) AS line
FROM information_schema.columns WHERE table_schema = current_schema()
UNION ALL
SELECT format('idx %s %s', tablename, regexp_replace(indexdef, ' ON \S+\.', ' ON '))
FROM pg_indexes WHERE schemaname = current_schema()
) s;3. Report stragglers and drift.
-- PostgreSQL · registry · tenants behind the target, and fingerprints differing within a version
SELECT tenant_id, version FROM tenant_schema_state WHERE version < 58 ORDER BY version;
SELECT version, fingerprint, count(*) FROM tenant_schema_state GROUP BY 1, 2 ORDER BY 1 DESC, 3 DESC;Permanent Fix / Long-Term Pattern
Run the reconciler on a schedule (hourly is typical) and after every rollout wave, with a small concurrency limit so it does not load the databases. Store the expected fingerprint for each version — computed from a freshly migrated reference tenant — and alert when any tenant at that version differs. Feed three consumers from the central table: dashboards showing the version distribution; the deploy gate, which refuses to ship code requiring a version above the fleet minimum; and the contract-migration gate, which refuses to drop structure until every tenant is past the release that stopped using it, as described in handling partial failures in fleet-wide migrations.
Include provisioning and restore paths. New tenants should be created from the current baseline and registered immediately; restores should trigger an immediate reconciliation for that tenant, because a restored tenant is exactly the one most likely to be behind. The same fingerprint approach works for single databases, as in detecting production schema drift against a desired state.
Normalisation is what makes fingerprints useful. Leave out anything that legitimately differs between tenants — schema names, OIDs, sequence current values, tenant-specific partition names — and include everything that should be identical: tables, columns, types, nullability, defaults, indexes, constraints, triggers and functions. Sort every component before hashing. When a fingerprint differs, store the normalised text as well as the hash, so an engineer can diff an outlier against the reference in seconds instead of re-deriving it.
History tables deserve their own sanity checks. A tenant whose history lists version 58 but whose fingerprint matches version 55 has had its history edited or its schema restored without its history; a tenant whose history has gaps (57 missing between 56 and 58) was migrated out of order. Both are worth flagging, because they predict failures in the next contract migration long before it runs.
Verification Checklist
Frequently Asked Questions
Why not rely on the orchestrator’s records? They reflect only what the orchestrator did. Restores, manual fixes and tenants created from old templates change the actual schema without updating those records. Reading each tenant’s own history table is the only authoritative source.
What is a schema fingerprint for? Detecting drift within a version. Two tenants can both report version 58 while one has an extra index added during an incident or a half-applied non-transactional migration. Comparing hashes of the normalised schema finds them.
How expensive is reconciliation across thousands of tenants? Each tenant needs a couple of catalog queries, which are cheap. With a small concurrency limit, reconciling thousands of tenants takes minutes and adds negligible load.
Where should the fleet version table live? In a central registry database that the orchestrator, reconciler, deploy tooling and dashboards can all reach — often the same database that holds tenant metadata. It should not live inside any tenant’s database.
Should tenants be allowed to stay on old versions? Only temporarily and visibly. Every straggler blocks contract migrations for the whole fleet, so track their age and resolve them as part of normal operations.