Обсуждение: 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
2015-04-13 10:43 GMT+02:00 Albe Laurenz <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.
Regards
Pavel
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 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@gmail.com> wrote:
2015-04-13 10:43 GMT+02:00 Albe Laurenz <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.RegardsPavel
Yours,
Laurenz Albe
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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