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 that in the past pg_dump was painfully slow when working with many schemas but I'm 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. On the plus side, moving a schema from one physical server to another becomes easy, as well as restoring a schema from backup. It also makes sense to partition data that way.
However, at the same time the number of schemas required here is very large and would be so from day 1 (its a rework of an existing system). The shared approach works well, but I'd like to explore other avenues.