You are designing a multi-tenant product where each tenant could get its own schema, potentially tens of thousands of schemas and millions of catalog rows. What are the catalog-level consequences, and how would you decide between schema-per-tenant and one shared schema with a tenant identifier column?

## The catalog is a capacity dimension Designers size disks for rows and memory for the buffer pool, then forget that **objects are also rows** — in the dictionary. A schema-per-tenant design does not multiply only your data; it multiplies the catalog. Take a modest application: 20 tables, 3 indexes each, 15 columns each, plus constraints, sequences and per-column statistics. Per tenant that is roughly 20 relations + 60 indexes + 300 column rows + constraints + statistics rows — call it 500 dictionary rows. At 1,000 tenants that is half a million: fine. At 30,000 tenants it is 15 million dictionary rows and 2.4 million relations. Now the dictionary is a large, hot, frequently written table set that must itself be cached, planned against, cleaned up and backed up. ## What breaks first **Introspection latency.** `INFORMATION_SCHEMA` queries are wide joins with privilege filters; on a dictionary of that size, a query an ORM runs at startup can take seconds. Frameworks that introspect per connection or per boot turn this into a thundering-herd problem after a deploy. **Planning and cache pressure.** Every backend caches relation descriptors and plans. With millions of objects, working sets stop fitting; cache churn shows as CPU spent resolving metadata instead of executing queries. Connection setup gets more expensive. **Migrations.** A single `ALTER TABLE` becomes 30,000 of them, each taking a lock, writing catalog rows and broadcasting invalidations. The migration is no longer a statement, it is a batch job with throttling, resumability, partial-failure handling and a version-skew window during which tenants are on different schemas — which the application must tolerate. **Maintenance and metadata churn.** Statistics gathering, cleanup of dead versions, and index maintenance all iterate over objects. So do backups: dumping metadata for millions of objects is itself slow, and restore time grows even when the data is small. **Catalog bloat.** Heavy tenant onboarding and offboarding means constant catalog inserts and deletes, leaving dead versions in the dictionary. The catalog needs the same cleanup as user tables, and the same long-running-transaction hazards apply to it. ## What schema-per-tenant genuinely buys It is not irrational. It gives physical separation of tenant data (simple, auditable isolation and an easy story for regulators), per-tenant backup and restore, per-tenant customization when tenants legitimately have different columns, and trivially correct queries — no risk of forgetting a tenant predicate and leaking data. It also keeps per-tenant index statistics precise, since each table is small and its distribution is not blended with 30,000 others. Those benefits are real up to roughly the low thousands of tenants. Past that, the operational tax dominates. ## Shared schema and its costs One table set with a `tenant_id` column keeps the catalog constant regardless of tenant count: one migration, one statistics set, one backup. The costs are pushed into query design and safety: - **Every index must lead with the tenant key**, or queries scan across tenants. - **Isolation becomes logical.** You either enforce it in a single data-access layer or use row-level security so the database rejects unscoped access; relying on developers to always add `WHERE tenant_id = ?` is how leaks happen. - **Skew.** One tenant with 60% of the rows distorts optimizer statistics and creates hot pages; column statistics blended across tenants can produce bad plans for both the giant and the small. - **Per-tenant operations get harder.** Restoring one tenant to yesterday, or deleting a departing tenant's data, is now a data job rather than a `DROP SCHEMA`. Partitioning by tenant (or by a hash of it) on the largest tables recovers much of this: per-partition statistics, pruning, cheap bulk delete of a tenant's partition — at a much lower catalog cost than a full schema per tenant, though partitions are still catalog objects and thousands of them have their own planning cost. ## How I would decide 1. **Estimate the tenant count and its growth curve honestly**, then multiply by objects per tenant. If the product is above roughly a million dictionary rows, treat schema-per-tenant as disqualified unless something else forces it. 2. **Ask what isolation is actually required.** If the answer is contractual or regulatory hard isolation, the right unit is a separate database or cluster, not a schema in a shared instance — a schema gives you neither resource isolation nor an independent failure domain. 3. **Default to shared schema with a tenant key**, enforced by row-level security or a single audited access layer, with tenant-aware indexing and partitioning for the biggest tables. 4. **Allow a hybrid**: the long tail shares a schema; a handful of very large or compliance-bound tenants get their own database. This is the shape most mature multi-tenant systems converge on. 5. **Whatever you choose, instrument the catalog**: track object count, dictionary size and introspection latency as first-class capacity metrics, and set a threshold that triggers a re-architecture conversation before it becomes an incident.