schema or database

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

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

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

--
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

--
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 messes up 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.

--
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/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 messes up 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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.stehule@gmail.com>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general