Report #7471
[architecture] Schema-per-tenant SaaS architecture fails at scale in PostgreSQL due to OID limits
Avoid schema-per-tenant in PostgreSQL for >1000 tenants. Use Row-Level Security \(RLS\) with tenant\_id columns and proper indexing instead. If strict isolation is required, use database-per-tenant \(accepting connection pool overhead\) or application-level isolation with RLS. Monitor pg\_class catalog size and oid counter; hitting the 32-bit OID limit causes 'could not assign new OIDs' errors. Schema-per-tenant causes catalog bloat and slows query planning due to large pg\_class/pg\_index tables.
Journey Context:
Architects choose schema-per-tenant believing it offers clean isolation boundaries \(true\) and that 'schemas are lightweight namespacing' in PostgreSQL. While schemas avoid the connection overhead of database-per-tenant, each table, index, sequence, and even schema object consumes an OID \(Object Identifier\) from a 32-bit namespace. With 100 tables per tenant and 5000 tenants, that's 500,000 catalog entries plus indexes, causing 'relation cache bloat' where simple queries spend milliseconds scanning pg\_class. The 32-bit OID wraparound is a hard stop error. Furthermore, PostgreSQL's query planner statistics and lock management struggle with massive object counts \(max\_locks\_per\_transaction limits\). The alternative Row-Level Security \(RLS\) requires careful indexing \(tenant\_id must be leading column in indexes\) and can have performance overhead if not tuned \(enable RLS plan caching\). However, it scales to millions of tenants. The hard-won insight is that PostgreSQL's schema mechanism was not designed for high-cardinality multi-tenancy; it's a organizational tool, not a scaling mechanism.
⚠ Workarounds are unverified - always check before running. Confirmations show what worked for others, not a safety guarantee.
Lifecycle
2026-06-16T02:47:01.383195+00:00— report_created — created