Re: Database vs Schema Separation

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 different DBs 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 t2 WHERE ... 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, the size 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 official name 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 should be 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