Re: Multiple Schemas vs. Multiple Databases

On 10/13/2017 12:29 PM, Igal @ Lucee.org wrote:
>
> 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?
>

generally, yeah, unless you eventually decide to split off the two
databases onto separate servers for performance reasons.   Of course, to
access the 'other' database, you'd need to use postgres_fdw or dblink.

--
john r pierce, recycling bits in santa cruz