|From:||Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>|
|To:||PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>|
|Cc:||Dimitri Fontaine <dim(at)tapoueh(dot)org>|
|Subject:||Built-in connection pooler|
|Views:||Raw Message | Whole Thread | Download mbox | Resend email|
I am working for some time under built-in connection pooler for Postgres:
Unlike existed external pooler, this solution supports session semantic
for pooled connections: you can use temporary tables, prepared
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
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
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
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
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):
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
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
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:
Also I attach patch to the master to this mail.
Will be please to receive your comments.
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
|Next Message||Alvaro Herrera||2019-01-24 17:18:11||Re: problems with foreign keys on partitioned tables|
|Previous Message||Alvaro Herrera||2019-01-24 16:45:49||Re: PSA: we lack TAP test coverage on NetBSD and OpenBSD|