References: <30A5D829-58E2-4934-9C93-25968D05183B@foursource.pt>
User-agent: mu4e 0.9.18; emacs 26.1
From: Tim Cross <theophilusx@gmail.com>
To: Joao Ribeiro <joao.ribeiro@foursource.pt>
Cc: pgsql-admin@postgresql.org
Subject: Re: Schema's vs Single Database with prefix on tables
In-reply-to: <30A5D829-58E2-4934-9C93-25968D05183B@foursource.pt>
Date: Mon, 10 Sep 2018 08:02:19 +1000
Message-ID: <871sa25nys.fsf@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk


Joao Ribeiro <joao.ribeiro@foursource.pt> writes:

> Hello,
>
> We are using Postgres and we are with a issue. We have splited our tables=
 by domain and
> each domain has a separated database, but we are trying to change it to a=
 single database
> model. We have two options, create the same database in a single schema a=
nd add a prefix
> on each database domain or create a schema for each database (we have abo=
ut 15
> different models). In this approach we still want to do some joins and ot=
her queries cross
> schema, but we don=E2=80=99t know what would be the best approach :) Coul=
d you help us to know
> what would be the best approach ?
>
> * having just one database with one schema and all domain databases with =
a prefix
> * having a schema for each domain (15 domains) with the domain databases =
(+/ 20 tables)
> (knowing that we do cross schema queries)
>
> _____

It is probably just a matter of taste to some extent. I personally would
favour separate schemas over tables in same schema with prefixes because
I think that gives you more flexibility i.e. easier to select all the
data associated with a domain as it is all in one schema. I also think
it is easier to define security roles on a per schema basis rather than
complex roles in the same schema, especially if you add new objects
etc. Auditing is also less complex.

There are no issues with cross-schema queries/joins etc apart from
having to include the schema name in the query. Some people don't like
this because you have to type more and have a longer search_path, but if
the alternative is table prefixes, the amount of typing is similar
anyway.

Essentially, the schema gives you a predefined unit which many
tools/commands understand. If everything is in the same schema, then you
will often need to replicate some level of this functionality yourself
and then ensure it is maintained. Extent to which this has an impact
really depends on your use case.=20

Tim

--=20
Tim Cross