dimanche 1 mars 2015

What are the pros and cons using many schemas in PostgreSql as opposed to just one?


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?





Aucun commentaire:

Enregistrer un commentaire