Built-in connection pooler

Hi hacker,

I am working for some time under built-in connection pooler for Postgres:
https://www.postgresql.org/message-id/flat/a866346d-5582-e8e8-2492-fd32732b0783%40postgrespro.ru#b1c354d5cf937893a75954e1e77f81f5

Unlike existed external pooler, this solution supports session semantic
for pooled connections: you can use temporary tables, prepared
statements, GUCs,...
But to make it possible I need to store/restore session context.
It is not so difficult, but it requires significant number of changes in
Postgres code.
It will be committed in PgProEE-12 version of Postgres Professional
version of Postgres,
but I realize that there are few changes to commit it to mainstream
version of Postgres.

Dimitri Fontaine proposed to develop much simpler version of pooler
which can be community:

> The main idea I want to pursue is the following:
>
> - only solve the “idle connection” problem, nothing else, making idle connection basically free
> - implement a layer in between a connection and a session, managing a “client backend” pool
> - use the ability to give a socket to another process, as you did, so that the pool is not a proxy
> - allow re-using of a backend for a new session only when it is safe to do so

Unfortunately, we have not found a way to support SSL connections with
socket redirection.
So I have implemented solution with traditional proxy approach.
If client changes session context (creates temporary tables, set GUC
values, prepare statements,...) then its backend becomes "tainted"
and is not more participate in pooling. It is now dedicated to this
backend. But it still receives data through proxy.
Once this client is disconnected, tainted backend is terminated.

Built-in proxy accepts connection on special port (by default 6543).
If you connect to standard port, then normal Postgres backends will be
launched and there is no difference with vanilla Postgres .
And if you connect to proxy port, then connection is redirected to one
of proxy workers which then perform scheduling of all sessions, assigned
to it.
There is currently on migration of sessions between proxies. There are
three policies of assigning session to proxy:
random, round-robin and load-balancing.

The main differences with pgbouncer&K are that:

1. It is embedded and requires no extra steps for installation and
configurations.
2. It is not single threaded (no bottleneck)
3. It supports all clients (if client needs session semantic, then it
will be implicitly given dedicated backend)

Some performance results (pgbench -S -n):

#Connections
Proxy
Proxy/SSL
Direct
Direct/SSL
1
13752
12396
17443
15762
10
53415
59615
68334
85885
1000
60152
20445
60003
24047

Proxy configuration is the following:

session_pool_size = 4
connection_proxies = 2

postgres=# select * from pg_pooler_state();
 pid  | n_clients | n_ssl_clients | n_pools | n_backends |
n_dedicated_backends | tx_bytes | rx_bytes | n_transactions
------+-----------+---------------+---------+------------+----------------------+----------+----------+----------------

 1310 |         1 |             0 |       1 |          4
|                    0 | 10324739 |  9834981 |         156388
 1311 |         0 |             0 |       1 |          4
|                    0 | 10430566 |  9936634 |         158007
(2 rows)

This implementation contains much less changes to Postgres core (it is
more like invoking pgbouncer as Postgres worker).
The main things I have added are:
1. Mechanism for sending socket to a process (needed to redirect
connection to proxy)
2. Support of edge pooling mode for epoll (needed to multiplex reads and
writes)
3. Library libpqconn for establishing libpq connection from core

Proxy version of built-in connection pool is in conn_proxy branch in the
following GIT repository:
https://github.com/postgrespro/postgresql.builtin_pool.git

Also I attach patch to the master to this mail.
Will be please to receive your comments.

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