8

For a large SAAS application (backed by PostgreSql 9.4), with over 300,000 accounts (and growing), what are the pros and cons of using a schema per account to partition the data vs putting all the data in one schema and using foreign keys to partition it in the queries?

I know in the past pg_dump was painfully slow when working with many schemas but not sure if that is the case today. I'm also aware any change in the database structure will have to be done on all the schemas. And I know that on the plus side, moving a schema from one physical server to another is easy, as well as restoring a schema from backup, not to mention it makes sense to partition data that way.

So what are the pros and cons I'm missing?

  • Neither looks good. A single huge table ("vertical growth") is hard to manage and a huge number of schemas ("horizontal growth") is hard to manage too. – Daniel Vérité Mar 2 '15 at 15:35
  • I'm rebuilding an old system which has that number of accounts on it (and even larger number of users). It is using a shared approach (using mySql) and works fine as far as performance goes. My concern is to maintain that level of performance but add maintainability to it. – Harel Mar 2 '15 at 22:44
  • @Harel I am curious, did you try it with 400k schemas or switched to another architecture / technology? – sthzg Jan 7 '16 at 15:23
  • 1
    I gave up on the idea after looking deeper into it. The amount of schemas I was going to create would defeat any practical use of this. I went with the good old account id field in every record. What i did do as well though, was to drop numeric auto increment ids in favour of UUIDs which means I can take an entire account from one db to another quite easily without having to worry about breaking integrity. – Harel Jan 7 '16 at 15:36
3

Obviously, you are dealing with the same tables in each user schema. Have you considered inheritance for this? It can give you the best of both worlds for some use cases. There are also some limitations. You can have a separate schema for each user and still search all user tables at once very conveniently.

Related:

Other than that, at least granting / revoking privileges has to be mentioned, which is much simpler with separate schemas.

  • 2
    I will look into inheritance. However, my concern is with the scale here. Everywhere I read people are talking about multi-tenant schema strategy but referring to tens, hundreds or thousands schemas. One place mentioned 20K schemas. The question is - is 400K schemas too much? Will it cause a file descriptor madness and kill the server? Am I pushing it? – Harel Mar 2 '15 at 10:51
  • Also, I intended to keep the tenant data (accounts and users) in the public schema, while maintaining the schemas themselves as actual user data. That data is not, and will never be, shared across schemas. – Harel Mar 2 '15 at 10:55
  • Inheritance won't help me here I don't think. The shared approach uses a single schema with mandatory foreign keys to the user or tenant so nothing gained from inheriting I'm afraid. – Harel Mar 2 '15 at 12:49
  • 1
    From this article influitive.io/… I think multi-schema mode is not a good way for large number tenants. Column tenant_id (the ancient way) comes better. – Xiaohui Zhang Dec 30 '18 at 8:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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