Builtin connection polling

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 whichdevalues 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