Built-in connection pooler

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
Date: 2019-01-24 17:14:41
Message-ID: ac873432-31cf-d5e4-0b80-b5ac95cfe385@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Hi hacker,

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
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
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
(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
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.

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

Attachment Content-Type Size
builtin_connection_proxy-1.patch text/x-patch 97.3 KB


Browse pgsql-hackers by date

  From Date Subject
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