Pros and cons of 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?

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:

• Select rows from table where each row refers to a different schema

• Find out which schema based on table values

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

StackExchange Database Administrators Q#94129, answer score: 4