Postgres Pro Shardman implements a “process per user” client/server model. In this model, every client process connects to exactly one backend process. As we do not know ahead of time how many connections will be made, we have to use a “supervisor process” that spawns a new backend process every time a connection is requested. This supervisor process is called postmaster and listens at a specified TCP/IP port for incoming connections. Whenever it detects a request for a connection, it spawns a new backend process. Those backend processes communicate with each other and with other processes of the instance using semaphores and shared memory to ensure data integrity throughout concurrent data access.

Unlike single-instance systems, the distributed clusters require consistency and visibility across DBMS instances, with each of them working with its own sessions and connections. The key task here is to guarantee the distributed transactions on behalf of different users (single connection point for client connections). It is achieved with the postgres_fdw mode that allows authorization on foreign servers to be handled by a single service user that has SSL certificate configured for on all node clusters. At the moment of an SQL query on behalf of the specific user, a connection is established under the service user profile. Once the session is going, the user ID is changed to the ID of the user that initiated the query on the coordinator, all privileges applied.

At the same time, trusted users can authorize with the SSL certificate (based on libpq) that is generated per server or cluster.

See the following chart for details:

Postgres Pro Shardman also has an alternative transport feature, Silk.

Note that the client process can be any program that understands the Postgres Pro Shardman protocol described in Chapter 56. Many clients are based on the C-language library libpq, but several independent implementations of the protocol exist, such as the Java JDBC driver.

Once a connection is established, the client process can send a query to the backend process it's connected to. The query is transmitted using plain text, i.e., there is no parsing done in the client. The backend process parses the query, creates an execution plan, executes the plan, and returns the retrieved rows to the client by transmitting them over the established connection.