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

121–130 of 264 posts

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

#121

Post by hardwaresofton »

Schemas[0] are the scalable way to do this, not databases, at least in Postgres.

If you're going to go this route you might also want to consider creating a role-per-user and taking advantage of the role-based security features[1].

That said, this is not how people usually handle multi-tenancy, for good reason, the complexity often outweighs the security benefit, there are good articles on it, and here's one by CitusData[2] (pre-acquisition).

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

[1]: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

[2]: https://www.citusdata.com/blog/2016/10/03/designing-your-saa...

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

#122

Post by krainboltgreene »

gomox wrote: post #110
sky_rw wrote: post #108

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.…

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.

Here I'll give you one: If you want to change a property of the database that will give your specific use improved performance, you have no way to transactionally apply that change. Rolling back becomes a problem of operational scale, rolling out as well.

What if you need to release some feature, but that feature requires a database feature enabled? Normally you enable it once, in a transaction hopefully, and then roll out your application. With this style you have to wait for N database servers to connect, enable, validate, then go live before you can even attempt the application being deployed, much less if you get it wrong.

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

#123

Post by krainboltgreene »

cornishpixels wrote: post #118
gomox wrote: post #115

Earlier quoted context omitted.

Indeed, but if you are making a B2B enterprise/SMB SaaS, I think you are most likely to regret the opposite choice [1][2]. A lot of companies run a single instance, multitenant application and have to develop custom sharding functionality down the road when they realize the inevitable: that most joins and caches are only needed on strict subsets of the data that are tenant specific. If you get successful enough in th…

You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily. First and foremost, you can simply run the full multi-tenant system with only a single tenant, which if nothing else enables progressive development (you can slowly remove those now-unnecessary WHERE clauses, etc).

> You can always take a multi-tenant system and convert it into a single-tenant system a lot more easily.

This is not true if your primary keys are int or bigint.

It's also not true if you have any sort of unique indexes that are scoped to a table.

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

#124

Post by krainboltgreene »

hardwaresofton wrote: post #121

Schemas[0] are the scalable way to do this, not databases, at least in Postgres. If you're going to go this route you might also want to consider creating a role-per-user and taking advantage of the role-based security features[1]. That said, this is not how people usually handle multi-tenancy, for good reason, the complexity often outweighs the security benefit, there are good articles on it, and here's one by Citus…

Schemas should be approached with extreme caution. They still suffer from the same issues as multiple databases, but now also you have to juggle search path and when I used schemas our databases backups were insane. This is because two same name tables with same name indexes can't be compressed together, as I understand it.

I would also be very careful about using roles this way as it makes connection pool formulas a lot more magical and less deterministic.

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

#125

Post by krainboltgreene »

thayne wrote: post #38
barrkel wrote: post #27

Yes, for multi-tenancy. Database per tenant works alright if you have enterprise customers - i.e. in the hundreds, not millions - and it does help in security. With the right idioms in the codebase, it pretty much guarantees you don't accidentally hand one tenant data belonging to a different tenant. MySQL connections can be reused with database per tenant. Rack middleware (apartment gem) helps with managing applying…

It also makes sharding/ horizontal scalability a non-issue, at least until you get a big enough customer.

But like, every database is easy to shard/horizontally scale until you get a big enough customer. Why do the extra leg work?

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

#126

Post by abraxas »

I frequently toy with the idea of creating a platform like this using Raspberry Pi or some other SBC where every customer gets not only their own database but their own app server and everything else on a small piece of hardware. Due to heavily localized data and application code you can likely get away with the puny hardware and the cost per account is very sensible even with a pretty high spec SBC.

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

#127

Post by krainboltgreene »

toast0 wrote: post #29
thomascgalvin wrote: post #8

The inability to reuse database connections would be a huge performance hit. In a traditional webapp backend, you have a pool of connections to the database. User01 hits your service, and grabs a connection off the pool. User02 does the same, and so on. These connections get put back in the pool for reuse once a user is done with them. In your design, every time a user hits your service, a new connection, specific to…

> 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…

> 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 was much less, I just don't remember that far).

One of the major ways I helped Mastodon was improving their connection pooling situation. If you haven't encountered connection size issues with your database count yourself lucky.

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

#129

Post by sergiotapia »

krainboltgreene wrote: post #125
thayne wrote: post #38

Earlier quoted context omitted.

It also makes sharding/ horizontal scalability a non-issue, at least until you get a big enough customer.

But like, every database is easy to shard/horizontally scale until you get a big enough customer. Why do the extra leg work?

It's mostly a contractual decision, not because the engineering team wants to implement it for giggles.

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

#130

Post by jcims »

abraxas wrote: post #126

I frequently toy with the idea of creating a platform like this using Raspberry Pi or some other SBC where every customer gets not only their own database but their own app server and everything else on a small piece of hardware. Due to heavily localized data and application code you can likely get away with the puny hardware and the cost per account is very sensible even with a pretty high spec SBC.

Seems like you could start with containers to test the model out then move to hardware if the economics worked out. A couple SaaS applications I use right now seem like they are operating multi-tenant on raspberry pi.
Post reply on HN