Built-in connection pooler - Mailing list pgsql-hackers

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