Обсуждение: [ADMIN] Database vs Schema Separation

Поиск
Список
Период
Сортировка

[ADMIN] Database vs Schema Separation

От
Rob Emery
Дата:
Hi All,

We're in the process of migrating parts of our system from SQL Server
onto PostgreSQL. In SQL Server we have a db-per-customer, all of which
have the same schema (i.e. table definitions) and we only use the dbo
schema.

I'm looking at options for a similar model into PG; the only real
differences that I can see between customer-per-db and
customer-per-schema is the ability to join between tables in the same
database but different schema. This is not an option for
customer-per-db as once connected into a database, you have to open
another connection to the other one.

I can see there would be connection-pooling advantages for shared-db
models and scalability advantages for per-db (I can move given
customers out onto their own servers more easily etc if I wanted).

Are there any particular gotcha's with either model that I should be
aware of? From what I can see backups, restores and permissions are
all equally configurable per schema as they are per-database.

Many Thanks
Rob

--
 <https://codeweavers.net>

Codeweavers
February
 Newsletter
<http://us6.campaign-archive1.com/?u=db934935d4174e46e9063c8c4&id=895274c19a>


*MultiCalc enables car buyers to select a quote based on their credit
rating
<https://codeweavers.net/company-blog/multicalc-can-display-different-quotes-based-on-credit-score>*



*Phone:* 0800 021 0888  * Email: *contactus@codeweavers.net
*Codeweavers Ltd* | Barn 4 | Dunston Business Village | Dunston | ST18 9AB
Registered in England and Wales No. 04092394 | VAT registration no. 974
9705 63


<http://t.sidekickopen50.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Fcodeweavers-limited&si=4882959321006080&pi=b30721f0-51f0-448c-8020-e9ac33f685b8>


<http://t.sidekickopen50.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fvimeo.com%2Fcodeweaversltd&si=4882959321006080&pi=b30721f0-51f0-448c-8020-e9ac33f685b8>


<http://t.sidekickopen50.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fplus.google.com%2Fb%2F105942302039373248738%2F%2BCodeweaversNet%2Fposts&si=4882959321006080&pi=b30721f0-51f0-448c-8020-e9ac33f685b8>


<http://t.sidekickopen50.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Ftwitter.com%2FCodeweaversTeam%3Flang%3Den-gb&si=4882959321006080&pi=b30721f0-51f0-448c-8020-e9ac33f685b8>


Re: [ADMIN] Database vs Schema Separation

От
Achilleas Mantzios
Дата:
On 22/02/2017 20:45, Rob Emery wrote:
> Hi All,
>
> We're in the process of migrating parts of our system from SQL Server
> onto PostgreSQL. In SQL Server we have a db-per-customer, all of which
> have the same schema (i.e. table definitions) and we only use the dbo
> schema.
from my experience mssql DBs in the same cluster act more or less like PgSQL schemas, you can join tables from
differentDBs in mssql from what I gather. 
So you might migrate code which did smth like : select * from YOUR_MSDB.dbo.yourtable t1, YOUR_OTHER_MSDB.dbo.yourtable
t2WHERE ... to something like this in PgSQL : select * from yourns1.yourtable  
t1, yourns.yourtable t2 WHERE ...

You should consider the effort to migrate the code, this might depend on whether you use some ORM technology or not,
thesize of the app, and the levels of flexibility (always access to the lower  
layers gives advantages in fine tuning performance, concurrency, etc)
>
> I'm looking at options for a similar model into PG; the only real
> differences that I can see between customer-per-db and
> customer-per-schema is the ability to join between tables in the same
> database but different schema. This is not an option for
> customer-per-db as once connected into a database, you have to open
> another connection to the other one.
Postgresql supports foreign tables very well. No need for a second connection or dblink.
>
> I can see there would be connection-pooling advantages for shared-db
> models and scalability advantages for per-db (I can move given
> customers out onto their own servers more easily etc if I wanted).
>
> Are there any particular gotcha's with either model that I should be
> aware of? From what I can see backups, restores and permissions are
> all equally configurable per schema as they are per-database.
IMHO working with a single database generally feels better. It depends on the business. Think of the database as the
officialname in the information schema : a catalog. A DB should represent a self  
sufficient system of tables. If the need to access tables in other local DBs is very often, then maybe the design
shouldbe redone with a larger database. However, distinct business domains should be  
represented with different DBs.
> Many Thanks
> Rob
>


--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt