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

251–260 of 264 posts

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

#251

Post by gomox »

bearjaws wrote: post #230
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 custom…

Flexible and disciplined is a good alternative to rigid, and for young software business, probably the safer one!

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

#252

Post by randlet »

sitkack wrote: post #218
randlet wrote: post #106

Earlier quoted context omitted.

The way you are thinking would be much cheaper than what I'm doing with the downside that clients data exists on the same server (but maybe they won't care) and the fact that a performance issue for one client can impact other clients. I haven't found an AWS account per customer to be to bad to manage yet and makes keeping track of your AWS spend per client simple. It's also trivial to run your resources in say Sydne…

I love that you went up an encapsulation level compared to what even the OP was asking. The ability to scale and customize on a single customer level is amazing. Do you find yourself capitalizing on this capability fairly often?

80% of my clients run the same "master" version while 20% have minor customization's specific to their organization.

I don't want to end up maintaining 20 different applications, but being able to do minor modifications to the UI or data model on a per customer basis has been a nice selling point.

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

#253

Post by gomox »

bearjaws wrote: post #233
gomox wrote: post #64

Earlier quoted context omitted.

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…

For context, we implemented your exact model at my job prior to switching to "conventional multi-tenant". I agree with your premise that there are a lot of off the shelf tools to help this, but in enterprise I feel like we hit dozens of other issues and our customers didn't even care if they shared the same database as someone else. If the production environment is extremely similar to dev, the problems are numerous,…

Both of the setups I'm familiar with run multiple application servers on the same "iron" (EC2 clusters), with shared services (database servers, caches, routing, load balancing, etc).

Monitoring, backup, ELK, New Relic etc are global, or shared among sets of clusters depending on the specifics.

I agree that 12 containerized services per customer seems like a headache. The applications I have experience with run fewer components, and they are mostly shared among clusters.

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

#254

Post by gomox »

sk5t wrote: post #242
gomox wrote: post #93

Earlier quoted context omitted.

Many versions of the schema: indeed, that's one of the main tradeoffs. I consider this to be a feature, not a bug, in an enterprise application context. The correct version of the schema that corresponds to a given version of the code is unique, which dramatically simplifies debugging and migration. Separate backup scripts: only if you want, you can always backup the whole database server/instance at once (i.e. an RD…

I should have included the context that my notes pertain somewhat more to Postgres than other database systems; "USE" may work for MSSQL (and others) where the database is treated as more of a namespace concept, whereas Postgres requires a new connection per database. If you've got 1,000 databases on a Postgres cluster and are limited to 1,000 connections, there's going to be a tradeoff in connection setup latency vs…

I can see how that can be a problem. I run MySQL but other commenters said that Postgres has "schemas" which appear to be more similar to MySQL's "databases".

In the same way I thought you meant the query result cache, not the query plan cache. In my experience query planning hasn't been a significant bottleneck, but that's got to be workload dependent.

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

#256

Post by barrkel »

latch wrote: post #215
barrkel wrote: post #213

Earlier quoted context omitted.

The idea is prevent a simple SQL mistake from exposing information across tenants.

RLS seems like a simpler solution.

How well does that work with mysql 5.5 in 2012?

Exactly.

(It's not actually simpler when query execution over 100s of millions of rows is a perf bottleneck, and each tenant has several billion rows in the main tables. Then you're grateful you can schlep them around, and keep small tenants fast, etc. Even now, Postgres would still be a dubious choice due to the unpredictability of its query planner, though I use it for all my hobby projects.)

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

#257

Post by shakna »

programmarchy wrote: post #56
shakna wrote: post #18

There aren't a lot of benefits to doing it. If you have frequent migrations, then it probably isn't something you ever want to do. For a site I run, I have one large shared read-only database everyone can access, and then one database per user. The per-user DB isn't the most performant way of doing things, but it made it easier to: + Encrypt an entire user's data at rest using a key I can't reverse engineer. (The use…

Can you elaborate on how you achieved encryption at rest with a key you can’t access? I’m assuming the key is sent in an authorization header, then lives in memory for the duration of the session, but wondering what your tool chain looks like.

Pretty much.

The encoded key is sent, once, in a header, and then stored in a secure session cookie, that has a reasonable timeout on it, and is user-revokable, and is encrypted in memory server-side, unless it is being accessed.

(Setting up session cookies to only decrypt when being accessed sort of required reinventing the wheel, as that's apparently not something anyone goes to the effort of usually, and sends you down some optimisation paths around timing that will have you pulling out your hair).

User-revokable session cookies are simple enough - each user gets their own session cookie key, and they can roll that over from a settings page.

Worth noting: This is a great way to decimate your server performance, because most websites aren't constantly handling decryption.

The prototype was written for Flask [0], then rewritten for Bottle [1] when it was clear I wasn't using 90% of the Flask stack, and monkeypatching most of what I was using. Nowadays it's a strange mix of Hug [3] and Bottle.

But there's nothing there that's unique to Python or even the framework. It's easily doable in just about any language. I made three prototypes when I was coming up with this batty idea, the Flask prototype, one for vibe.d (D), and one for Go. I settled on Python for no particular reason. They all had similar performance, because encryption became the bottleneck.

[0] https://flask.palletsprojects.com/en/1.1.x/

[1] http://bottlepy.org/

[2] https://hugapi.github.io/hug/

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

#258

Post by brigandish »

polote wrote: post #40
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…

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

There's always schema to consider too, you can have thousands of them too. An often overlook tool.

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

#259

Post by programmarchy »

shakna wrote: post #257
programmarchy wrote: post #56

Earlier quoted context omitted.

Can you elaborate on how you achieved encryption at rest with a key you can’t access? I’m assuming the key is sent in an authorization header, then lives in memory for the duration of the session, but wondering what your tool chain looks like.

Pretty much. The encoded key is sent, once, in a header, and then stored in a secure session cookie, that has a reasonable timeout on it, and is user-revokable, and is encrypted in memory server-side, unless it is being accessed. (Setting up session cookies to only decrypt when being accessed sort of required reinventing the wheel, as that's apparently not something anyone goes to the effort of usually, and sends you…

Thanks for sharing, that’s an interesting approach. Does seem very hard to scale. Do they just set their key from a settings page and then off to the races? i.e. no login credentials?

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

#260

Post by shakna »

programmarchy wrote: post #259
shakna wrote: post #257

Earlier quoted context omitted.

Pretty much. The encoded key is sent, once, in a header, and then stored in a secure session cookie, that has a reasonable timeout on it, and is user-revokable, and is encrypted in memory server-side, unless it is being accessed. (Setting up session cookies to only decrypt when being accessed sort of required reinventing the wheel, as that's apparently not something anyone goes to the effort of usually, and sends you…

Thanks for sharing, that’s an interesting approach. Does seem very hard to scale. Do they just set their key from a settings page and then off to the races? i.e. no login credentials?

Certificate file. Generated on registration and handed over as a download and shredded server-side. Not as trust-fulfilling as a user supplying one, but less of a learning curve. (Still need validation on it either way, which can be painful).

Which, of course, means "forgot my password" doesn't work.

Post reply on HN