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

181–190 of 264 posts

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

#181

Post by delta1 »

boulos wrote: post #85

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

> PITR

"Point-in-time recovery" for those of us that don't know the acronym

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

#182

Post by lemiffe »

We use a database per account, it is necessary for some ISO (and other) certifications to have single-tenant DBs.

Of course this requires a bunch of extra tooling, like upgrade scripts that don't ALTER tables directly but rather lock-copy-delete-rename, etc.

There are many tools out there which help out with this, and whatever we couldn't find we built ourselves. Tools like JOOQ can update code entities based on the database, so a database-first approach is what we used, but you can go either way.

The benefit of this approach is ultimately security and less multi-tenant catastrophes leaking data from customers, etc.

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

#183

Post by chrisacky »

lemiffe wrote: post #182

We use a database per account, it is necessary for some ISO (and other) certifications to have single-tenant DBs. Of course this requires a bunch of extra tooling, like upgrade scripts that don't ALTER tables directly but rather lock-copy-delete-rename, etc. There are many tools out there which help out with this, and whatever we couldn't find we built ourselves. Tools like JOOQ can update code entities based on the…

Can you explain why the ALTER approach isn't feasible? If you are locking anyway, is it not the same thing?

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

#184

Post by mozey »

When using a single db I'd highly recommend adding `account_id` to every single table that contains data for multiple accounts. It's much easier to check every query contains `account_id`, as opposed to checking multiple foreign keys etc. Depending on the db you can then also easily export all data for a specific account using filters on the dump tool

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

#185

Post by Keats »

abhishektwr wrote: post #25

For Postgress you can use and scale one schema per customer (B2B). Even then, depending on the instance size you will be able to accommodate 2000-5000 customers at max on a Postgres database instance. We have scaled one schema per customer model quite well so far ( https://axioms.io/product/multi-tenant/ ). That said, there are some interesting challenges with this model like schema migration and DB backups etc. some…

there's a typo in the title of that page: Mulit-tenant

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

#186

Post by abhishektwr »

Keats wrote: post #185
abhishektwr wrote: post #25

For Postgress you can use and scale one schema per customer (B2B). Even then, depending on the instance size you will be able to accommodate 2000-5000 customers at max on a Postgres database instance. We have scaled one schema per customer model quite well so far ( https://axioms.io/product/multi-tenant/ ). That said, there are some interesting challenges with this model like schema migration and DB backups etc. some…

there's a typo in the title of that page: Mulit-tenant

Thank you. Saved me.

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

#187

Post by Fiahil »

kiloreven wrote: post #176

We're a small company (~50 customers) delivering SaaS using Django/Postgres/uWSGI for a niche B2B market where privacy and data confidentiality is paramount. Currently we deploy one DB + unique uWSGI instances for each customer. This has some drawbacks which has made us look a bit into multi-tenancy as well. Everything is served on dedicated hardware, using common codebase, and each customer is served on a unique sub…

We're deploying unique kubernetes cluster per client with their own application, database, task runner and, yes, DNS. Unlike your situation, most of it is completely automated ( :) ) on Azure and AWS using terraform, good old bash scripts and a custom go CLI we maintain.

Each client is billed for their own resource usage and we can have version disparities between clusters.

On the downsides, maintenance, upgrades and deployments take more time, but we are thinking about potential solutions for managing a fleet of k8s clusters.

This approach makes a lot of sense for B2B customers, and I would add that it's better to separate everything down to the infrastructure level, rather than stopping at the database schema. I would probably do it again in a similar situation !

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

#188

Post by lemiffe »

chrisacky wrote: post #183
lemiffe wrote: post #182

We use a database per account, it is necessary for some ISO (and other) certifications to have single-tenant DBs. Of course this requires a bunch of extra tooling, like upgrade scripts that don't ALTER tables directly but rather lock-copy-delete-rename, etc. There are many tools out there which help out with this, and whatever we couldn't find we built ourselves. Tools like JOOQ can update code entities based on the…

Can you explain why the ALTER approach isn't feasible? If you are locking anyway, is it not the same thing?

My mistake, I didn't check before posting.

The use case was: Keep the table online and don't bring down the Galera cluster (which happened when running an ALTER on a table with millions of rows).

We went for pt-online-schema-change (from Percona) which copies, alters the new table, keeps them in sync, and then replaces it. All automated which is pretty sweet.

One of the answers on here has more info:

https://stackoverflow.com/questions/463677/alter-table-witho...

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

#189

Post by patio11 »

I am aware of at least one company which does this from my consulting days, and would caution you that what you get in perceived security benefits from making sure that tenants can't interact with each others' data you'll give back many times over with engineering complexity, operational issues, and substantial pain to resolve ~trivial questions.

I also tend to think that the security benefit is more theatre than reality. If an adversary compromises an employee laptop or gets RCE on the web tier (etc, etc), they'll get all the databases regardless of whose account (if any) they started with.

(The way I generally deal with this in a cross-tenant application is to ban, in Rails parlance, Model.find(...) unless the model is whitelisted (non-customer-specific). All access to customer-specific data is through @current_account.models.find(...) or Model.dangerously_find_across_accounts(...) for e.g. internal admin dashboards. One can audit new uses of dangerously_ methods, restrict them to particular parts of the codebase via testing or metaprogramming magic, etc.

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

#190

Post by tilolebo »

I have no experience in that field, but the isolation brought by a 1 customer 1 DB relationship sounds VERY appealing to me. I'm talking about schemas, no dedicated database servers.

Creating dedicated database app users with the proper permissions pretty much guarantees that you'll never serve data to the wrong customer.

It also probably makes bug and performance troubleshooting much easier.

The biggest downside is probably the maintenance overhead, but I suppose automation can mitigate it quite well.

It maybe makes internal reporting a bit harder, as you can't get the whole data from a single SQL query. You'd have to handle this in the application layer.

Post reply on HN