Re: [GENERAL] 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



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general