Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fzBhe-0005Uh-OF for pgsql-admin@arkaria.postgresql.org; Mon, 10 Sep 2018 02:13:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fzBhc-0002S4-JC for pgsql-admin@arkaria.postgresql.org; Mon, 10 Sep 2018 02:13:24 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fzBhc-0002Rx-2Y for pgsql-admin@lists.postgresql.org; Mon, 10 Sep 2018 02:13:24 +0000 Received: from st14p37im-asmtp002.me.com ([17.41.209.31]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fzBhZ-0006u0-61 for pgsql-admin@postgresql.org; Mon, 10 Sep 2018 02:13:22 +0000 Received: from process-dkim-sign-daemon.st14p37im-asmtp002.me.com by st14p37im-asmtp002.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) id <0PET00F00I1E7800@st14p37im-asmtp002.me.com> for pgsql-admin@postgresql.org; Mon, 10 Sep 2018 02:13:20 +0000 (GMT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=mac.com; s=04042017; t=1536545600; bh=NPZbO5GWeIXT+3UKRj5hUyjY99MhbJmxM5COZ4UZPn0=; h=From:Message-id:Content-type:MIME-version:Subject:Date:To; b=weceOvnioDABqKtoN3KidWq2xXTq4P8muyeQsh/58/QNVBYLIyJ8s5J/njRciFQ56 0VrqQjj7fKSDzoqPs8tJO3TkUQrEuWHNQ/gAlUWW+s0RTJQhVdfvg8MnQQtDsuBQLt 8KUAmLhFmFqO1ey3XhrG1XquG1I61Hgdnk6CE45mbiW8PmQfJE7s7CuVYbk9PEqGS9 HlIZkzfuDAeR9Z5Op9fpLExyg9JA0b7mI02slcOjYTbSLOeXndN96rz5/1Bjuj8wYr mDLk2zB1mtovS2qx/mtgoR3vPDVwAB6n2qJnwYedgiRnmnrsIBLtCF6VU5f0SDENgl SEmC6AbQOgzVQ== Received: from icloud.com ([127.0.0.1]) by st14p37im-asmtp002.me.com (Oracle Communications Messaging Server 8.0.2.2.20180531 64bit (built May 31 2018)) with ESMTPSA id <0PET00A62I667Q40@st14p37im-asmtp002.me.com>; Mon, 10 Sep 2018 02:13:19 +0000 (GMT) X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 spamscore=0 clxscore=1011 suspectscore=0 malwarescore=0 phishscore=0 adultscore=0 bulkscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.0.1-1707230000 definitions=main-1809100022 X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10434:,, definitions=2018-09-10_01:,, signatures=0 From: Evan Bauer Message-id: <7A563FA6-5134-4DD6-8BC9-79FCD0804652@mac.com> Content-type: multipart/alternative; boundary="Apple-Mail=_9340C2EB-D5AD-46A5-AD35-09BB592D007E" MIME-version: 1.0 (Mac OS X Mail 11.5 \(3445.9.1\)) Subject: Re: Schema's vs Single Database with prefix on tables Date: Sun, 09 Sep 2018 22:13:17 -0400 In-reply-to: <871sa25nys.fsf@gmail.com> Cc: Joao Ribeiro , pgsql-admin@postgresql.org To: Tim Cross References: <30A5D829-58E2-4934-9C93-25968D05183B@foursource.pt> <871sa25nys.fsf@gmail.com> X-Mailer: Apple Mail (2.3445.9.1) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_9340C2EB-D5AD-46A5-AD35-09BB592D007E Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 Joao, I strongly agree with Tim=E2=80=99s recommendation to create a schema = for each =E2=80=9Cdomain.=E2=80=9D In addition to the reasons below, = the prefix model would seem to condemn your programs and programmers to = using dynamic SQL to construct your business logic in a manner that = would work for any domain, as the table names for each SQL statement = would vary based on the domain. Schemas are a solid approach to multi-domain design, they should work = well for you. Cheers, - Evan Evan Bauer eb@evanbauer.com +1 646 641 2973 Skype: evanbauer > On Sep 9, 2018, at 18:02, Tim Cross wrote: >=20 >=20 > Joao Ribeiro > writes: >=20 >> Hello, >>=20 >> 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 and add a prefix >> on each database domain or create a schema for each database (we have = about 15 >> different models). In this approach we still want to do some joins = and other queries cross >> schema, but we don=E2=80=99t know what would be the best approach :) = Could you help us to know >> what would be the best approach ? >>=20 >> * 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) >>=20 >> _____ >=20 > 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. >=20 > 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. >=20 > 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 >=20 > Tim >=20 > --=20 > Tim Cross --Apple-Mail=_9340C2EB-D5AD-46A5-AD35-09BB592D007E Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8 Joao,

I = strongly agree with Tim=E2=80=99s recommendation to create a schema for = each =E2=80=9Cdomain.=E2=80=9D  In addition to the reasons below, = the prefix model would seem to condemn your programs and programmers to = using dynamic SQL to construct your business logic in a manner that = would work for any domain, as the table names for each SQL statement = would vary based on the domain.

Schemas are a solid approach to = multi-domain design, they should work well for you.

Cheers,

- Evan

Evan Bauer
eb@evanbauer.com
+1 646 641 2973
Skype: evanbauer


On Sep 9, 2018, at 18:02, Tim Cross <theophilusx@gmail.com> wrote:


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 and add a = prefix
on each database domain or create a schema for each = database (we have about 15
different models). In this = approach we still want to do some joins and other queries cross
schema, but we don=E2=80=99t know what would be the best = approach :) Could 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. 

Tim

-- 
Tim = Cross

= --Apple-Mail=_9340C2EB-D5AD-46A5-AD35-09BB592D007E--