PostgreSQL: Is it better to use multiple databases with one schema each, or one database with multiple schemas?

Question

After this comment to one of my question, I'm thinking if it is better using one database with X schemas or vice versa.

My situation: I'm developing a web application where, when people register, I create (actually) a database (no, it's not a social network: everyone must have access to his own data and never see the data of the other user).

That's the way I used for the previous version of my application (that is still running on MySQL): through the Plesk API, for every registration, I do:

1. Create a database user with limited privileges;
2. Create a database that can be accessed just by the previous created user and the superuser (for maintenance)
3. Populate the database

Now, I'll need to do the same with PostgreSQL (the project is getting mature and MySQL... don't fulfill all the needs).

I need to have all the databases/schemas backups independent: pg_dump works perfectly in both ways, and the same for the users that can be configured to access just one schema or one database.

So, assuming you are more experienced PostgreSQL users than me, what do you think is the best solution for my situation, and why?

Will there be performance differences using $x database instead of $x schemas? And what solution will be better to maintain in the future (reliability)?

All of my databases/schemas will always have the same structure!

For the backups issue (using pg_dump), is maybe better using one database and many schemas, dumping all the schemas at once: recovering will be quite simple loading the main dump in a development machine and then dump and restore just the schema needed: there is one additional step, but dumping all the schema seem faster than dumping them one by one.

UPDATE 2012

Well, the application structure and design changed so much during those last two years. I'm still using the one db with many schemas approach, but still, I have one database for each version of my application:

Db myapp_01
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema
Db myapp_02
    \_ my_customer_foo_schema
    \_ my_customer_bar_schema

For backups, I'm dumping each database regularly, and then moving the backups on the development server.

I'm also using the PITR/WAL backup but, as I said before, it's not likely I'll have to restore all database at once... so it will probably be dismissed this year (in my situation is not the best approach).

The one-db-many-schema approach worked very well for me since now, even if the application structure is totally changed:

I almost forgot: all of my databases/schemas will always have the same structure!

...now, every schema has its own structure that change dynamically reacting to users data flow.

Answer 1

A PostgreSQL "schema" is roughly the same as a MySQL "database". Having many databases on a PostgreSQL installation can get problematic; having many schemas will work with no trouble. So you definitely want to go with one database and multiple schemas within that database.