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

I worked for a company that did this, and our scale was quite large. It took a lot of work to get AWS to give us more and more databases on RDS. We had some unique challenges with scaling databases to appropriately meet the needs of each account. Specifically, it was difficult to automatically right-size a DB instance to the amount of data and performance a given customer would need. On the other hand, we did have the flexibility to manually bump an account's database to a much larger node size if we needed to help someone who was running into performance issues.

I think the biggest problems had to do with migrations and backups. We maintained multiple distinct versions of the application, and each had a unique DB schema, so there was frequent drift in the actual schemas across accounts. This was painful both from a maintenance POV, and for doing things like change data capture or ETLs into the data warehouse for data science/analysis.

Another big problem was dealing with backup/restore situations.

I suspect this decision was made early in the company's history because it was easier than figuring out how to scale an application originally designed to be an on-prem solution to become something that could be sold as a SaaS product.

Anyway, I think choosing a solution that nets your business fewer, larger database nodes will probably avoid a lot of maintenance hurdles. If you can think ahead and design your application to support things like feature flags to allow customers to gradually opt in to new versions without breaking backwards compatibility in your codebase, I think this is probably the better choice, but consider the safety and security requirements in your product, because there may be reasons you still want to isolate each tenant's data in its own logical database.

The idea is prevent a simple SQL mistake from exposing information across tenants.

RLS seems like a simpler solution.

This leads me to believe that everything you mentioned is already subtly broken, it is the new DB/account model that just exposes it.

Is there something between the two solutions or pieces that could be modified that collapse the problem?

What about going with DB account per app account and using views to limit exposure to data. If user level views are applied before the business logic has access, then the death knell scenario should be avoided.

Migrations seem hard regardless, they have to be automated anyway and have a verified rollback/undo mechanism. Code should be multiversion aware if you are doing live upgrades.

I am personally a fan of read-only mode while doing the last phase of an ETL upgrade on the backend. Snapshot databases, run a bunch of Hive jobs and throw it back down on a new set of DBs.

In the cases you outlined where it was dangerous or fragile, I think those systems were already that way, and that fragility is a system of a different problem. Fix that and lots of other stuff disappears.

We did it many years (11) ago for a B2C Product, around 200K users (free, 5k paid). We both had a common Postgres where we stored generic info and the customer database in Derby (product was in java).

At the time we weren't that much experiences so we also implemented migrations (whereas today I would have chosen flyway or something similar).

Customer data was text based so we didn't have that many custom fields but at the sime time we had many migrations during the years.

So, would we still do it? Yes and no. It was amazing because it let us isolate each user. With Filesystem backups we could restore just in time the data from each user without having to reset the whole database and with little to know system knowledge.

At the same time it restricted our choices when improving the product. If we had to do a whole refactor of the project we always thought "How do I migrate all the users together?" We had to think about versioning, upgrade in different times and so on.

From a tech standpoint it wasn't much of a problem, but it burdened the project a little bit. Bigger refactorings are harder and you need much more depth when you think about big new features.

I still think it was a nice solution, but this solution came also because we didn't knew a thing about partitioning in databases and we weren't expert in clustering. All in all it allowed us to create a product sustaining a decent customer base with low knowledge for that time.

With today technology I'd test if we can do it other ways.