Practical use of schemas in Postgresql - when are they needed?

Question

PostgreSQL

I read https://www.postgrespro.ru/doc/ddl-schemas.html
but there are some misunderstandings:

So that one database can be used by several users, independently of each other.

And what will happen if several users connect to one public scheme?

To organize database objects into logical groups in order to make them more transparent to manage.
Different applications can be placed in separate schemas so that there are no conflicts in the use of names or other objects.

that is, when working in postgre it is preferable instead of separate databases to make different schemas in one and use one database with different schemas for small projects?? Or is it all focused on large infrastructures with shard replicas and other things, but for stern hamsters you don’t have to bother with this?
or does it make sense to separate into separate schemas tables with logs to which partitioning is applied, voluminous tables with statistics analysts

Answer 1

In general, think of schemas as namespaces. Schemas help to logically organize the database structure. Various applications or application components within themselves have a higher connectivity than between themselves, the same is true with data in the database - often the tables themselves are organized into logical groups, and there are quite a few links within the group (foreign keys, some common stored procedures etc.), and between these groups - little or not at all.
It is important to understand that different databases are not well suited for logical grouping, because database partitioning is more for administrators than for applications. Plus, in most DBMS, where there is a concept of a schema, it is possible to put foreign keys on tables in another schema, but not on tables in another database. In other words, it is convenient to create separate databases when you share data from completely unrelated applications or services. For example, warehouse accounting and a user support forum. On the other hand, if you want to logically separate tables according to the components of one application (for example, a corporate portal: 4 tables for authorization support, 10 tables for forum support, 5 more for chat with the support service or sales department) - then the schemes will be convenient mechanism for this.
In short, database partitioning is for database administrators, and schema partitioning is for data administrators and application developers to make it easier for them.
In addition to being a namespace, a schema is also a security space in most DBMSs. Even within a single multi-component application, it makes sense to set security boundaries to limit possible losses and destruction in the event of a compromise of one of the components.
Let's say you have a separate schema for the authorization and authentication table and a separate schema for the corporate forum. Your authorization service is implemented separately from the forum (for example, authorization issues tokens to the user, with which he can then enter the forum). From a security point of view, it would be logical to give the authorization service and the forum different users in the database - then, when the forum is hacked, it will be impossible to access the passwords in the database or change the rights on the portal by correcting the data in the role table. Of course, many DBMSs allow you to set rights on individual tables, but in this case the schema plays the role of a container and allows you to set uniform rules for all tables inside it.
As you already understood - for independent applications that can and should exist separately from each other - it is better to make different bases. Then administrators will be able to easily transfer databases from server to server, backup them independently, etc. You need to use diagrams for the components of a single application.
Here's another good example for you. You have an application for bookkeeping and warehouse accounting in the company. At the same time, it so happened that you need to store data from several different companies on one server (for example, you provide a turnkey service to several clients). In this situation, it is more than logical to store the data of different clients in different databases, and the accounting and warehouse accounting data in different schemes within the same database of a particular client.