Builtin connection polling - Mailing list pgsql-hackers

Hi hackers,

My recent experiments with pthread version of Postgres show that although pthread offers some performance advantages comparing with processes for large number of connections, them still can not  eliminate need in connection pooling. Large number even of inactive connections cause significant degrade of Postgres performance.

So we need connection pooling.  Most of enterprise systems working with Postgres are using pgbouncer or similar tools.
But pgbouncer has the following drawbacks:
1. It is an extra entity which complicates system installation and administration.
2. Pgbouncer itself can be a bottleneck and point of failure. For example with enabled SSL, single threaded model of pgbouncer becomes limiting factor when a lot of clients try to simultaneously reestablish connection. This is why some companies are building hierarchy of pgbouncers.
3. Using pool_mode other than "session" makes it not possible to use prepared statements and session variables.
Lack of prepared statements can itself decrease speed of simple queries up to two times.

So I thought about built-in connection pooling for Postgres. Ideally it should be integrated with pthreads, because in this case scheduling of sessions can be done more flexible and easily.
But I decided to start with patch to vanilla Postgres.

Idea is the following:
1. We start some number of normal backends (which forms backend pool for serving client sessions).
2. When number of connections exceeds number of backends, then instead of spawning new backend we choose some of existed backend and redirect connection to it.
There is more or less portable way in Unix to pass socket descriptors between processes using Unix sockets:
for example https://stackoverflow.com/questions/28003921/sending-file-descriptor-by-linux-socket/
(this is one of the places where pthreads Postgres will win). So a session is bounded to a backend. Backends and chosen using round-robin policy which should guarantee more or less unform distribution of sessions between backends if number of sessions is much larger than number of backends. But certainly skews in client application access patterns can violate this assumption.
3. Rescheduling is done at transaction level. So it is enough to have one entry in procarray for backend to correctly handle locks. Also transaction level pooling eliminates
problem with false deadlocks (caused by lack of free executors in the pool). Also transaction level pooling minimize changes in Postgres core needed to maintain correct session context:
no need to suspend/resume transaction state, static variables, ....
4. In the main Postgres query loop in PostgresMain  we determine a moment when backend is not in transaction state and perform select of sockets of all active sessions and choose one of them.
5. When client is disconnected, then we close session but do not terminate backend.
6. To support prepared statements, we append session identifier to the name of the statement. So prepared statements of different sessions will not interleave. As far as session is bounded to the backend, it is possible to use prepared statements.

This is minimal plan for embedded session pooling I decided to implement as prototype.

Several things are not addressed now:

1. Temporary tables. In principle them can be handled in the same way as prepared statements: by concatenating session identifier to the name of the table.
But it require adjusting references to this table in all queries. It is much more complicated than in case of prepared statements.
2. Session level GUCs. In principle it is not difficult to remember GUCs modified by session and save/restore them on session switch.
But it is just not implemented now.
3. Support of multiple users/databases/... It is the most critical drawback. Right now my prototype implementation assumes that all clients are connected to the same database
under the same user with some connection options. And it is a challenge about which I want to know option of community. The name of the database and user are retrieved from client connection by ProcessStartupPacket function. In vanilla Posgres this function is executed by spawned backend. So I do not know which database a client is going to access before calling this function and reading data from the client's socket. Now I just choose random backend and assign connection to this backend. But it can happen that this backend is working with different database/user. Now I just return error in this case. Certainly it is possible to call ProcessStartupPacket at postmaster and then select proper backend working with specified database/user.
But I afraid that postmaster can become bottleneck i this case, especially in case of using SSL. Also larger number of databases/users can significantly suffer efficiency of pooling if each backend will be responsible only for database/user combination. May be backend should be bounded only to the database and concrete role should be set on session switch. But it can require flushing backend caches which devalues idea of embedded session pooling. This problem can be easily solved with multithreaded Postgres where it is possible to easily reassign session to another thread.

Now results shown by my prototype. I used pgbench with scale factor 100 in readonly  mode (-S option).
Precise pgbench command is "pgbench -S -c N -M prepared -T 100 -P 1 -n". Results in the table below are in kTPS:

Connections	Vanilla Postgres	Postgres with session pool size=10
10	186	181
100	118	224
1000	59	191

As you see instead of degrade of performance with increasing number of connections, Postgres with session pool shows stable performance result.
Moreover, for vanilla Postgres best results at my system are obtained for 10 connections, but Postgres with session pool shows better performance for 100 connections with the same number of spawned backends.

My patch to the Postgres is attached to this mail.
To switch on session polling set session_pool_size to some non-zero value. Another GUC variable which I have added is "max_sessions" which specifies maximal number of sessions handled by backend. So total number of handled client connections is session_pool_size*max_sessions.

Certainly it is just prototype far from practical use.
In addition to the challenges mentioned above, there are also some other issues which should be considered:

1. Long living transaction in client application blocks all other sessions in the backend and so can suspend work of the Postgres.
So Uber-style programming when database transaction is started with opening door of a car and finished at the end of the trip is completely not compatible with this approach.
2. Fatal errors cause disconnect not only of one client caused the problem but bunch of client sessions scheduled to this backend.
3. It is possible to use PL-APIs, such as plpython, but session level variables may not be used.
4. There may be some memory leaks caused by allocation of memory using malloc or in top memory context which is expected to be freed on backend exit.
But it is not deallocated at session close, so large number of handled sessions can cause memory overflow.
5. Some applications, handling mutliple connections inside single thread and multiplexing them at statement level (rather than on transaction level) may not work correctly.
It seems to be quite exotic use case. But pgbench actually behaves in this way! This is why attempt to start pgbench with multistatement transactions (-N) will fail if number of threads (-j) is smaller than number of connections (-c).
6. The approach with passing socket descriptors between processes was implemented only for Unix and tested only at Linux, although is expected to work also as MacOS and other Unix dialects. Windows is not supported now.

I will be glad to receive an feedback and suggestion concerning perspectives of embedded connection pooling.

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company