Обсуждение: schema or database

hi, all;

I am new here. And I need some suggestion.

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.

Thanks

michael

On 13/04/15 11:08, Michael Cheung wrote:
> hi, all;
>
> I am new here. And I need some suggestion.
>
> 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.

If as you say access to the database is via a single application database
user, it will probably make more sense to use multiple schemas rather than
multiple databases. Keeping everything in one database will simplify
administration (e.g. making backups - ypu'll just need to dump the one database
rather than looping through a variable number) and will make life easier if you
ever need to do some kind of query involving multiple customers.
There will also be less overhead when adding a new schema vs adding
a new database.


Regards

Ian Barwick

--
 Ian Barwick                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

On 4/12/2015 7:20 PM, Ian Barwick wrote:
> If as you say access to the database is via a single application database
> user, it will probably make more sense to use multiple schemas rather than
> multiple databases. Keeping everything in one database will simplify
> administration (e.g. making backups - ypu'll just need to dump the one database
> rather than looping through a variable number) and will make life easier if you
> ever need to do some kind of query involving multiple customers.
> There will also be less overhead when adding a new schema vs adding
> a new database.

and less overhead in connections, as one client connection can serve
multiple customers

--
john r pierce, recycling bits in santa cruz

Thanks for your suggestion.
I'd like to use schema as you suggest.

yours, michael

On Mon, 13 Apr 2015 11:20:59 +0900
Ian Barwick <ian@2ndquadrant.com> wrote:

> On 13/04/15 11:08, Michael Cheung wrote:
> > hi, all;
> >
> > I am new here. And I need some suggestion.
> >
> > 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.
>
> If as you say access to the database is via a single application database
> user, it will probably make more sense to use multiple schemas rather than
> multiple databases. Keeping everything in one database will simplify
> administration (e.g. making backups - ypu'll just need to dump the one database
> rather than looping through a variable number) and will make life easier if you
> ever need to do some kind of query involving multiple customers.
> There will also be less overhead when adding a new schema vs adding
> a new database.
>
>
> Regards
>
> Ian Barwick
>
> --
>  Ian Barwick                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thanks for your additional comment.
It is more clear, I'd better to using schema more than using database.

yours, michael



On Sun, 12 Apr 2015 19:24:30 -0700
John R Pierce <pierce@hogranch.com> wrote:

> On 4/12/2015 7:20 PM, Ian Barwick wrote:
> > If as you say access to the database is via a single application database
> > user, it will probably make more sense to use multiple schemas rather than
> > multiple databases. Keeping everything in one database will simplify
> > administration (e.g. making backups - ypu'll just need to dump the one database
> > rather than looping through a variable number) and will make life easier if you
> > ever need to do some kind of query involving multiple customers.
> > There will also be less overhead when adding a new schema vs adding
> > a new database.
>
> and less overhead in connections, as one client connection can serve multiple customers
>
> -- john r pierce, recycling bits in santa cruz
>
>
>
> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

> On 13 Apr 2015, at 4:20, Ian Barwick <ian@2ndquadrant.com> wrote:
>
> On 13/04/15 11:08, Michael Cheung wrote:
>> hi, all;
>>
>> I am new here. And I need some suggestion.
>>
>> 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.
>
> If as you say access to the database is via a single application database
> user, it will probably make more sense to use multiple schemas rather than
> multiple databases. Keeping everything in one database will simplify
> administration (e.g. making backups - ypu'll just need to dump the one database
> rather than looping through a variable number) and will make life easier if you
> ever need to do some kind of query involving multiple customers.

That's easier to backup, sure, but you can't restore a single customer's schema easily that way. So if one customer
messesup their data big time, you'll need to restore a backup for all customers in the DB. 

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

On 4/13/2015 12:07 AM, Alban Hertroys wrote:
> That's easier to backup, sure, but you can't restore a single customer's schema easily that way. So if one customer
messesup their data big time, you'll need to restore a backup for all customers in the DB. 

if you use pg_dump -Fc, then you can specify the schema at pg_restore time.



--
john r pierce, recycling bits in santa cruz

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.

Yours,
Laurenz Albe

On 4/13/15 6:21 AM, Anil Menon wrote:
> 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

Please don't top-post.

> On Mon, Apr 13, 2015 at 4:48 PM, Pavel Stehule <pavel.stehule@gmail.com
> <mailto:pavel.stehule@gmail.com>> wrote:
>
>
>
>     2015-04-13 10:43 GMT+02:00 Albe Laurenz <laurenz.albe@wien.gv.at
>     <mailto:laurenz.albe@wien.gv.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.

Two things no one has mentioned. First, you could also use row-level
security. If you plan on each customer having a fairly small amount of
data, this is by far your most efficient option. Anything else will
result in either huge catalogs or a lot of wasted catalog space.

Second, if you do per-database, that makes it trivial to scale across
multiple servers.

Regarding backups; you can easily do partial either way with pg_dump;
there's really no difference. You can't do partial with PITR, but that's
true for both schema and database.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com