Обсуждение: [ADMIN] Database vs Schema Separation
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>
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