When Schema Count Becomes the Real Scaling Limit
There’s a class of PostgreSQL deployments where the database isn’t just large—it’s structurally hostile to the tools meant to manage it. One such case involved a system with 3,500 schemas, each containing roughly 800 tables. On modern hardware, a routine pg_upgrade ballooned into a 48-hour operation that consumed over 128GB of RAM and still threatened to OOM. The sheer volume of catalog entries made even the most basic maintenance operations grind to a halt.
The eventual workaround was to abandon pg_upgrade entirely for a version migration from PostgreSQL 11 to 14. Instead, the team parallelized pg_dump at the schema level, breaking the migration into three sequential batches of 64 parallel dumps, each covering roughly 20 schemas. The full dump-and-restore completed in about four hours—a dramatic improvement over the multi-day estimate for the standard path. But the fix came with its own heavy cost: each pg_dump process consumed 14GB of RAM, requiring a 1TB machine just to host the parallel dump instances.
The bottleneck was traced to find_loops logic inside pg_dump, which determines the correct output ordering of objects. In this environment, that routine alone accounted for the multi-day stall, though the exact triggering condition was never isolated.
Operational Failure at Scale
Running such a regime in production breaks nearly every conventional assumption. autovacuum spends minutes just parsing statistics to decide whether it needs to act. Connection poolers were forced to fully refresh their caches every 30 seconds to mitigate catcache memory growth—and still occasionally fell short during peak load. The legacy stats collector process hammers the disk with its periodic refresh cycles, adding another layer of avoidable I/O pressure.
Debugging was further hampered by the platform: this ran on Amazon RDS for PostgreSQL 11, which predates the memory contexts view and offers zero server-level access. The database continued operating on a combination of hope and sustained engineering effort, with no visibility into the internal memory state.
The situation has not improved over time. The same server is still in production, reportedly in worse condition than when it was first identified as problematic. The application is being re-architected to support multiple servers rather than a single monolithic instance, in part because migrating out of PostgreSQL 14 would otherwise require an unacceptable downtime window.
