Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

news.ycombinator.com

Post reply on HN
Print view View original

81–90 of 264 posts

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

#81

Post by toast0 »

llimllib wrote: post #39
toast0 wrote: post #29

Earlier quoted context omitted.

> In your design, every time a user hits your service, a new connection, specific to that user, will have to be made. This will incur network traffic and the overhead of logging in to the DBMS. If connecting to your DB is significantly increasing your page times, you've got seriously fast pages. Even back when I was working with a MySQL database regularly in 2010, connect + login was 5 ms at maximum (and I think it w…

> connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far). I try to get all my endpoints under 10ms! 5ms per call would be huge. (Obviously I can't succeed all the time, but 5ms is big numbers imo)

Ok, you and I can be friends. A lot of people are using 'lightweight' frameworks where hello world is 30 ms, and then they call slow services and run slow queries, etc.

If your target is 10 ms, then you probably should worry about db connection time.

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

#82

Post by sk5t »

gomox wrote: post #68
xmodem wrote: post #58

I have a bit of experience with this. A SaaS company I used to work with did this while I worked there, primarily due to our legacy architecture (not originally being a SaaS company) We already had experience writing DB migrations that were reliable, and we had a pretty solid test suite of weird edge cases that caught most failures before we deployed them. Still, some problems would inevitably fall through the cracks…

You don't need an RDS per customer. In MySQL jargon, an RDS instance is like a MySQL "server". You just need a "database" per customer. On the other hand, if a tenant pays you less than $12 a month or any amount where the hosting costs are significant in the grand scheme, then I agree that this is likely not a good architecture.

If you make one database per tenant, then some issues to consider might nevertheless include maintaining many versions of the schema, separate backup scripts (other than backing up the entire cluster/instance), busting query cache, busting connection pooling, baking into the app layer (uncommon) logic to get a connection to the proper database, some complexity to collect usage-based info for billing or anomaly detection; others?

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

#83

Post by nitwit005 »

snuxoll wrote: post #19
wheelerwj wrote: post #16

Seems pretty odd. The closest example I can think of would be maybe salesforce? Which basically, as far as I can tell, launches a whole new instance of the application (hosted by heroku?) for each client. I'm not a 100% sure about this, but i think this is how it works.

Not at all how Salesforce works, they take a lot of pride in their multi-tenant setup (for better or worse). Every org on a given instance shares the same application servers and Oracle cluster. If I were to make a Salesforce competitor that’s one thing I would do differently, with tools like Kubernetes it’s a lot easier to just give every customer their own instances. Yes, it can take up more resources - but I canno…

Salesforce has a lot of trial or developer orgs, which would be quite expensive if you tried to host them all as some sort of VM in a cloud hosting companies.

I assume most of them end up as a few rows in some set of DBs, which is far cheaper.

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

#84

Post by zerkten »

This is a common approach outside of the SaaS space. I'd worry less about Rails and tools, and more about the outcomes you need. If you have a smaller number of high value customers (big enterprises or regulated industries), or offer customers custom add-ons then it can be advantageous to give each customer their own database. Most of the HN audience will definitely not need this.

In some industries you'll also have to fight with lawyers about being allowed to use a database shared between customers because their standard terms will start with this separation. This approach is helpful when you have to keep data inside the EU for customers based there. If you want to get creative, you can also use the approach to game SLAs by using it as the basis to split customers into "pods" and even if some of these are down you may not have a 100% outage and have to pay customers back.

This design imposes challenges with speed of development and maintenance. If you don't know your requirements (think: almost any SaaS startup in the consumer or enterprise space) which is trying to find a niche, then following this approach is likely to add overhead which is inadvisable. The companies that can use this approach are going after an area they already know, and are prepared to go much more slowly than what most startup developers are used to.

Using row-level security or schemas are recommended for most SaaS/startup scenarios since you don't have N databases to update and keep in sync with every change. If you want to do any kind of split then you might consider a US/EU split, if your customers need to keep data in the EU, but it's best to consider this at the app-level since caches and other data stores start to become as important as your database when you have customers that need this.

Consideration should be given to URL design. When you put everything under yourapp.com/customername it can become hard to split it later. Using URLs like yourapp.com/invoice/kfsdj28jj42 where "kfsdj28jj42" has an index for the database (or set of web servers, databases, and caches) encoded becomes easier to route. Using customer.yourapp.com is a more natural design since it uses DNS, but the former feels more popular, possibly because it can be handled more easily in frameworks and doesn't need DNS setup in developer environments.

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

#85

Post by boulos »

Disclosure: I work on Google Cloud.

tl;dr: Wait until you need it, but there are good reasons for it!

Since I didn’t see anyone mention it, the term I’ve seen a lot of people use for this pattern is “multi single tenant”.

Part of the reason we have Tenant Projects [1] is precisely so you can do a 1:1 mapping of “Customer A can have different controls, settings, and blast radii from Customer B”.

Many of our first-party services do this, but then again, many of the largest-scale ones do “true multitenancy” instead. There’s no great way to offer a scale-to-zero database like Datastore or BigQuery without “internalizing” the multitenancy. But then it’s on you as the implementor to ensure isolation (both security and performance).

In your scenario, if you could make each database small enough (or use a database that handles the multitenancy for you) you gain a lot in terms of “capability” for enterprises. Customer X wants a feature that you know how to do but aren’t sure it should be for everyone? Their instance is separate. Customer Y has a drastically different read pattern and should have a high-IO instance? Great.

The easiest advice though is: wait until after you need it. A single replicated pgsql or MySQL can go insanely far. Let your business needs push you to the more complicated architecture, later. Prefer whatever gets you to a success disaster. Just don’t forget backups, PITR, and actually testing that :). (Another thing that’s harder with a shared database!).

Edit: Adding a newline and a tl;dr.

[1] https://cloud.google.com/service-infrastructure/docs/manage-...

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

#86

Post by mildavw »

The apartment gem enables multi-tenant Rails apps using the Postgres schemas approach described by others here.

It’s slightly clunky in that the public, shared schema tables, say, the one that holds the list of tenants, exists in every schema — they’re just empty.

I rolled my own based on apartment that has one shared public schema, and a schema for each tenant. Works well.

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

#87

Post by snuxoll »

nitwit005 wrote: post #83
snuxoll wrote: post #19

Earlier quoted context omitted.

Not at all how Salesforce works, they take a lot of pride in their multi-tenant setup (for better or worse). Every org on a given instance shares the same application servers and Oracle cluster. If I were to make a Salesforce competitor that’s one thing I would do differently, with tools like Kubernetes it’s a lot easier to just give every customer their own instances. Yes, it can take up more resources - but I canno…

Salesforce has a lot of trial or developer orgs, which would be quite expensive if you tried to host them all as some sort of VM in a cloud hosting companies. I assume most of them end up as a few rows in some set of DBs, which is far cheaper.

Salesforce charges and arm and a leg for extra sandbox instances anyway, and at that scale you aren’t paying Amazon for compute either.

A full-copy sandbox past the included one for Unlimited Edition orgs is something like 30% your annual spend.

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

#88

Post by oblib »

IBM's "Cloudant" is not a web app per se, it's db as a service, but the service is a db per user.

You might want to check out the CouchDB docs, and maybe take a look at their mailing list for both users and developers. Their dev team can provide the answers you're looking for as far a CouchDB goes.

It's my understanding that scaling up or down is a key feature of CouchDB. It's designed to make it easy to create clusters of them working together. But I really do not know much about that myself.

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

#89

Post by conductr »

I’ve done this. But the service was suited for it in a couple ways;

1. Each tenant typically only has 20. And load is irregular, maybe only ever dealing with 2-3 connections simultaneously. Maybe 2. Tenants creates and archives a large number of rows on some tables. Mutable but in practice generally doesn’t change much. But >100M row count not unusual after couple years on service. Not big data by any means, limited fields with smallish data types, but...

I didn’t want to deal with sharding a single database. Also given row count would be billions or trillions at a point the indexing and performance tuning was beyond what I wanted to manage. Also, this was at a time before most cloud services/CDNs and I could easily deploy close to my clients office if needed. It worked well and I didn’t really have to hire a DBM or try to become one.

Should be noted, this was a >$1000/month service so I had some decent infrastructure budget to work with.

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

#90

Post by carapace »

It's not really the same thing, but the question reminds me that we almost had SQLite in the browser[1], but Mozilla spiked it in favor of IndexedDB[2] (yet "Firefox saves Web storage objects in a SQLite file"[3] so I dunno what to conclude from all that. SQLite is good enough for FF devs but not users?)

Anyway, if you have a web app you already have a DB-per-user, FWIW.

[1] https://en.wikipedia.org/wiki/Web_SQL_Database

[2] https://en.wikipedia.org/wiki/Indexed_Database_API

[3] https://en.wikipedia.org/wiki/Web_storage

Post reply on HN