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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
