PostgreSQL: Should we create Multiple Databases OR create Multiple Schemas into one Database?

This title looks like a very basic topic, but this is one of the most Important topics for PostgreSQL database professionals.

I have observed that most of the new PostgreSQL users are creating multiple databases into PostgreSQL Server.

This is not wrong, we all have common practice to create multiple databases for different purpose of the project.
But in the PostgreSQL server, we should create multiple schemas instead of creating multiple databases.

I recommended 1-database many-schema approach for the PostgreSQL Server because cross database queries is very costly in PostgreSQL.

In the Microsoft SQL Server, we are creating different databases and we can also execute query between multiple databases by specifying a database name.
But this is not possible with the PostgreSQL.

For more details, You can visit this article of cross database queries in PostgreSQL.

The Schemas in PostgreSQL are a lot like packages in Oracle. A number of schemas should be more lightweight than a number of databases.

We require to take a backup of only single database, including all schemas. We can also add or remove particular schema during the backup or restore operation.

We can also create different database permission group & role for each and every different schemas so that we can restrict the database user.

Specially for the PostgreSQL Server, we should go with multiple schemas into one database because this is more flexible and usable approach.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of