pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSchema's vs Single Database with prefix on tables
6+ messages / 5 participants
[nested] [flat]
* Schema's vs Single Database with prefix on tables
@ 2018-09-09 10:39 Joao Ribeiro <joao.ribeiro@foursource.pt>
2018-09-09 22:02 ` Re: Schema's vs Single Database with prefix on tables Tim Cross <theophilusx@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Joao Ribeiro @ 2018-09-09 10:39 UTC (permalink / raw)
To: pgsql-admin
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’t 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)
_____
Best regards,
João Ribeiro
joao.ribeiro@foursource.pt
www.foursource.com <http://www.foursource.com/;
Attachments:
Webp.net-resizeimage.png (image/png, 4.0K) download
view image
* Re: Schema's vs Single Database with prefix on tables
2018-09-09 10:39 Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
@ 2018-09-09 22:02 ` Tim Cross <theophilusx@gmail.com>
2018-09-10 02:13 ` Re: Schema's vs Single Database with prefix on tables Evan Bauer <evanbauer@mac.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tim Cross @ 2018-09-09 22:02 UTC (permalink / raw)
To: Joao Ribeiro <joao.ribeiro@foursource.pt>; +Cc: pgsql-admin
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’t 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Schema's vs Single Database with prefix on tables
2018-09-09 10:39 Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
2018-09-09 22:02 ` Re: Schema's vs Single Database with prefix on tables Tim Cross <theophilusx@gmail.com>
@ 2018-09-10 02:13 ` Evan Bauer <evanbauer@mac.com>
2018-09-10 02:47 ` Re: Schema's vs Single Database with prefix on tables Carrie Berlin <berlincarrie@gmail.com>
2018-09-10 05:28 ` Re: Schema's vs Single Database with prefix on tables Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
0 siblings, 2 replies; 6+ messages in thread
From: Evan Bauer @ 2018-09-10 02:13 UTC (permalink / raw)
To: Tim Cross <theophilusx@gmail.com>; +Cc: Joao Ribeiro <joao.ribeiro@foursource.pt>; pgsql-admin
Joao,
I strongly agree with Tim’s recommendation to create a schema for each “domain.” 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 <mailto: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’t 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Schema's vs Single Database with prefix on tables
2018-09-09 10:39 Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
2018-09-09 22:02 ` Re: Schema's vs Single Database with prefix on tables Tim Cross <theophilusx@gmail.com>
2018-09-10 02:13 ` Re: Schema's vs Single Database with prefix on tables Evan Bauer <evanbauer@mac.com>
@ 2018-09-10 02:47 ` Carrie Berlin <berlincarrie@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Carrie Berlin @ 2018-09-10 02:47 UTC (permalink / raw)
To: Evan Bauer <evanbauer@mac.com>; +Cc: Joao Ribeiro <joao.ribeiro@foursource.pt>; Tim Cross <theophilusx@gmail.com>; pgsql-admin
I agree with Evan. Schemas are a great way to stay organized.
Additionally there is lots of flexibility with pgdump creating backups of
schemas to create dev and test environments. I would carefully design and
plan a central schema that has an established reference tables, this will
make your life and developer’s life much easier for doing reporting across
all or a subset of domains. The alias you choose for each domain can be in
a reference table and used when creating public synonyms for individual
schema tables. The sooner you have this standardized the better.
Your central schema can own functions, global views, types and so forth.
When a new domain is added you have a simple to
Add to database without impacting ongoing operations
On Sun, Sep 9, 2018 at 22:13 Evan Bauer <evanbauer@mac.com> wrote:
> Joao,
>
> I strongly agree with Tim’s recommendation to create a schema for each
> “domain.” 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’t 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
>
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Schema's vs Single Database with prefix on tables
2018-09-09 10:39 Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
2018-09-09 22:02 ` Re: Schema's vs Single Database with prefix on tables Tim Cross <theophilusx@gmail.com>
2018-09-10 02:13 ` Re: Schema's vs Single Database with prefix on tables Evan Bauer <evanbauer@mac.com>
@ 2018-09-10 05:28 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-10 07:55 ` Re: Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
1 sibling, 1 reply; 6+ messages in thread
From: Mark Kirkwood @ 2018-09-10 05:28 UTC (permalink / raw)
To: Evan Bauer <evanbauer@mac.com>; Tim Cross <theophilusx@gmail.com>; +Cc: Joao Ribeiro <joao.ribeiro@foursource.pt>; pgsql-admin
Another option is to embed the domain id (assuming there is one) into
each table's primary key (i.e compound primary key) and thus use 1
schema and 1 database, without the need to dynamically change table
prefixes.
regards
Mark
On 10/09/18 14:13, Evan Bauer wrote:
> Joao,
>
> I strongly agree with Tim’s recommendation to create a schema for each
> “domain.” 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 <mailto:eb@evanbauer.com>
> +1 646 641 2973
> Skype: evanbauer
>
>
>> On Sep 9, 2018, at 18:02, Tim Cross <theophilusx@gmail.com
>> <mailto:theophilusx@gmail.com>> wrote:
>>
>>
>> Joao Ribeiro <joao.ribeiro@foursource.pt
>> <mailto: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’t 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Schema's vs Single Database with prefix on tables
2018-09-09 10:39 Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
2018-09-09 22:02 ` Re: Schema's vs Single Database with prefix on tables Tim Cross <theophilusx@gmail.com>
2018-09-10 02:13 ` Re: Schema's vs Single Database with prefix on tables Evan Bauer <evanbauer@mac.com>
2018-09-10 05:28 ` Re: Schema's vs Single Database with prefix on tables Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
@ 2018-09-10 07:55 ` Joao Ribeiro <joao.ribeiro@foursource.pt>
0 siblings, 0 replies; 6+ messages in thread
From: Joao Ribeiro @ 2018-09-10 07:55 UTC (permalink / raw)
To: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>; Tim Cross <theophilusx@gmail.com>; Evan Bauer <evanbauer@mac.com>; +Cc: pgsql-admin
Thanks guys for the help :)
_____
Best regards,
João Ribeiro
joao.ribeiro@foursource.pt
www.foursource.com <http://www.foursource.com/;
> No dia 10/09/2018, às 06:28, Mark Kirkwood <mark.kirkwood@catalyst.net.nz> escreveu:
>
> Another option is to embed the domain id (assuming there is one) into each table's primary key (i.e compound primary key) and thus use 1 schema and 1 database, without the need to dynamically change table prefixes.
>
> regards
>
> Mark
>
>
> On 10/09/18 14:13, Evan Bauer wrote:
>> Joao,
>>
>> I strongly agree with Tim’s recommendation to create a schema for each “domain.” 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 <mailto:eb@evanbauer.com>
>> +1 646 641 2973
>> Skype: evanbauer
>>
>>
>>> On Sep 9, 2018, at 18:02, Tim Cross <theophilusx@gmail.com <mailto:theophilusx@gmail.com>> wrote:
>>>
>>>
>>> Joao Ribeiro <joao.ribeiro@foursource.pt <mailto: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’t 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
>>
>
Attachments:
Webp.net-resizeimage.png (image/png, 4.0K) download
view image
end of thread, other threads:[~2018-09-10 07:55 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-09-09 10:39 Schema's vs Single Database with prefix on tables Joao Ribeiro <joao.ribeiro@foursource.pt>
2018-09-09 22:02 ` Tim Cross <theophilusx@gmail.com>
2018-09-10 02:13 ` Evan Bauer <evanbauer@mac.com>
2018-09-10 02:47 ` Carrie Berlin <berlincarrie@gmail.com>
2018-09-10 05:28 ` Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
2018-09-10 07:55 ` Joao Ribeiro <joao.ribeiro@foursource.pt>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox