Re: [GENERAL] Multiple Schemas vs. Multiple Databases - Mailing list pgsql-general

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