Yet another use case related to scalability is when the number of database connections is huge. We have already mentioned connection pooling in the Chapter 15, Using PosgreSQL in Python Applications, and discussed the necessity and benefits of it. However, when a single database is used in an environment with a lot of micro services and each of them has its own connection pool, even if they do not perform too many queries it is possible that hundreds or even thousands connections are opened in the database. Each connection consumes server resources and only the requirement to handle huge number of connections can already be a problem without even performing any queries.

If the applications would not use connection pooling and would open connections only when they need to query the database and close them immediately, another problem could happen. Establishing a database connection takes time. Not too much, but when the number of operations is huge, the total overhead will be significant.

There is a tool named PgBouncer (https://PgBouncer.github.io/) that implements connection pool functionality. It can accept connections from many applications as if it was a PostgreSQL server and then open a limited number of connections towards the database. It would reuse the same database connections for multiple applications' connections. The process of establishing a connection from an application to PgBouncer is much faster then connecting to a real database because PgBouncer does not need to initialize a database backend process for the session.

The way PgBouncer works can be represented in a diagram, as follows:

PgBouncer establishes several connections to the database. When an application connects to PgBouncer and starts a transaction, PgBouncer would assign an existing database connection to that application, forward all SQL commands to the database, and deliver the results back. When the transaction is finished, PgBouncer will dissociate the connections, but not close it. If another application starts a transaction, the same database connection could be used.

Pgbouncer can also assign a database connection to an application for every single SQL statement. That can be useful when applications perform only read queries and the number of them is big. Another option is to assign a database connection to the whole lifetime of the application's connection. This can be used to reduce the connection overhead, but not to reduce the number of connections.