In addition to all these comments
- If you use multiple databases, if you want to keep some "common" tables (example counties_Table, My_company_details), its going to be a pain
- If you use multiple databases, if you want to keep some "common" tables (example counties_Table, My_company_details), its going to be a pain
- if you want to access tables across databases - you might need to start using FDWs (which is going to be a administrative pain - syncing passwords and stuff)
- you could set up security easier with multiple schemas - example userA can only use schema A and no access to other schemas
Regards
AK
On Mon, Apr 13, 2015 at 4:48 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
2015-04-13 10:43 GMT+02:00 Albe Laurenz <laurenz.albe@xxxxxxxxxx>:Michael Cheung wrote:
> I have many similar database to store data for every customer.
> Structure of database is almost the same.
> As I use same application to control all these data, so I can only use
> one database user to connect to these database.
> And I have no needs to query table for different customer together.
>
> I wonder which I should use, different shema or different database to store data?
>
> I 'd like to know the advantage and disadvantage for using schema or database.
In addition to what others have said:
If you use multiple schemas within one database, the danger is greater that
data are written to or read from the wrong schema if your application has a bug
ans does not make sure to always set search_path or qualify every access with a
schema name.
With multiple databases you are guaranteed not to access data from a different
database.
The main downside that I see to multiple databases is the overhead: each of
the databases will have its own pg_catalog tables.It can be advantage - if your schema is pretty complex - thousands procedures, tables, then separate pg_catalog can be better - there are issues with pg_dump, pg_restore.So it depends on catalog size and complexity.RegardsPavel
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
