Subject: Many databases ou many schemas

From: Daniel Bergmann <[email-masked]>
Date: Fri, 20 May 2022 11:03:38 -0300

Folks ! who can help, I'm migrating a sql server system that contains many
databases and that the queries in these make join between the databases,
going to postgres I create a database with only several schemas? or several
databases using dblink to do the joins? in case of a single database how
can I manage the tablespaces? per schema (1 schema in 1 tablespace) ?

Thanks,
Daniel

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 20 May 2022 16:13:17 +0200

On Fri, 2022-05-20 at 11:03 -0300, Daniel Bergmann wrote:
> Folks ! who can help, I'm migrating a sql server system that contains many databases
> and that the queries in these make join between the databases, going to postgres I
> create a database with only several schemas? or several databases using dblink to
> do the joins? in case of a single database how can I manage the tablespaces?
> per schema (1 schema in 1 tablespace) ?

If you want to join the tables, use different schemas in a single database.
dblink or foreign data wrappers would just add additional complexity and
slow down operation.

Don't create any tablespaces, keep everything in the default tablespace.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

---

From: MichaelDBA Vitale <[email-masked]>
Date: Fri, 20 May 2022 10:15:42 -0400 (EDT)

Hi Daniel,

My preference would be to create multiple schemas in one database and avoid the headache of using dblink/postgres_fdw to communicate back and forth.  Regarding tablespaces, no need to do anything special there, just use the default tablespace that all objects go into.  In PG, tablespaces only have importance when mapping them to different mount points that might have different IO response times.

Regards,

Michael Vitale

On 05/20/2022 10:03 AM Daniel Bergmann <[email-masked]> wrote:

Folks ! who can help, I'm migrating a sql server system that contains many databases and that the queries in these make join between the databases, going to postgres I create a database with only several schemas? or several databases using dblink to do the joins? in case of a single database how can I manage the tablespaces? per schema (1 schema in 1 tablespace) ?

Thanks,

Daniel

---

From: MichaelDBA Vitale <[email-masked]>
Date: Fri, 20 May 2022 10:16:22 -0400 (EDT)

ditto to Laurenz!  We both must have written at the same time!

On 05/20/2022 10:13 AM Laurenz Albe <[email-masked]> wrote:

slow down operation.

Yours,

Laurenz Albe

--

---

From: Ron <[email-masked]>
Date: Fri, 20 May 2022 14:57:47 -0500

On 5/20/22 09:15, MichaelDBA Vitale wrote:
> In PG, tablespaces only have importance when mapping them to different 
> mount points that might have different IO response times. 

Or if for some reason "you" don't want to use lvm.

Likewise, auditors or upper management might say that each "database" must 
be in a separate folder.

-- 
Angular momentum makes the world go 'round.