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

Also have thousands of MSSQL databases, but with significant investment in tooling it really is transparent from a feature development standpoint, and our feature toggles are dirt simple.

Another comment suggested doing queries across so many databases is challenging, and it's just not, we have both adhoc query capabilities across the fleet and a traditional data warehouse...

Now, trying to make additional infrastructure changes is challenging, but the architecture is robust enough to solve all the immediate business needs.

I would be interested to know what is driving you to consider this approach?

For example, I imagine one database per account would make it easier to provide your customers with a flexible reporting solution.

Most of the reporting solutions available do not have good support for row level security.

We're a small company (~50 customers) delivering SaaS using Django/Postgres/uWSGI for a niche B2B market where privacy and data confidentiality is paramount.

Currently we deploy one DB + unique uWSGI instances for each customer. This has some drawbacks which has made us look a bit into multi-tenancy as well. Everything is served on dedicated hardware, using common codebase, and each customer is served on a unique sub-domain.

The two primary drawbacks of running unique instances for each customer are ease of deployment and utilization of resources.

When a new customer is deployed we need to set up the database, run migrations, set up DNS, deploy the application, deploy the task runner, set up DNS and configure the HTTP vhost. Most of this is painfully manual right now, but we're looking into automating at least parts of the deployment.

In the future, we aim to offer an online solution for signup and onboarding, where (potential) customers can trigger the provisioning of a new instance, even for a limited demo. If we were doing multi-tenancy that would just require a new row in the database + some seed data, which would make the deployment process exceptionally simpler.

The other issue is the utilization of resources. Running a few instances of the application with a big worker pool would be much easier to scale than running 50+ instances with their own isolated worker pool.

We're considering maybe going for a hybrid multi-tenant architecture, where each customer has their own isolated DB, but with a DB router in the application. That would give us a compromise between security (isolated databases - SQL queries don't cross into another customer's data) and utilization (shared workers across customers). But this would add another level of complexity and new challenges for deployment.

Do anyone have a similar case as this?

I considered it a few years ago for a project. My options were one db per account, one schema per account or good old fashioned multitenant. The first two options dropped after realising what a maintenance nightmare that can be. In my scenario I would have thousands and thousands of accounts. So a migration becomes a crazy notion you'll be trying to avoid. The same applies for multi schema - there is a limit of how many schemas you can run reasonably before performance is affected.

What I ended up doing is going for a multi tenant architecture, but I built it in such a way where the data is transient from one db to another. All account data lives in a separate 'master db', and the data in another. All ids are UUIDs. I build database routing into the application so each "account" can have it's data on a different database, and if needed I can move one account's data from a loaded db to a more available one.

While not a web-app, we too have ~200 B2B customers running our application and each one have their own DB. Some self-host, most others are hosted by us.

We have written our own tool to upgrade the DB schema, it's nothing fancy, just takes an XML description, compares with the current DB and makes changes. However it ensures the upgrade process is automated.

New versions of our application come with a new DB schema, and we also have an auto-updater for our software. We have a small launcher application which allows the users to select a few older versions of our software, so upgrades are almost always safe in the sense that if the user encounters a bug with the new version, they can try in the previous one.

If the DB schema upgrade tool fails for some reason, the application upgrade will be held back and we'll get a notification.

Combined this allows us to be quite aggressive with pushing out new versions, and to do so without manual intervention.

A limitation with this setup is that DB changes have to be backwards compatible. So we do have a few cases of "if this table exists do this, else do that" type logic and similar, but mostly it's not a big deal.

For example I recently had to refactor some old table where they had used five columns for some additional references (ref_1, ref_2 etc), into a new table of additional references. To handle this I just made a new view which would return either the refs from the new table, or the data from the columns in case the new table didn't have any data for that record.

I then changed the application to use a grid instead of five separate edit controls, and to use this new view. If the user had to use an older version, that version would just write to the five fields and not populate a row in the new table, and the view would then return this data if the user later viewed it in the new version of our application.

So the "overhead" of backwards compatibility in this case was just ensuring this would be sufficient and writing that view, so just a few minutes.