2

I have a client who's insisting in a SaaS application to have one table for all users, and then for each user to have a separate database on which we perform CRUD operations based on an authentication token that contains permission only for that database. This will be done with python using psycopg2, from what I've read is that PostgreSQL has a max_connections option and this would mean that at one point in future we're going to reach a bottleneck and will no longer be able to serve our application for an x simultaneous number of clients. What other arguments can I provide to make my client change his mind and avoid such mistake that might cost him too much as he's also had some amount of app development experience?

4
  • 1
    Maybe its a feature of his app that he can adverise, non-shared database. Have you asked for his reasons? Commented Jun 18, 2023 at 9:49
  • @RohitGupta His primary reason is performance, instead of having one big database, he'll separate it by having small databases. I don't think it's a feature, he didn't advertise it as such, but I might think he is thinking about some issue he had with his previous SaaS concerning database performance. I think he might not be aware of query optimization, indexes, datatypes... etc Commented Jun 18, 2023 at 9:59
  • 2
    Seems to me like that customer does not really understand the technology. Commented Jun 18, 2023 at 17:01
  • 1
    Having a database per user will only work well if you have few users. Tell the client that this is not MySQL, where "database" is a synonym for "schema". You will not be able to use a connection pool with that design, which is detrimental for performance. Too many databases (or schemas) slow down metadata queries, which causes interesting problems. If the concern is isolation of customers from each other, use row level security. If the consideration is performance: with the proper indexes, size doesn't matter. Also, there is partitioning if a table grows too large. Commented Jun 19, 2023 at 5:05

2 Answers 2

3

His primary reason is performance, instead of having one big database, he'll separate it by having small databases.

Well he's mostly wrong about using it as a design decision for performance. I'm sure he thinks his performance issues were due to the amount of data in one place and splitting it up this way reduces the amount, but indexes already split up the data in an exponentially more performant way.

There's only a few potential performance improvements one may gain by doing this. One is by reducing lock contention on the tables. But lock contention shouldn't normally be much of an issue with a properly architected database with well designed queries. And can be helped with proper isolation level usage.

The other potential performance improvement is more specific statistics to the data can be generated. One client's data may have a lot more rows in a given table, and another client may have very little rows in that table. In the same table, the statistics would be based on the aggregate of both clients' data, resulting in what may not be optimal for one or both clients when certain queries are ran against those tables. Separate databases (and ergo separate tables) for each client allows more targeted statistics about the data in those tables, which can help improve performance in certain cases.


Now there are reasons in a SaaS application to model the database as a separate database per client. It makes some management operations easier such as backup restores at the individual client level. It also gives more flexibility with customizing the database catered towards the client, even just at a performance level with indexing that may make more sense for one client's set of data that varies greatly from the norm. And there's a better assurance of security towards the clients that only they can access their data (with it not being intermingled with other clients' data in the same database).

So it's not a poor design choice to do this. But it also makes management for you as the developer more difficult for things such as deploying schema changes, for example.


from what I've read is that PostgreSQL has a max_connections option and this would mean that at one point in future we're going to reach a bottleneck and will no longer be able to serve our application for an x simultaneous number of clients.

This is a limitation on the server, and will be true regardless if you use a single database or separate databases for you SaaS clients. You're still going to have X number of clients using your application to connect to the database(s) either way. It's actually an argument that can be used in favor of using a separate database per client, so that they can be easily migrated to other servers as needed, to help balance out the connection contention on the current server.


FWIW, when I worked for a SaaS company in my past, we followed the separate database per client model. It worked well for us. We had about 1,000 databases / clients across a couple of servers. This wasn't done directly for performance reasons, rather for improved management, security, and flexibility reasons, as previously mentioned. We did a lot of analytical querying on the data though, so custom indexing and query tuning was needed for certain clients' databases.

1

All databases have a max_connections options.

I already replied to this question here and I then wrote blog post about that.

The rule of thumb should be: are you serving customers or are you serving users?

  • Customers: You are selling a SaaS, your customers are actual companies with an office --> go single tenant
  • Users: your users can create an account with a @gmail.com e-mail, they come and go -- go multi tenant

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.