On Fri, Oct 13, 2017 at 3:29 PM, Igal @ Lucee.org <i...@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 <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.
