Actually, I think conventional multitenant (shared database with tenant_id on database rows) is a bad idea (as well as premature optimization) in the previously described context.
One of the companies I am thinking of had no dedicated ops team until 300+ customers, running on 300+ instances of the setup. Probably 40+ employees at the time the first dedicated sysadmin was hired.
The trick is that since the production environment is extremely similar to the developer environment, all the tools to upgrade versions, migrate databases, etc, exist and are constantly used by all the developers.
When the customer base grows, horizontal expansion is baked into the architecture. When your largest customer grows beyond what you can host, you scale up with trivial mechanisms (database replication, multiple application servers). All you need is orchestration of extremely common, well understood and mature tools.
Orchestration is home baked anyway at every company that I've had experience with (there is no off the shelf "multitenant application management system"), but by doing it this way you rely on extremely mature components for all the underlying tools, because they have been used to scale websites since 2000.
For context, we implemented your exact model at my job prior to switching to "conventional multi-tenant". I agree with your premise that there are a lot of off the shelf tools to help this, but in enterprise I feel like we hit dozens of other issues and our customers didn't even care if they shared the same database as someone else.
If the production environment is extremely similar to dev, the problems are numerous, how do you ensure that a bug in one app server doesn't let users access other request data? We solved this via multiple app servers, but then how do you solve deploying to hundreds of individual app servers? We have 12 services per customer, replicated to 4 physical locations, that means 124500(customers) 24,000 containers to roll out any upgrade completely. (99.99% SLA)
Also some customers get different services depending on their tier, so now we need to bake in some form of Salesforce integration into our deployment so we know which services to start up(we didn't do this unfortunately, so all services started up and some didn't do anything).
If you split into many physical database servers vs many "logical" databases, how do you monitor that an individual customers performance is adequate? What about APM? One NewRelic instance per customer? One ELK cluster per customer? How do your developers / customer support engineers get access to a specific customer instances both in the software, in the database and APM? I cannot fathom having to manage 500 ELK clusters, even with AWS managed Elastic search.
How do you enable ETL and reporting solutions that support all these databases. What if you have data aggregation rights and need to combine different customer's data? What happens when your product team needs to report across all customers to determine what features to work on (gotta be data driven)?
Maybe this is only related to healthcare (where I work), but in our industry I cannot imagine our health systems and manufacturers not wanting answers to these. These answers are infinitely simpler in our new multi-tenant model vs our old multiple host model. Writing the processes and documentation around guaranteeing segmentation took less than 8 hours and is only 3 pages of our entire security model. Hi-trust specifically calls out many of these points, and I am sure SOC-2 does as well.
In my experience, not a single one of our health systems or auditors has had an opinion either way on this. They only care when you aren't doing APM properly, or taking the necessary steps to prevent attacks both from external parties and by internal parties. We do in-depth third party audits that are shared with our customers. Ensuring good processes for managing risk is always more important than wishful thinking that a design pattern will make your enterprise model work.
Our total lines of code for logical segmentation takes up less than 300 lines in all of our services combined.
Finally, given that Postgres10+ now natively supports row level security, I fail to see why anyone starting a new project would choose the multiple database option when you can bake in authorization straight into the DB at the lowest level.
Sorry this is really long, but I wanted to share my experience & thoughts.
Both of the setups I'm familiar with run multiple application servers on the same "iron" (EC2 clusters), with shared services (database servers, caches, routing, load balancing, etc).
Monitoring, backup, ELK, New Relic etc are global, or shared among sets of clusters depending on the specifics.
I agree that 12 containerized services per customer seems like a headache. The applications I have experience with run fewer components, and they are mostly shared among clusters.
One of the companies I am thinking of had no dedicated ops team until 300+ customers, running on 300+ instances of the setup. Probably 40+ employees at the time the first dedicated sysadmin was hired.
The trick is that since the production environment is extremely similar to the developer environment, all the tools to upgrade versions, migrate databases, etc, exist and are constantly used by all the developers.
When the customer base grows, horizontal expansion is baked into the architecture. When your largest customer grows beyond what you can host, you scale up with trivial mechanisms (database replication, multiple application servers). All you need is orchestration of extremely common, well understood and mature tools.
Orchestration is home baked anyway at every company that I've had experience with (there is no off the shelf "multitenant application management system"), but by doing it this way you rely on extremely mature components for all the underlying tools, because they have been used to scale websites since 2000.