11–20 of 264 posts
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
Easier restores if needed
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.
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.
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…
You can use the same connection across multiple databases without any problem.
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…
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.
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.
If I were to make a Salesforce competitor that’s one thing I would do differently, with tools like Kubernetes it’s a lot easier to just give every customer their own instances. Yes, it can take up more resources - but I cannot imagine the security nightmare involved with letting multiple customers execute code (even if it’s theoretically sandboxed) in the same process, plus the headache that is their database schema.
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
Better separation Easier restores if needed
Depends on the reason why you need a restore. If something botches many databases at once (because the filer holding them dies or whatever), you might be looking at a fun time restoring hundreds or thousands of databases with a playbook that was meant for one or maybe ten databases and thus isn't sufficiently automated.
Not saying that these kinds of errors are likely. But you cannot just make these assertions without the context of your actual threat model. Same for the "better separation" part. How much separation you need depends on what you're protecting against what.