This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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 which is not wrong
We all have common practice for creating multiple databases for the different purpose of the project.
But in the PostgreSQL server, we should create multiple schemas instead of multiple databases.
I recommended 1-database many-schemas 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 the 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 groups & roles for each and every different schema so that we can control the access of database user.
