Multiple Schemas vs. Multiple Databases

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/>