# From Unrecoverable to Under Control: Taming Multi-Tenant PostgreSQL at 17,000 Schemas > A national employee-benefits administration provider runs its entire business on a single PostgreSQL cluster. Its benefits-administration platform gives every client company a schema of its own: roughly 17,000 to 19,000 identically structured schemas in one database, a schema-per-tenant architecture at a scale most teams never encounter. The data is HIPAA-regulated, the platform is the system of record for benefits processing, and the cluster is self-managed PostgreSQL 11 on Azure virtual machines, with no managed-cloud safety net underneath it. # From Unrecoverable to Under Control Taming Multi-Tenant Postgres with 17,000 Schemas [ Learn More ]() [ Get Support ]() * **Industry:** Employee benefits administration (HIPAA-regulated) * **Organization:** A national employee-benefits administration provider (HIPAA-regulated) * **Environment:** Self-managed PostgreSQL 11 on Azure VMs (primary + streaming replica), pgBackRest to Azure storage, Zabbix + pgBadger monitoring * **Engagement:** Proactive DBA managed services, client since 2024, an engagement that began as an inbound emergency * **Services:** Backup & disaster recovery · Autovacuum and catalog-scale tuning · Monitoring build-out · Cost optimization Outcome --- Unrecoverable primary database fully recovered; disaster recovery now validated end-to-end, with a full restore completing in roughly 90 minutes (parallel restore plus WAL catch-up) ~$3,600/month of orphaned backup storage identified for elimination, and a retention policy designed for a backup set that had grown past 12 TB 19 of 19 high-priority issues resolved (100%), with zero emergency-priority incidents since onboarding Application-down autovacuum blocking incident resolved with no recurrence, and vacuum capacity re-tuned for a catalog spanning ~17,000 tenant schemas Results at a glance ## Get in touch We would love to hear from you! Let us become your new partner in Postgres and Open Source. [Learn More]() ## The Client A national employee-benefits administration provider runs its entire business on a single PostgreSQL cluster. Its benefits-administration platform gives every client company a schema of its own: roughly 17,000 to 19,000 identically structured schemas in one database, a schema-per-tenant architecture at a scale most teams never encounter. The data is HIPAA-regulated, the platform is the system of record for benefits processing, and the cluster is self-managed PostgreSQL 11 on Azure virtual machines, with no managed-cloud safety net underneath it. ## The Challenge The client first reached us as a non-customer, mid-emergency. A backup had failed, and repeated restore attempts were stalling during WAL replay with corrupt-record errors. The primary database, and the benefits data of 17,000+ tenants with it, was effectively unrecoverable. The root cause was a fragile, hand-rolled rsync-based restore process layered on an Azure storage account whose hierarchical-namespace setting was silently incompatible with pgBackRest. The emergency exposed the deeper problem: almost nothing about standard PostgreSQL operations survives contact with 17,000 schemas unmodified. The system catalog is enormous, so maintenance jobs that iterate every tenant object can balloon lock-manager memory. Autovacuum, tuned for a normal schema count, falls structurally behind. At one point workers ran so long they blocked transactional queries and brought applications down, and transaction-ID age later drifted to roughly 292 million before monitoring flagged it for a managed freeze plan. Backups had swollen past 12 TB with no retention policy. An orphaned legacy storage account was burning about $3,600 per month for nothing. The application connected as superuser with no pooler, periodically exhausting reserved connection slots. All of it on an end-of-life PostgreSQL 11. ## The Solution We followed our standard arc: diagnose, stabilize, fix root causes, then automate so problems never recur. * **Rescue and rebuild the backup pipeline.** We rebuilt the restore on a properly configured pgBackRest stanza and a new, compatible storage account, recovering the original cluster. We then combined the disaster-recovery test with building a streaming replica, validating restorability and adding high availability in one motion. The full restore completed in about an hour of parallel restore plus half an hour of WAL catch-up. * **Stand up real observability.** We built Zabbix monitoring and nightly pgBadger analysis from scratch, including backup-job monitoring that later caught and resolved its first genuine backup failure before the client ever noticed. * **Re-tune vacuum for extreme schema scale.** We right-sized the servers, increased autovacuum worker capacity, resolved the application-down blocking incident without recurrence, and put transaction-ID-age tracking and freeze planning in place as a standing practice rather than an ad-hoc scramble. * **Coach the application at catalog scale.** We diagnosed a cross-schema batch extract whose anonymous DO block was iterating thousands of schemas in a single transaction and exploding lock-manager entries, and we showed the team how to convert it to a procedure that releases locks per object. We delivered the idle-in-transaction monitoring query the client now relies on, and we traced connection exhaustion to superuser application logins. * **Cut waste and plan the future.** We flagged the orphaned ~$3,600/month storage account for deletion, designed retention for the 12 TB backup set, kept security patching current on primary and replica, and delivered an architecture-lifespan review with a schema-consolidation and partitioning design: the modernization path off PostgreSQL 11 and out of the schema sprawl. ## The Results A database that could not be restored is now protected by a validated, monitored pgBackRest pipeline with a proven ~90-minute recovery path and a warm replica. Every one of the 19 high-priority issues raised across the engagement has been resolved, a 100% record, with zero emergency-priority incidents and no data loss since onboarding. Even an accidental DELETE without a WHERE clause was reversed from backup the same day. Identified savings of roughly $3,600 per month offset a substantial share of the engagement's cost. And the relationship itself is the clearest result: an emergency call from a stranger converted within days into an ongoing proactive managed-services engagement. ## Why it Matters Schema-per-tenant multi-tenancy is easy to start and brutal to operate at scale. At 17,000 schemas, autovacuum, backups, catalog locks, and upgrades all stop behaving the way the documentation assumes. This engagement shows the pattern that works: stabilize recoverability first, instrument everything, re-tune maintenance for the real catalog size, and fix application behavior at the root. An architecture most teams would call unmanageable becomes a system that simply runs. --- [View this page online](https://www.commandprompt.com/about/success-stories-case-studies/from-unrecoverable-to-under-control-taming-multi-tenant-postgresql-at-17000-schemas/)