Example where schema are better than multiple databases

Question

In several database systems (e.g., PostgreSQL and MS SQL Server) there is a concept of schema. There are many questions out there asking what schema are and how they are used; here is a good one. Usually the answer boils down to "namespace" and "permissions". However, what remains unclear to me is why a schema has any advantage over just adding a new database. A different database allows one to have both separate namespaces and permission assignments.

Can someone provide an example in PostgreSQL where multiple schema in a single database provide an advantage over multiple databases without schema? From a purely organizational perspective they appear equivalent.

Answer 1

Postgres documentation indeed provides the answer, although it is spread across its many sections. It begins with this general statement:

It is not possible to access more than one database per connection. [...] Databases are physically separated and access control is managed at the connection level. If one PostgreSQL server instance is to house projects or users that should be separate and for the most part unaware of each other, it is therefore recommended to put them into separate databases. If the projects or users are interrelated and should be able to use each other's resources, they should be put in the same database but possibly into separate schemas.

There are some things that you can control only at the database level, but not at the schema level. Among them:

• Object privileges. The database owner role privileges override anything granted at the object (schema, table, etc.) level, unless explicitly revoked.
• Database connection privileges. You can control at the database level what users can connect to it from what IP addresses.
• Configuration parameters. This allows you to tune each database for its specific workload.
• Maintenance. While you can backup and restore individual schemas using pg_dump and pg_restore, operations like point-in-time recovery, log shipping, and streaming replication work at the database (actually, cluster) level.

The minor downside of having multiple databases on the same server is the storage and memory overhead of maintaining and caching system catalogs for each database.

If you often query tables in different namespaces you might also prefer multiple schemas over databases. It is possible to query tables across different databases, but this requires setting up foreign data wrappers, which brings about additional maintenance and possible performance implications.

In short, if your applications require strict security isolation (e.g. the administrator in one namespace must not have access to other namespaces), or they have very different workload patterns, and you don't need to query tables in different databases, you should choose to implement multiple databases.

If, on the other hand, the workload pattern is the same for all namespaces, and you don't require strict separation of duties, and you often query tables across namespaces, you should probably use schemas in a single database for logical isolation of data. This is often the preferred scenario for multi-tenant applications.