Re: [GENERAL] Multiple Schemas vs. Multiple Databases - Mailing list pgsql-general

From Melvin Davidson
Subject Re: [GENERAL] Multiple Schemas vs. Multiple Databases
Date
Msg-id CANu8FiwVc90iEJ2ziE1Xda7y9p2iui0E-XisOtfsXRG5-O8trA@mail.gmail.com
Whole thread Raw
In response to [GENERAL] Multiple Schemas vs. Multiple Databases  ("Igal @ Lucee.org" <igal@lucee.org>)
Responses Re: [GENERAL] Multiple Schemas vs. Multiple Databases
List pgsql-general


On Fri, Oct 13, 2017 at 3:29 PM, Igal @ Lucee.org <igal@lucee.org> wrote:

Hello,

I have read quite a few articles about multiple schemas vs. multiple databases, but they are all very generic so I wanted to ask here for a specific use case:

I am migrating a Web Application from MS SQL Server to PostgreSQL.  For the sake of easier maintenance, on SQL Server I have two separate databases:

  1) Primary database containing the data for the application

  2) Secondary database containing "transient" data, e.g. logging of different activities on the website in order to generate statistics etc.

Both databases belong to the same application with the same roles and permissions.

The secondary database grows much faster, but the data in it is not mission-critical , and so the data is aggregated daily and the summaries are posted to the primary database, because only the aggregates are important here.

To keep the database sizes from growing too large, I periodically delete old data from the secondary database since the data becomes obsolete after a certain period of time.

At first I thought of doing the same in Postgres, but now it seems like the better way to go would be to keep one database with two schemas: primary and transient.

The main things that I need to do is:

  a) Be able to backup/restore each "part" separately.  Looks like pg_dump allows that for schemas via the --schema=schema argument.

  b) Be able to query aggregates from the secondary "part" and store the results in the primary one, which also seems easier with multiple schemas than multiple databases.

Am I right to think that two schemas are better in this use case or am I missing something important?

Thanks,

Igal Sapir
Lucee Core Developer
Lucee.org


>b) Be able to query aggregates from the secondary "part" and store the results in the primary one, which also seems easier with multiple >schemas than multiple databases.

If that is what you need to do, then definitely use multiple schemas. In PostgreSQL, the only way to do cross db queries / DML, is with the dblink extension, and from personal use, it is a PIA to use.

--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [GENERAL] REASSIGN OWNED simply doesn't work
Next
From: John R Pierce
Date:
Subject: Re: [GENERAL] Multiple Schemas vs. Multiple Databases