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

221–230 of 264 posts

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

#221

Post by sitkack »

ryanbrunner wrote: post #204
sp332 wrote: post #73

Earlier quoted context omitted.

> USE `tenant` But if the idea is to isolate accounts form each other, the different schemas would be available to different DB users. You would have to re-authenticate to get access to the other DB.

Using schemas gives you imperfect but still improved isolation. It's still possible for a database connection to cross into another tenant, but if your schema search path only includes the tenant in question, it significantly reduces the chance that cross-customer data is accidentally shared.

I think numeric ids should be allocated out of the same key space, other identifiers should be hierarchical and scoped to the tenant in the database.

The same query run across all databases should either return 1 query (for the valid tenant) and empty set for all other databases, OR it should return the same result set regardless.

I just realized what I am proposing, a hidden out of band column that is effectively the "database id" for that row.

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

#222

Post by lurker213 »

I like having one DB and defining RLS rules (in postgresql) for the majority of my tables. I also have all 'business entity' tables relate back to one table called records, which has a tenant_id on it and timestamps on it. This way I can keep FK constraints without doing the silly polymorphic rails model stuff.

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

#223

Post by ryanbrunner »

sitkack wrote: post #217
ryanbrunner wrote: post #206

Earlier quoted context omitted.

Echoing this as well, I worked for Influitive and was one of the original authours of apartment (sorry!) There are a lot of headaches involved with the "tenant per schema" approach. Certainly it was nice to never have to worry about the "customer is seeing data from another customer" bug (a death knell if you're in enterprisish B2B software), but it added so many problems: - Migrations become a very expensive and tim…

This leads me to believe that everything you mentioned is already subtly broken, it is the new DB/account model that just exposes it. Is there something between the two solutions or pieces that could be modified that collapse the problem? What about going with DB account per app account and using views to limit exposure to data. If user level views are applied before the business logic has access, then the death knel…

I agree that migrations are painful at the best of times, but dealing with the complexity of migrating a single database is far simpler than dealing with migrating hundreds of schemas:

- Migrations will first of all just take longer - you're multiplying the number of schema changes by the number of tenants you have.

- While in an ideal world migrations should be purely run within a transaction, occasionally performance considerations mandate that you run without DDL transactions - when some tenants fail and your migrations are in a partially completed state for some of your tenants and not others, it can be scary and painful.

- In my experience, almost no one approaches data migrations in a way that is purely backwards compatible 100% of the time without exception. You certainly can, but there's a significant tax associated with this, and if you're in a traditional scoped environment, you can often get away with the potential for errors in the minuscule time that a schema change is operating (of course, some schema changes aren't run in minuscule times, but those are the ones you're more likely to plan for)

Going read only during migrations is an interesting approach, but there's real business costs associated with that (particularly if your migration speed is multiplied by running it across tenants).

I don't want to say that you should never isolate data on a schema level, but I do think it's something that shouldn't be a standard tool to reach for. For the vast majority of companies, the costs outweigh the benefits in my mind.

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

#224

Post by ryanbrunner »

sitkack wrote: post #221
ryanbrunner wrote: post #204

Earlier quoted context omitted.

Using schemas gives you imperfect but still improved isolation. It's still possible for a database connection to cross into another tenant, but if your schema search path only includes the tenant in question, it significantly reduces the chance that cross-customer data is accidentally shared.

I think numeric ids should be allocated out of the same key space, other identifiers should be hierarchical and scoped to the tenant in the database. The same query run across all databases should either return 1 query (for the valid tenant) and empty set for all other databases, OR it should return the same result set regardless. I just realized what I am proposing, a hidden out of band column that is effectively th…

If you built a tenanting library that used partioning rather than schemas, you'd probably end up with something that looked pretty close to what you're describing.

With schemas, it's definitely possible to use the same generator for ids across schemas (at least, I'm 90% sure it is in Postgres), but you'll probably end up fighting against ORM libraries to get it to work properly (Rails for instance makes a LOT of assumptions about how the id column works), and you aren't technically guaranteed uniqueness since you'll still have distinct PK columns.

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

#226

Post by Aweorih »

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…

Or you have a connection pool for every user, which is basically the same except that you must do some mapping by yourself and you have more connection pools and open connections.

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

#228

Post by itsthecourier »

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

Some ideas about this: I have seen more than 200 dbs in rds with around 100 tables each working and migrating just fine. The author explicitly says he didn't dig up what the cause of the slow down was.

About the rails problem he says activerecord always check all the tables in the schema and create a huge 500MB object with its properties. Apartment wasn't able to come to some solution, but that's a rails specific problem.

About the joins, he should have use a reference to current_schema just as he needs to do it to tenant_id in joins

About the serialization of IDs, it all depends on the business and whether they need unique series of referable short IDs and whether uuid is acceptable for them

About migrations, of course, you need to use migrations with dbs on production. You cannot rely in a dev file to match the current state of production db. Been working with a huge amount of production dbs in my life without issues, just by using it

Thinks I don't see mentioned and are important, the need to have a connection pool that allows schema switches, i.e. pg_bouncer

About db_migrations, actually this method works. there are some rules floating around about how to do non-blocking of migrations, but let me tell you. At some point you will need them and it's way easier to do it incrementally one schema at the time, than just do it on the 500GB and failing for some records at some of the last GB

What about two versions of the db running in production? That should be possible. You should be running v1 and v2 and automatically switching down v1 when no dbs are using it

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

#229

Post by ryanbrunner »

gomox wrote: post #150
cornishpixels wrote: post #147

Earlier quoted context omitted.

How is it not? Uniqueness would cease to matter between different tenants. They're unique by virtue of using a different database.

See my sibling comment with the Hubspot example. Even though the system might work internally, other things will break if you start having duplicate account IDs because other systems don't think of the account ID as a cluster-local identifier, but as a global one.

Just thinking through this, but if it's an entirely separate environment, just host it on a separate subdomain and the account id becomes irrelevant. If you have a functioning staging environment, you already have the ability to run an isolated environment without issue, this is just a different application of the same idea.

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

#230

Post by bearjaws »

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…

Your biggest advantage is actually a disadvantage, you have literally enabled your individual customers to boss you around and fork your codebase into a mess of different functionality per customer. Are you a contract shop where you get SOWs to expand your software? This seems like a terrible idea for both product team and the development team, who now need to know what bastardized version of the software each customer is running. What happens when a customer goes "I don't want that update"? Unless you can guarantee that each customer ends up on the same software quickly, this will get messy.
Post reply on HN