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

61–70 of 264 posts

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

#61

Post by geofft »

Slack does (or did, as of a few years ago) something like this - they shard teams onto a fleet of MySQL servers. See https://www.infoq.com/presentations/slack-infrastructure/ starting around the 10-minute mark and https://www.infoq.com/podcasts/slack-keith-adams/ starting around 7m15.

If I'm understanding this right, every team gets its own database, which is active-active replicated across two MySQL servers. There's also a separate mapping from teams to MySQL servers (which is itself a separate pair of MySQL servers), and of those two servers, one is primary for each team, distributed roughly evenly. Each MySQL server instance in the fleet is hosting many Slack workspaces, and they can scale that up/down as needed (i.e., presumably the tiny Slack I created years ago and forgot about is hosted on the same pair of MySQL servers as lots of other Slack instances in the same state and my employer's active Slack instance with thousands of people is on a less-crowded pair of MySQL server).

One user-visible effect is that it's possible to have outages that affect some fraction of workspaces. I also suspect this plays into things like rolling out changes over the course of many months - they don't need to do a database migration for everyone at one.

I don't think they use this for security - while yes, a buggy query can only affect one workspace at once, it doesn't sound to me like they do sharding at the PHP layer, it sounds like they're running a fleet of generic PHP instances that have the ability (and authorization) to talk to any workspace's database, not that they're running separate web/PHP instances per customer. But it definitely sounds like they rely on this for scalability/performance.

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

#62

Post by SkyMarshal »

In Postgres, Schemas are intended to solve this problem - enable user account isolation without creating multiple databases:

https://www.postgresql.org/docs/current/ddl-schemas.html

Given how popular Postgres is, I’m sure there are lots of teams using this architecture.

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

#63

Post by a-wu »

CloudKit actually does this.

https://www.vldb.org/pvldb/vol11/p540-shraer.pdf

> within each container CloudKit uniquely divides the data- space into many private databases that manage user-scoped data

> Each private database belongs to exactly one container and one user

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

#64

Post by gomox »

robot wrote: post #50
gomox wrote: post #37

If an "account" is an "enterprise" customer (SMB or large, anything with multiple user accounts in it), then yes, I know at least a few successful companies, and I would argue in a lot of scenarios, it's actually advantageous over conventional multitenancy. The biggest advantage is flexibility to handle customers requirements (e.g. change management might have restrictions on versioning updates) and reduced impact of…

Do you not have to have a large dev ops team to handle upgrades or changes for each customer? I'd be against it to do more with fewer people, especially for an unproven < 5 year old startup.

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.

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

#66

Post by gomox »

andrewflnr wrote: post #60
koolba wrote: post #15

Earlier quoted context omitted.

You can use schemas. Also it’s still a terrible idea.

Why is it a terrible idea?

It's a terrible idea in the same way that using PHP instead of Rust to build a production large scale application is a terrible idea (i.e. it's actually a great idea but it's not "cool").

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

#68

Post by gomox »

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.

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

#69

Post by gomox »

amanzi wrote: post #48

WordPress Multisite gives each blog a set of tables within a single database, with each set of tables getting the standard WordPress prefix ("wp_") followed by the blog ID and another underscore before the table name. Then with the hyperdb plugin you can create rules that let you shard the tables into different databases based on your requirements. That seems like a good model that gives you the best of both worlds.

My hunch is that they did this because that was a reasonable way to operate in the old shared hosting days (where you got a single application and a single database server). A lot of people would also run multiple "blogs" from one account.

This requires application level code to manage the different sets of tables and the corresponding access permissions. Unless your use case requires this flexibility, I think one database per tenant is a better default architecture.

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

#70

Post by gomox »

brianwawok wrote: post #42
polote wrote: post #40

Earlier quoted context omitted.

What would be the best, different tables per customer or different db?

There’s overhead both ways. Multi-tenant is more efficient. What is the most possible users you could have in 5 years? Can your choice be supported in that technology?

Enterprise systems have little to no cross-customer functionality, and customer isolation is paramount. I think you are assuming that they will have a database per individual user, but the original question pointed to "tenants" (i.e. companies using the system).
Post reply on HN