9

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?

Improve this question
4
  • 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é
    Commented Mar 2, 2015 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
    Commented Mar 2, 2015 at 22:44
  • @Harel I am curious, did you try it with 400k schemas or switched to another architecture / technology?
    – sthzg
    Commented Jan 7, 2016 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
    Commented Jan 7, 2016 at 15:36

1 Answer 1

Reset to default
4

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.

Improve this answer
4
  • 3
    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
    Commented Mar 2, 2015 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
    Commented Mar 2, 2015 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
    Commented Mar 2, 2015 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
    Commented Dec 30, 2018 at 8:40

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.