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

191–200 of 264 posts

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

#191

Post by rbanffy »

sah2ed wrote: post #151
lonelappde wrote: post #142

Earlier quoted context omitted.

You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering.

> You roll the feature out incrementally to the users who are ready. You build backwards compatible features. Basic software engineering. The parent mentioned having to support ~150 B2B customers, so the effort is amplified x100 — more than 100 individual customers databases have to be coddled as you’ve described, albeit they are stuck with poor tooling to manage changes across their unusual architecture.

Doing the same thing to 100 databases is usually easy to automate.

I managed a group that had a dozen customers on a single multi-tenant database and some migrations took more than a day - we needed to set maintenance windows on weekends (and once we had to roll back after an error).

Having a dozen databases we could roll out updates to on a client per client basis would have saved us from some enormous headaches.

OTOH, no two companies are alike. You do you.

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

#192

Post by thrownaway954 »

i did this a loooong time ago with coldfusion and the saas i wrote. in the beginning each customer had their own database and instance of the application. at the time i had 25+ customers and doing update to the application or the database took the entire weekend. over the course of 2 months i wrote everything into a multi tenet app with a single database and never looked back.

i think that my thinking at the time that it would be easier from a security perspective since everyone had their own data and also speed wise since multiple smaller database could fit into memory better.

security wise, especially with an orm, it's not a huge concern as long as you use the top level model as an entry point, it will isolate everyone 95% of the time.

as for database sizes... we live in a world now where you can pretty much scale in an instance by throwing more virtual resources at anything, so i wouldn't worry.

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

#193

Post by DecoPerson »

I worked for one of the biggest boarding school software companies. The only option was full-service, but clients could chose between hosted by us or hosted by them. We didn’t just do 1 database per school, we did entirely separate hardware/VMs per school. Some regions have very strict data regulations and the school’s compliance advisors tended to be overly cautious; they interpreted the regulations and translated them to even stricter requirements. These requirements were often impossible to satisfy. (How can the emergency roll call app both work offline AND comply with “no student PII saved to non-approved storage devices”? Does swap memory count as saving to a storage device?? Is RAM a “storage device”??? Can 7 red lines be parallel!?!?)

Shared DB instances would have been completely off the table. Thankfully, most boarding schools have massive IT budgets, so cost minimization was not as important as adding additional features that justified more spend. Also the market was quite green when I was there. Strangely, the software seemed to market itself; the number of out-of-the-blue demo requests was very high, so first impressions and conversion to paying clients was the primary focus.

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

#195

Post by michaelt »

DecoPerson wrote: post #193

I worked for one of the biggest boarding school software companies. The only option was full-service, but clients could chose between hosted by us or hosted by them. We didn’t just do 1 database per school, we did entirely separate hardware/VMs per school. Some regions have very strict data regulations and the school’s compliance advisors tended to be overly cautious; they interpreted the regulations and translated t…

> How can the emergency roll call app both work offline AND comply with “no student PII saved to non-approved storage devices”?

By having its offline data store approved as a storage device?

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

#196

Post by eigilsagafos »

Early stage B2B startup ShiftX here. We are successfully doing this with FaunaDB. In Fauna databases are as lightweight as tables and are nested in a hierarchy. This enables us to do management in the “root” database and keep all customer data separated in child databases. So when a user signs in to our app he gets a session that is tied to the specific tenant database. This model would also allow us to do completely separate database deployments for customers with special requirements.

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

#197

Post by qatanah »

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

Can confirm the experience, I had to work on a company before with similar tech using Django.

https://github.com/bernardopires/django-tenant-schemas

All I can say, that it was a nightmare to do deployment and migration and some backward incompatibility features restrained us in some ways.

The company valued data isolation as a priority rather than ease of developing it. Hard to work with but great for data isolation.

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

#198

Post by zupa-hu »

I did something like this early on for a website builder using MySQL but it very quickly hit bottlenecks.

I also wanted one-filesystem-per-customer support and full isolation as in Docker, so I ended up writing a new OS to support all of this natively. It runs in production on boomla.com, a hosted website builder that is also a programmable platform.

WARNING: it gets even weirder, databases can be nested, a database entry is also a file and a directory at the same time, or a nested database if you want. There is no way understood this sentence. :) For example, you can store files in files. As in, style.css/background.png, etc.. Every change also creates a new filesystem snapshot, which may also be nested. All right I stop here.

This is not as much an advice for OP (to write a new OS) but more like a plug for the intellectually curious to explore a parallel universe. :)

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

#199

Post by Thristle »

What we do at my current job is server per multiple accounts each server holds 500-1000 "normal sized" customers and the huge or intensive customers get their own server with another 10-50 customers Currently moving from EC2 + mysql 5.7 to RDS, mainly for ease of managing.

However, we dont use a tenent id in all tables to differentiate customers we use (confusingly named) DB named prefix + tenent id for programatically making the connection.

Have a single server + db for shared data of tenents like product wide statistics, user/tenent data and mappings and such things. In the tenent table just have column for the name of the DB server for that tenent and that's pretty much it. Migrations are handled by an internal tool that executes the migrations on each tenent DB and 99% of the time everything works just fine if you are careful on what kind of migration you do and how you write your code

Some pitfalls concern column type changes + the read replicas going out of sync but that was a single incident that only hurt the replica.

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

#200

Post by Thristle »

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…

>connect + login was 5 ms at maximum (and I think it was much less, I just don't remember that far)

is that with creating a new connection in the pool? we work with microservices so we have a "db gateway" so any request to the DB goes through that and it routes to the correct db server for that tenent. our latency for an already "hot" connection is about 40-50 on average but i belive the lowest number i got (for query by primary key in a kinda small table) was no less than 20-30 and opening a new connection added a couple of 10's atleast to that number

Post reply on HN