From: | Anil Menon <gakmenon(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Cc: | "Michael Cheung *EXTERN*" <vividy(at)justware(dot)co(dot)jp> |
Subject: | Re: schema or database |
Date: | 2015-04-13 11:21:32 |
Message-ID: | CAHzbRKc8cim_fM=cmx0kuc5tZn5u4qRjLCE0ntZWfe0eLPwxPQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 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(dot)stehule(at)gmail(dot)com>
wrote:
>
>
> 2015-04-13 10:43 GMT+02:00 Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at>:
>
>> 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.
>
> Regards
>
> Pavel
>
>
>
>
>>
>> Yours,
>> Laurenz Albe
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ramesh T | 2015-04-13 13:53:32 | Pgagent |
Previous Message | Pavel Stehule | 2015-04-13 08:48:48 | Re: schema or database |