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

Seems impractical and slow at scale to manage even a few hundred separate databases. You lose all the advantages of the relational model — asking simple questions like “Which customers ordered more than $100 last month” require more application code. You might as well store the customer info in separate files on disk, each with a different possible format and version.

This will be very inefficient due to the way DBMS commonly lay out data in pages. And if you want to do any kind of aggregate queries (e.g. analytics) you're probably in for some royal pain.

If you want to do this for security, why not layer the DB behind some system that requires and verifies the users access tokens for each request?

The only situation where such a setup might make sense is when you actually need per-user migrations to cater to specific customer's needs, but then you'll make it very hard to work with all customer's data through a generic interface.

This talk may be helpful[1]. It's given by Jeremy Evans, the maintainer of Sequel, and it's about how he's made Roda (a Rack-based web framework) more secure than your average web framework does by using database security and some of the features of databases that all too often are overlooked by app developers. You could possibly use Roda for the authentication phase of a Rails app (among other things) but the insights will be helpful regardless.

[1] https://www.youtube.com/watch?v=z3HZZHXXo3I

I’ve seen this done for performance. At one point, it was more common than you might think. These days, most data stores have a way to indicate how to store the data on disk, or one is using SSDs so random access/seeks are less expensive than they were with spinning disks.

As one example, New Relic had a table per (hour, customer) pair for a long time. From http://highscalability.com/blog/2011/7/18/new-relic-architec... (2011):

> Within each server we have individual tables per customer to keep the customer data close together on disk and to keep the total number of rows per table down.

In a situation like that, all queries and table operations are customer- and time-specific anyway. At the time, dropping an entire table took less I/O than deleting specific rows in a multi-customer table (for MyISAM tables, this may still be true: https://mariadb.com/kb/en/big-deletes/). Also, there was no risk from locking the table.

https://www.slideshare.net/newrelic/how-to-build-a-saas-app-... has a bit more. I think Lew Cirne gave that presentation in 2011 but I can’t find a video of it.

In the example you gave, if the goal is to support customer-defined fields, I don't think most people would map the customer-defined fields directly to SQL columns. Consider something like Postgres hstore (with indices as needed) or the many similar implementations in other data stores.

What advantages do you envision for the db-per-account approach?

Depending on that answer, you may be interested in using row-level security: https://www.postgresql.org/docs/current/ddl-rowsecurity.html

My multi-tenant web app does this but I don't know if you'd call 100 unique users a day "at scale".

I believe it will be helpful if it's necessary to separate customers into "pods" as we grow.

The main advantage I feel we get however is that it was quite easy to write a wrapper around mysqldump to retrieve data for development purposes.

I worked at a company that stored all customer data in a single database. The performance was comparable but the agility was poor. Firstly, you had to download all customer data to get a copy to debug a problem. This was a security concern I had, and eventually we had to build a serialisation format to retrieve the "slice" of data we needed. This tool needed frequent updating as new tables were added.

You might argue that we should just try to imagine the bug and recreate it but we have some pretty complicated data structures which can make investigations very hard.

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 that user, will have to be made. This will incur network traffic and the overhead of logging in to the DBMS.

If you're thinking about using something like SQLite, you will hit a hard wall when the OS isn't able to open any more file descriptors, as well.

Like you said, DB administration will be a huge pain in the ass. Rather than having Flyway or Liquidbase or whatever run a migration on one database, you'll have to run it on thousands of databases. There will be significant downtime when your databases are not in a consistent state with one another. There will also be bloat from the migration tool's record keeping, which will be duplicated for every user, rather than every database.

A lot of the tools a database gives you for free will also need to be implemented in application logic, instead. For example, you might want to run a query that says "show me every user using more than 1GB of storage," but under your schema, you'll have to log into every user's database individually, determine storage used, and add it to an in-memory list.

If you ever want to allow users to collaborate, you will end up replicating the owner_id field type metadata anyway, and the entire benefit of this schema will evaporate.

Most frameworks are not set up to handle this style of database access, either. I don't use Rails, but Spring Boot would fight you every step of the way if you tried to do this.

    $ time mysql -u mythtv -h 192.168.0.12 mythconverg \
     -e 'select * from credits where person = 147628 limit 1' > /dev/null

    real    0m0.023s

One company in SF using MeteorJS deploys a whole container with DB and everything per customer.

I think their primary reasoning is that Meteor doesn't scale super easy, so it was easier to just "shard" the whole stack per customer.

Personally, it's a lot of work. It depends on what you're doing to make the tradeoffs worthwhile.

I see this as an optimization. Build everything so you can deploy once for all your customers. If you need to shard by customer later, it's just an infrastructure problem.

Seems like a ton of extra work with no real upside. For one, if your migrations fail to complete across all databases for whatever reason then you could hit a point where you have databases with differing schema.

Additionally, like someone else pointed out, trying to run any reporting data across multiple customers will become difficult code wise and less performant.

Realistically, if you are handling the sort of scale that would require more interesting scaling solutions for typical db software, you are most certainly making enough money to implement better approaches.

FWIW, I worked for a company that was handling a few hundred thousand customers with millions of order records on a relatively small AWS RDS server. Set up a database cluster and you're rolling for a while.

I've maintained an enterprise saas product for ~1500 customers that used this strategy. Cross account analytics were definitely a problem, but the gaping SQL injection vulnerabilities left by the contractors that built the initial product were less of a concern.

Snapshotting / restoring entire accounts to a previous state was easy, and debugging data issues was also much easier when you could spin up an entire account's DB from a certain point in time locally.

We also could run multiple versions of the product on different schema versions. Useful when certain customers only wanted their "software" updated once every 6 months.

Seems pretty odd. The closest example I can think of would be maybe salesforce? Which basically, as far as I can tell, launches a whole new instance of the application (hosted by heroku?) for each client. I'm not a 100% sure about this, but i think this is how it works.

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 user's DB can only be accessed by the user whilst they're logged in.)

+ Securely delete a user's data once they delete their account. (A backup of their account is maintained for sixty days... But I can't decrypt it during that time. I can restore the account by request, but they still have to login to access it).

There are other, better, ways of doing the above.

What’s ‘scale?’

We do it, but everyone gets the same schema and same app. .net/IIS/sql server and when we update the schema, we apply to all dbs via in-house scripts. It provides security warm fuzzies to our clients that their data is separated from other clients. If you want to try and version the app/schema for different accounts, that’s where your headaches are, regardless of db model

In the past I worked at a company that managed thousands of individual MSSQL databases for individual customers due to data security concerns. Effectively what happened is the schema became locked in place since running migrations across so many databases became hard to manage.

I currently work at a company where customers have similar concerns around data privacy, but we've been to continue using a single multitenant DB instance by using PostgreSQL's row level security capabilities where rows in a table are only accessible by a given client's database user:

https://www.postgresql.org/docs/9.5/ddl-rowsecurity.html

We customized both ActiveRecord and Hibernate to accommodate this requirement.

I'm not sure if Roam technically uses separate databases, but it certainly calls each user's environment a "database." They're on Firebase (Cloud Firestore?) though, so it might just be a way of naming things and not a true db-per-user model.

We have separate google project -> bigquery for each of our clients. their data is big to us, tiny by big org standards (~ a billion rows /cycle depending on ads). It's political - and BQ views don't work well with metabase and some permissions things. There's a master google project with only a few people can access, and then as data comes in it's duped to a client google project with different IAM account.

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 of which can be easily overcome by smartly using workers and queuing. We run migration per schema using a queue to track progress and handle failures. We also avoid migrations by using Postgres JSON fields as much as possible. For instance, creating two placeholder fields in every table like metadata and data. To validate data in JSON fields we use JSONSchema extensively and it works really well.

Probably you also need to consider application caching scenarios. Even you managed to do one database per customer running Redis instance per customer will be a challenge. Probably you can run Redis as a docker container for each customer.

The now closed Ubuntu One FileSync service (a Dropbox like service) had a 1 database per user approach. And they were actually using it in top of SQLITE I think. The project is opensource now. And is based on U1DB https://launchpad.net/u1db but I know it didn't get lot of traction.

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 migrations across all databases, and with the mechanics of configuring connections to use a tenant based on Host header as requests come in.

For async jobs in Java, allocating a connection can idiomatically only be done via a callback - e.g. `executeInTenantContext(Tenant tenant, Runnable block)` - which ensures that all connections handed out have the right tenant selected, and everything is reset to the default tenant (an empty database) when exiting. Per-tenant jobs either iterate through all tenants, or have a tenant parameter under which they get executed, and the rest of the code can be more or less tenant unaware.

It gives you the "freedom" to move tenants to separate servers, or consolidate them into single servers, if tenants are much larger or much smaller than expected. In reality this is sufficiently painful it's mostly illusory. We're looking at Vitess to help scale out, and move away from a mix of multi-tenant servers and single-tenant servers.

Yes!! In hosted forum software this is the norm. If you want to create an account you create an entire database for this user. It isn't that bad! Basically when a user creates an account you run a setup.sql that creates the db schema. Devops is pretty complex but is possible. EG! Adding a column - would be a script.

Scaling is super easy since you can move a db to another host.

I would take a look at this article for a good primer on multi-tenancy patterns as it relates to Rails. I have not used the apartment gem but there are numerous tutorials on how to set this up.

https://rubygarage.org/blog/three-database-architectures-for...

We've a huge app, we use managed document database from major PaaS and we've our own mysql which syncs to Document database. Problem with document database is that it can't run complex queries but advantage is that data is safe in the hand of the major cloud operator.

We've never lost a single piece of data since we started using it.

Azure has a product built specifically for this, so it must be at least vaguely common. The rationale given in docs is: "A common application pattern is to provision a single database for each customer. But different customers often have varying and unpredictable usage patterns, and it's difficult to predict the resource requirements of each individual database user."

https://docs.microsoft.com/en-us/azure/sql-database/sql-data...

A client of mine used some SaaS from a vendor. The vendor sold you an “Instance“ that basically is an EC2 instance. Each instance has a self-contained app and self-contained database. Migration and app update are handled a single client/single instance at a time. Standard backup and restore tools can be used. It’s a more expensive approach but the software was specialized and expensive too. An upside of this model, it lends itself well to clients who require on-premise deployment. And it seems for heavy use clients, you can scale up their instance and database as needed.

I have similar. One PG database per tenant (640). Getting the current DSN is part of auth process (central auth DB), connect through PGBouncer.

Schema migrations are kind of a pain, we roll out changes, so on auth there is this blue/green decision.

Custom fields in EAV data-tables or jsonb data.

Backups are great, small(er) and easier to work with/restore.

Easier to move client data between PG nodes. Each DB is faster than one large one. EG: inventory table is only your 1M records, not everyone's 600M records so even sequential scan queries are pretty fast.