Re: Multiple Schemas vs. Multiple Databases

On Fri, Oct 13, 2017 at 3:29 PM, Igal @ Lucee.org <igal(at)lucee(dot)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 <http://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.