title: Is it better to use multiple databases with one schema each, or one database with multiple schemas?
body: <p>After <a href="https://stackoverflow.com/questions/1130555/postgresql-pitr-backup-best-practices-to-handle-multiple-databases/1145332#1145332">this comment</a> to one of my questions, I'm thinking if it is better using one database with X schemas or vice versa.</p>
  <p>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:</p>
  <ol>
  <li>Create a database user with limited privileges;</li>
  <li>Create a database that can be accessed just by the previous created user and the superuser (for maintenance)</li>
  <li>Populate the database</li>
  </ol>
  <p>Now, I'll need to do the same with PostgreSQL (the project is getting mature and MySQL don't fulfil all the needs). I need to have all the databases/schemas backups independent: <code>pg_dump</code> works perfectly in both ways, and the same for the users that can be configured to access just one schema or one database.</p>
  <p>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 <em>always</em> have the same structure!</p>
  <p>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.</p>
  <h2>UPDATE 2012</h2>
  <p>Well, the application structure and design changed so much during those last two years. I'm still using the &quot;one db with many schemas&quot; -approach, but still, I have one database <em>for each version</em> of my application:</p>
  <pre><code>Db myapp_01
      \_ my_customer_foo_schema
      \_ my_customer_bar_schema
  Db myapp_02
      \_ my_customer_foo_schema
      \_ my_customer_bar_schema
  </code></pre>
  <p>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 <em>all database</em> at once. So it will probably be dismissed this year (in my situation is not the best approach).</p>
  <p>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 <em>always</em> have the same structure! Now, every schema has its own structure that change dynamically reacting to users data flow.</p>