Ask HN: Has anybody shipped a web app at scale with 1 DB per account?

Where I work we're about to move from a single DB across all tenants to a separation of sorts, due to scaling and customer demands. Very large enterprise customers will get their own DB as a "group of one", and "groups" of smaller customers will share a DB. Certain groups will get more up-to-date software with more software version churn, likely a higher number of issues. Other groups will get only rock-solid older versions with back-ported bug fixes ... both kinds of groups will then see benefits along a feature-to-stability curve. Tenants who pay will get test tenants and a chance for those to be in a "group" that's ahead, software-version-wise, of their normal formal tenant.

We do not generally want to fork the product for different versions or schemas or special features -- the goal instead is to roll upgrades through different groups so we have more time to react to issues. We still want one single software version and data storage layout lineage. This matches the Salesforce.com model, so we won't need to deal with lots of different data migration histories, custom fields, etc. (I'm curious to see how long we stick with that). (I realize SFDC is all about custom objects, fields, UIs, etc. ... but their underlying software is same for all tenants. We also have some measure of customization, but within the same underlying DB layout that's the same across all tenants.)

The backend tenants use is written largely in Java / Spring with managed-RDBMS and other data-storage technologies from one of the big cloud vendors. Orchestration is into a Kubernetes/ISTIO environment provisioned from raw cloud-compute, not a managed service. The coordinator between the managed storage-and-other services, Kubernetes/ISTIO, the Docker backend-software registries, the secrets-managers, etc., is a custom Django REST Framework (DRF) server app that lets DevOps provision "groups", attached to them fixed data resources (that don't usually change from deployment-to-deployment) as well as periodically revised/upgraded software resources (i.e., Docker containers with backend software).

The DRF server app's main job is to let DevOps define the next-desired-state aka "deployment" for a "group" (upgrading one or more of the backend servers ... changing the provisioning parameters for a fixed resource ... etc.), and then the kick off a transition to that desired state. Each such "deployment" reviews and validates once again all resource availability, credentials, secrets, etc. ... stopping along the way as appropriate for human verifications. Each step is done within a Django transaction, leading from "old deployment" to "new deployment". Any failure in any step (after an appropriate number of retries) leads to a rollback to the previous deployment state. There's only one low-level step whose failure would lead to an undetermined "emergency" state getting stuck "between deployments", and that's very unlikely to fail since by that point all elements needed for the crucial "switch" in upgraded software have been touched multiple times such that failure at that point is real unlikely. There's a fairly straightforward recovery from that state as well, after human intervention.

We chose this custom method because there are so many elements in so many different infrastructures to verify and tie together that wrapping all the operations in transaction-mediated Python made sense, plus the Python APIs for all infrastructure elements a very good, and mostly involve sending/receiving/inspecting JSON or JSON-like data. There's plenty of logging, and plenty of side-data stored as JSON blobs in DB records for proper diagnosis and accounting when things to go wrong. Groups can have their software upgraded without impact to other groups in the system. Another advantage is that as the "architecture" or "shape" of data and software resources attached to a "group" changes (changes to how configuration is done; introduction of a new backend service; introduction of a new datastore), the DRF server app can seamlessly transition the group from the old to the new shape (after software revision to make the DRF server app aware of what those changes are).

The DRF server app itself is easy to upgrade, and breaking changes can be resolved by an entire parallel deployment of the DRF server app and all the "groups" using the same per-group backend datastores .. the new deployment listens on a "future" form of all tenant URLs. At switchover time the pre-existing DRF server app's tenant URLs get switched to an "past" form, the new DRF server app's groups tenant URLs get switched.

In any case, these are some of the advantages of the approach. The main takeaways so far have been:

  - there was major commitment to building this infrastructure, it hasn't been easy

  - controlled definition of "groups" and upgrades to "groups" are very important, we want to avoid downtime

  - Kubernetes and ISTIO are great platforms for hosting these apps -- the topology of what a "group" and its tenants look like is a bit complicated but the infrastructure works well

  - giving things a unique-enough name is crucial ... as a result we're able to deploy multiple such constellations of fake-groups-of-tenants in development/test environments, each constellation managed by a DRF server

  - the DRF will host an ever-growing set of services related to monitoring and servicing the "groups" -- mostly it can be a single-source-of-data with links to appropriate consoles in Kibana, Grafana, cloud-provider infrastructure, etc.,

We're still early in the use but so far so good.

Somewhat tangetial: If we use something like bigquery which handles multitenancy well, there still doesn't appear to be a good way to expose it to a customer directly (say for a BI tool). Like with a simple username/pwd.

Any pointers?

The way you are thinking would be much cheaper than what I'm doing with the downside that clients data exists on the same server (but maybe they won't care) and the fact that a performance issue for one client can impact other clients.

I haven't found an AWS account per customer to be to bad to manage yet and makes keeping track of your AWS spend per client simple. It's also trivial to run your resources in say Sydney for your Australian clients and London for your European clients.

My startup currently does just this 'at scale', which is for us ~150 b2b customers with a total database footprint of ~500 GB. We are using Rails and the Apartment gem to do mutli-tenancy via unique databases per account with a single master database holding some top-level tables.

This architecture decisions is one of my biggest regrets, and we are currently in the process of rebuilding into a single database model.

FWIW, this process has worked well for what it was originally intended to do. Data-security has a nice db level stopgap and we can keep customer data nicely isolated. It's nice for extracting all data from a single customer if we have extended debugging work or unique data modeling work. It saves a lot of application layer logic and code. I'm sure for the most part it makes the system slightly faster.

However as we have grown this has become a huge headache. It is blocking major feature refactors and improvements. It restricts our data flexibility a lot. Operationally there are some killers. Data migrations take a long time, and if they fail you are left with multiple databases in different states and no clear sense of where the break occurred.

Lastly, if you use the Apartment gem, you are at the mercy of a poorly supported library that has deep ties into ActiveRecord. The company behind it abandoned this approach as described here: https://influitive.io/our-multi-tenancy-journey-with-postgre...

Happy to expand on this if anybody is interested. It's currently a cause of major frustration in my life.

How does the architecture block major refactors or improvements? Are you running a single codebase for all your tenants, albeit with separate schemas for each?

Edit: on reading the link you included, it seems like a lot of the problems are on the Rails implementation of the architecture with ActiveRecord and Apartment rather than with the architecture itself.